SQL Performance Tuning Through Execution Plans: A Practical Guide

vladimir oselsky n.w
1 / 25
Embed
Share

Discover how to optimize SQL performance through execution plans with Vladimir Oselsky's comprehensive insights. Learn about estimated and actual plans, graphical representations, plan cache management, and more.

  • SQL
  • Performance Tuning
  • Execution Plans
  • Optimization
  • Vladimir Oselsky

Uploaded on | 0 Views


Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.

E N D

Presentation Transcript


  1. Vladimir Oselsky Performance Tuning for SQL Developers through Execution Plans

  2. About the Speaker Vladimir Oselsky (Vlady) @VladyOselsky (Twitter), voselsky (LinkedIn) 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.

  3. 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.

  4. Execution Plan Graphical Graphical representation of steps SQL Server took to execute the query. (Easy to Read) Text Textual representation of steps, containing details about every operation. (Hard to Read) SHOWPLAN_ALL, SHOWPLAN_TEXT, STATISTICS PROFILE XML XML representation of steps outlined in structured way. (Maybe easy for some)

  5. Plan Cache 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)

  6. Plan Basics Button for displaying Estimated Execution Plan

  7. Plan Basics Plans are read from RIGHT to LEFT.

  8. Plan Basics To get more details need to hover over specific icons or view properties screen

  9. Plan Basics To get more details need to hover over specific icons or view properties screen

  10. Plan Basics Text plan.

  11. Plan Basics STATISTICS IO Outputs information related to execution to messages tab

  12. Plan Basics STATISTICS TIME Outputs information related to execution to messages tab

  13. Plan Basics XML Plan One options is to run SHOWPLAN_XML command or right click on Actual Plan and select Show PLAN XML Option

  14. XML Plan

  15. 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;

  16. Plan vs Plan Estimated vs Actual may be different Stale Statistics Estimated Plans are invalid - DEMO 02 Cost for Parallelism is reached

  17. Graphical Plan Explained Reference Material for further reading List of all operators for SQL Server 2008R2 https://docs.microsoft.com/en-us/previous- versions/sql/sql-server-2008-r2/ms175913(v=sql.105) List of all operators for SQL Server 2017 https://docs.microsoft.com/en-us/sql/relational- databases/showplan-logical-and-physical-operators- reference?view=sql-server-2017

  18. Possible Problems Scan Operations

  19. Possible Problems Other performance hindering candidates

  20. Possible Problems Other performance hindering candidates

  21. Possible Problems Physical JOIN Operations DEMO 03 HASH MERGE LOOP

  22. Possible Problems IMPLICIT CONVERSION DEMO 04

  23. SentryOne Demo Download: https://www.sentryone.com/plan-explorer

  24. Q&A Send questions to: vlady.oselsky@gmail.com Blog: https://dg-sql.blogspot.com/ Twitter: @VladyOselsky Thank you

  25. 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.

More Related Content