Vladimir Oselsky Performance Tuning for SQL Developers through
- Slides: 25
Vladimir Oselsky Performance Tuning for SQL Developers through Execution Plans
About the Speaker Vladimir Oselsky (Vlady) @Vlady. Oselsky (Twitter), voselsky (Linked. In) https: //dg-sql. blogspot. com/ • Born and raised in Siberia (it does get cold, and sometimes hot) • B. S. Software Engineering Technology (I know some. NET) • Various IT Jobs since 1998. • Starting in 2010 worked with SQL Server and Oracle.
Execution Plans Is an attempt of SQL Server optimizer to calculate the most optimal route to retrieve the data. Estimated Plan: Possible plan SQL Server will use when executing a query. Actual Plan: Actual plan SQL Server used when executing a query.
Execution Plan Graphical representation of steps SQL Server took to execute the query. (Easy to Read) Textual representation of steps, containing details about every operation. (Hard to Read) SHOWPLAN_ALL, SHOWPLAN_TEXT, STATISTICS PROFILE XML representation of steps outlined in structured way. (Maybe easy for some)
Plan Cache – The place SQL Server stores actual and estimated plans. Lazywriter – process that cleans up plans from Plan Cache based on age and complexity. DBCC FREEPROCCACHE – command to clear plan cache (DO NOT RUN IN PROD)
Plan Basics Button for displaying Estimated Execution Plan
Plan Basics Plans are read from RIGHT to LEFT.
Plan Basics To get more details need to hover specific icons or view properties screen
Plan Basics To get more details need to hover specific icons or view properties screen
Plan Basics Text plan.
Plan Basics STATISTICS IO – Outputs information related to execution to messages tab
Plan Basics STATISTICS TIME – Outputs information related to execution to messages tab
Plan Basics XML Plan – One options is to run SHOWPLAN_XML command or right click on Actual Plan and select Show PLAN XML Option
XML Plan
Stored Plans System views can used to retrieve stored plans – DEMO 01 SELECT [cp]. [refcounts] , [cp]. [usecounts] , [cp]. [objtype] , [st]. [dbid] , [st]. [objectid] , [st]. [text] , [qp]. [query_plan] FROM sys. dm_exec_cached_plans cp CROSS APPLY sys. dm_exec_sql_text( cp. plan_handle ) st CROSS APPLY sys. dm_exec_query_plan( cp. plan_handle ) qp;
Plan vs Plan Estimated vs Actual may be different Stale Statistics Estimated Plans are invalid - DEMO 02 Cost for Parallelism is reached
Graphical Plan Explained Reference Material for further reading List of all operators for SQL Server 2008 R 2 https: //docs. microsoft. com/en-us/previousversions/sql-server-2008 -r 2/ms 175913(v=sql. 105) List of all operators for SQL Server 2017 https: //docs. microsoft. com/en-us/sql/relationaldatabases/showplan-logical-and-physical-operatorsreference? view=sql-server-2017
Possible Problems Scan Operations
Possible Problems Other performance hindering candidates
Possible Problems Other performance hindering candidates
Possible Problems • Physical JOIN Operations – DEMO 03 • HASH • MERGE • LOOP
Possible Problems • IMPLICIT CONVERSION – DEMO 04
Sentry. One Demo Download: https: //www. sentryone. com/plan-explorer
Q&A • Send questions to: vlady. oselsky@gmail. com • Blog: https: //dg-sql. blogspot. com/ • Twitter: @Vlady. Oselsky • Thank you
Presentation has ended. Error code #20181120 If you like to know more, feel free to email questions to: Vlady. Oselsky@gmail. com NOTE: All communication sent to incorrect address will be ignored.
