A buddy of mine told me today that instead of bouncing SQL Server, I could simply detach and then re-attach a database and this action would clear the given database's pages and plans from cache. I disagreed and provide my evidence below. If you disagree with me or have a better rebuttal, than by all means supply it.
I am using AdventureWorks2012 on this version of SQL Server:
SELECT @@VERSION; Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
Having loaded the database, I run the following query:
Firstly, run Jonathan K's AW fattening script found here:
--------------------------- -- Step 1: Bpool Stuff? --------------------------- USE [AdventureWorks2012]; GO 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 WHERE b.database_id = DB_ID() AND p.object_id > 100 GROUP BY p.object_id , p.index_id ORDER BY buffer_count DESC;
The result is shown here:
Detach and re-attach the database and then re-run the query.
--------------------------- -- Step 2: Detach/Attach --------------------------- -- Detach USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2012' GO -- Attach USE [master]; GO CREATE DATABASE [AdventureWorks2012] ON ( FILENAME = N'C:\sql server\files\AdventureWorks2012_Data.mdf' ) , ( FILENAME = N'C:\sql server\files\AdventureWorks2012_Log.ldf' ) FOR ATTACH; GO
What is in the bpool now?
--------------------------- -- Step 3: Bpool Stuff? --------------------------- USE [AdventureWorks2012]; GO 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 WHERE b.database_id = DB_ID() AND p.object_id > 100 GROUP BY p.object_id , p.index_id ORDER BY buffer_count DESC;
And the result:
Are all the reads logical at this point?
-------------------------------- -- Step 4: Logical Reads Only? -------------------------------- USE [AdventureWorks2012]; GO SET STATISTICS IO ON; SELECT * FROM DatabaseLog; GO SET STATISTICS IO OFF; /* (1597 row(s) affected) Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads 0, read-ahead reads 768, lob logical reads 94, lob physical reads 4, lob read-ahead reads 24. */
And we can see that the buffer pool was not totally blown away by the detach/attach. Seems like my buddy was wrong. Does anyone disagree or have a better argument?
Another option is to offline and then online the database. Let us try that.
-------------------------------- -- Step 5: Offline/Online? -------------------------------- ALTER DATABASE [AdventureWorks2012] SET OFFLINE; GO ALTER DATABASE [AdventureWorks2012] SET ONLINE; GO --------------------------- -- Step 6: Bpool Stuff? --------------------------- USE [AdventureWorks2012]; GO 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 WHERE b.database_id = DB_ID() AND p.object_id > 100 GROUP BY p.object_id , p.index_id ORDER BY buffer_count DESC;
It appears that the offline/online operation worked a lot better.
Best Answer
I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...
Your test is flawed.
What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.
Yes. You're interpreting
physical reads 0
as meaning there were not any physical readsAs described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.
None of the pages required to satisfy your query were in memory until read-ahead put them there.
As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.