SQL Server Performance Tuning OldSchool Practical Guide Denis

SQL Server Performance Tuning Old-School Practical Guide Denis Reznik Data Architect at Intapp Microsoft Data Platform MVP

Sponsors Gold sponsors: Silver sponsors: Bronze sponsors:

About me § § § Denis Reznik Kyiv, Ukraine Data Architect at Intapp Microsoft Data Platform MVP Co-Founder of Ukrainian Data Community Kyiv Co-author of “SQL Server MVP Deep Dives 2”

Agenda § Data Collection § Data Analysis § Query Optimization 4 |

Tools § SQL Profiler § Performance Monitor § SQL Server Management Studio

Indexes and Statistics Quick Overview

Heap 1. . 100 1 K. . 5 K 12 K. . 15 K 22 K. . 41 K 7 K. . 8 K 51 K. . 71 K 100. . 1 k 6 K. . 7 K 10 K. . 11 K 9 K. . 10 K 8 K. . 9 K 1 M. . 2 M 5 K. . 6 K 15 K. . 21 K. . 22 K 41 K. . 51 K 71 K. . 1 M 2 M. . 3 M

Clustered Index 1. . 1 M 1. . 2 K 1. . 300 301. . 800 801. . 1, 5 K 2 K+1. . 4 K 1, 5 K+1. . 2 K … 1 M-2 K. . 1 M …

Index Seek SELECT * FROM Users WHERE Id = 523 1. . 1 M 1. . 2 K 1. . 300 301. . 800 801. . 1, 5 K 2 K+1. . 4 K 1, 5 K+1. . 2 K … 1 M-2 K. . 1 M …

Index Scan SELECT * FROM Users 1. . 1 M 1. . 2 K 1. . 300 301. . 800 801. . 1, 5 K 2 K+1. . 4 K 1, 5 K+1. . 2 K … 1 M-2 K. . 1 M …

Index Range Scan SELECT * FROM Users WHERE Id BETWEEN 700 AND 1700 1. . 1 M 1. . 2 K 1. . 300 301. . 800 801. . 1, 5 K 2 K+1. . 4 K 1, 5 K+1. . 2 K … 1 M-2 K. . 1 M …

Non-Clustered Index (Name) A. . Z SELECT * FROM Users WHERE Name = 'John Dow' Clustered Index (Id) A. . C C. . K 1. . 1 M … X. . Z Heap 1. . 2 K 2 K+1. . 4 K … 1 M-2 K. . 1 M 2 K. . 4 K 1 M-2 K. . 1 M

Allocation Order Scan Index Allocation Map (IAM) Extent 1 Extent 2 Clustered Index (Id) Extent N 1. . 1 M Extent 1 1. . 2 K Extent N 2 K+1. . 4 K … 1 M-2 K. . 1 M

SELECT * FROM Users 2100 AND 5000 2500 WHERE Id BETWEEN 200 Statistics 1200 1000 800 500 10 1 800 2000 2800 4500 5400

Parameter Sniffing and Dynamic SQL Quick Overview

Parameter Sniffing - Stored Procedure Query Processor EXEC Annual. Sales. Report @User. Id = 1 EXEC Annual. Sales. Report @User. Id = 22 SQL Server Cache Procedure cache Query Plan created executes andusing cached thefor query the plan created for @User. Id = 1

Parameter Sniffing - Parametrized Query Processor sp_executesql N'SELECT * FROM Users WHERE Id = @Id', N'@Id int', 1 sp_executesql N'SELECT * FROM Users WHERE Id = @Id', N'@Id int', 22 SQL Server Cache Procedure cache Plan andusing cached the plan created for Querycreated executes thefor query @Id = 1

Dynamic SQL – Multiple Plans Query Processor SELECT * FROM Users WHERE Id = 1 SELECT * FROM Users WHERE Id = 22 SQL Server Cache SELECT * FROM Users WHERE Id = 1 Procedure cache Query New query executed plan created again using created the and query cached. and plan, cached. Query created for executed Query the executed firstusing query. newly using created newly created plan.

Summary § Data Collection § Data Analysis § Query Optimization 19 |

Sponsors Gold sponsors: Silver sponsors: Bronze sponsors:

Thank You! Denis Reznik @denisreznik@live. ru