SQL Server Performance Tuning: Common Problems, Possible Solutions

Identifying which queries are running the slowest, or using the most resources is relatively well documented. However, once you identify the query you need to fix, what are you supposed to do next? This session will walk through a bunch of the most common performance problems and how you go about identifying those problems. From there, we'll discuss some possible solutions for those problems as a way to move you more quickly to a more highly performing database.

1. Common Problems Possible Solutions

2. Grant Fritchey DevOps Advocate Microsoft Data Platform MVP AWS Community Builder About me @gfritchey [email protected] scarydba.com

3. Learn common performance problems Understand possible solutions Discover methods of validation Agenda

4. Unique constraints Foreign keys Check constraints Missing, or Incorrect Constraints

5. Wide columns Poor selectivity, especially on leading edge Poor or missing clustered index Clustered index updated frequently Columnstore Index Design

6. Cost Threshold for Parallelism Max Degree of Parallelism Parallelism

7. Out of date Missing Statistics

8. Non- sargeable filter Calculation in filter Optimizer hints Nesting views Object ownership Implicit data conversion T-SQL Code

9. Data skew Out of date statistics Parameter Sniffing

10. Execution Plans DMVs Extended Events Query Store Mk I Eyeball Validating Solutions

11. Thanks for attending!

12. Grant Fritchey DevOps Advocate Microsoft Data Platform MVP AWS Community Builder About me @gfritchey [email protected] scarydba.com

13. Photo Credits Crash | Arslan | Flickr crash | CSX and a empty semi flat bed. No injuries, | Ruin Raider | Flickr Crash ! | I was quietly looking at a book and moved slightly… | Flickr Crash | kozzmen | Flickr Crashed | Frankfurt 2011 | List_84 | Flickr Car crash | Driver walked away from this. | huggs2 | Flickr D&W Autos - Mechanic At Work 2 | This was taken at a local g… | Flickr