April 23, 2009 at 1:44 pm
If you want to compare physical reads you need to clear the data cache, not the procedure cache.
Checkpoint
DBCC DROPCLEANBUFFERS
The checkpoint will write all dirty (modified) pages to disk. The DROPCLEANBUFFERS will then remove all clean buffer pages from memory.
Do not run this on a production server. It will cause performance degradation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2009 at 2:06 pm
Did I get that right - if I call DBCC DROPCLEANBUFFERS without calling CHECKPOINT, there is a risk of data loss?
I thought SQL Server wrote changes to disk immediately..
April 23, 2009 at 2:19 pm
fayilt (4/23/2009)
Did I get that right - if I call DBCC DROPCLEANBUFFERS without calling CHECKPOINT, there is a risk of data loss?
No, not at all.
DROPCLEANBUFFERS, as it's name says will only drop clean buffers from memory. Any buffer pages that are modified (dirty) will no be thrown out of memory. They can't be, the changes have to be written to disk first.
I suggest checkpoint first to ensure that all data pages are removed from memory.
I thought SQL Server wrote changes to disk immediately..
No.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply