SQLSentry PlanExplorer

Most of the times at work, I was focussed on getting things done. There are lot more tools / ideas which could have been leveraged if I had taken a step back to try out different tools/ideas. Time vs Task always compete with each other. Best alternative is to take time to learn outside work.

Today we will look at another free tool SQL Sentry. This tool provides richer GUI interface for interpreting SQL Query Plans.

Step 1. Download Tool from link

Step 2. Installed the Tool. Shortcut SQL Sentry Plan Explorer would be created in desktop

Step 3. Provided below query and clicked on Actual Plan. In the pop up window provide database settings to execute the query.

Step 4. Quick Observation here, Test Connection button is not provided. If provided this can be used to test connection is successful


Step 5. Click on Estimated Plan. Actual Rows columns are blank here.

Step 6. Click on Actual Plan. You can see estimated vs actual rows.

Step 7. Let us reuse one of the procedures in previous posts and compare cost/plan against SSMS and SQL Sentry Plan Analysis Tool. Reusing from post

Step 8. Executed below TSQL Code

DECLARE @ID INT, @Name VARCHAR(100), @City VARCHAR(100)
SET @ID = NULL
 SET @City = 'City 1'
 SET @Name = NULL
 IF @ID IS NOT NULL
     SELECT * FROM TestforCode WHERE (ID = @ID)
 ELSE
    IF @Name IS NOT NULL
    SELECT * FROM TestforCode WHERE (NAME = @Name)
 ELSE
    IF @City IS NOT NULL
    SELECT * FROM TestforCode WHERE (CITY = @City)

 SET @ID = NULL

 SET @City = 'City 1'
 SET @Name = NULL
 SELECT * FROM TestforCode WHERE (ID = @ID) OR
(NAME = @Name) OR (CITY = @City)

Step 9. Similar to SSMS when you hover over it would provide you Actual vs Estimated Rows and Stats. This is also provided in the tool

Provided below is SSMS snapshot

Step 10. Very useful feature is IO cost, IO%, CPU cost, CPU% every thing is listed in the output. By one click you would be able to see the results. This is very handy and great information to get started.

This is very good information to get started on where to focus first for optimization. This is a great tool!!!

More Reads -

Five Favorite Free SQL Server Downloads
SQL Sentry's Plan Explorer : a closer look
SQL Sentry Plan Explorer Documentation
SQL Bits - SQL Server 2008 Database Internals

Happy Reading!!!