August 22, 2011 at 10:02 pm
Comments posted to this topic are about the item A faster DBCC CHECKDB?
August 22, 2011 at 11:02 pm
Nice - but really only the equivalent of BACKUP DATABASE ... WITH CHECKSUM.
I would strongly advise not using this for the same reasons I strongly advise not using BACKUP DATABASE ... WITH CHECKSUM as an alternative to DBCC CHECKDB - it will not detect errors introduced by memory problems, SQL Server bugs, or IO subsystem corruptions of pages using torn-page detection.
Bottom line - you can't avoid running DBCC CHECKDB - don't fall into the trap of running something faster. Offload your checks to a non-production server but continue running DBCC CHECKDB - it's the only thing that will find all corruptions.
Thanks
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
August 22, 2011 at 11:09 pm
Thanks for the heads up and advice Paul.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 23, 2011 at 12:15 am
Thanks Paul.
Agreed that this is not an optimal solution. But if I want to know where an error is, and quickly, this method does seem fastest for most IO related errors (it stops immediately there is an error!).
Sometimes, for whatever reasons, there isn’t time or space to backup to another server (although, again agreed, this is the preferred method).
As stated, this is NOT a substitute for DBCC CHECKDB, but a quick ah-hoc method of finding the most common errors very quickly.
Thanks
Ian
August 23, 2011 at 1:05 am
Nice method. Thanks for the good article.
And thanks to Paul Randal for the sage advice.
-- Gianluca Sartori
August 23, 2011 at 1:24 am
Nice.
However when i try i get:
Msg 103, Level 15, State 4, Line 117
The identifier that starts with 'TableName: Land.ATL_Tag_TagVidTrafikplats_RapportSomSkaGoras. IndexName: PK_ATL_Tag_TagVidTrafikplats_RapportSomSkaGoras. IndexI' is too long. Maximum length is 128.
The identifier that includes full table name and index name gets too long. Need to do some minor change and put names somewhere else.
/Sten
August 23, 2011 at 2:15 am
After the following modification to the Dynamic SELECT it works with long tablenames and indexnames.
-- Build SQL to read each page in each index (including clustered index).
SELECT @CheckIndexesSQL = @CheckIndexesSQL + CHAR(10)
+ 'SELECT COUNT_BIG(*) AS [Rows], ''TableName: ' + SchemaName + '.'
+ TableName + ''' AS [Tablename], ''IndexName: ' + ISNULL(IndexName, 'HEAP')
+ '. IndexId: ' + CAST(indid AS VARCHAR(3)) + ''' AS [Index] FROM '
+ QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
+ ' WITH (INDEX(' + CAST(indid AS VARCHAR(3)) + '));'
FROM #IndexDetails
August 23, 2011 at 3:41 am
I've just used this to make it work with large table names and index names, as the max number of characters is 128 for an identifier.
-- Build SQL to read each page in each index (including clustered index).
SELECT @CheckIndexesSQL = @CheckIndexesSQL + CHAR(10) + 'SELECT COUNT_BIG(*) AS [' + LEFT(SchemaName + '.' + TableName + '.'
+ ISNULL(IndexName, 'HEAP') + '. IxId:' + CAST(indid AS VARCHAR(3)), 128) + '] FROM ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
+ ' WITH (INDEX(' + CAST(indid AS VARCHAR(3)) + '));'
FROM #IndexDetails
August 23, 2011 at 4:37 am
Hi,
Reading through the indexes is a smart move! I might steal the idea 🙂
If I may have some improvement suggestions:
1. Instead of index(1), use select * simply, orelse LOB pages are not checked.
2. I'd prefer checking the system tables as well.
3. Watch out for filtered indexes - orelse QP will kill the script, not the IO error 🙂
4. I'd pick a different title. As Paul highlighted, DBCC CHECKDB has no real alternative. So the title is catchy but probably a "Detect Disk Corruptions Superfast" or similar would manage expectations better.
Cheers,
Erik
-- Erik http://blog.rollback.hu
August 23, 2011 at 5:38 am
Excellent code. Nice article. Well explained.
But I have to say, articles like this really do make me nervous. It seems like a very healthy percentage of our fellow database professionals are missing a few fundamentals. Suggesting a way to avoid something as important as DBCC CHECKDB seems rife with potential for disaster. I'd just hate to hear "I was following your advice and now we've lost our production system."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 23, 2011 at 6:40 am
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.
August 23, 2011 at 7:01 am
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.
August 23, 2011 at 7:06 am
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
August 23, 2011 at 7:17 am
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.
August 23, 2011 at 9:37 am
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
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply