May 31, 2017 at 1:42 am
Checkpoint writes dirty pages from buffer cache to disk ,my question is when a page is written to disk the data no more exists in cache and if the page is frequently modified then SQL Server have to call the page from disk after every checkpoint? or the page will stay in buffer as a fresh page(not modified) if yes then based on what criteria?
May 31, 2017 at 2:31 am
You can try this for yourself - not on a production server, though. Use sys.dm_os_buffer_descriptors to see what pages of data are in memory. Use DBCC PAGE to inspect the contents of any page you are interested in.
John
May 31, 2017 at 3:44 am
Rechana Rajan - Wednesday, May 31, 2017 1:42 AMmy question is when a page is written to disk the data no more exists in cache and if the page is frequently modified then SQL Server have to call the page from disk after every checkpoint?
Checkpoint writes dirty pages to disk, it doesn't remove pages from cache.
If you save a Word document, Word doesn't close it as a result of the save. Same thing here.
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
June 1, 2017 at 1:06 am
GilaMonster - Wednesday, May 31, 2017 3:44 AMRechana Rajan - Wednesday, May 31, 2017 1:42 AMmy question is when a page is written to disk the data no more exists in cache and if the page is frequently modified then SQL Server have to call the page from disk after every checkpoint?Checkpoint writes dirty pages to disk, it doesn't remove pages from cache.
If you save a Word document, Word doesn't close it as a result of the save. Same thing here.
Thanks a lot Gail. The example was simple but very powerful that it is easy to understand. U r awesome.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply