Does restoring database clear buffer cache and stored proc cache

Question

on our development server, we refresh database (restore from production backup) every week. my question is does restore db clear its buffer cache/stored procedure cache from sql memory? Does it have the same/similar effect of DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS

Thanks.

• Kermit: If you're restoring from a backup, then I don't believe it does. I could be wrong and will happily hear why.
• Mark Storey-Smith: Related/similar: Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database?

Answer 1

Yes. The buffer and plan cache associated with the database before restore are not related to the database post-restore, so they will be cleared. To all intents and purposes the restored database is a completely separate, unrelated entity. There is nothing in cache that can be reused.

Answer 2

Interesting question and (now I see that @MarkStorey-Smith has answered I hesitate to answer - so accept his since he answered first) I decided to do some checking. I have a database named analysis on one of my local VMs. So I ran this script (more information on the buffer and the basis for my queries can be found here):

SELECT database_id 
 FROM sys.databases
 WHERE name = 'analysis';
GO
-- database_id = 7
 DECLARE @total_buffer INT;
 SELECT @total_buffer = cntr_value
 FROM sys.dm_os_performance_counters
 WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
 AND (counter_name = 'Total Pages' --this is 2008
 OR counter_name = 'Database pages'); -- this is 2012
 ;WITH src AS (
 SELECT
 database_id, DB_Buffer_Pages = COUNT_BIG(*)
 FROM sys.dm_os_buffer_descriptors
 --WHERE database_id BETWEEN 5 AND 32766
 GROUP BY database_id
 )
 SELECT 
 @@SERVERNAME AS InstanceName,
 GETDATE() AS collection_date,
 database_name = DB_NAME(mem.database_id), 
 db_buffer_pages, 
 cached_size_mb,
 db_buffer_pct, 
 cpu_time, 
 cpu_percent_usage, 
 total_io_mb, 
 io_percent 
 FROM ( 
 SELECT
 database_id,
 db_buffer_pages = DB_Buffer_Pages,
 cached_size_mb = DB_Buffer_Pages / 128.0,
 db_buffer_pct = CONVERT(DECIMAL(6,3),
 DB_Buffer_Pages * 100.0 / @total_buffer) 
 FROM src
 ) mem
 JOIN (
 SELECT 
 database_id, cpu_time, 
 CAST(cpu_time * 1.0 / SUM(cpu_time) OVER() * 100.0 AS DECIMAL(5, 2)) AS cpu_percent_usage
 FROM (
 SELECT database_id, SUM(total_worker_time) AS cpu_time
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY (SELECT CONVERT(int, value) AS database_id 
 FROM sys.dm_exec_plan_attributes(qs.plan_handle)
 WHERE attribute = N'dbid') AS F_DB
 GROUP BY database_id) DB_CPU_Stats
 ) cpu
 ON mem.database_id = cpu.database_id
 JOIN (
 SELECT database_id, io_in_mb AS [total_io_mb],
 CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [io_percent]
 FROM (
 SELECT database_id,
 CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
 GROUP BY database_id) Aggregate_IO_Statistics) [io]
 ON mem.database_id = [io].database_id 
 where mem.database_id = 7
GO 
-- 471
USE master
GO
BACKUP DATABASE analysis TO DISK=N'analysis.bak';
GO
BACKUP LOG analysis TO DISK=N'analysis.trn' WITH NORECOVERY;
GO
RESTORE DATABASE analysis FROM DISK=N'analysis.bak' WITH NORECOVERY;
GO
RESTORE DATABASE analysis FROM DISK=N'analysis.trn';
GO
 DECLARE @total_buffer INT;
 SELECT @total_buffer = cntr_value
 FROM sys.dm_os_performance_counters
 WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
 AND (counter_name = 'Total Pages' --this is 2008
 OR counter_name = 'Database pages'); -- this is 2012
 ;WITH src AS (
 SELECT
 database_id, DB_Buffer_Pages = COUNT_BIG(*)
 FROM sys.dm_os_buffer_descriptors
 --WHERE database_id BETWEEN 5 AND 32766
 GROUP BY database_id
 )
 SELECT 
 @@SERVERNAME AS InstanceName,
 GETDATE() AS collection_date,
 database_name = DB_NAME(mem.database_id), 
 db_buffer_pages, 
 cached_size_mb,
 db_buffer_pct, 
 cpu_time, 
 cpu_percent_usage, 
 total_io_mb, 
 io_percent 
 FROM ( 
 SELECT
 database_id,
 db_buffer_pages = DB_Buffer_Pages,
 cached_size_mb = DB_Buffer_Pages / 128.0,
 db_buffer_pct = CONVERT(DECIMAL(6,3),
 DB_Buffer_Pages * 100.0 / @total_buffer) 
 FROM src
 ) mem
 JOIN (
 SELECT 
 database_id, cpu_time, 
 CAST(cpu_time * 1.0 / SUM(cpu_time) OVER() * 100.0 AS DECIMAL(5, 2)) AS cpu_percent_usage
 FROM (
 SELECT database_id, SUM(total_worker_time) AS cpu_time
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY (SELECT CONVERT(int, value) AS database_id 
 FROM sys.dm_exec_plan_attributes(qs.plan_handle)
 WHERE attribute = N'dbid') AS F_DB
 GROUP BY database_id) DB_CPU_Stats
 ) cpu
 ON mem.database_id = cpu.database_id
 JOIN (
 SELECT database_id, io_in_mb AS [total_io_mb],
 CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [io_percent]
 FROM (
 SELECT database_id,
 CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
 GROUP BY database_id) Aggregate_IO_Statistics) [io]
 ON mem.database_id = [io].database_id 
 where mem.database_id = 7
--242

471 Buffer Pages before the restore, 242 afterwards. Unfortunately I didn't aggregate the types of pages in the cache but I daresay that the pages in cache have to do with me running the buffer analysis queries.

Here are the results of the query for pre and post restore analysis of ... analysis.

PRE: enter image description here

POST: enter image description here

Answer 3

Like Swasheck and Mark I started playing when this one came out. Please don't accept this answer, accept Mark's, in fact don't even give me points, I have no screenshots when I saw Swasheck post what I had started working on, but this is too long for a comment.

So the short answer is: Yes, you are fine

That said, I have heard of reports in past versions of SQL where people have seemed to think this was required. So I wanted to be sure and I did a test. I created a database, created some tables and then used Glenn's query to see how much of that database was in the buffer cache before the restore. Before I had about 50.2MB of data, after I had about 1.5MB - this is most likely because of something after the restore, not because of data hanging about.

I also ran several queries before and included table aliases like "foo" so I could find them in the cache using the query below. I saw them all there, did the restore and they were gone.

SELECT st.text, qs. sql_handle, qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
where text like '%foo%'

So I can say for sure in my limited testing on SQL Server 2012 the traces of the DB were gone from the buffer pool and the plan cache completely. Which makes sense because the DB that they related to disappeared, that is the expected behavior.

That said - after a restore one thing you definitely should consider doing is updating your statistics. I like to start with clean stats build up and I really like to see people using production like data in dev and I like starting with clean statistics. So good on you for using real data in dev. (Caveat - sometimes you need to deidentify your data before doing this, but yay for testing with real data distributions and sizes.)

Answer 4

I am glad to report back. restore was done few minutes ago for a VLDB (1TB) (SQL2008R2 SP2CU7). As suspected, the data cache are pretty much gone after restore. So, all the answers above are correct.

PRIOR
ObjectName object_id index_id buffer size(MB) buffer_count
table_1 185767719 1 29434 3767645
table_2 1531152500 1 559 71604
POST 
ObjectName object_id index_id buffer size(MB) buffer_count
table_1 185767719 1 34 4367
table_2 1531152500 1 22 2879

To get the stats, I used a script borrowed/stole from related post hinted by Mark. Very helpful. Thanks.

---

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;