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.

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.

share|improve this question
2  
If you're restoring from a backup, then I don't believe it does. I could be wrong and will happily hear why. –  Kermit Nov 21 '13 at 22:04
6  
add comment

migrated from stackoverflow.com Nov 21 '13 at 22:00

This question came from our site for professional and enthusiast programmers.

4 Answers

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.

share|improve this answer
    
+1 learned something today –  Kermit Nov 21 '13 at 22:25
add comment

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

share|improve this answer
    
Your screenshots aren't pretty, but here's an upvote. –  Kermit Nov 21 '13 at 22:26
3  
@FreshPrinceOfSO form follows function. –  swasheck Nov 21 '13 at 22:28
add comment

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

share|improve this answer
3  
Have an upvote for being late to the party. –  Kermit Nov 21 '13 at 22:36
3  
Fashionably though.. I mean.. Look at that smile in that old picture. –  Mike Walsh Nov 21 '13 at 22:37
2  
Great answers guys. I will report back when this happens again this weekend (we have few VLDB). we have a SSIS package to do all the heavy lifting, including changing users, identification etc. maybe we should add update stats too... thanks. –  Joy Walker Nov 22 '13 at 3:00
add comment

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;
share|improve this answer
add comment

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.