Geek Answers Handbook

DBCC freeproccache?

We recently had a performance issue, and this was fixed by running DBCC freeproccache ... Now we have many more questions to answer;

  • What made caching procedures obsolete?
  • If the indexes or statistics from which the date is out of date, why the request was not to recompile itself?
  • Is it good to plan DBCC freeproccache as a job?
  • Is there a way to identify potential outbound query plans?
  • Is there a way to identify an abusive request?

Any help is appreciated!

+5
source share
1 answer

, . . ( , ). Kimberly Tripp, " adhoc" - sys.dm_exec_cached_plans, , . , , . , , freeproccache - , .

"" , "" . , / - , .. , , , , . , , :

;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;

, . , -, CEO , , . , , :

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

, DBCC FREEPROCCACHE , , , , .

, . , , , , .. , LINQ2SQL, , . , "", , "", VARCHAR(7) vs. VARCHAR(8). , , /, , " ".

+8

More articles:


All Articles