SET STATISTICS PROFILE ON

SET_STATISTICS_PROFILE_ON_1

For example, in the above plan (zoomed out), it’s tedious to figure out where cardinality estimation has gone bad. You hover the mouse cursor over every arrow to see the numbers and its painful!

One technique is SET STATISTICS PROFILE ON.

SET STATISTICS PROFILE ON

<and your query>

And you get the following additional output:

   

SET_STATISTICS_PROFILE_ON_2

With the output of STATISTICS PROFILE, you can easily scroll through to figure out the numbers and take the next course of action in your query tuning endeavors.

Note that I dragged ‘EstimatedRows’ & ‘EstimatedExecutions’ columns from far right and brought them next to the ‘Rows’ and ‘Executes’ column so that it becomes easier to compare.

Hope this helps!

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published.