March 24, 2017 at 12:38 am
We have a huge database in TB size.We have a job that reorganizes/rebuilds indexes on all dbs and then does DBCC checkdb on all databases in the instance. It is running for last 5 days. Currently it is in suspended state with command DBCC. Waittype is PAGEIOLATCH_SH.There is no blocking and io_stall values are high. Please help in fixing this
March 24, 2017 at 2:30 pm
coolchaitu - Friday, March 24, 2017 12:38 AMWe have a huge database in TB size.We have a job that reorganizes/rebuilds indexes on all dbs and then does DBCC checkdb on all databases in the instance. It is running for last 5 days. Currently it is in suspended state with command DBCC. Waittype is PAGEIOLATCH_SH.There is no blocking and io_stall values are high. Please help in fixing this
Depends on how many TB, how long does it normally take, the I/O stalls depend on what you normally see, etc.
Not sure how you are monitoring it but I would first try to take a look with sys.dm_exec_requests - something along the lines of: SELECT session_id,
blocking_session_id,
command,
[status],
wait_type,
wait_resource,
wait_time,
last_wait_type,
cpu_time,
reads,
writes,
total_elapsed_time,
percent_complete,
estimated_completion_time
FROM sys.dm_exec_requests
WHERE session_id = <SessionID for the DBCC>
Those columns can sometimes give you a better idea of what it's doing or where it's at in the process and if it's actually doing anything.
If it always runs for a long time, I'd consider just restoring a backup to another non-production server and running CHECKDB there. If that's not feasible, you could look at splitting apart the DBCC CHECKDB process and run the individual pieces - CHECKCATALOG, CHECKTABLE, CHECKALLOC, etc and run those during different times.
Other things that can affect the time it takes for CHECKDB to run are addressed in this write up:
CHECKDB From Every Angle: How long will CHECKDB take to run?
Sue
March 24, 2017 at 2:36 pm
I think a key indicator here is that a single job does the index maintenance on all databases and then proceeds to do the checkdb.
Which piece of the process has been running the longest? From the looks of it is the entire job that has been running for five days. Depending on the statements used in the index maintenance, the last command can show as a DBCC when it is the index maintenance that is running (reminder that DBCC does not mean what everybody thinks - it is DataBase Console Command and there are hundreds of DBCC statements).
Without more explicit information about the configuration of these jobs and what step is indeed running, the troubleshooting via forums would be frivolous and a shot in the dark.
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
March 24, 2017 at 2:39 pm
Sue_H - Friday, March 24, 2017 2:30 PMcoolchaitu - Friday, March 24, 2017 12:38 AMWe have a huge database in TB size.We have a job that reorganizes/rebuilds indexes on all dbs and then does DBCC checkdb on all databases in the instance. It is running for last 5 days. Currently it is in suspended state with command DBCC. Waittype is PAGEIOLATCH_SH.There is no blocking and io_stall values are high. Please help in fixing thisIf that's not feasible, you could look at splitting apart the DBCC CHECKDB process and run the individual pieces - CHECKCATALOG, CHECKTABLE, CHECKALLOC, etc and run those during different times.
Unfortunately, that does not perform a complete checkdb. While it does get the main components, there are other checks that would be skipped if this is all that was done. For that reason, the checkdb statement is still recommended to be run on a routine basis in addition to the individual pieces you have mentioned if the process is broken down.
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
March 24, 2017 at 3:23 pm
SQLRNNR - Friday, March 24, 2017 2:39 PMSue_H - Friday, March 24, 2017 2:30 PMcoolchaitu - Friday, March 24, 2017 12:38 AMWe have a huge database in TB size.We have a job that reorganizes/rebuilds indexes on all dbs and then does DBCC checkdb on all databases in the instance. It is running for last 5 days. Currently it is in suspended state with command DBCC. Waittype is PAGEIOLATCH_SH.There is no blocking and io_stall values are high. Please help in fixing thisIf that's not feasible, you could look at splitting apart the DBCC CHECKDB process and run the individual pieces - CHECKCATALOG, CHECKTABLE, CHECKALLOC, etc and run those during different times.
Unfortunately, that does not perform a complete checkdb. While it does get the main components, there are other checks that would be skipped if this is all that was done. For that reason, the checkdb statement is still recommended to be run on a routine basis in addition to the individual pieces you have mentioned if the process is broken down.
I'd go with Paul Randal's suggestions for VLDB since he wrote most of CHECKDB:
CHECKDB From Every Angle: Consistency Checking Options for a VLDB
Sue
March 24, 2017 at 10:19 pm
Sue_H - Friday, March 24, 2017 3:23 PMSQLRNNR - Friday, March 24, 2017 2:39 PMSue_H - Friday, March 24, 2017 2:30 PMcoolchaitu - Friday, March 24, 2017 12:38 AMWe have a huge database in TB size.We have a job that reorganizes/rebuilds indexes on all dbs and then does DBCC checkdb on all databases in the instance. It is running for last 5 days. Currently it is in suspended state with command DBCC. Waittype is PAGEIOLATCH_SH.There is no blocking and io_stall values are high. Please help in fixing thisIf that's not feasible, you could look at splitting apart the DBCC CHECKDB process and run the individual pieces - CHECKCATALOG, CHECKTABLE, CHECKALLOC, etc and run those during different times.
Unfortunately, that does not perform a complete checkdb. While it does get the main components, there are other checks that would be skipped if this is all that was done. For that reason, the checkdb statement is still recommended to be run on a routine basis in addition to the individual pieces you have mentioned if the process is broken down.
I'd go with Paul Randal's suggestions for VLDB since he wrote most of CHECKDB:
CHECKDB From Every Angle: Consistency Checking Options for a VLDBSue
Yup he did write most of CheckDB. That said, some important notes from the article are found in the comments:
1. Paul always recommends a full checkdb on a weekly basis at minimum with the exception being extenuating circumstances.
2. Jonathan has a great article concerning the performance of checkdb and an article linked in the comments (pertains largely to vldb and it should be read).
3. Read the linked article by Argenis about an extenuating circumstance in his vldb and sparse columns with indexes.
And since we don't truly know the version of SQL Server the OP is using, we need to take into consideration what will be missed if a checkdb is not performed in favor of the broken up approach such as:
SPATIAL INDEX
XML INDEX
Query Store
ColumnStore
Filestream checks
Service Broker
These are some of the reasons I am sure Paul still recommends a full weekly checkdb.
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
March 25, 2017 at 12:41 am
SQLRNNR - Friday, March 24, 2017 10:19 PMSue_H - Friday, March 24, 2017 3:23 PMSQLRNNR - Friday, March 24, 2017 2:39 PMSue_H - Friday, March 24, 2017 2:30 PMcoolchaitu - Friday, March 24, 2017 12:38 AMWe have a huge database in TB size.We have a job that reorganizes/rebuilds indexes on all dbs and then does DBCC checkdb on all databases in the instance. It is running for last 5 days. Currently it is in suspended state with command DBCC. Waittype is PAGEIOLATCH_SH.There is no blocking and io_stall values are high. Please help in fixing thisIf that's not feasible, you could look at splitting apart the DBCC CHECKDB process and run the individual pieces - CHECKCATALOG, CHECKTABLE, CHECKALLOC, etc and run those during different times.
Unfortunately, that does not perform a complete checkdb. While it does get the main components, there are other checks that would be skipped if this is all that was done. For that reason, the checkdb statement is still recommended to be run on a routine basis in addition to the individual pieces you have mentioned if the process is broken down.
I'd go with Paul Randal's suggestions for VLDB since he wrote most of CHECKDB:
CHECKDB From Every Angle: Consistency Checking Options for a VLDBSue
Yup he did write most of CheckDB. That said, some important notes from the article are found in the comments:
1. Paul always recommends a full checkdb on a weekly basis at minimum with the exception being extenuating circumstances.
2. Jonathan has a great article concerning the performance of checkdb and an article linked in the comments (pertains largely to vldb and it should be read).
3. Read the linked article by Argenis about an extenuating circumstance in his vldb and sparse columns with indexes.And since we don't truly know the version of SQL Server the OP is using, we need to take into consideration what will be missed if a checkdb is not performed in favor of the broken up approach such as:
SPATIAL INDEX
XML INDEX
Query Store
ColumnStore
Filestream checks
Service BrokerThese are some of the reasons I am sure Paul still recommends a full weekly checkdb.
server version is 2014 EE 64 bit
March 25, 2017 at 12:46 am
SQLRNNR - Friday, March 24, 2017 2:36 PMI think a key indicator here is that a single job does the index maintenance on all databases and then proceeds to do the checkdb.Which piece of the process has been running the longest? From the looks of it is the entire job that has been running for five days. Depending on the statements used in the index maintenance, the last command can show as a DBCC when it is the index maintenance that is running (reminder that DBCC does not mean what everybody thinks - it is DataBase Console Command and there are hundreds of DBCC statements).
Without more explicit information about the configuration of these jobs and what step is indeed running, the troubleshooting via forums would be frivolous and a shot in the dark.
command is dbcc and statement text is select i.name as [IndexName],cast(index_id
March 25, 2017 at 12:48 am
SQLRNNR - Friday, March 24, 2017 2:36 PMI think a key indicator here is that a single job does the index maintenance on all databases and then proceeds to do the checkdb.Which piece of the process has been running the longest? From the looks of it is the entire job that has been running for five days. Depending on the statements used in the index maintenance, the last command can show as a DBCC when it is the index maintenance that is running (reminder that DBCC does not mean what everybody thinks - it is DataBase Console Command and there are hundreds of DBCC statements).
Without more explicit information about the configuration of these jobs and what step is indeed running, the troubleshooting via forums would be frivolous and a shot in the dark.
index maintenance is running from 5 days.command is being shown as DBCC
March 25, 2017 at 12:54 am
Sue_H - Friday, March 24, 2017 2:30 PMcoolchaitu - Friday, March 24, 2017 12:38 AMWe have a huge database in TB size.We have a job that reorganizes/rebuilds indexes on all dbs and then does DBCC checkdb on all databases in the instance. It is running for last 5 days. Currently it is in suspended state with command DBCC. Waittype is PAGEIOLATCH_SH.There is no blocking and io_stall values are high. Please help in fixing thisDepends on how many TB, how long does it normally take, the I/O stalls depend on what you normally see, etc.
Not sure how you are monitoring it but I would first try to take a look with sys.dm_exec_requests - something along the lines of:SELECT session_id,
blocking_session_id,
command,
[status],
wait_type,
wait_resource,
wait_time,
last_wait_type,
cpu_time,
reads,
writes,
total_elapsed_time,
percent_complete,
estimated_completion_time
FROM sys.dm_exec_requests
WHERE session_id = <SessionID for the DBCC>Those columns can sometimes give you a better idea of what it's doing or where it's at in the process and if it's actually doing anything.
If it always runs for a long time, I'd consider just restoring a backup to another non-production server and running CHECKDB there. If that's not feasible, you could look at splitting apart the DBCC CHECKDB process and run the individual pieces - CHECKCATALOG, CHECKTABLE, CHECKALLOC, etc and run those during different times.
Other things that can affect the time it takes for CHECKDB to run are addressed in this write up:
CHECKDB From Every Angle: How long will CHECKDB take to run?
Sue
command=DBCC
status=suspended
wait_type=PAGEIOLATCH_SH
percent_complete=0
March 25, 2017 at 10:14 am
coolchaitu - Saturday, March 25, 2017 12:48 AMSQLRNNR - Friday, March 24, 2017 2:36 PMI think a key indicator here is that a single job does the index maintenance on all databases and then proceeds to do the checkdb.Which piece of the process has been running the longest? From the looks of it is the entire job that has been running for five days. Depending on the statements used in the index maintenance, the last command can show as a DBCC when it is the index maintenance that is running (reminder that DBCC does not mean what everybody thinks - it is DataBase Console Command and there are hundreds of DBCC statements).
Without more explicit information about the configuration of these jobs and what step is indeed running, the troubleshooting via forums would be frivolous and a shot in the dark.
index maintenance is running from 5 days.command is being shown as DBCC
That is because index maintenance is a DBCC statement under the covers. As I mentioned DBCC means DataBase Console Command, and there are hundreds of those.
You need to check for blocking and really unwieldy indexes that could take a really long time to defrag.
It is also worth understanding what process you use to defrag indexes. Is it just a maintenance plan (really bad) or an intelligent process that dynamically picks which index to defrag, how to defrag and to what level it should be defraged?
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
March 30, 2017 at 1:27 am
SQLRNNR - Saturday, March 25, 2017 10:14 AMcoolchaitu - Saturday, March 25, 2017 12:48 AMSQLRNNR - Friday, March 24, 2017 2:36 PMI think a key indicator here is that a single job does the index maintenance on all databases and then proceeds to do the checkdb.Which piece of the process has been running the longest? From the looks of it is the entire job that has been running for five days. Depending on the statements used in the index maintenance, the last command can show as a DBCC when it is the index maintenance that is running (reminder that DBCC does not mean what everybody thinks - it is DataBase Console Command and there are hundreds of DBCC statements).
Without more explicit information about the configuration of these jobs and what step is indeed running, the troubleshooting via forums would be frivolous and a shot in the dark.
index maintenance is running from 5 days.command is being shown as DBCC
That is because index maintenance is a DBCC statement under the covers. As I mentioned DBCC means DataBase Console Command, and there are hundreds of those.
You need to check for blocking and really unwieldy indexes that could take a really long time to defrag.
It is also worth understanding what process you use to defrag indexes. Is it just a maintenance plan (really bad) or an intelligent process that dynamically picks which index to defrag, how to defrag and to what level it should be defraged?
But it ran fine last 10 weeks. Only this time, it is taking so much time
March 30, 2017 at 2:34 am
I notice you've raised another topic regarding memory pressure. Is this happening on the same server?
March 30, 2017 at 8:37 am
NorthernSoul - Thursday, March 30, 2017 2:34 AMI notice you've raised another topic regarding memory pressure. Is this happening on the same server?
Heh.... yeah.... the one with 27GB allocated to SQL Server?
--Jeff Moden
March 30, 2017 at 9:37 am
coolchaitu - Thursday, March 30, 2017 1:27 AMSQLRNNR - Saturday, March 25, 2017 10:14 AMcoolchaitu - Saturday, March 25, 2017 12:48 AMSQLRNNR - Friday, March 24, 2017 2:36 PMI think a key indicator here is that a single job does the index maintenance on all databases and then proceeds to do the checkdb.Which piece of the process has been running the longest? From the looks of it is the entire job that has been running for five days. Depending on the statements used in the index maintenance, the last command can show as a DBCC when it is the index maintenance that is running (reminder that DBCC does not mean what everybody thinks - it is DataBase Console Command and there are hundreds of DBCC statements).
Without more explicit information about the configuration of these jobs and what step is indeed running, the troubleshooting via forums would be frivolous and a shot in the dark.
index maintenance is running from 5 days.command is being shown as DBCC
That is because index maintenance is a DBCC statement under the covers. As I mentioned DBCC means DataBase Console Command, and there are hundreds of those.
You need to check for blocking and really unwieldy indexes that could take a really long time to defrag.
It is also worth understanding what process you use to defrag indexes. Is it just a maintenance plan (really bad) or an intelligent process that dynamically picks which index to defrag, how to defrag and to what level it should be defraged?
But it ran fine last 10 weeks. Only this time, it is taking so much time
Arguments such as that have absolutely no bearing in reality. Just because the defrag ran fine previously has no bearing on your current issue. You really should have checked for the issues I mentioned.
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply