Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get Performance Tips Directly From SQL Server

By Ian Stirk, (first published: 2009/06/11)

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).

Column name Data type Description
SQL nvarchar(max) Text of the SQL query.
cacheobjtype nvarchar(34) Type of object in the cache e.g. compiled plan.
objtype nvarchar(16) Type of object e.g. proc, adhoc
DatabaseName varchar Name of the database the plan relates to.
Plan usage int Number of times this cached object has been used.
Query_plan XML XML version of the query plan.

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.

Cached execution plans

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

XML of Execution plan

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

CREATE PROC [dbo].[dba_SearchCachedPlans]
@StringToSearchFor VARCHAR(255)
AS
/*----------------------------------------------------------------------
Purpose: Inspects cached plans for a given string.
------------------------------------------------------------------------

Parameters: @StringToSearchFor - string to search for e.g. '%<MissingIndexes>%'.

Revision History:
03/06/2008 Ian_Stirk@yahoo.com Initial version

Example Usage:
1. exec dbo.dba_SearchCachedPlans '%<MissingIndexes>%'
2. exec dbo.dba_SearchCachedPlans '%<ColumnsWithNoStatistics>%'
3. exec dbo.dba_SearchCachedPlans '%<TableScan%'
4. exec dbo.dba_SearchCachedPlans '%CREATE PROC%MessageWrite%'

-----------------------------------------------------------------------*/
BEGIN
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, DB_NAME(st.dbid)AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor
ORDER BY cp.usecounts DESC
END

Listing 1

Resources:

perftips.sql
Total article views: 46463 | Views in the last 30 days: 21
 
Related Articles
FORUM

Can explain difference and details between 'Index Scan' and 'Index seek' in Query plan?

Can explain difference and details between 'Index Scan' and 'Index seek' in Query plan?

FORUM

Indexes for Booking Search

Indexes for Booking Search

FORUM

All INDEX details query - gives duplicate entires

I mixed and matched many queries tto give all details of index needed for analysis. Howeever, the qu...

FORUM

Search query fluctuation in exection

Search query fluctuation in exection

FORUM

Full text search VS Indexing

Full text search VS Indexing

Tags
cached execution plan    
dmv    
performance tuning    
search    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones