Finding procedure cache bloat

Explicitly parameterizing queries is a well-known best-practice for database app developers.  There are cases where it can make sense to execute a query unparameterized, but, in general, you should default to explicit parameterization.  It can make your queries faster by avoiding unnecessary compilation when the "same" query is run repeatedly with different parameters, and it can reduce competition for limited memory in SQL Server's visible buffer pool.  If you don't already know how to parameterize your queries, or if you want more information about the benefits of parameterization, check out:

      https://weblogs.sqlteam.com/dang/archive/2008/02/21/Dont-Bloat-Proc-Cache-with-Parameters.aspx

      https://weblogs.sqlteam.com/dang/archive/2008/02/18/Why-Parameters-are-a-Best-Practice.aspx

Below is a handy DMV query that can quickly identify which queries have many different plans cached for the same query.  In most cases, this indicates a lack of explicit parameterization of the query.  This uses the query plan hash/query plan fingerprint feature that I wrote about in an earlier post.  It relies on the fact that two queries that have different inline literal values (e.g. "SELECT...WHERE col1 = 123", vs. "SELECT...WHERE col1 = 456") will get two different query plans, but the plans will have the same query_hash value.  You must be running SQL Server 2008 or later to use this.   

---

 WITH duplicated_plans AS (
    SELECT TOP 20
query_hash,
        (SELECT TOP 1 [sql_handle] FROM sys.dm_exec_query_stats AS s2 WHERE s2.query_hash = s1.query_hash ORDER BY [sql_handle]) AS sample_sql_handle,
        (SELECT TOP 1 statement_start_offset FROM sys.dm_exec_query_stats AS s2 WHERE s2.query_hash = s1.query_hash ORDER BY [sql_handle]) AS sample_statement_start_offset,
        (SELECT TOP 1 statement_end_offset FROM sys.dm_exec_query_stats AS s2 WHERE s2.query_hash = s1.query_hash ORDER BY [sql_handle]) AS sample_statement_end_offset,
        CAST (pa.value AS INT) AS dbid,
        COUNT(*) AS plan_count
    FROM sys.dm_exec_query_stats AS s1
    OUTER APPLY sys.dm_exec_plan_attributes (s1.plan_handle) AS pa
    WHERE pa.attribute = 'dbid'
    GROUP BY query_hash, pa.value
    ORDER BY COUNT(*) DESC
)
SELECT
    query_hash,
    plan_count,
    CONVERT (NVARCHAR(80), REPLACE (REPLACE (
        LTRIM (
            SUBSTRING (
                sql.[text],
                (sample_statement_start_offset / 2) + 1,
                CASE
                    WHEN sample_statement_end_offset = -1 THEN DATALENGTH (sql.[text])
                    ELSE sample_statement_end_offset
                END - (sample_statement_start_offset / 2)
            )
        ),
        CHAR(10), ''), CHAR(13), '')) AS qry,
    OBJECT_NAME (sql.objectid, sql.[dbid]) AS [object_name],
    DB_NAME (duplicated_plans.[dbid]) AS [database_name]
FROM duplicated_plans
CROSS APPLY sys.dm_exec_sql_text (duplicated_plans.sample_sql_handle) AS sql
WHERE ISNULL (duplicated_plans.[dbid], 0) != 32767 -- ignore queries from Resource DB
AND plan_count > 1;

 

 

---

 

 

 

---

Comments

• Anonymous
  February 05, 2015
  Awesome query!It took quite a while to complete on a system with ~50,000 ad hoc query plans in the plan cache. :)But this query is extremely useful for big servers that have many different ad-hoc workloads running on them.Thanks a million.