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

104 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
  21. هل تبحث عن شركة متخصصة فى مكافحة الحشرات نحن نقدم لكم افضل شركة مكافحة حشرات فى الدمام كما انه تقدم خدمة متميزة ايضا وهى تنظيف المنازل والمجالس والفلل والقصور وسبب تقديم هذه الخدم انه تهتم بالاناقة والمنظر الخلاب والصحة العامة خدمات شركتنا كاتالى شركة تنظيف منازل بالدمام
    شركة ركن الاندلس من افضل الشركات التى لها باع طويل فى مجال الخدمات المنزلية ولانه افضل شركة متخصصة نقدم لكم اليوم بعض من خدمتنا المميزة مثل شركة تنظيف بالقصيم
    شركة تنظيف خزانات بالقصيم
    شركة تنظيف مجالس بالقصيم
    شركة نقل عفش بالرياض
    شركة تنظيف بالرياض
    شركة تسليك مجارى بالرياض
    شركة نقل عفش بالرياض
    شركة تنظيف منازل بالرياض

    ReplyDelete
  22. Packers and movers in dwarka delhi@
    http://www.transmoving.in/packers-movers-dwarka delhi.html
    Packers and movers in faridabad@
    http://www.transmoving.in/packers-movers-faridabad.html
    Packers and movers in goa@
    http://www.transmoving.in/packers-movers-goa.html
    Packers and movers in indore@
    http://www.transmoving.in/packers-movers-indore.html
    Packers and movers in jaipur@
    http://www.transmoving.in/packers-movers-jaipur.html
    Packers and movers in thane@
    http://www.transmoving.in/packers-movers-thane.html
    Packers and movers in varanasi@
    http://www.transmoving.in/packers-movers-varanasi.html
    Packers and movers in surat@
    http://www.transmoving.in/packers-movers-surat.html

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

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

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

    Webseiten Gestaltung Lüdenscheid

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

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

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



    ReplyDelete
  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.
    تنظيف موكيت شمال الرياض
    تنظيف موكيت شرق الرياض
    تنظيف موكيت غرب الرياض
    تنظيف موكيت جنوب الرياض
    تنظيف بيوت شعر بالرياض


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


    ReplyDelete
  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

    ReplyDelete
  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
    شركة تنظيف بخميس مشيط

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



    ReplyDelete
  33. This comment has been removed by the author.

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  36. quite informative, thanks for sharing with us

    if you are interested in reading my blogs
    - http://www.learndigital.co

    ReplyDelete
  37. Thanks for sharing the information, Salesforce experts a lot of openings in multi-level companies, for more information n
    Salesforce Training
    Professional Salesforce CRM Training
    Salesforce Training online in India

    ReplyDelete
  38. Amazing Blog,Thanks for sharing the valuable post,you have put good efforts.keep on sharing the latest updates. Best software Training institute in Bangalore

    ReplyDelete
  39. Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts.
    Devops training in Chennai

    Devops training in Bangalore

    Devops training in Pune

    Devops training in Online

    Devops training in Pune

    Devops training in Bangalore"

    Devops training in tambaram"

    Devops training in Sollonganallur"

    ReplyDelete
  40. A very nice guide. I will definitely follow these tips. Thank you for sharing such detailed article. I am learning a lot from you.

    java training in chennai | java training in bangalore

    java online training | java training in pune

    selenium training in chennai

    selenium training in bangalore

    ReplyDelete
  41. Hello! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done an outstanding job.
    Best AWS Training in Chennai | Amazon Web Services Training in Chennai

    AWS Training in Bangalore | Amazon Web Services Training in Bangalore

    Amazon Web Services Training in Pune | Best AWS Training in Pune

    ReplyDelete
  42. Packers and Movers Gurgaon Provide Reliable, Safe and Certified Service Provider list, Get Free ***Best Price Quotaition and Compare Charges. ✔✔✔ Hassle free Household Shifting Services, High Quality packing Material, Office Relocation, Car Transportaion, ###Local and Domestic Shifting Service @
    Packers And Movers Gurgaon

    ReplyDelete
  43. hi very good article
    thanks for sharing keep up the good work

    ReplyDelete
  44. Thank you again for a wonderfully informative item. So much to do…..

    ac servcie ambattur

    ReplyDelete
  45. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging

    Azure Training
    Azure Training in Chennai

    ReplyDelete
  46. Manish Packers and Movers Pvt Ltd as a Services providing company can make all the difference to your Home Relocation experience. Indore based Company which offers versatile solutions, Right team that easily reduce the stress associated with a Household Shifting, Vehicle Transportation. we help things run smoothly and reduce breakages and offer you seamless, Affordable, Reliable Shifting Services, Compare Shifting Charges, Visit :
    Manish Packers and Movers Ptv Ltd @ https://www.manishpackersmoversindore.in/
    Packers and Movers Indore @ https://www.manishpackersmoversindore.in/packers-and-movers-indore.html
    Manish Packers Indore @ https://www.manishpackersmoversindore.in/manish-packers-and-movers-indore.html
    Packers and Movers Gurgaon @ https://www.manishpackersmoversindore.in/packers-movers-gurgaon.html
    Packers and Movers Kolkata @ https://www.manishpackersmoversindore.in/packers-movers-kolkata.html
    Packers and Movers Mumbai @ https://www.manishpackersmoversindore.in/packers-movers-mumbai.html
    Packers and Movers Bhopal @ https://www.manishpackersmoversindore.in/packers-movers-bhopal.html
    Packers and Movers Nagpur @ https://www.manishpackersmoversindore.in/packers-movers-nagpur.html
    Packers and Movers Raipur @ https://www.manishpackersmoversindore.in/packers-movers-raipur.html
    Packers and Movers Ahmedabad @ https://www.manishpackersmoversindore.in/packers-movers-ahmedabad.html

    ReplyDelete
  47. Hire Manish Packers and Movers Pvt Ltd in India for hassle-free Household Shifting, Office Relocation, Car Transportation, Loading Unloading, packing Unpacking at affordable Price Quotation. Top Rated Safe and Secure Service Providers who can help you with 24x7 and make sure a Untroubled Relocation Services at Cheapest/Lowest Rate. Visit More :-
    Manish Packers and Movers Pvt Ltd
    Packers and Movers Bangalore
    Packers and Movers Gurgaon
    Packers and Movers Indore
    Packers and Movers Kolkata
    Packers and Movers Mumbai
    Packers and Movers Nagpur
    Packers and Movers Ahmedabad
    Manish Packers in Indore
    Manish Packers and Movers Pvt Ltd Sitemap

    ReplyDelete
  48. nice work keep it up thanks for sharing the knowledge.Thanks for sharing this type of information, it is so useful.
    block adhesive manufacturer in delhi

    ReplyDelete
  49. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.I wanted to leave a little comment to support you and wish you a good continuation. Wishing you the best of luck for all your blogging efforts read this.
    python training in velachery
    python training institute in chennai

    ReplyDelete
  50. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.
    Devops Training in pune

    ReplyDelete
  51. I would like to say thank you for the amazing details and concepts you are sharing in this.

    Mobile Apps Training
    Mobile Apps Training in Chennai

    ReplyDelete
  52. This comment has been removed by the author.

    ReplyDelete
  53. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    rpa interview questions and answers
    automation anywhere interview questions and answers
    blueprism interview questions and answers
    uipath interview questions and answers
    rpa training in chennai

    ReplyDelete
  54. Great reviews on this article.Once again thanks for your great share.
    web design company in chennai
    web development company in chennai

    ReplyDelete
  55. Very good post which I really enjoyed reading. It is not every day that I have the possibility to see something like this.




    accounting services in greenwood
    CPA in greenwood
    Accounting services in Houston
    Accountant in Chicago

    ReplyDelete
  56. Excellent blog, good to see someone is posting quality information.
    DevOps Online Training

    ReplyDelete
  57. Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
    angularjs online Training

    angularjs Training in marathahalli

    angularjs interview questions and answers

    angularjs Training in bangalore

    angularjs Training in bangalore

    angularjs Training in chennai

    ReplyDelete
  58. It is common to find ourselves in situation where the dates to vacate your facilityis predetermined and you are not ready yet to move into the would-be facility. The situation may apply to both – home as well as office. In such situations, you could opt for our storage and warehousing services.
    packers and movers coimbatore
    packers and movers in nashik
    packers and movers in panchkula
    packers and movers in zirakpur
    packers and movers allahabad
    packers and movers in amritsar
    packers and movers mangalore
    packers and movers bilaspur
    packers and movers in ajmer
    packers and movers pondicherry

    ReplyDelete
  59. I am very glad to visit your website. I extremely grateful that you perform this piece of writing very simply

    TekSlate Online Trainings

    ReplyDelete
  60. Your blog was really amazing and informative. Thanks for sharing with us. Visit for
    Web Designing Company in Delhi
    SEO Service in Delhi

    ReplyDelete
  61. Interesting article..... Thanks for sharing this article.....
    DevOps Online Training

    ReplyDelete
  62. Really a great post. Appreciate the effort in educating us.
    DevOps Online Training

    ReplyDelete
  63. Amazing Article ! I have bookmarked this article page as i received good information from this. All the best for the upcoming articles. I will be waiting for your new articles. Thank You ! Kindly Visit Us @ Coimbatore Travels | Ooty Travels | Coimbatore Airport Taxi | Coimbatore taxi | Coimbatore Taxi

    ReplyDelete