SQL Server Performance Optimization

Introduction

The Query Performance Tuning Process

Common Performance Issues

Insufficient or poor indexes

 Inaccurate or missing statistics

 Bad T-SQL

 Problematic execution plans

 Excessive blocking

 Deadlocks

 Non-set-based operations

 Incorrect database design

 Poor execution plan reuse

 Frequent recompilation of queries

 

Indexes

Clustered Indexes

Nonclustered Indexes

Columnstore Index

Index Behaviors

Covering indexes

Index intersection

Index joins

Filtered indexes

 

Execution plan

Estimated vs. Actual Execution Plans

Capturing Execution Plans

SQL Server Management Studio

Dynamic Management Views

Query Store

What Do You Look for in an Execution Plan?

First operator

Warnings

Most costly operations

Fat pipes

Scans

Lookups

Estimate vs. Actual counts

 

Query Store

Query Store Function and Design

Controlling Query Store

Query Store Reporting

Query Store Function and Design

Controlling Query Store

 

Statistics

Statistics on Indexed Columns

Statistics on Nonindexed Columns

Statistics on a Multicolumn Index

Statistics on a Filtered Index

Statistics Maintenance

 

Execution Plan Cache Behavior

Querying the Plan Cache

Ad Hoc Workload

Prepared Workload

Stored Procedure

Performance Benefits of Stored Procedures

sp_executesql

 

Parameter-Sensitive Queries: Causes and Solutions

How Does Parameter Sniffing Work?

Identifying Queries That Are Sensitive to Parameter Values

Parameter Sensitive Plan Optimization

 

Dealing with Index Fragmentation

Page Anatomy

How Fragmentation Occurs in Rowstore Indexes

How Fragmentation Occurs in the Columnstore Indexes

Fragmentation Overhead

Fragmentation Solutions

 

Query Design Analysis

Query Design Recommendations

Limit the Columns in Your SELECT List

Filter Your Data Through a WHERE Clause

Use Indexes Effectively

Use Effective Search Conditions

BETWEEN vs. IN/OR

LIKE Condition

Custom Scalar UDF

Inline UDF

User-Defined Constraints

 

Reduce Query Resource Use

Avoiding Resource-Intensive Queries

Use Appropriate Data Types

Favor UNION ALL Over UNION

Ensure Indexes Are Used for Aggregate and Sort Operations

Use SET NOCOUNT

 

Blocking and Blocked Processes

Blocking Fundamentals

Lock Types

Lock Modes

Isolation Levels

Effect of Indexes on Locking

Capturing Blocking Information

Recommendations to Reduce Blocking

 

Causes and Solutions For Deadlocks

Deadlock Fundamentals

Choosing the Deadlock Victim

Analyzing the Causes of Deadlocks

Capturing Deadlock Information

Mechanisms to Prevent Deadlocks

 

Memory-Optimized OLTP Tables and Procedures

In-Memory OLTP Fundamentals

Creating memory-optimized  Tables

In-Memory Indexes

Memory Optimization Advisor

Natively Compiled Stored Procedures

Native Compilation Advisor

 

Intelligent Query Processing

Introduction to IQP

Adaptive Query Processing

Batch mode processing and approximate query processing

Table variable deferred compilation and scaler UDF inlining

Memory grant feedback

Automatic plan correction

IQP features for SQL Server 2017

IQP features for SQL Server 2019

IQP features for SQL Server 2022

 

SQL Server Performance Monitoring

SQL Server Profiler and Extended Events

Performance Monitor (PerfMon)

Third-Party Monitoring Tools

Monitoring SQL Server Resources

CPU Monitoring

Memory Monitoring

Disk I/O Monitoring

Network Monitoring