Downloaded 14 times
Uploaded byMarek Maśko
SQL Server - Using Tools to Analyze Query Performance
This document summarizes tools that can be used to analyze query performance in SQL Server. It discusses the query optimizer, SQL Trace, SQL Server Profiler, Extended Events, SET session options, dynamic management objects, and execution plans. The query optimizer uses cost-based optimization and cardinality estimation. SQL Trace and Profiler are easy to use but produce overhead. Extended Events provide a more efficient alternative. Dynamic management objects and SET options provide insight into query execution. Execution plans show the optimizer's estimated plan and the actual plan used.
SQL Server - Using Tools to Analyze Query Performance
- 1. Polish SQL ServerUser Group Using Tools to Analyze Query Performance CHAPTER 14
- 2. Polish SQL ServerUser Group About the Author Marek Maśko • Principal Database Analyst at Sabre • Working with SQL Server since 2010 • SQL DBA, Dev & Architect • MCP since 2012 • Contact Information: Email: marek.masko@gmail.com LinkedIn: https://pl.linkedin.com/in/marekmasko Twitter: @MarekMasko
- 3. Polish SQL ServerUser Group Agenda • Query Optimizer • SQL Trace • SQL Server Profiler • SQL Server Extended Events • SET Session Options • Dynamic Management Objects • Execution Plans
- 4. Polish SQL ServerUser Group QUERY OPTIMIZER
- 5. Polish SQL ServerUser Group Query Performance • Time • CPU • Memory • I/O
- 6. Polish SQL ServerUser Group Query Optimization Relational Engine T-SQL Statement Parser Algebrizer Query Optimizer Storage Engine Query Parsing Parse Tree Normalization and Binding Query Processor Tree Query Optimization Execution Plan
- 7. Polish SQL ServerUser Group Query Optimizer • Cost based • Cardinality Estimation
- 8. Polish SQL ServerUser Group SQL TRACE & SQL SERVER PROFILER
- 9. Polish SQL ServerUser Group SQL Trace & Profiler https://msdn.microsoft.com/en-us/library/hh245121.aspx
- 10. Polish SQL ServerUser Group Benefits and drawbacks • Easy to use • Produces overhead: –Local resources –Network • Results grid can consume a lot of memory • Deprecated!
- 11. Polish SQL ServerUser Group SQL SERVER EXTENDED EVENTS
- 12. Polish SQL ServerUser Group Extended Events Engine https://technet.microsoft.com/en-us/library/dd822788(v=sql.100).aspx
- 13. Polish SQL ServerUser Group Extended Events Engine https://technet.microsoft.com/en-us/library/dd822788(v=sql.100).aspx
- 14. Polish SQL ServerUser Group SET SESSION OPTIONS
- 15. Polish SQL ServerUser Group SET Session Options • SET STATISTICS IO • SET STATISTICS TIME
- 16. Polish SQL ServerUser Group DYNAMIC MANAGEMENT OBJECTS
- 17. Polish SQL ServerUser Group DMO • System views and functions • More than 130 • Exist in sys system schema • Names start with dm_ • Shows current state or data cumulated from instance start
- 18. Polish SQL ServerUser Group DMO • SQLOS related • Execution related • Index related
- 19. Polish SQL ServerUser Group Most Important DMOs • dm_exec_requests • dm_exec_sessions • dm_exec_sql_text() • dm_exec_query_stats • dm_os_wait_stats • dm_os_waiting_tasks • dm_db_missing_index_details • dm_db_missing_index_columns • dm_db_missing_index_groups • dm_db_missing_index_group_stats
- 20. Polish SQL ServerUser Group EXECUTION PLANS
- 21. Polish SQL ServerUser Group Estimated and Actual Execution Plans Estimated execution plan • Output from the Optimizer • Query doesn’t have to be executed • Tells you what SQL Server would most likely do • Stored in the plan cache Actual execution plan • Output from the actual query execution • Tells you exactly what SQL Server did
- 22. Polish SQL ServerUser Group Execution Plan Formats • Graphical Plans • Text Plans • XML Plans
- 23. Polish SQL ServerUser Group Graphical Plan
- 24. Polish SQL ServerUser Group Text Plan
- 25. Polish SQL ServerUser Group XML Plan
- 26. Polish SQL ServerUser Group SET Session Options • Text plans –SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL for estimated plans –SET STATISTICS PROFILE for actual plans • XML plans –SET SHOWPLAN_XML for estimated plans –SET STATISTICS XML for actual plans
- 27. Polish SQL ServerUser Group THANK YOU!
- 28. Polish SQL ServerUser Group Resources • Book: Training Kit Exam 70-461 • Scripts for Training Kit