Does backup verification impact plan cache?

  • I recently noticed a drop in performance on one of my database servers. Initially, I suspected this was because of competition for resources since the verify backup portion of my maintenace routine was running shortly before the users reported the problem. Now, I am not so sure- the actual report came in about 30 minutes after the maintenance had finished, and the user seems sure that the problem was detected AFTER the restore verify_only had completed. I captured some status and found that the buffer cache hit ratio was WAY below the normally observed baseline.

    I recently read from MS:

    "Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. As of SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval. "

    Do we know whether the RESTORE VERIFY_ONLY clears the plan cache?

  • This query can give you an idea what cached plans you have on the server:

    SELECT a.*, text

    FROM sys.dm_exec_cached_plans a

    CROSS APPLY sys.dm_exec_sql_text(plan_handle)

    ORDER BY a.usecounts DESC;

    GO

    This one gives you the sizes of all the cached plans.

    SELECT sum(size_in_bytes) as cached_plan_ToTal_size

    FROM sys.dm_exec_cached_plans

    You can run the above two queries Before and After the RESTORE VERIFY_ONLY operation to compare what is the effect.

  • That only occurs when a restore is run. It has to occur because after a database is restored, there is no way that SQL Server can be assured that what's in the cache accurately reflects the code in the database (and stats & indexes & tables...).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply