Thursday, 15 March 2018

Querying the Execution Plan for Subtree Cost - Take 2

A few weeks ago I shared a query for probing the execution plan for the Estimated Subtree Cost value.

That query was rather simple but it got the job done. In that post I also explained how, although there are several ways of finding missing index suggestions, they were not always very helpful. After posting I was thinking that it would be helpful to combine the two, or at least display the results so that it is easy to determine which execution plans include missing index warnings.


I found the following query in a post by Jonathan Kehayias on sqlskills.com. It is a good way of not only finding execution plans with missing index warnings, but displaying the index recommendations within.

Obviously, I wanted to combine the above with my desire to find execution plans with an Estimated Subtree Cost of greater than x. So, I came up with the stored procedure below. I decided to make the query a stored procedure. As I was testing it I noticed that this query was actually becoming one of the most resource intensive queries. I obviously wanted to exclude it and the easiest way was to create it as a stored procedure and then delete any rows with this stored procedure in the ObjectName column of the temp table. Regular readers of my blog (are there any?) may remember that my team maintains a DBA database. In my current role we call it SYSDBA. It holds a number of stored procedures, functions and tables that are used for maintenance tasks and other regularly used queries that the DBA team needs. This stored proc will now enter our arsenal.

Let me restate that my XML query skills are virtually non-existent. The query below was cobbled together with a lot of trial and error. I welcome any tips and feedback on making it tidier and more efficient. This query will take a long time to run on large and/or busy databases.

Finally, let me describe how to run the stored procedure and the result set returned.
The stored procedure has only 1 parameter: @Cost which is an integer. This is the lower threshold for the EstimatedSubtreeCost value. The stored procedure will find all execution plans that contain an EstimatedTotalSubtreeCost value of greater than @Cost.

The stored procedure's result set is as follows:

Column Description
query_plan The full XML execution plan.
plan_handle The identity of the execution plan.
cost The Estimated Subtree Cost of at least one section of the plan.
DBName The database name from where this query is run. It may not be the database where that the query affects. For example, if the stored proc is stored on DB1 and a statement within it access DB2, DB1 will be displayed.
objectid If the query is a stored procedure an id will be displayed otherwise it will be null.
param_list Declare statements for all the parameters listed within the execution plan.
sql_text The sql statement or stored procedure within the execution plan.
Index_impact Impact of any missing index. The value will be NULL if no index is suggested.
Index_database_id The database id where for the missing index. The value will be NULL if no index is suggested.
Index_object_id The object id (Table) for any missing index. The value will be NULL if no index is suggested.
Index_statement The SQL statement that for the missing index. The value will be NULL if no index is suggested. The value will most probably be the same as the sql_text column value.
Index_equality_columns The list of equality columns for any missing index. The value will be NULL if no index is suggested.
Index_inequality_columns The list of inequality columns of any missing index. The value will be NULL if no index is suggested or if there are no inequality columns.
Index_include_columns The list of included columns of any missing index. The value will be NULL if no index is suggested or if there are no included columns.


Finally, here is the stored procedure:

You can download it from my Google Drive as well.

2 comments:

meena resma said...

Thanks for sharing this post. Your post is really very helpful its students.
SQL server dba Online Training Hyderabad

srisan ps said...

Data science is a fast-moving field – if you’re pursuing a data science career, or even if you’re just interested in data-related topics, you need to invest time to keep up with the trends. Following a few top blogs is a great way to stay abreast of developments in data analysis, statistical software, data visualization, and more. These AUTOMATIONMINDS bloggers consistently offer great resources and tutorials, along with opportunities to connect with and learn from other leading data science professionals.
DATA SCIENCE training in chennai