Home Dashboard Directory Help

CheckDB takes considerably longer on SQL 2012 vs SQL 2008 (R1 & R2) with many partitions by Dan Gray



Sign in
to vote
Type: Bug
ID: 833842
Opened: 3/14/2014 5:01:59 AM
Access Restriction: Public
User(s) can reproduce this bug


There is a database at one of my clients with a table with circa 10k partitions & two non-clustered indexes on the table.

A checkdb on this database on SQL 2008 R2 used to take in the region of 2hrs to complete.
This database was then migrated to, a more powerful, SQL 2012 server & checkdb took around 26 hours.

If dbcc checkdb (or checktable on the table with the high numbr of partitions) is specified with noindex then performance is equivalent between 2008 & 2012.

I have raised a call with MS & they have acknowledged that there is a "by design" change in SQL 2012 as that does more detailed checks and this slowdown can be attributed to this. MS Support have raised a RFC with the development team to get this looked into.

I'm raising this as a Connect item so there is visibility in the community and to encourage up votes so the RFC gets a higher priority with the Development team.

I have attached a reproduction script. The increased time for checkdb with high number of partitions is also present in SQL 2014 CTP2
Sign in to post a comment.
Posted by James Lupolt on 5/14/2014 at 11:28 PM
The repro script is also at http://pastebin.com/aSBZtnPn
Sign in to post a workaround.
Posted by Dan Gray on 3/14/2014 at 5:02 AM
Specify checkdb with noindex results in equivalent performance between 2008 and 2012