Pages

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:


CREATE DATABASE Mydb

GO
BACKUP DATABASE Mydb TO DISK='D:\mydb.bak'
GO
DBCC TRACEON(3604)
DBCC PAGE('MYDB',1,6,3)


FIG 1
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
DBCC PAGE('MYDB',1,6,3)


FIG 2






















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.

BACKUP DATABASE Mydb TO DISK='D:\Mydbdifferential.bak' WITH DIFFERENTIAL

DBCC PAGE('MYDB',1,6,3)

FIG 3

















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

(32-0)+(64-48)+(88-72)+(168-112)+(1672-176)=1616

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.

BACKUP DATABASE Mydb TO DISK='D:\MydbFull.bak' 

DBCC PAGE('MYDB',1,6,3)

FIG 4








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. 


BACKUP DATABASE Mydb TO DISK='D:\MydbFull_Copy.bak' WITH COPY_ONLY

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

26 comments:

  1. Good read, thanks for sharing
    Dave
    http://eMarkethosting.com

    ReplyDelete
  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…
    Regards
    Aamala
    SEO Training in Chennai

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

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

    ReplyDelete
  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

    ReplyDelete
  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

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


    SEO Company in Chennai

    ReplyDelete
  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



    ReplyDelete




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

    ReplyDelete
  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

    ReplyDelete
  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

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

    ReplyDelete
  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

    ReplyDelete

  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

    ReplyDelete
  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

    ReplyDelete

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

    ReplyDelete

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

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

    ReplyDelete