SQL Sentry Plan Explorer

1. SQL Sentry Plan Explorer

2. Who is SQL Sentry? • Developers of a suite of products to help you monitor and optimize Windows/SQL Server performance • Performance Advisor – awareness and control over Windows and SQL Server performance issues • Event Manager – Calendar, scheduling, alerting and response system • Fragmentation Manager – view and manage index fragmentation • Plan Explorer – Plan Analysis made easier! • Download a FULL evaluation @ http://www.sqlsentry.com

3. Plans and DemoS • Demo plans from can be obtained from: http://bit.ly/PEDemoKit • Jonathan Kehayias Plan Explorer training on Pluralsight: http://bit.ly/PETraining

4. Plan Layout • SSMS • Wasted space, more scrolling • Limited zooming options • Important attributes (object/index names) hidden in properties window • Plan Explorer • Optimized spacing, minimized scrolling • Dynamic zooming with Ctrl + mouse scroll wheel • Attributes fully displayed and easy-to-read

5. Performance Identification • SSMS • Difficult to quickly find expensive operators • Useful information about potential problems hidden in tooltips • Runtime metrics not collected as part of execution plan • Plan Explorer • Provides color-highlights to quickly and easily find expensive operators • Plan Tree grid provides useful information on operators and highlights potential issues automatically • Runtime metrics collected for actual plans

6. Missing Indexes • SSMS • In a multi statement batch, missing index information is shown for each statement (which statement contains the missing index?) • Plan Explorer • Missing index information shown for the appropriate statement • “Missing Indexes” column dynamicallyaddedtothe Statement Tree for easy identification

7. Large Plans • SSMS • Often becomes unresponsive/freezes • Becomes even more difficult to find expensive operators • Unable to view cumulative costs per branch • Plan Explorer • Optimized to handle large plans • Statement/Plan Tree/Color coding simplifies identification of expensive operators • Can show cumulative costs per branch to help identify the most expensive portion(s) of the plan

8. Plan Explorer Free • User friendly Interface • Command Text – contains T-SQL text for the plan • Results tab • Statement Tree – grid which simplifies navigation of multiple statements within the execution plan • Plan Information – Text Data, Plan XML • Plan Details - Plan Diagram, Parameters, Join Diagram, Plan Tree, Top Operations, Query Columns, Expressions, Table I/O • Post to SQLPerformance.com – get expert help with plan analysis! http://answers.sqlperformance.com

9. Plan Explorer PRO All Plan Explorer Free features + … More Layout Customization Multiple tabs – open multiple plans within a single window History Tracking – track code/plan changes with comments Wait Stats – collected using Extended Events with (Actual Plan) Graphical Deadlock Analysis

10. Important Links • Download: http://www.sqlsentry.com • Plans used in the demo: http://bit.ly/PEDemoKit • Comprehensive demo of Plan Explorer: Pluralsight • Our Community Team: community@sqlsentry.com

11. THANK YOU! • E-mail ebooks@sqlsentry.com for free copies of our e-books: • Just tell them where you met me