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

Indexed views

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

 Cursor

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

         Trigger

         Resource Governor

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