Database Maintenance

Question

Database Maintenance

Oracle_91

SSChampion

Points: 14548
More actions
August 30, 2011 at 6:07 am

#243915

Hi All,
What is the best order while creating maintenance jobs so that sql optimizer can pickup the best execution plan.
Daily i have 3 jobs configured to run in the midnight.
job1 : reorgising the indexes
job2 : integrity checks
job3 : update statistics
I believe if i execute the jobs in the below order makes sense , please correct me if am wrong.
Also, would like to know does index re-org will do auto update stats or do we need to create a separate job or maintenance plan ?
Order
-----
1. Check integrity checks
2. Re-organise all indexes
3. update statistics
Also, can anyone tell me whether index rebuild is better option or index reorganise is fine ?
Thanks in Advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

an index re-org will not update stats. Auto update stats will also not update all your stats, so if you want to update stats do it after any index operations.

I did look at index re-orgs once but ditched in favour of index rebuilds.

Rebuild indexes ( or re-org if you must )

do stats

do constancy checks last

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Oracle_91 SSChampion Points: 14548 More actions · Answer 2

He Colin,

Why cant have consistency checks in second place ?

Any reasons keep checks at last before stats?

Just wanted to understand logically 😉

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

You may want to consider Rebuilding the Indexes depending upon the level of fragmentation.

It will Update the Statistics when the index is rebuilt.

http://sqlserverpedia.com/blog/sql-server-bloggers/update-statistics-before-or-after-an-index-rebuild/

http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Oracle_91 SSChampion Points: 14548 More actions · Answer 4

Oracle_91

SSChampion

Points: 14548

September 4, 2011 at 7:17 pm

#1376929

Hi ,

Both links are not working.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Sorry about that, they work now.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 6

Oracle_91 (8/30/2011)
Hi All,
What is the best order while creating maintenance jobs so that sql optimizer can pickup the best execution plan.
Daily i have 3 jobs configured to run in the midnight.
job1 : reorgising the indexes
job2 : integrity checks
job3 : update statistics
I believe if i execute the jobs in the below order makes sense , please correct me if am wrong.
Also, would like to know does index re-org will do auto update stats or do we need to create a separate job or maintenance plan ?
Order
-----
1. Check integrity checks
2. Re-organise all indexes
3. update statistics
Also, can anyone tell me whether index rebuild is better option or index reorganise is fine ?
Thanks in Advance.

Your jobs will help the overall performance no matter what order you do them. You execution plan will try to use that information but first I'd have a look at the query plans. A good general area is have look at the long running queries and determine whether you have a covering index, whether you are using clustered or non-clustered indexes. Are you 'scanning' or 'seeking', there is a big difference. If you can, are you using the included columns capability. All these thing and more 🙂

Woot ! Love these indexes

CodeOn

😛

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Malcolm Daughtree (9/5/2011)
Oracle_91 (8/30/2011)
Your jobs will help the overall performance no matter what order you do them.

Bad Advice.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

never, ever use maintenance plans! get the awesome free and fully documented maintenance suite from ola.hallengren.com.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service