October 7, 2013 at 12:48 pm
Per BOL: http://msdn.microsoft.com/en-us/library/bb522682.aspx
The plan cache for the instance of SQL Server is cleared by setting one of the following options
Which includes OFFLINE and ONLINE.
I don't doubt it, but we like to test things. So far, setting the database offline, even for 10 minutes, and bringing it back online did not appear to clear the cache as evidenced by no differences before and after in sys.dm_exec_query_stats, sys.dm_exec_cached_plans, and:
SELECT
OBJECT_NAME(p.object_id) AS [ObjectName]
, p.object_id
, p.index_id
, COUNT(*) / 128 AS [buffer size(MB)]
, COUNT(*) AS [buffer_count]
FROM
sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
GROUP BY
p.object_id
, p.index_id
ORDER BY
buffer_count DESC;
Any thoughts on what we are doing wrong to test this? We tested on a 2008R2 and 2012 instance.
Jared
CE - Microsoft
October 7, 2013 at 2:11 pm
October 7, 2013 at 2:33 pm
did you get the 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". in the errorlog?
I must admit I cannot remember ever seeing that when offlining databases.
---------------------------------------------------------------------
October 7, 2013 at 2:36 pm
george sibbald (10/7/2013)
did you get the 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". in the errorlog?I must admit I cannot remember ever seeing that when offlining databases.
We did not. That was another concern for us.
Jared
CE - Microsoft
October 7, 2013 at 2:49 pm
well if it does not I guess that is a good thing. 🙂
All I can think of is to run dbcc proccache immediately before and after or run profiler with the SP:cacheremove event to try and trap a proc cache clear.
If those do not pick it up it cannot be happening and MS are misleading us.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply