Microsoft SQL Server 2012 Performance Tuning Cookbook

Table of Contents

• Preface

• Chapter 1: Mastering SQL Trace Using Profiler
• Introduction
• Creating a trace or workload
• Filtering events
• Detecting slow running and expensive queries
• Creating trace with system stored procedures

• Chapter 2: Tuning with Database Engine Tuning Advisor
• Introduction
• Analyzing queries using Database Engine Tuning Advisor
• Running Database Engine Tuning Advisor for workload
• Executing Database Tuning Advisor from command prompt

• Chapter 3: System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command
• Introduction
• Monitoring system health using system statistical functions
• Monitoring with system stored procedure
• Monitoring log space usage statistics with DBCC command

• Chapter 4: Resource Monitor and Performance Monitor
• Introduction
• Monitoring of server performance
• Monitoring CPU usage
• Monitoring memory (RAM) usage

• Chapter 5: Monitoring with Execution Plans
• Introduction
• 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

• Chapter 6: Tuning with Execution Plans
• Introduction
• 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

• Chapter 7: Dynamic Management Views and Dynamic Management Functions
• Introduction
• Monitoring current query execution statistics
• Monitoring index performance
• Monitoring performance of TempDB database
• Monitoring disk I/O statistics

• Chapter 8: SQL Server Cache and Stored Procedure Recompilations
• Introduction
• Monitoring compilations and recompilations at instance level using Reliability and Performance Monitor
• Monitoring recompilations using SQL Server Profiler

• Chapter 9: Implementing Indexes
• Introduction
• 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

• Chapter 10: Maintaining Indexes
• Introduction
• 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

• Chapter 11: Points to Consider While Writing Queries
• Introduction
• 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

• Chapter 12: Statistics in SQL Server
• Introduction
• 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

• Chapter 13: Table and Index Partitioning
• Introduction
• 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)

• Chapter 14: Implementing Physical Database Structure
• Introduction
• 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

• Chapter 15: Advanced Query Tuning Hints and Plan Guides
• Introduction
• 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

• Chapter 16: Dealing with Locking, Blocking, and Deadlocking
• Introduction
• Determining long-running transactions
• Detecting blocked and blocking queries
• Detecting deadlocks with SQL Server Profiler
• Detecting deadlocks with Trace Flag 1204

• Chapter 17: Configuring SQL Server for Optimization
• Introduction
• 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

• Chapter 18: Policy-based Management
• Introduction
• Evaluating database properties
• Restricting database objects

• Chapter 19: Resource Management with Resource Governor
• Introduction
• Configuring Resource Governor with SQL Server Management Studio
• Configuring Resource Governor with T-SQL script
• Monitoring Resource Governor

• Index