When working with SQL Server, it’s important to optimize the performance of your queries. One way to do this is by inspecting the cached plans and extracting information that can help improve performance. In this article, we will discuss a utility called dba_SearchCachedPlans that allows you to search for specific details in the cached plans.

When SQL Server compiles a query, it creates an optimal plan and stores it in the plan cache. This plan contains details of table and index access, which are used by the optimizer to execute the query efficiently. However, if the optimizer cannot find certain information, it adds details of what it wanted to use to the plan. By inspecting these plans, we can identify missing indexes, columns with no statistics, and occurrences of table scans, which can impact the performance of our SQL.

The dba_SearchCachedPlans utility makes use of Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) in SQL Server 2005 or greater. It allows you to search the cached plans for specific strings, such as missing indexes, columns with no statistics, or table scans.

To use the utility, you need to provide a parameter value that corresponds to the string you want to search for. For example, to find plans with missing indexes, you would call the utility with a parameter value of ‘%<MissingIndexes>%’. The utility will then return 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 you searched for.

By examining these plans, you can identify areas for improvement in your SQL queries. For example, if you find a query that has run slowly, you can use the utility to obtain the plan that was used and analyze it to understand why the query ran slowly. This can help you make necessary optimizations to improve performance.

It’s important to note that not all plans will be in the cache. Some plans are never included, such as DBCC commands, and others are removed when there are memory pressures or when SQL Server is restarted. Despite these limitations, the dba_SearchCachedPlans utility can still be valuable in the everyday work of a SQL Server DBA or developer.

Further work can be done to enhance the utility, such as adding filters based on database name or database ID to report only on the database of interest. Additionally, other items that might be worth searching for include bookmark lookup, sorts, CONVERT_IMPLICIT (datatype conversion), and the usage of various SQL hints.

In conclusion, the dba_SearchCachedPlans utility is a powerful tool for inspecting cached plans and extracting information that can help improve the performance of your SQL queries. By analyzing these plans, you can identify areas for optimization and make necessary changes to enhance the overall performance of your SQL Server.

Author: Ian Stirk

Contact: Ian_Stirk@yahoo.com