Maintenance Task

Question

Hi ,

   I'm planning to implement index maintenance & DBCC CHECKDB in my environment. Can I know which one I have to do first, Index rebuild or DBCC CHECKDB?

Answer 1

Hi ,

   I'm planning to implement index maintenance & DBCC CHECKDB in my environment. Can I know which one I have to do first, Index rebuild or DBCC CHECKDB?

Both are two different thing .You can start with anyone of these.For Index rebuild you can use Ola hallengren script , running Checkdb depends on size of your database. 

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 2

Both are different role, Where Reindx(helps for query Optimizer & removes fragmentation) is part of theSQL Server Optimization and CHECKDB is part of the Integrity(where this performs the database health check to ensure that database is free from corruptions)
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
http://ola.hallengren.com/

You need to schedule both, you can schedule how you wish but do it during off hours, usually you can schedule CHECKDB weekly & fragmentation removal requires analysis based on that you can decide when you want do with reindex or reorganize.

Seen few of servers weekly scheduled both with different times, you can schedule how you need you can go for REINDEX & CHECKDB (but schedule different times).

Also ensure before this runs you have known good backup(for best practice)

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 3

Most likely you don't want to do them in the same job anyhow. CHECKDB should be done every day, if at all possible. Index rebuild you generally do once a week (and perhaps using a smart script such as ola.hallengren.com). If yo still feel you like to do them in the same job, then the CHECKDB job might be a bit quicker if you do index rebuild first (since fragmentation is reduced before the CHECKDB).

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 4

Rebuild - reorganize your index before the Integrity Check.

Sometimes integrity check fails because of a corrupted index so first mantain the index and after do integrity check (and at the end perform a full backup, you don't wont to backup a not integer db right?)

Answer 5

Most likely you don't want to do them in the same job anyhow. CHECKDB should be done every day, if at all possible. Index rebuild you generally do once a week (and perhaps using a smart script such as ola.hallengren.com). If yo still feel you like to do them in the same job, then the CHECKDB job might be a bit quicker if you do index rebuild first (since fragmentation is reduced before the CHECKDB).

---

Tibor Karaszi, SQL Server MVP | web | blog

I'd had the same thought about indexing beforehand might speed up CHECKDB performance, but couldn't find any documentation on this, so assumed it wasn't so.  Do you have a link to anything?  thanks,  Andrew

---

Thanks, Andrew
My blog...

Answer 6

"Do you have a link to anything?"

Not really, Andrew. I just reason based on my undrstanding on how SQL Server work. Imagine CHECKDB checking a table. For each inde, it will check that you have same number of rows as in the table. So I assume that an IAM scan is performed, and defrag can reduce number of pages, hence cut down time. I doubt that SQL Server will follow the linked lists, so the potential performance benefit will not likely revolutionize your world... :-)

It would be interesting to produce a test case where you restore a database, cause bunc of fragmentation and try checkdb before and after defrag. But not interesting enough for me to actually do such test, methink.... :-)

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 7

haha, I guess not.  If it's a slow day at work tomorrow, I may power up my test machine and give it a go.  Thanks for responding... Andrew

---

Thanks, Andrew
My blog...

Answer 8

Cool. Let us know the outcome if you do so...

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 9

I did a few tests today.  In summary: on a moderately sized database, I ran DBCC CHECKDB with high levels of fragmentation, and also on the same database with little fragmentation.  The timings were nearly identical, to within a few seconds.

I'm going to try and widen the test criteria, over a longer period with more fragmentation vs less fragmentation, to hopefully highlight any differences in performance, but at the moment, it doesn't look like it matters that much whether the database is fragmented or not.

---

Thanks, Andrew
My blog...

Answer 10

Thanks Andrew. One less thing to consider; the order for checkdb/defrag - in case you run them in the same job. :-)

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 11

Index fragmentation is irrelevant as far as CHECKDB performance goes. CHECKDB does its readahead based on the allocation order of all pages in the current batch (at least one table plus all its indexes), not the logical order of pages in each index. It won't make any difference if you do CHECKDB before or after.

Why have you created fake profile by name of Paul Randal and also used SQLSkill.com company name .Please delete your profile ans create a new on which belongs to you

Paul randal genuine profile

http://social.msdn.microsoft.com/profile/paul%20randal/?type=forum&referrer=http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqldisasterrecovery&filter=alltypes&sort=lastpostdesc

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers