PacktLib: Microsoft SQL Server 2012 Performance Tuning Cookbook

Microsoft SQL Server 2012 Performance Tuning Cookbook


About the Authors



About the Reviewers


Mastering SQL Trace Using Profiler


Creating a trace or workload

Filtering events

Detecting slow running and expensive queries

Creating trace with system stored procedures

Tuning with Database Engine Tuning Advisor


Analyzing queries using Database Engine Tuning Advisor

Running Database Engine Tuning Advisor for workload

Executing Database Tuning Advisor from command prompt

System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command


Monitoring system health using system statistical functions

Monitoring with system stored procedure

Monitoring log space usage statistics with DBCC command

Resource Monitor and Performance Monitor


Monitoring of server performance

Monitoring CPU usage

Monitoring memory (RAM) usage

Monitoring with Execution Plans


Working with estimated execution plan

Working with actual execution plan

Monitoring performance of a query by SET SHOWPLAN_XML

Monitoring performance of a query by SET STATISTICS XML

Monitoring performance of a query by SET STATISTICS IO

Monitoring performance of a query by SET STATISTICS TIME

Including and understanding client statistics

Tuning with Execution Plans


Understanding Hash, Merge, and Nested Loop Join strategies

Finding table/index scans in execution plan and fixing them

Introducing Key Lookups, finding them in execution plans, and resolving them

Dynamic Management Views and Dynamic Management Functions


Monitoring current query execution statistics

Monitoring index performance

Monitoring performance of TempDB database

Monitoring disk I/O statistics

SQL Server Cache and Stored Procedure Recompilations


Monitoring compilations and recompilations at instance level using Reliability and Performance Monitor

Monitoring recompilations using SQL Server Profiler

Implementing Indexes


Increasing performance by creating a clustered index

Increasing performance by creating a non-clustered index

Increasing performance by covering index

Increasing performance by including columns in an index

Improving performance by a filtered index

Improving performance by a columnstore index

Maintaining Indexes


Finding fragmentation

Playing with Fill Factor

Enhance index efficiency by using the REBUILD index

Enhance index efficiency by using the REORGANIZE index

How to find missing indexes

How to find unused indexes

Enhancing performance by creating an indexed view

Enhancing performance with index on Computed Columns

Determining disk space consumed by indexes

Points to Consider While Writing Queries


Improving performance by limiting the number of columns and rows

Improving performance by using sargable conditions

Using arithmetic operator wisely in predicate to improve performance

Improving query performance by not using functions on predicate columns

Improving performance by Declarative Referential Integrity (DRI)

"Trust" your foreign key to gain performance

Statistics in SQL Server


Creating and updating statistics

Effects of statistics on non-key column

Find out-of-date statistics and get it correct

Effect of statistics on a filtered index

Table and Index Partitioning


Partitioning a table with RANGE LEFT

Partitioning a table with RANGE RIGHT

Deleting and loading bulk data by splitting, merging, and switching partitions (sliding window)

Implementing Physical Database Structure


Configuring data file and log file on multiple physical disks

Using files and filegroups

Moving the existing large table to separate physical disk

Moving non-clustered indexes on separate physical disk

Configuring the tempdb database on separate physical disk

Advanced Query Tuning Hints and Plan Guides


Using NOLOCK table query hint

Using FORCESEEK and INDEX table hint

Optimizing a query using an object plan guide

Implementing a fixed execution plan using SQL plan guide

Dealing with Locking, Blocking, and Deadlocking


Determining long-running transactions

Detecting blocked and blocking queries

Detecting deadlocks with SQL Server Profiler

Detecting deadlocks with Trace Flag 1204

Configuring SQL Server for Optimization


Configuring SQL Server to use more processing power

Configuring memory in 32 bit versus. 64 bit

Configuring "Optimize for Ad hoc Workloads"

Optimizing SQL Server instance configuration

Policy-based Management


Evaluating database properties

Restricting database objects

Resource Management with Resource Governor


Configuring Resource Governor with SQL Server Management Studio

Configuring Resource Governor with T-SQL script

Monitoring Resource Governor