GETTING STARTED WITH EXECUTION PLANS Scott Fallen Sales

GETTING STARTED WITH EXECUTION PLANS Scott Fallen • • • Sales Engineer, SQL Sentry Twitter: @Scott. Fallen Blog: scottfallen. blogspot. com

AGENDA • About Scott and SQL Sentry o SQL Sentry Plan Explorer • Getting Started with Execution Plans o What are plans and why are they important? o How to collect and read plans. o Tuning opportunities to look for starting out. o Plan Demos. • Summary, Q&A, and Resources

WHAT IS AN EXECUTION PLAN? • An execution plan is SQL Server’s estimate of how to retrieve the data requested as efficiently as possible. • Plans show what SQL Server is doing internally to satisfy your query. • Generated by the query optimizer.

WHY SHOULD I CARE ABOUT PLANS? • Query performance and tuning o “Why is my query so slow? ” • Index utilization or lack thereof • Memory use (Plan Cache)

PLAN CREATION • What happens when SQL Server receives a query? • First, the query is parsed. This determines the validity of the T-SQL and formatting. • Second, the parsed query is sent to the algebrizer to have all of the object names and data types identified within the database. This outputs a query processor tree. • The query processor tree is passed on to the query optimizer, and its hash is checked against the plans in the plan cache. If a plan is found, it will be used. If not, we go on to plan compilation.

PLAN CREATION (CONT. ) • The query optimizer creates what SQL Server thinks will be the most efficient execution plan based on the query processor tree and the statistics available for the data. • “Efficiency” is based on I/O and CPU costs for each operation required to retrieve the data. • Often more than one plan will be created. The one that is fastest and most efficient will be selected and used. • Plan creation is expensive, so sometimes SQL Server decides that it will be cheaper to use a sub-optimal plan than to continue generating new plans. • SQL Server tries to reuse plans, and stores them in memory.

STATISTICS • SQL Server stores and maintains statistics about the distribution and selectivity of the data in the columns and indexes stored in a database. • Statistics are essential to plan creation, and incorrect or out-of-date statistics are an extremely common reason for the query optimizer to return an inefficient plan. • If you have a query that ran fast yesterday and runs slow today, check stats!

TYPES OF PLANS • Estimated • Actual o While usually the same, the actual plan may differ from the estimated plan. This can be due to stale statistics, parallelism request, or invalidity of the estimated plan (ex: temporary resource doesn’t exist yet). • XML, Graphical, and Text varieties. • We will focus on XML plans viewed Graphically

CAPTURING PLANS • Demos: o SSMS/Plan Explorer for Actual and Estimated Plans o Querying DMOs for Estimated Plans in the Plan Cache • Other options, we didn’t demo everything. o SET SHOWPLAN TEXT etc. (deprecated) o Trace Capture

READING THE PLAN • • • The basic structure, arrows and operator icons. Right to left! Costs Rowcounts / Data Sizes Number of executions Some information is available at a glance, other data requires looking at tooltips, the Properties Pane in SSMS, or other tabs in Plan Explorer.

PLAN OPERATORS • Logical and Physical Operators (Blue) o Parallel operators (Also Blue) • Cursor Operators (Yellow) • T-SQL Language Operators (Green) • Blocking and Non-Blocking Operators o Non-Blocking operators stream data through. Data goes out even as it’s still coming in and being worked on. o Blocking operators must complete fully before passing data on. Can lead to issues with concurrency. • Let’s walk through some of the more common operators.

COMMON OPERATORS (1) • Scan – Bring back everything! o Table Scan o Clustered Index Scan o Nonclustered Index Scan • Seek – Navigate the B-tree to get only the required data. o Clustered Index Seek o Non-Clustered Index Seek • Generally, seeks are preferable to scans because they retrieve only the requested data. That said don’t worry about a scan that only picks up a handful of rows!

COMMON OPERATORS (2) • Join – Bringing together data from multiple tables. o Nested Loops Join • Generally the best type of join, except for very large data sets. o Merge Join • Joins data already ordered on join columns, non-blocking operator. o Hash Match Join • Blocking Operator, Uses Temp. DB. Can be inefficient! • Bookmark Lookup – Always accompanied by a join. o Key Lookup • Go back to the Clustered Index to retrieve other columns. o RID Lookup • Same, but using the RID (row ID) for heaps.

COMMON OPERATORS (3) • Sort o Required for some operations and/or because an ORDER BY clause was used. Can spill to Temp. DB, generating a Sort Warning that can be captured via trace, and plan XML in 2012+. • Filter • Compute Scalar o Produces a value from a calculation. • Hash Match (Aggregate) o Aggregations can be costly, try to limit the amount of data that needs to be aggregated with a WHERE clause.

LET’S LOOK AT SOME PLANS • Putting it all together: DEMO

INSERTS, UPDATES, AND DELETES • Keep in mind that there are some new operators for INSERT, UPDATE, and DELETE statements. • The basic concepts for reading the plans remain the same.

RESOURCES • SQL Server Execution Plans, Second Edition, by Grant Fritchey. o The definitive guide, and the e-book is free! I learned a ton from this book. o https: //www. simple-talk. com/books/sql-server-execution-plans, -second -edition, -by-grant-fritchey/ • Microsoft Books Online o http: //technet. microsoft. com/en-us/library/ms 178071%28 v=sql. 105%29. aspx o http: //technet. microsoft. com/en-us/library/ms 175913%28 v=sql. 105%29. aspx • Paul White’s Blog o http: //sqlblog. com/blogs/paul_white/ • Aaron Bertrand’s Plan Explorer Demo Kit o http: //sqlperformance. com/2014/03/t-sql-queries/updated-plan-explorer-demo-kit • Contains the demo plans I used for this presentation.

FOR FRIENDS OF SQL SENTRY • Free Plan Explorer download: http: //www. sqlsentry. com/products/planexplorer/sql-server-query-view#download • Free query tuning consultations: http: //answers. sqlperformance. com. • Free new ebook (regularly $10) to attendees. Send request to sales@sqlsentry. com. • SQL Server educational videos, scripts, and slides: http: //SQLSentry. TV • • Tuning blog: http: //www. sqlperformance. com/ Monthly e. News tips and tricks: http: //www. sqlsentry. net/newsletter-archive. asp

THANK YOU! • Please feel free to contact me with questions: sfallen@sqlsentry. com. • Thanks to all of the attendees and organizers!