Check Database Integrity Performance Issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Check Database Integrity Performance Issue

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-08-31 : 11:08:18

I'm currently running this as a maintenance plan nightly. I just purchased some SQL performance monitoring software and found a huge spike in waits at midnight when this task runs. I'm pretty confident that the "include indexes" portion of this task is causing the performance issue as this is the sql statement singled out for causing the waits...


DECLARE  
@BlobEater VARBINARY(8000)  
SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)  
FROM { IRowset 0x201F5FA505000000 }  
GROUP BY ROWSET_COLUMN_FACT_KEY  
>>  
WITH  
ORDER BY ROWSET_COLUMN_FACT_KEY,  
   ROWSET_COLUMN_SLOT_ID,  
   ROWSET_COLUMN_COMBINED_ID,  
   ROWSET_COLUMN_FACT_BLOB OPTION ( 
ORDER  
GROUP)

How important is it to include indexes?

Kristen
Test

22859 Posts

Posted - 2011-08-31 : 11:13:55

If CHECKDB is a performance issue the easiest solution is to restore a FULL backup to a.n.other machine and do the CHECKDB on that database (copy). No load on the primary database at all then.

We run CHECKDB with all possible checks turned on - which is its slowest mode of course. If CHECKDB is going to be able to find anything "bust" I'd like to know about it sooner rather than later, so I'd hate to have to turn any options off.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-08-31 : 11:29:50

Wow - that was quick. Sadly, I don't have another machine at my disposal and we are hosted at Rackspace so getting another one setup there is a costly proposal. It sounds like I just have to accept this performance hit.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-31 : 12:02:29

If you don't do a complete checkDB on a regular basis, you have no way to detect database corruption. If your database becomes corrupt and you don't have regular checkDB running, the change of identifying it soon enough to avoid data loss during repair is not good.

btw, the code you pulled doesn't prove it's the 'include indexes' option. That controls whether nonclustered indexes are included in the check or not, the CheckIndex will also be for clustered indexes (the actual data)

--
Gail Shaw
SQL Server MVP

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-08-31 : 13:55:23

quote:
btw, the code you pulled doesn't prove it's the 'include indexes' option. That controls whether nonclustered indexes are included in the check or not, the CheckIndex will also be for clustered indexes (the actual data)

--
Gail Shaw
SQL Server MVP

Good to know. I suspect it is though since our largest table has 13 nonclustered indexes and our database in general has many nonclustered indexes (probably too many in fact).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources