Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

A faster DBCC CHECKDB? Expand / Collapse
Author
Message
Posted Tuesday, August 23, 2011 6:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
I'm glad to see that I'm not the only one having 2nd thoughts about this one.

Don't get me wrong I love the ingenuity behind the idea. But the fact remains that to troubleshoot a corrupt db you need ALL errors and you need more details than just x table's index is screwed.

Bravo on the smarts to come up with this idea and putting it together.

On a personal choice level I won't be using this.
Post #1163928
Posted Tuesday, August 23, 2011 7:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 3,670, Visits: 72,433
I'm with the crowd here, this will not be replacing DBCC CHECKDB on my servers either.

I was more than a busted out script because an error was found.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1163947
Posted Tuesday, August 23, 2011 7:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:12 AM
Points: 48, Visits: 839
Hi everyone

Thanks for your replies.

I feel like a sinner today. I did wonder several times if I should publish this article, but decided it hopefully has more advantages than disadvantages. On a related note, a while back I created a C# utility that opens password protected Excel spreadsheets or Word documents, I even created an article about it, but decided not to publish…

Firstly, I keep thinking I should say “This utility does not replace DBCC CHECKDB”, but then I notice I have, many times.

Sten/Simon, thanks for the code fix! Much appreciated.

Erik, thank you for your suggestions, all very sensible.

Grant, hopefully I’ve mentioned this utility is not a substitute for DBCC CHECKDB enough times for people to be aware. That said, I do understand your concerns. I would hope, in the right hands, this utility will prove very useful.

Let me tell you all why I wrote this utility. I had a 5 terrabyte database, where the weekend DBCC CHECKDB typically took 10 hours to complete, and when there were problems it would sometimes take 48 hours to complete before it gave me any indication of errors. During this time I decided it might be nice to get a head start on what the underlying problems were, and how serious (heap/clustered index problems are much more troublesome). Using this utility, while the associated DBCC CHECKDB was running on another box, allowed me to identify the severity of the error very quickly, and plan/implement corrective action.

Whilst for large databases you might run a DBCC CHECKDB on a weekly basis (typically due to its duration), it should be possible to run this utility on a daily basis, resulting in some degree of confidence of the state of the database’s underlying data, between weekend DBCC CHECKDB checks.

I feel like I need to say it one more time, so here goes… “This utility does not replace DBCC CHECKDB”

Thanks
Ian



Post #1163955
Posted Tuesday, August 23, 2011 7:17 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
That's especially why I commended you on thinking on your feet.

From us (regular posters), we know the avg level on knowledge of people coming here for help (and the state of mind they are in) so we want to make it absolutely cristal clear as of why not to use it. And when to possibly have a utility for it.


That being said I'll keep this in mind if I ever hit this issue like you did.



I don't want to start a new debate over this, but this is also why I use checksum + verify on all backups... and then do a test restore on all of them + checkdb then. I can't go more than 24 hours into coruption before detecting it and then I have 1 month PIT recovery available so I'd be really hard pressed to really lose anything.
Post #1163966
Posted Tuesday, August 23, 2011 9:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
Hey Ian - one thing to check out with your script is what effect it has on the buffer pool. CHECKDB won't flush the buffer pool as all the pages it uses are marked for immediate disfavor andd reuse by the lazywriter. For table/index scans, this only happens if the amount being read if more than 10% of buffer pool memory. That could mean that on some systems the script will flush the buffer pool, causing dire perf issues while it warms up again. Can you run some tests on your system using the sys.dm_io_virtual_file_stats script I blogged a while back?

Again, nice idea.

Cheers


Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #1164117
Posted Tuesday, August 23, 2011 5:16 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 7, 2011 7:06 AM
Points: 4, Visits: 60
have not tested this but in case someone already knows the answer, wouldn't this miss items like data purity, filestream and others mentioned specifically in BOL?

http://msdn.microsoft.com/en-us/library/ms176064.aspx
Post #1164357
Posted Tuesday, August 23, 2011 5:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 8:32 AM
Points: 31, Visits: 247
DBCC rule... thank Ian and Paul too
Post #1164358
Posted Tuesday, August 23, 2011 5:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
sqlmashup (8/23/2011)
have not tested this but in case someone already knows the answer, wouldn't this miss items like data purity, filestream and others mentioned specifically in BOL?

http://msdn.microsoft.com/en-us/library/ms176064.aspx


Yes - it will only find IO subsystem corruptions.


Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #1164362
Posted Wednesday, August 24, 2011 7:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 2:54 PM
Points: 10, Visits: 99
"By clearing the buffer pool, using the command DBCC DROPCLEANBUFFERS, we ensure any data is read from the underlying physical disks, and this is where we want to find any errors."

This sentence is enough to dissuade it's use for me. On a production system that is running 24/7 dropping the data buffers would be terrible for performance. My understanding of DBCC CHECKDB is that it doesn't pollute the data buffers with its reads. Those reads are marked as 'can go away first' so the memory is reused as needed. Obviously Paul knows more on the workings of this but beware of DROPCLEANBUFFERS on a production system.
Post #1164627
Posted Friday, August 26, 2011 8:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 150, Visits: 1,773
How would you title this article to make it more innocuous?

A Non-Production Index Error Checker?

Post #1166524
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse