sql server - using tools to analyze query performance
TRANSCRIPT
Polish SQL Server User Group
Using Tools to Analyze Query Performance
CHAPTER 14
Polish SQL Server User 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: [email protected]
LinkedIn: https://pl.linkedin.com/in/marekmasko
Twitter: @MarekMasko
Polish SQL Server User Group
Agenda
• Query Optimizer
• SQL Trace
• SQL Server Profiler
• SQL Server Extended Events
• SET Session Options
• Dynamic Management Objects
• Execution Plans
Polish SQL Server User Group
QUERY OPTIMIZER
Polish SQL Server User Group
Query Performance
• Time
• CPU
• Memory
• I/O
Polish SQL Server User Group
Query Optimization
Relational EngineT-SQL
Statement Parser AlgebrizerQuery
Optimizer
Storage Engine
Query Parsing
Parse Tree
Normalization and Binding
Query Processor
Tree
Query Optimization
Execution Plan
Polish SQL Server User Group
Query Optimizer
• Cost based
• Cardinality Estimation
Polish SQL Server User Group
SQL TRACE & SQL SERVER PROFILER
Polish SQL Server User Group
SQL Trace & Profiler
https://msdn.microsoft.com/en-us/library/hh245121.aspx
Polish SQL Server User Group
Benefits and drawbacks
• Easy to use
• Produces overhead:
– Local resources
–Network
• Results grid can consume a lot of memory
• Deprecated!
Polish SQL Server User Group
SQL SERVER EXTENDED EVENTS
Polish SQL Server User Group
Extended Events Engine
https://technet.microsoft.com/en-us/library/dd822788(v=sql.100).aspx
Polish SQL Server User Group
Extended Events Engine
https://technet.microsoft.com/en-us/library/dd822788(v=sql.100).aspx
Polish SQL Server User Group
SET SESSION OPTIONS
Polish SQL Server User Group
SET Session Options
• SET STATISTICS IO
• SET STATISTICS TIME
Polish SQL Server User Group
DYNAMIC MANAGEMENT OBJECTS
Polish SQL Server User 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
Polish SQL Server User Group
DMO
• SQLOS related
• Execution related
• Index related
Polish SQL Server User 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
Polish SQL Server User Group
EXECUTION PLANS
Polish SQL Server User 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
Polish SQL Server User Group
Execution Plan Formats
• Graphical Plans
• Text Plans
• XML Plans
Polish SQL Server User Group
Graphical Plan
Polish SQL Server User Group
Text Plan
Polish SQL Server User Group
XML Plan
Polish SQL Server User 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
Polish SQL Server User Group
THANK YOU!
Polish SQL Server User Group
Resources
• Book: Training Kit Exam 70-461
• Scripts for Training Kit