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.