A faster DBCC CHECKDB?

  • ianstirk

    Ten Centuries

    Points: 1310

    Comments posted to this topic are about the item A faster DBCC CHECKDB?

  • Paul Randal

    One Orange Chip

    Points: 29438

    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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • ianstirk

    Ten Centuries

    Points: 1310

    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

  • spaghettidba

    SSC Guru

    Points: 105661

    Nice method. Thanks for the good article.

    And thanks to Paul Randal for the sage advice.

  • stenperers

    SSC Enthusiast

    Points: 122

    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

  • stenperers

    SSC Enthusiast

    Points: 122

    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

  • simon.hughes-697036

    SSC Rookie

    Points: 35

    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

  • Erik Bitemo

    SSCrazy

    Points: 2062

    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

  • Grant Fritchey

    SSC Guru

    Points: 395586

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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.

  • mtassin

    SSC-Insane

    Points: 23096

    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[/url]
    For tips on how to post your problems[/url]

  • ianstirk

    Ten Centuries

    Points: 1310

    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

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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.

  • Paul Randal

    One Orange Chip

    Points: 29438

    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 29 total)

You must be logged in to reply to this topic. Login to reply