When to Run DBCC CHECKDB or Best Practise?
If your database is very small then no worries you can run and see the results,how ever if the Database is very large then you have to take right decision when you can run--
Because DBCC CHECKDB is a CPU- and disk I/O intensive operation. Each data page it has to scan & first it has to be read from disk into memory. SQL server database engine uses tempdb to do sorting operations for it's own(note in SQL server 2005 if you have not specified Sort_tempdb =Off, the sorting operation runs on the same database which you fired the query DBCC CHECKDB)
So you can use the below Recommendations for DBCC considering the Performance point of view:
1.Identify that the prefer time where the system usage is low.
2. Ensure that you are not performing any other disk I/O operations, Like SQL database backups to disk.
3.Always good Pratcise that place tempdb database files on a separate disk system or a fast disk subsystem, so that you can Avoid I/O.
Also ensure that you have enough space for tempdb to expand on the drive. you Use DBCC with ESTIMATE ONLY to estimate how much space will be needed for tempdb.
4. Avoid running Bulk queries which can cause CPU to eat like intensive queries or batch jobs or adhoc queries.
5. Reduce active transactions while a DBCC command is running.
Because If actively performing transactions while DBCC CHECKDB is running, the transaction log continues to grow because the DBCC command blocks log truncation until it has finished reading the log.
6. Use the NO_INFOMSGS option DBCC command to reduce processing and tempdb usage significantly.
Note that :DBCC CHECKDB will fails/Terminates to complete in below situation(there are many reasons which fails few are which I have seen as mentioned below).
A)When system tables got corrupted.
B)When you have ran DBCC CHECKDB during busy hours -due to insufficient memory available to run DBCC CHECKDB
C)When your Temdb database files runs out of space.
Please add if you know some more.
If your database is very small then no worries you can run and see the results,how ever if the Database is very large then you have to take right decision when you can run--
Because DBCC CHECKDB is a CPU- and disk I/O intensive operation. Each data page it has to scan & first it has to be read from disk into memory. SQL server database engine uses tempdb to do sorting operations for it's own(note in SQL server 2005 if you have not specified Sort_tempdb =Off, the sorting operation runs on the same database which you fired the query DBCC CHECKDB)
So you can use the below Recommendations for DBCC considering the Performance point of view:
1.Identify that the prefer time where the system usage is low.
2. Ensure that you are not performing any other disk I/O operations, Like SQL database backups to disk.
3.Always good Pratcise that place tempdb database files on a separate disk system or a fast disk subsystem, so that you can Avoid I/O.
Also ensure that you have enough space for tempdb to expand on the drive. you Use DBCC with ESTIMATE ONLY to estimate how much space will be needed for tempdb.
4. Avoid running Bulk queries which can cause CPU to eat like intensive queries or batch jobs or adhoc queries.
5. Reduce active transactions while a DBCC command is running.
Because If actively performing transactions while DBCC CHECKDB is running, the transaction log continues to grow because the DBCC command blocks log truncation until it has finished reading the log.
6. Use the NO_INFOMSGS option DBCC command to reduce processing and tempdb usage significantly.
Note that :DBCC CHECKDB will fails/Terminates to complete in below situation(there are many reasons which fails few are which I have seen as mentioned below).
A)When system tables got corrupted.
B)When you have ran DBCC CHECKDB during busy hours -due to insufficient memory available to run DBCC CHECKDB
C)When your Temdb database files runs out of space.
Please add if you know some more.
No comments:
Post a Comment