Contents
Chapter 1: SQL Query Performance Tuning
The Performance Tuning Process
SQL Server Performance Killers
Excessive Blocking and Deadlocks
Frequent Recompilation of Queries
Improper Configuration of the Database Log
Excessive Use or Improper Configuration of tempdb
Chapter 2: System Performance Analysis
Paging File %Usage and Page File %Usage
Target Server Memory (KB) and Total Server Memory (KB)
Additional Memory Monitoring Tools
Optimizing Application Workload
Allocating More Memory to SQL Server
Changing from a 32-bit to a 64-bit Processor
Enabling 3GB of Process Address Space
Avg. Disk Sec/Read and Avg. Disk Sec/Write
Additional I/O Monitoring Tools
Optimizing Application Workload
Creating Multiple Files and Filegroups
Placing the Table and Index on Separate Disks
Moving the Log Files to a Separate Physical Disk
Other Tools for Measuring CPU Performance
Sys.dm_os_workers and Sys.dm_os_schedulers
Processor Bottleneck Resolutions
Optimizing Application Workload
Eliminating Excessive Compiles/Recompiles
Using More or Faster Processors
Running More Efficient Controllers/Drivers
Not Running Unnecessary Software
Network Bottleneck Resolutions
Optimizing Application Workload
SQL Server Overall Performance
Considerations for Monitoring Virtual Machines
Creating a Reusable List of Performance Counters
Creating a Counter Log Using the List of Performance Counters
Performance Monitor Considerations
System Behavior Analysis Against Baseline
Chapter 3: SQL Query Performance Analysis
Finishing the Wizard and Starting the Session
Creating a Session Script Using the GUI
Defining a Session Using Stored Procedures
Extended Events Recommendations
Set Max File Size Appropriately
Partition Memory in the Sessions
Other Methods for Query Performance Metrics
Costly Queries with a Single Execution
Costly Queries with Multiple Executions
Identifying Slow-Running Queries
Analyzing a Query Execution Plan
Identifying the Costly Steps in an Execution Plan
Actual vs. Estimated Execution Plans
Examine the WHERE Clause and JOIN Criteria Columns
Relationship with Nonclustered Indexes
Clustered Index Recommendations
Poor Design Practices for a Clustered Index
Nonclustered Index Maintenance
Nonclustered Index Recommendations
Clustered vs. Nonclustered Indexes
Benefits of a Clustered Index over a Nonclustered Index
Benefits of a Nonclustered Index over a Clustered Index
Additional Characteristics of Indexes
Index on BIT Data Type Columns
CREATE INDEX Statement Processed As a Query
Considering the Database Engine Tuning Advisor
Chapter 5: Database Engine Tuning Advisor
Database Engine Tuning Advisor Mechanisms
Database Engine Tuning Advisor Examples
Tuning from the Procedure Cache
Database Engine Tuning Advisor Limitations
Analyzing the Cause of a Lookup
Chapter 7: Statistics Analysis
The Role of Statistics in Query Optimization
Statistics on an Indexed Column
Benefits of Updated Statistics
Drawbacks of Outdated Statistics
Statistics on a Nonindexed Column
Benefits of Statistics on a Nonindexed Column
Drawback of Missing Statistics on a Nonindexed Column
Statistics on a Multicolumn Index
Statistics on a Filtered Index
Auto Update Statistics Asynchronously
Status of Auto Create Statistics
Status of Auto Update Statistics
Analyzing the Effectiveness of Statistics for a Query
Resolving a Missing Statistics Issue
Resolving an Outdated Statistics Issue
Backward Compatibility of Statistics
Automatic Update Statistics Asynchronously
Amount of Sampling to Collect Statistics
Chapter 8: Fragmentation Analysis
Page Split by an UPDATE Statement
Page Split by an INSERT Statement
Analyzing the Amount of Fragmentation
Analyzing the Fragmentation of a Small Table
Dropping and Re-creating the Index
Re-creating the Index with the DROP_EXISTING Clause
Executing the ALTER INDEX REBUILD Statement
Executing the ALTER INDEX REORGANIZE Statement
Significance of the Fill Factor
Chapter 9: Execution Plan Cache Analysis
Components of the Execution Plan
Analyzing the Execution Plan Cache
Plan Reusability of an Ad Hoc Workload
Plan Reusability of a Prepared Workload
Query Plan Hash and Query Hash
Execution Plan Cache Recommendations
Explicitly Parameterize Variable Parts of a Query
Create Stored Procedures to Implement Business Functionality
Code with sp_executesql to Avoid Stored Procedure Maintenance
Implement the Prepare/Execute Model to Avoid Resending a Query String
Prefer sp_executesql over EXECUTE for Dynamic Queries
Parameterize Variable Parts of Queries with Care
Do Not Allow Implicit Resolution of Objects in Queries
Chapter 10: Query Recompilation
Benefits and Drawbacks of Recompilation
Identifying the Statement Causing Recompilation
Analyzing Causes of Recompilation
Don’t Interleave DDL and DML Statements
Avoiding Recompilations Caused by Statistics Change
Using the KEEPFIXED PLAN Option
Disable Auto Update Statistics on the Table
Avoiding Changing SET Options Within a Stored Procedure
Chapter 11: Query Design Analysis
Operating on Small Result Sets
Limit the Number of Columns in select_list
Use Highly Selective WHERE Clauses
Avoid Nonsargable Search Conditions
Avoid Arithmetic Operators on the WHERE Clause Column
Avoid Functions on the WHERE Clause Column
Using Domain and Referential Integrity
Declarative Referential Integrity
Avoiding Resource-Intensive Queries
Use EXISTS over COUNT(*) to Verify Data Existence
Use UNION ALL Instead of UNION
Use Indexes for Aggregate and Sort Conditions
Avoid Local Variables in a Batch Query
Be Careful When Naming Stored Procedures
Reducing the Number of Network Round-Trips
Execute Multiple Queries Together
Intent Shared (IS), Intent Exclusive (IX), and Shared with Intent Exclusive (SIX) Modes
Schema Modification (Sch-M) and Schema Stability (Sch-S) Modes
Effect of a Nonclustered Index
Effect of Indexes on the Serializable Isolation Level
Capturing Blocking Information
Capturing Blocking Information with SQL
Extended Events and the blocked_process_report Event
Recommendations to Reduce Blocking
Automation to Detect and Collect Blocking Information
Using Error Handling to Catch a Deadlock
Collecting Deadlock Information
Accessing Resources in the Same Physical Order
Decreasing the Number of Resources Accessed
Convert a Nonclustered Index to a Clustered Index
Use a Covering Index for a SELECT Statement
Chapter 14: Cursor Cost Analysis
Cost Comparison on Cursor Location
Cost Comparison on Cursor Concurrency
Cost Comparison on Cursor Type
Analyzing Overhead with T-SQL Cursors
Chapter 15: Database Performance Testing
Capturing Data with the Server Side Trace
Distributed Replay for Database Testing
Chapter 16: Database Workload Optimization
Workload Optimization Fundamentals
Identifying the Costliest Query
Determining the Baseline Resource Use of the Costliest Query
Analyzing and Optimizing External Factors
Analyzing the Connection Options Used by the Application
Analyzing the Effectiveness of Statistics
Analyzing the Need for Defragmentation
Analyzing the Internal Behavior of the Costliest Query
Analyzing the Query Execution Plan
Identifying the Costly Steps in the Execution Plan
Analyzing the Processing Strategy
Optimizing the Costliest Query
Analyzing the Application of a Join Hint
Avoiding the Clustered Index Scan Operation
Analyzing the Effect on Database Workload
Iterating Through Optimization Phases
Chapter 17: SQL Server Optimization Checklist
Balancing Under- and Overnormalization
Benefiting from Entity-Integrity Constraints
Benefiting from Domain and Referential Integrity Constraints
Adopting Index-Design Best Practices
Avoiding the Use of the sp_ Prefix for Stored Procedure Names
Minimizing the Use of Triggers
Use the Command SET NOCOUNT ON
Explicitly Define the Owner of an Object
Avoid Nonsargable Search Conditions
Avoid Arithmetic Expressions on the WHERE Clause Column
Ensure No Implicit Data Type Conversions
Adopt Best Practices for Reusing Execution Plans
Adopt Best Practices for Database Transactions
Eliminate or Reduce the Overhead of Database Cursors
Cost Threshold for Parallelism
Keep the Statistics Up-to-Date
Maintain a Minimum Amount of Index Defragmentation
Avoid Database Functions Such As AUTO_CLOSE or AUTO_SHRINK