Introduction

When SQL is compiled, an ‘optimal’ plan is created and stored in the plan cache, the plan contains details of table and index access. To produce this plan, the optimiser uses information from various sources, including indexes, statistics and row counts.

If the optimiser would like to use certain information but can’t find it, it adds details of what it wanted to use to the plan. Inspecting the plans for these details will help us improve the performance of our SQL.

For example, if a certain index could have been useful for a given SQL statement, the optimiser looks for that index, if that index is not found, the missing index’s details are stored together with the plan.

There are various performance related items we can search for including: missing indexes, columns with no statistics, and the occurrence of table scans.

This utility makes use of Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), so can be used by SQL Server 2005 or greater.

dba_SearchCachedPlans Utility

The SQL used in this utility dba_SearchCachedPlans is given in Listing 1 and is available as a download in the Resources section below.

The routine expects a single parameter (@StringToSearchFor), this is the string you want to search the cached plans for.

The main body of the code relates to linking together a DMV and two DMFs. The DMV sys.dm_exec_cached_plans contains details of the cached plans. This DMV is linked to the DMF sys.dm_exec_sql_text, which contains the underlying SQL text, via the plan_handle column. The cached plan DMV is also linked to the DMF sys.dm_exec_query_plan, again via the plan_handle column, this function contains the actual cached plan as XML. The WHERE clause ensures only appropriate cached plans are returned.

Note the cached plan has to be cast to a nvarchar(max) to ensure it is possible to search with a LIKE operator. To give some kind of weighting to the output, it is ordered by the number of times the routine has been used. Additionally, to limit the amount of output, only the top 20 items are obtained.

The utility selects relevant fields from the above DMVs/DMFs. The selected fields are described in figure 1 (largely taken from SQL Server 2005 Books online).

Figure 1 Columns in the dba_SearchCachedPlans utility.

To search the cached plans for missing indexes, the utility should be called with a parameter value of '%<MissingIndexes>%', to find plans that have columns with missing statistics use '%<ColumnsWithNoStatistics>%', and to search for table scans use '%<TableScan%'.

Running the utility on my SQL Server, to return cached plans that contain details of missing indexes, gives the results given in Figure 2.

Figure 2 Output from running the dbo.dba_SearchCachedPlans utility.

The results show the SQL text, the database name, the number of times the plan has been used, and the query plans that contain details of the string we searched for (missing indexes in this case).

Clicking on the plan given in the query_plan column will open the plan in XML format (in 2008 you will get the graphical version of the plan, so right mouse click on this to select the plan as XML). Part of a sample plan is given in figure 3

Figure 3 Details of the missing indexes in a partial query plan.

If you save the XML version of the plan with an extension of .sqlplan, when you open this saved file, it will be displayed as a graphical execution plan.

Discussion

This utility allows you to inspect cached plans and extract information that can be useful in improving the performance of your SQL.

Although this article has concentrated on finding plans with items that could improve performance, this utility is generic and allows you to search cached plans for any string, not just those that relate to performance. This could be useful in extracting and examining cached plans of queries that have already run, that may otherwise be expensive to obtain.

If you find a query has run slowly, run this utility with the parameter set to some identifier of the slow query (e.g. the query name or part of the query itself) to obtain the plan that was used. Examining this plan might help explain why the query ran slowly.

Note that not all plans will be in the cache, some plans are never included (e.g. DBCC commands), others are removed when there are memory pressures. Additionally, plans are removed when SQL Server is restarted. Despite these limitations this utility should still prove useful.

Further work

It is possible to extend this utility to report only on the database you are interested in, by providing a filter based on database name or database id.

Other items that might be worthwhile searching for include bookmark lookup, sorts, CONVERT_IMPLICIT (datatype conversion) and the usage of various SQL hints.

It might be useful to use a CLR regular expression function in place of the LIKE operator. However in my testing I didn’t find a significant improvement in performance in using this.

Conclusion

This utility allows you to inspect cached plans and extract information that can be useful in improving the performance of your SQL, and should prove valuable in the everyday work of the SQL Server DBA/developer.

Credits

Ian Stirkhas been working in IT as a developer, designer, and architect since 1987. He holds the following qualifications: M.Sc., MCSD.NET, MCDBA, and SCJP. He is a freelance consultant working with Microsoft technologies in London England. He can be contacted at Ian_Stirk@yahoo.com.

He is the author of SQL Server DMVs in Action: Better Queries with Dynamic Management Views.

Code

Listing 1