DBCC freeproccache?

Question

We recently had a performance problem and this got resolved by executing DBCC freeproccache...Now, we have lot more questions to answer;

• What made the the procedure cache out dated?
• If indexes or Statistics where out dated, why the query did not recompile itself?
• Is it a good practice to schedule DBCC freeproccache as a JOB?
• Is there a way to identify potential out dated query plans?
• Is there a way to identify an offending query?

Any Help is appreciated!

Solution

Your questions are all over the place, so I'll try to address them all. The procedure cache is only so large. Your procedure cache may have been filled with single-use plans (this has no impact on statistics, though statistics can impact the plan cache). You can read a lot of details about single-use plans in Kimberly Tripp's blog post, "Plan cache and optimizing for adhoc workloads" - including a query against sys.dm_exec_cached_plans that will help identify when the cache is populated with a lot of single-use plans. As she suggests, you can prevent this bloating by using optimize for ad hoc workloads. If you are finding the need to do this often, I would say that scheduling freeproccache as a job is a band-aid, not a solution.

In order to clear out a "bad" plan, first you need to identify the "bad" plan. This could be a plan that exceeds a certain size and/or has not been executed in some time, or that you have identified by a long-running query, etc. Unfortunately it is not simple to identify a plan that is a victim of parameter sniffing unless you already know the query or queries that are impacted. Let's assume you want to find the oldest plans in the cache that hasn't been run in over a week:

;WITH x AS 
(
    SELECT TOP 10 
        qs.[sql_handle], qs.plan_handle,
        txs = qs.statement_start_offset, 
        txe = qs.statement_end_offset,
        [size] = cp.size_in_bytes, 
        [uses] = SUM(cp.usecounts), 
        [last] = MAX(qs.last_execution_time)
    FROM 
        sys.dm_exec_query_stats AS qs
    INNER JOIN 
        sys.dm_exec_cached_plans AS cp 
        ON qs.plan_handle = cp.plan_handle
    WHERE 
        qs.last_execution_time < DATEADD(DAY, -7, CURRENT_TIMESTAMP)
    GROUP BY 
        qs.[sql_handle], qs.plan_handle, cp.size_in_bytes,
        qs.statement_start_offset, qs.statement_end_offset
    ORDER BY 
        [size] DESC
) 
SELECT 
    x.plan_handle, 
    size, uses, [last],
    [statement] = COALESCE(NULLIF(
        SUBSTRING(t.[text], x.txs/2, 
          CASE WHEN x.txe = -1 THEN 0 ELSE (x.txe - x.txs)/2 END
          ), ''), t.[text]) 
FROM x 
CROSS APPLY sys.dm_exec_sql_text(x.[sql_handle]) AS t;

Now you need to verify that you really want to clear out this plan. For example, if you recognize that query as something the CEO might run tomorrow, maybe it's best to leave it there. If you want to clear the plan, you can clear it directly by saying:

DBCC FREEPROCCACHE([paste plan handle from above query here]);

This sounds like a lot more work than running DBCC FREEPROCCACHE globally, but if you have a lot of good plans in the cache, it is certainly going to be better for your users overall.

Still, this really sounds like a band-aid. If your cache is filling up with junk and performance goes in the toilet until you free the cache up, you need to look at a higher level at the architecture, how queries are submitted, etc. This is the behavior I'd expect from the very first iteration of LINQ2SQL, where it would cache a version of a plan for a query for each string argument that was a different length. So if you had a parameter of 'January' you'd get a different plan than with a parameter of 'February' because it would define the data type as VARCHAR(7) vs. VARCHAR(8). Pretty sure that behavior is fixed but I don't know enough about your environment / application to suggest where precisely to look for "bad ideas."

Comments

[Adir D]

Disregard optimize for ad hoc workloads - this was introduced with SQL Server 2008.