Tuesday, 23 July 2013

SQL Server : Understanding Differential Change Map (DCM) Page and Differential Backups

In our earlier posts, we have discussed about different types of pages.Data Pages, GAM and SGAM,PFS page and IAM page. In this post, we will try to understand the Differential Change Map (DCM) pages and differential backups.

Differential Change Map(DCM)SQL Server uses Differential Changes Map (DCM) page  to track extent modified after the last full backup operation. DCM page is the 6th page in the data file.DCM page track the extents modified after the full backup.DCM has a bit for every extent it tracks. If the bit is 1, the corresponding extent is modified after the last full backup, if the bit is 0, there is no change in the corresponding extent after the last full backup.A DCM page can hold information of around 64000 extents. Precisely DCM pages repeat after every 511232 pages .One DCM page can track the changes of exactly 63904 extents.The second DCM page will be 511238th page.

Differential backups read the DCM pages to determine which extents have been modified after the last full backup. This greatly reduces the number of pages that a differential backup must scan. The length of time that a differential backup runs is proportional to the number of extents modified since the last full backup and not the overall size of the database.

Let us see an example:



From the output it is clear that, four extents which starts from 0th page to 32th page have changed after the last full backup. It is not necessary to have changes in all these 32 pages. But we can clearly say that , there were changes in at least four  pages , one page from each extents.This might happen due to the changes triggered by full backup command in the internal tables

Now let us try to add some data to this database and again see the DCM page

SELECT * INTO mydb..SalesOrderDetail FROM AdventureWorks2008.sales.SalesOrderDetail


As part of select we have create a table and inserted data into that. It will trigger changes in many of the system tables. As a result we can see many extents are marked as modified after the last full backup.Till the page number 1:176 , the changes are due to the internal objects modification. Pages from 1:176 to 1:1671 are got modified due to data inserted into the SalesOrderDetail table.When we take differential backup, SQL server just read the DCM pages and include the pages marked as changed in the differential backup. For example as we mentioned earlier,there might be only changes to four pages in the first entry (1:0 - 1:24) but differential backup include all 32 pages in the backup. Let us take a differential backup.




After the differential backup , there is no change in the DCM pages except two more extents are marked as changed.Before the differential backups, the extents 72-80 and 136-144 were marked as not changed. After the differential backup, these two extents are marked as changed. Refer Fig 2 and Fig 3.This is happened due the changes triggered by differential backup in the internal tables.From this it is clear that, differential backup will not clear the DCM page. In Short differential backup always include all the changes happened from the last full backup. Not the changes from the last differential backup.There is a misconception that, the differential backup contain the changes happened from the last differential backup which is completely wrong.  

If we look into the FIG 3, we can see that the size of the differential backup file is 13452 KB.Let us try ti calculate it using the DCM page information.From the Fig 3 , we can see that DCM page has marked 1616 pages as changed. The calculation is given below


The size of the page is 8 KB. So the 1616 page account to 12928 KB (1616X8). Which is very close to the size of the differential backup file. The difference is overhead of the file header etc.

Now Let us take a full backup and see what will happen.




Now SQL server cleared DCM page.Everything is marked as Not Changed except the four extents 1:0 -1:32. No this backup will work as base for all subsequent differential backups.There is an option to take full backup with out disturbing the existing backup chain. 


In this scenario SQL server will not reset the DCM pages. Still MydbFull.bak will be the base full backup for subsequent differential backups. This will be helpful in some scenario where you need to take full backup but do not want disturb the backup chain.

To summaries, SQL server tracks the changes to the extents through the differential change map(DCM) page.While taking differential backup, SQL server just backup the extents marked as changed in the DCM pages. It helps SQL server to speed up the differential backup process with out scanning through all pages to check it is modified or not after the last full backup.Differential backup will not clear the DCM page and differential backup contain all the changes happened from the last full backup. While taking full backup, SQL server reset the bits in DCM page to mark it is not changed.

If you liked this post, do like my page on FaceBook


  1. Good read, thanks for sharing

  2. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates…
    SEO Training in Chennai

  3. Thank you for having taken your time to provide us with your valuable information relating to your stay with us.we are sincerely concerned. Keep it up. And provide us more blog
    SAP ABAP Training in Chennai

  4. This blog having the details of Processes running. The way of runing is explained clearly. The content quality is really great. The full document is entirely amazing.
    Thank you very much for this blog.
    Android Training in Chennai

  5. This article creates a new hope and inspiration with in me. Thanks for sharing article like this. The way you have stated everything above is quite awesome. Keep blogging like this. Thanks.

    SAP training in Chennai

  6. I really enjoyed reading your blog, you have lots of great conten.
    Packers And Movers Gurgaon

  7. First is to thank you for all this informative posts you give us for free; i bet all of us are happy.
    Such a great idea of yours! You have been a big help for me. Thanks a lot. more post for interesting topic. Great!

    SAP training in Chennai

  8. This article creates a new hope and inspiration with in me. Thanks for sharing article like this. The way you have stated everything above is quite awesome. Keep blogging like this. Thanks.

    SAP training in Chennai

  9. Great and useful article. Creating content regularly is very tough. Your points are motivated me to move on.

    SEO Company in Chennai

  10. SAP HANA training in hyderabad,This is the best path You can Learn COmplete Course with full fledge knowledge of SAP.
    SAP HANA online training


  11. I loved the way you discuss the topic great work thanks for the share, Let me share this, vmware training in pune

  12. Thanks for sharing the post. This is a wonderful and amazing article.I am very glad to be here.Thanks for sharing.
    Packers And Movers Bangalore

  13. very good post which I really enjoy reading. It is not every day that I have the possibility to see something like this.
    Packers and Movers Mumbai

  14. من المعروف ان نظافة المنازل من بين اهم الخدمات الضرورية في التنظيف والترقية بمنازلنا الى مصاف المنازل العصرية والتي عرفت تنظيفا عصريا من شانه ان يوفر لساكنته ظروفا حياتية راقية ولابد من توفر هيئة تختص في المجال مع تقديم ضمانات متكاملة من شانه ان تجعل الافراد يضمنون نجاح خدمة التنظيف ولا حاجة للمزيد من ضياع الاموال عبر طلبات خدمة نظافة المنازل التي لا تلبي حاجياتهم ولا تتماشى مع رغباتهم لأن العديد من شركات تنظيف المنازل تسعى فقط الى عرض خدماتها عبر طرق ترويجية فحسب من اجل كسب المال فقط دون مراعاة الوازع الاخلاقي والضمير المهني الذي يحتم على مدراء مثل هاته الشركات ان يسعوا خلف ارضاء العملاء ليس اكثر من هذا عبر اتقان تنظيف المنازل شركة تنظيف خزانات بالرياض
    شركة تنظيف مجالس بالرياض
    شركة نظافة عامة بالرياض
    شركة رش مبيد بالرياض
    شركة نقل عفش بالرياض
    شركة مكافحة حشرات بالرياض
    شركة تنظيف شقق بالرياض
    شركة تنظيف منازل بالرياض

  15. It is really very excellent blog; I find all of your blogs were amazing and awesome. The way to get expert tips from everyone, not only I like that posts all peoples like that post. Because of all given info was wonderful and it's very helpful for everyone.
    Oracle Fusion HCM Technical Training


  16. Learning new technolgy would help oneself at hard part of their career. And staying updated is the only way to survive in current position. Your content tells the same. Thanks for sharing this information in here. Keep blogging like this.

    iOS App Development Company

  17. Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care and we take your comments to heart.As always, we appreciate your confidence and trust in us
    Java Training in Chennai


  18. شركة نقل عفش
    اهم شركات مكافحة حشرات بالخبر كذلك معرض اهم شركة مكافحة حشرات بالدمام والخبر والجبيل والخبر والاحساء والقطيف كذلك شركة رش حشرات بالدمام ومكافحة الحشرات بالخبر
    شركة مكافحة حشرات بالدمام
    شركة تنظيف خزانات بجدة الجوهرة من افضل شركات تنظيف الخزانات بجدة حيث ان تنظيف خزانات بجدة يحتاج الى مهارة فى كيفية غسيل وتنظيف الخزانات الكبيرة والصغيرة بجدة على ايدى متخصصين فى تنظيف الخزانات بجدة
    شركة تنظيف خزانات بجدة
    شركة كشف تسربات المياه بالدمام
    شركة نقل عفش واثاث


  19. شركة نقل اثاث بالدمام التفاؤل شركة نقل اثاث بالخبر كما انها افضل شركة نقل اثاث بالجبيل نقل عفش واثاث بالجبيل والخبر والقطيف والدمام
    شركة نقل اثاث بالدمام
    شركة نقل اثاث بالجبيل

  20. شركة نقل عفش واثاث بالدمام ابيات الشرقيه لخدمات نقل العفش والاثاث بالدمام
    شركة نقل عفش بالدمام
    نقل عفش بالخبر
    شركة نقل اثاث الدمام
    نقل عفش الدمام
    نقل عفش بالدمام
    ان اردت نقل عفش منزلك بالدمام ابيات الشرقية من اهم شركات نقل العفش بالدمام والخبر والجبيل والقطيف والاحساء

  21. هل تبحث عن شركة متخصصة فى مكافحة الحشرات نحن نقدم لكم افضل شركة مكافحة حشرات فى الدمام كما انه تقدم خدمة متميزة ايضا وهى تنظيف المنازل والمجالس والفلل والقصور وسبب تقديم هذه الخدم انه تهتم بالاناقة والمنظر الخلاب والصحة العامة خدمات شركتنا كاتالى شركة تنظيف منازل بالدمام
    شركة ركن الاندلس من افضل الشركات التى لها باع طويل فى مجال الخدمات المنزلية ولانه افضل شركة متخصصة نقدم لكم اليوم بعض من خدمتنا المميزة مثل شركة تنظيف بالقصيم
    شركة تنظيف خزانات بالقصيم
    شركة تنظيف مجالس بالقصيم
    شركة نقل عفش بالرياض
    شركة تنظيف بالرياض
    شركة تسليك مجارى بالرياض
    شركة نقل عفش بالرياض
    شركة تنظيف منازل بالرياض

  22. Packers and movers in dwarka delhi@ delhi.html
    Packers and movers in faridabad@
    Packers and movers in goa@
    Packers and movers in indore@
    Packers and movers in jaipur@
    Packers and movers in thane@
    Packers and movers in varanasi@
    Packers and movers in surat@

  23. I read the post and I have really enjoyed your blogs posts.looking for the next post.
    Digital Marketing Training In Bangalore.

  24. I really enjoyed reading the Post. It was very informative and useful for me.
    Selenium Training In Bangalore

  25. Interesting post! This is really helpful for me. I like it! Thanks for sharing!

    Webseiten Gestaltung Lüdenscheid

  26. Nice information about sap. i am looking SAP HANA Online Training with real time project.

  27. Interesting post! This is really helpful for me. I like it! Thanks for sharing!
    seo lüdenscheid

  28. مؤسسة ركن كلين 0551425199 كــ ارخص شركة تنظيف منازل ,فلل ,شقق,بيوت,موكيت,سجاد بالرياض تقدم
    تنظيف الموكيت بالرياض
    Carpet cleaning is one of the inevitable things that must be done regularly to keep your home look and look beautiful and elegant. Most of the time our company Cleaning Company Mukait Riyadh is keen to invest excellent tools in getting the best results in addition to the equipment for cleaning and drying which makes the furniture clean in a very short time.

  29. Carpet cleaning is one of the inevitable things that must be done regularly to keep your home look and look beautiful and elegant. Most of the time our company Cleaning Company Mukait Riyadh is keen to invest excellent tools in getting the best results in addition to the equipment for cleaning and drying which makes the furniture clean in a very short time.
    تنظيف موكيت شمال الرياض
    تنظيف موكيت شرق الرياض
    تنظيف موكيت غرب الرياض
    تنظيف موكيت جنوب الرياض
    تنظيف بيوت شعر بالرياض

  30. This is very good blog for learners, Thanks for sharing valuable content on MSBI Online Training

  31. Hi,
    THANKS SO MUCH for sharing this! I would love to buy you a coffee since I now won’t be up all night that has been driving me crazy (until now!!). I just wish I knew what was going wrong but so glad it’s in the right place now! Thanks again:)
    Using excel connector can we format particular column as numeric? I have a data table which I get imported from my c# code to excel connector and its working fine. but in C# code also I am not able to format this particular column to numeric. Kindly assist.

    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
    Please keep providing such valuable information.
    Thank you,
    Kevin Martin

  32. Shorouk Shorouk for all household services from Cleaning of apartments, cleaning apartments, insect control and sewerage, offering you all the domestic services at the cheapest prices. Contact us to request the service you want Dear customer
    شركة تنظيف بخميس مشيط

    شركة تنظيف خزانات بالاحساء
    شركة تنظيف خزانات بابها
    شركة تنظيف خزانات بالاحساء

  33. This comment has been removed by the author.

  34. This is a much needed information thank you for sharing and it's very helpful to know about this information. Thanks for sharing it MSBI Online Training

  35. I strongly believe that there will be great opportunities for those who looked into this area, I would like to read this blog regularly to get more important stuff...
    Best Online Software Training Institute | SQL Server Training