none
DBCC Checkdb running for ~4days

    Question

  • Hi,

    DBCC Checkdb was taking 2 days until last week and now it is running for almost 4 days and showing that it only completed ~38% as per following data from sys.dm_exec_requests and most of the waits are on CKPACKET. This is 24 cpu machine with 48GB memory and is currently only used by checkdb on restored database from production, What can be the possible issue? 

    session_id request_id  start_time              status                         command          sql_handle                                                                                                                         statement_start_offset statement_end_offset plan_handle                                                                                                                        database_id user_id     connection_id                        blocking_session_id wait_type                                                    wait_time   last_wait_type                                               wait_resource                                                                                                                                                                                                                                                    open_transaction_count open_resultset_count transaction_id       context_info                                                                                                                                                                                                                                                       percent_complete estimated_completion_time cpu_time    total_elapsed_time scheduler_id task_address       reads                writes               logical_reads        text_size   language                                                                                                                         date_format date_first quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null transaction_isolation_level lock_timeout deadlock_priority row_count            prev_error  nest_level  granted_query_memory executing_managed_code group_id    query_hash         query_plan_hash
    ---------- ----------- ----------------------- ------------------------------ ---------------- ---------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------------------------------------ ------------------- ------------------------------------------------------------ ----------- ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------- ------------------------- ----------- ------------------ ------------ ------------------ -------------------- -------------------- -------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- ----------------- ---------- ----------------- ------------- ------------- ------------ ---------- ----------------------- --------------------------- ------------ ----------------- -------------------- ----------- ----------- -------------------- ---------------------- ----------- ------------------ ------------------
    55         0           2012-10-20 22:24:39.260 suspended                      DBCC TABLE CHECK 0x02000000FD7B49186B2EFC423F661E7F576C004979728236                                                                                 0                      -1                   0x06000900FD7B491840414843030000000000000000000000                                                                                 9           1           2396537F-A338-4990-AB6D-103866240C05 0                   CXPACKET                                                     324026692   CXPACKET                                                                                                                                                                                                                                                                                                                      1                      1                    6334466              0x                                                                                                                                                                                                                                                                 37.25394         553896082                 29777934    556451384          18           0x00000000050EA508 42418035             270                  43100347             1024        us_english                                                                                                                       mdy         7          1                 1          1                 0             1             1            1          1                       2                           -1           0                 8239114              0           1           357501               0                      2           0x0000000000000000 0x0000000000000000

    (1 row(s) affected)

    Wednesday, October 24, 2012 10:00 PM

Answers

  • Bala & Sean said right thing-in addition to that

    It also depends on the size overall database,also did you see anything I/O load on the server apart from your activity (use an perfmon counters to see)  just to ensure ,since the CHECKDB it reads all allocated pages and it reads the database pages from Physical level in case if it is already lot of I/O running against on the where your db file resides including your check db & other load may make your check db to run slow...!
    as Sean said there Might be corruption can lead to run long time to throw an error finally...! SAN level I/O bottleneck or drivers are not upto date.

    verify in the error log & event viewer..!

    also are you seeing CPU is 100%..and any Tempdb pressure? verify how Much memory set for SQL server --these are all just to ensure that's it but that is not the problem over here.

    Don't kill the CHECKDB let it run other wise it will get rollback & it May take more than what running currently of CHECKDB.

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    • Proposed as answer by vr.babu Thursday, October 25, 2012 9:42 AM
    • Marked as answer by Shulei ChenModerator Thursday, November 01, 2012 9:35 AM
    Thursday, October 25, 2012 9:27 AM
  • you can use the PHYSICAL_ONLY option , how ever complete DBCC CHECKDB plays an its own role.

    Let see others explanation as well. so that we can get know more information!

    some interesting topic discussed by Bob n Fastercheckdb

    A faster CHECKDB – Part II
    http://blogs.msdn.com/b/psssql/archive/2012/02/23/a-faster-checkdb-part-ii.aspx


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Thursday, October 25, 2012 3:24 PM

All replies

  • Hi,

    Is everything else the same i.e. you always run checkdb on this server from a restore from prod? What is the MAXDOP set to on this instance? Has the database changed dramatically in size? Sometimes when checkdb finds corruption it can take a lot longer as it has to run further checks to see what exactly is wrong so I would let it complete and see what it returns.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Wednesday, October 24, 2012 10:22 PM
  • 2 days benchmark is from same machine? Did you check IO bottleneck?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Thursday, October 25, 2012 1:43 AM
    Moderator
  • Bala & Sean said right thing-in addition to that

    It also depends on the size overall database,also did you see anything I/O load on the server apart from your activity (use an perfmon counters to see)  just to ensure ,since the CHECKDB it reads all allocated pages and it reads the database pages from Physical level in case if it is already lot of I/O running against on the where your db file resides including your check db & other load may make your check db to run slow...!
    as Sean said there Might be corruption can lead to run long time to throw an error finally...! SAN level I/O bottleneck or drivers are not upto date.

    verify in the error log & event viewer..!

    also are you seeing CPU is 100%..and any Tempdb pressure? verify how Much memory set for SQL server --these are all just to ensure that's it but that is not the problem over here.

    Don't kill the CHECKDB let it run other wise it will get rollback & it May take more than what running currently of CHECKDB.

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    • Proposed as answer by vr.babu Thursday, October 25, 2012 9:42 AM
    • Marked as answer by Shulei ChenModerator Thursday, November 01, 2012 9:35 AM
    Thursday, October 25, 2012 9:27 AM
  • >>>>>Don't kill the CHECKDB let it run other wise it will get rollback & it May take more than what running currently of CHECKDB

    That is incorrect... There is no transactions, you can safety stop the process.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Thursday, October 25, 2012 10:10 AM
    Answerer
  • Perhaps it found a corruption and is doing a deep dive to get details? I think you would see more on that in the errorlog. Some info in there: http://www.sqlskills.com/blogs/paul/post/CHECKDB-From-Every-Angle-Complete-description-of-all-CHECKDB-stages.aspx

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, October 25, 2012 11:38 AM
    Moderator
  • @Uri -Thanks!

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Thursday, October 25, 2012 11:48 AM
  • It finally finished last night and there were no errors. Just to answer the few questions asked:

    • This is the same machine so same IO characteristics
    • Database is changed with 1 week of data and nothing special and it is trending same way for last year
    • MAXDOP on server is default so it can use all the CPU so that is why most of the waits are on CXPACKET

    Is there anything that we can do to speed up the performance for next run (I can disable the parallelism but that may slow it down but never know so anything else that we can try?)

     

    Thursday, October 25, 2012 2:11 PM
  • you can use the PHYSICAL_ONLY option , how ever complete DBCC CHECKDB plays an its own role.

    Let see others explanation as well. so that we can get know more information!

    some interesting topic discussed by Bob n Fastercheckdb

    A faster CHECKDB – Part II
    http://blogs.msdn.com/b/psssql/archive/2012/02/23/a-faster-checkdb-part-ii.aspx


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Thursday, October 25, 2012 3:24 PM