1 / 30

Query Execution

Explore the expectations and realities of query execution in SQL Server, including parameter sniffing, dynamic SQL, cardinality estimation, and query optimization.

tobin
Download Presentation

Query Execution

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Query Execution Expectation-Reality Denis Reznik Data Architect at Intapp, Inc. Microsoft Data Platform MVP

  2. About Me • Denis Reznik • Kyiv, Ukraine • Data Architect at Intapp, Inc. • Microsoft Data Platform MVP • Co-Founder of Ukrainian Data Community Kyiv (PASS Chapter) • PASS Regional Mentor, Central and Eastern Europe • Co-author of “SQL Server MVP Deep Dives vol. 2”

  3. Just like Jimi Hendrix …  We love to get feedback Please complete the session feedback forms

  4. SQLBits - It's all about the community... Please visit Community Corner, we are trying this year to get more people to learn about the SQL Community, equally if you would be happy to visit the community corner we’d really appreciate it.

  5. Agenda • Expectation - Reality 1 • Expectation - Reality 2 • Expectation - Reality 3 • Expectation - Reality 4

  6. Parameter Sniffing Expectation – Reality 1

  7. Parameter Sniffing - Stored Procedure Query Processor EXEC ReportSecurityPermissions@UserId = 1 EXEC ReportSecurityPermissions@UserId = 22 SQL Server Cache Procedure cache Procedure cache Query executes using the query plan created for @UserId = 1 Plan created and cached for the @UserId = 1

  8. Parameter Sniffing - Parametrized Query Query Processor SELECT * FROM Users WHERE Id = @Id • sp_executesqlN'SELECT * FROM Users WHERE Id = @Id',N'@Idint', 1 • sp_executesqlN'SELECT * FROM Users WHERE Id = @Id',N'@Idint', 22 SELECT * FROM Users WHERE Id = @Id SQL Server Cache Procedure cache Procedure cache Plan created and cached for the @Id = 1 Query executes using the query plan created for @Id = 1

  9. 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 Procedure cache Procedure cache Query executed using the query plan, created for the first query. New query plan created and cached. Query executed using newly created plan. New query plan again created and cached. Query executed using newly created plan.

  10. Index Seek SELECT*FROMUsers WHEREId= 523 1 .. 1M 1M-2K .. 1M 2K+1 .. 4K 1 .. 2K … 1,5K+1 .. 2K 301 .. 800 801 .. 1,5K 1 .. 300 …

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

  12. DEMO Parameter Sniffing

  13. Cardinality Estimation Expectation – Reality 2

  14. Statistics SELECT*FROMUsers WHEREIdBETWEEN2100 AND2500 SELECT*FROMUsers WHEREIdBETWEEN 200 AND 5000 1 5400 4500 800 2000 2800

  15. Out of Range Statistics SELECT*FROMUsers WHEREId> 5400 ? 1 5400 4500 800 2000 2800

  16. Statistics SELECT*FROM Users u INNERJOIN Posts p ONu.Id=p.OwnerUserId WHEREu.DisplayNameLIKE'Jeff%' Users L S T ZZZZ A G Posts 2000 2800 4500 5400 1 800

  17. DEMO Cardinality Estimation

  18. Statistics Update • SQL Server 2014 and lower default behavior: • 20% of rows + 500 • SQL Server 2016 • Dynamic threshold for tables with 25000+ rows • TF 2371 since SQL Server 2008R2 SP1

  19. Deadlocks Expectation – Reality 3

  20. Lock Types - Shared X S S

  21. Lock Types - Exclusive S X X

  22. Lock Types - Update S U S X U X U X U

  23. Classic Deadlock DEADLOCK! BEGINTRAN UPDATE Users SETCityId= 2 WHERE Id = 4 UPDATE City SETName='Dnipro' WHERE Id = 3 BEGINTRAN UPDATE City SETName='Dnipro' WHERE Id = 3 UPDATE Users SETCityId= 2 WHERE Id = 4 X wait wait X

  24. DEMO Deadlocks

  25. Query Optimization Expectation – Reality 4

  26. Query Processing Parser Algebraizer Optimizer Executor Plan Cache

  27. Optimization Phase 2 Optimizer • Full Set of Optimization Rules • Indexed views • Returns Full Query Plan • All planned checks were done • Good Enough Plan was Found • Timeout • Not Enough Memory

  28. DEMO Query Optimization

  29. Summary • Parameter Sniffing • Slow in Application. Fast in SSMS. • Cardinality Estimation • SQL Server Version Upgrade • Deadlocks • Non-Detectable Deadlocks • Query Optimization • Query Optimization Timeout

  30. Thank You! Denis Reznik Twitter: @denisreznik Email: denisreznik@gmail.com Blog: http://reznik.uneta.com.ua Facebook: https://www.facebook.com/denis.reznik.5 LinkedIn: http://ua.linkedin.com/pub/denis-reznik/3/502/234

More Related