Sql Server Tuning for Dummies: August 2013

Saturday, August 24, 2013

Exam 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012

Skills measured

This exam measures your ability to accomplish the technical tasks listed below. The percentages indicate the relative weight of each major topic area on the exam. The higher the percentage, the more questions you are likely to see on that content area on the exam.
Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.

Show all

Design and implement a data warehouse (11%)

Design and implement dimensions
- Design shared/conformed dimensions; determine if you need support for slowly changing dimensions; determine attributes; design hierarchies; determine whether you need star or snowflake schema; determine the granularity of relationship with fact tables; determine the need for auditing or lineage; determine keys (business transactional or your own data warehouse/surrogate keys); implement dimensions; implement data lineage of a dimension table
Design and implement fact tables
- Design a data warehouse that supports many to many relationships; appropriately index a fact table; using columnstore indexes; partitioning; additive measures; semi additive measures; non additive measures; implement fact tables; determining the loading method for the fact tables; implement data lineage of a fact table; design summary aggregation tables

Troubleshoot data integration issues
- Performance issues; connectivity issues; execution of a task or tranformation failed; logic issues; demonstrate awareness of the new SSIS logging infrastructure; troubleshoot a failed package execution to determine the root cause of failure; troubleshoot SSIS package failure from an invalid datatype; implement break points; data viewers; profile data with different tools; batch cleanup
Install and maintain SSIS components
- Software installation (IS, management tools); development box and server; install specifics for remote package execution; planning for installation (32- versus 64-bit); upgrade; provisioning the accounts; creating the catalog
Implement auditing, logging, and event handling
- Audit package execution by using system variables; propagate events; use log providers; log an SSIS execution; create alerting and notification mechanisms; use Event Handlers in SSIS to track ETL events and errors; implement custom logging
Deploy SSIS solutions
- Create and configure an SSIS catalog; deploy SSIS packages by using the deployment utility; deploy SSIS packages to SQL or file system locations; validate deployed packages; deploy packages on multiple servers; how to install custom components and tasks; deploy SSIS packages by using DTUTIL
Configure SSIS security settings
- SSIS catalog database roles; package protection levels; secure Integration Services packages that are deployed at the file system; secure Integration Services parameters, configuration

Preparation resources

Build data quality solutions (15%)

Install and maintain data quality services
- Installation prerequisites; .msi package; adding users to the DQ roles; identity analysis, including data governance
Implement master data management solutions
- Install Master Data Services (MDS); implement MDS; create models, entities, hierarchies, collections, attributes; define security roles; import/export; subscriptions
Create a data quality project to clean data
- Profile Online Transaction Processing (OLTP) and other source systems; data quality knowledge base management; create data quality project; use data quality client; improve data quality; identity mapping and deduplicating; handle history and data quality; manage data quality/cleansing

Preparation resources

Exam 70-462 Administering Microsoft SQL Server 2012 Databases

Skills measured

Hide all

Install and configure (19%)

Plan installation
- Evaluate installation requirements; design the installation of SQL Server and its components (drives, service accounts, etc.); plan scale-up vs. scale-out basics; plan for capacity, including if/when to shrink, grow, autogrow, and monitor growth; manage the technologies that influence SQL architecture (for example, service broker, full text, scale out, etc.); design the storage for new databases (drives, filegroups, partitioning); design database infrastructure; configure a SQL Server standby database for reporting purposes; Windows-level security and service level security; Core mode installation; benchmark a server before using it in a production environment (SQLIO, Tests on SQL Instance); choose the right hardware
Install SQL Server and related services
- Test connectivity; enable and disable features; install SQL Server database engine and SSIS (not SSRS and SSAS); configure an OS disk
Implement a migration strategy
- Restore vs detach/attach; migrate security; migrate from a previous version; migrate to new hardware; migrate systems and data from other sources
Configure additional SQL Server components
- Set up and configure all SQL Server components (Engine, AS, RS and SharePoint integration) in a complex and highly secure environment; configure full-text indexing; SSIS security; filestream; filetable
Manage SQL Server Agent
- Create, maintain, and monitor jobs; administer jobs and alerts; automate (setup, maintenance, monitoring) across multiple databases and multiple instances; send to "Manage SQL Server Agent jobs"

Preparation resources

Maintain instances and databases (17%)

Manage and configure databases
- Design multiple file groups; database configuration and standardization: autoclose, autoshrink, recovery models; manage file space, including adding new filegroups and moving objects from one filegroup to another; implement and configure contained databases; data compression; configure TDE; partitioning; manage log file growth; DBCC
Configure SQL Server instances
- Configure and standardize a database: autoclose, autoshrink, recovery models; install default and named instances; configure SQL to use only certain CPUs (affinity masks, etc.); configure server level settings; configure many databases/instance, many instances/server, virtualization; configure clustered instances including MSDTC; memory allocation; database mail; configure SQL Server engine: memory, filffactor, sp_configure, default options
Implement a SQL Server clustered instance
- Install a cluster; manage multiple instances on a cluster; set up subnet clustering; recover from a failed cluster node
Manage SQL Server instances
- Install an instance; manage interaction of instances; SQL patch management; install additional instances; manage resource utilization by using Resource Governor; cycle error logs

Preparation resources

Optimize and troubleshoot (14%)

Identify and resolve concurrency problems
- Examine deadlocking issues using the SQL server logs using trace flags; design reporting database infrastructure (replicated databases); monitor via DMV or other MS product; diagnose blocking, live locking and deadlocking; diagnose waits; performance detection with built in DMVs; know what affects performance; locate and if necessary kill processes that are blocking or claiming all resources
Collect and analyze troubleshooting data
- Monitor using Profiler; collect performance data by using System Monitor; collect trace data by using SQL Server Profiler; identify transactional replication problems; identify and troubleshoot data access problems; gather performance metrics; identify potential problems before they cause service interruptions; identify performance problems;, use XEvents and DMVs; create alerts on critical server condition; monitor data and server access by creating audit and other controls; identify IO vs. memory vs. CPU bottlenecks; use the Data Collector tool
Audit SQL Server instances
- Implement a security strategy for auditing and controlling the instance; configure an audit; configure server audits; track who modified an object; monitor elevated privileges as well as unsolicited attempts to connect; policy-based management

Preparation resources

Manage data (19%)

Configure and maintain a back-up strategy
- Manage different backup models, including point-in-time recovery; protect customer data even if backup media is lost; perform backup/restore based on proper strategies including backup redundancy; recover from a corrupted drive; manage a multi-TB database; implement and test a database implementation and a backup strategy (multiple files for user database and tempdb, spreading database files, backup/restore); back up a SQL Server environment; back up system databases
Restore databases
- Restore a database secured with TDE; recover data from a damaged DB (several errors in DBCC checkdb); restore to a point in time; file group restore; page level restore
Implement and maintain indexes
- Inspect physical characteristics of indexes and perform index maintenance; identify fragmented indexes; identify unused indexes; implement indexes; defrag/rebuild indexes; set up a maintenance strategy for indexes and statistics; optimize indexes (full, filter index); statistics (full, filter) force or fix queue; when to rebuild vs. reorg and index; full text indexes; column store indexes
Import and export data
- Transfer data; bulk copy; bulk insert

Preparation resources

Implement security (18%)

Manage logins and server roles
- Configure server security; secure the SQL Server using Windows Account / SQL Server accounts, server roles; create log in accounts; manage access to the server, SQL Server instance, and databases; create and maintain user-defined server roles; manage certificate logins
Manage database permissions
- Configure database security; database level, permissions; protect objects from being modified
Manage users and database roles
- Create access to server / database with least privilege; manage security roles for users and administrators; create database user accounts; contained logins
Troubleshoot security
- Manage certificates and keys; endpoints

Preparation resources

Implement high availability (12%)

Implement AlwaysOn
- Implement a mirroring solution using AlwaysOn; failover
Implement database mirroring
- Set up mirroring; monitor the performance of database mirroring
- Troubleshoot replication problems; identify appropriate replication strategy

Preparation resources

Thursday, August 22, 2013

Exam 70-461 Querying Microsoft SQL Server 2012

Skills measured

Hide all

Create database objects (24%)

Create and alter tables using T-SQL syntax (simple statements)
- Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE
Create and alter views (simple statements)
- Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP
Design views
- Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications
Create and modify constraints (simple statements)
- Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints
Create and alter DML triggers.
- Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

Preparation resources

Work with data (27%)

Query data by using SELECT statements
- Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce
Implement sub-queries
- Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement
Implement data types
- Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns
Implement aggregate queries
- New analytic functions; grouping sets; spatial aggregates; apply ranking functions
Query and manage XML data
- Understand XML datatypes and their schemas and interop w/, limitations and restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML; XML indexing

Preparation resources

Modify data (24%)

Create and alter stored procedures (simple statements)
- Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL
Modify data by using INSERT, UPDATE, and DELETE statements
- Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement
Combine datasets
- Difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements
Work with functions
- Understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)

Preparation resources

Troubleshoot and optimize (25%)

Optimize queries
- Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in
Manage transactions
- Mark a transaction; understand begin tran, commit, and rollback; implicit vs explicit transactions; isolation levels; scope and type of locks; trancount
Evaluate the use of row-based operations vs. set-based operations
- When to use cursors; impact of scalar UDFs; combine multiple DML operations
Implement error handling
- Implement try/catch/throw; use set based rather than row based logic; transaction management

Preparation resources

Wednesday, August 14, 2013

Script to Generate Restore Statements

http://sqldavel.blogspot.com/2013/03/script-to-generate-restore-statements.html

-- Purpose:       Generate 'restore database' statements
-- Instructions: Edit 'where' clause and/or backup path information in variables as
--         needed. Run script to generate sql, then edit/modify as needed and run selected sql statements.
--
set nocount ON

DECLARE @RestoreDB VARCHAR (8000)
Declare @Move varchar (8000)
Declare @MoveWithoutComma varchar (8000)
Declare @Go varchar (8000)
Declare @firsttime varchar (5) -- = 'True'
declare @sd_name varchar(255)
declare @smf_name varchar(255)
declare @smf_physical_name varchar (255)
declare @hold_sd_name varchar (255)

select @firsttime = 'True'

DECLARE Database_cursor CURSOR FOR
SELECT sd.name,smf.name, smf.physical_name
FROM sys.master_files AS smf inner join sys.databases AS sd
on smf.database_id = sd.database_id

-- Edit where clause as desired
where sd.name not in ('master', 'tempdb', 'msdb', 'model')
ORDER BY sd.name
OPEN Database_cursor
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name

Select @hold_sd_name = @sd_name
WHILE @@FETCH_STATUS = 0
Begin
    if @firsttime = 'True'
    Begin
  -- Edit backup path as needed
  select @RestoreDB = 'RESTORE DATABASE [' + @sd_name + ']' + char(13) + char(10) + 'FROM DISK = ''J:\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\'
                                + @sd_name + '.bak''' + char(13) + char(10) + 'WITH MOVE ''' + @smf_name + ''' TO '''
    + @smf_physical_name + ''', replace ,' + 'stats = 10' + char(13) + char(10)
   print @RestoreDB

  select @Go = 'GO' + char(13) + char(10)

   FETCH NEXT FROM Database_cursor
  INTO @sd_name, @smf_name, @smf_physical_name

  if @hold_sd_name <> @sd_name or @@FETCH_STATUS <> 0
    select @hold_sd_name = @sd_name
  else select @firsttime = 'False'
end
else
Begin
  -- Move with comma ','
  select @Move = 'MOVE ''' + @smf_name + ''' TO '''
    + @smf_physical_name + ''','
  -- Last 'Move' without comma ','
  select @MoveWithoutComma = 'MOVE ''' + @smf_name + ''' TO '''
    + @smf_physical_name + ''''
  FETCH NEXT FROM Database_cursor
  INTO @sd_name, @smf_name, @smf_physical_name
  if @hold_sd_name <> @sd_name or @@FETCH_STATUS <> 0
  Begin
   print @MoveWithoutComma
   print @Go
   select @hold_sd_name = @sd_name
   select @firsttime = 'True'
  end
  Else print @Move
end
END
CLOSE Database_cursor
DEALLOCATE Database_cursor
GO

Tuesday, August 13, 2013

SQL Server Agent Job E-Mail Notification Query

http://theruntime.com/blogs/thomasswilliams/archive/2008/12/02/sql-server-agent-job-e-mail-notification-query.aspx

--*** SQL Server Agent Jobs and selected properties, by Thomas Williams ***
--*** this query has been tested on SQL Server 2005 only ***
--For more information on the sysjobs table, see:
-- http://msdn.microsoft.com/en-us/library/ms189817.aspx
--For more information on sysoperators, see:
-- http://msdn.microsoft.com/en-us/library/ms188406.aspx
SELECT --Job name
[Job Name] = sj.[name],
--Job owner
[Owner] = SUSER_SNAME(sj.[owner_sid]),
--Job category e.g. "Database Maintenance", "Report Server", "[Uncategorized (Local)]"
[Category] = c.[name],
--Operator to be e-mailed (may be NULL)
[Email Operator] = o.[name],
--notification in Event log (notify when job fails, succeeds, always, never)
[Event LOG Notification] = CASE sj.[notify_level_eventlog]
WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'When the job completes (regardless of the job outcome)'
ELSE 'UNKNOWN' END,
--e-mail notification (notify when job fails, succeeds, always, never)
[Email Notification] = CASE sj.[notify_level_email]
WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'When the job completes (regardless of the job outcome)'
ELSE 'UNKNOWN' END
FROM msdb.dbo.[sysjobs] sj LEFT OUTER JOIN
--E-Mail Operator
msdb.dbo.[sysoperators] o ON
sj.[notify_email_operator_id] = o.[id] LEFT OUTER JOIN
--Job categories
msdb.dbo.[syscategories] C ON
sj.[category_id] = c.[category_id]
WHERE --ignore auto-created jobs (Reporting Services schedules)
NOT (sj.[name] LIKE '_____________-____-____-____________') AND
--only enabled Jobs
sj.[enabled] = 1
ORDER BY sj.[name]

Sql Server Tuning for Dummies

Saturday, August 24, 2013

Exam 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012

Skills measured

Preparation resources

Preparation resources

Preparation resources

Preparation resources

Preparation resources

Exam 70-462 Administering Microsoft SQL Server 2012 Databases

Skills measured

Preparation resources

Preparation resources

Preparation resources

Preparation resources

Preparation resources

Preparation resources

Thursday, August 22, 2013

Exam 70-461 Querying Microsoft SQL Server 2012

Skills measured

Preparation resources

Preparation resources

Preparation resources

Preparation resources

Wednesday, August 14, 2013

Script to Generate Restore Statements

Tuesday, August 13, 2013

SQL Server Agent Job E-Mail Notification Query

Blog Archive

About Me