1 / 11

Reading the SQL Server Execution Plan

Reading the SQL Server Execution Plan. Denny Cherry Manager of Information Systems mrdenny@mrdenny.com MVP, MCSA , MCDBA, MCTS, MCITP. Agenda. What is an Execution Plan Reading an Execution Plan Execution Plan Operators Changing the Execution Plan Reading the Execution Plan Demo.

vlad
Download Presentation

Reading the SQL Server Execution Plan

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Reading the SQL Server Execution Plan Denny Cherry Manager of Information Systems mrdenny@mrdenny.com MVP, MCSA, MCDBA, MCTS, MCITP

  2. Agenda • What is an Execution Plan • Reading an Execution Plan • Execution Plan Operators • Changing the Execution Plan • Reading the Execution Plan • Demo

  3. What is an Execution Plan • How the SQL Server access the data within the database. • Returned as XML or text when requested. • Displayed in a much more readable graphical form • Can not be edited directly • Can be adjusted with index hints • Can be set by pining Execution Plans • SQL chooses a Good Enough Plan not the perfect plan.

  4. Reading an Execution Plan • Graphical Plan • Read from Right To Left, Top to Bottom • Follow Arrows until next branch from below • Text Plan • Read from Left to Right, Top to Bottom • Each Operator has it’s own CPU and IO Statistics, both Estimates and Actuals

  5. Reading an Execution Plan

  6. Operators Finding Data • Index Seek – Reads the portion of the index which contains the needed data. • Index Scan – Reads the entire index for the needed data. • Table Scan – Reads the entire table for the needed data. • Key Lookup – Looks up values row by row for values which are missing from the index used. • Table Valued Function – Executes a table valued function within the database

  7. Operators Filtering and Sorting • Nested Loops - Performs inner, outer, semi and anti semi join operations. Performs search on the inner table for each row of the outer table. • Hash Match - Creates a hash for required columns for each row. Then creates a hash for second table and finds matches. • TOP - Returns the specified top number of rows. • Sort - Sorts the incoming rows. • Stream Aggregate – groups rows by one or more columns and calculates one or more aggregate expressions.

  8. Changing the Execution Plan • Index hints • NOLOCK • READPAST • FIRSTFAST • INDEX() • Pinning an Execution Plan • sp_create_plan_guide • sp_control_plan_guide • Adding Indexes To Tables • Modify Indexes to remove scan and lookup operations

  9. sp_create_plan_guide

  10. sp_control_plan_guide

  11. Denny Cherry Please fill out the survey at http://speakerrate.com/mrdenny. mrdenny@mrdenny.com http://itke.techtarget.com/sql-server Contact Info Blog Twitter

More Related