Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

AW Get Fat


---------------------------
-- 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: enter image description 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: enter image description here

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.

enter image description here

share|improve this question
4  
For plans you know you can use DBCC FLUSHPROCINDB(dbid) right? (Not officially documented, though, but sort of in this white paper.) Also note that you are detaching and immediately re-attaching - your buddies might be right if you let some time pass and there's actually some memory pressure from other databases. I'd also love to see what happened if you attached a totally different database with the same name, and what happens the first time you try to query one of those tables that's supposedly in the buffer (might not be) –  Aaron Bertrand Jun 17 '13 at 21:09
    
@AaronBertrand The wager was complete bpool wipe out upon immediate re-attach. I'll try the totally different DB with identical name angle and update the post. –  ooutwire Jun 17 '13 at 21:13
    
I would test (using STATISTICS IO or Plan Explorer) to see whether your first query against DatabaseLog did logical reads only. And if you attach the database to another instance, truncate the database log table, detach, and re-attach. What does the DMV say now? Does it update if you then run a query against that table? I'm betting the DMV just isn't reflecting the real truth right now until something is accessed and triggers a refresh. –  Aaron Bertrand Jun 17 '13 at 21:13
2  
@AaronBertrand I added JK's AW fat sauce and it appears to have altered the result. I have updated the post. I added Paul White's suggestion as well for offline/online; and that appears to work best. –  ooutwire Jun 17 '13 at 21:43
2  
I would suggest rephrasing your question as "what is the least intrusive way to clear plan and buffer pool for an individual database?" instead of "Hey, settle this bet I had with some buddies at work!" Much of your question should actually be part of the answer instead, and your "answer" that is in the question (that offline/online works best) doesn't really fit the title of the question or much of the preamble. –  Aaron Bertrand Jun 17 '13 at 22:32

1 Answer 1

up vote 7 down vote accepted

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.

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?

Yes. You're interpreting physical reads 0 as meaning there were not any physical reads

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.

As 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.

When SQL Server performs a sequential scan of a large table, the storage engine initiates the read ahead mechanism to ensure that pages are in memory and ready to scan before they are needed by the query processor. The read ahead mechanism tries to stay 500 pages ahead of the scan.

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.

share|improve this answer
    
I wonder though what flushes the cache when a DB is dumped. Is LRU set immediately to zero? I need to look into this further. –  ooutwire Jun 18 '13 at 1:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.