Reindex Maintenance Process - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

Reindex Maintenance Process

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-01-17 : 18:50:00

I have around 40 Dataabases in the server and Once in a week do the Following things:

UPDATE STATISTICS;
RECOMPILE ALL STORED PROCEDURES;
CHECKDB;
REINDEX

Earlier, it was consistent in timings as it does around 2 hours or so but for the last fortnight the timing for this process has increased to 3:30 Hours.

Couldn't estimate the reasons as to what would cause this TIME to increase, since there is no additional Trasactional rows, no more new databases and no additional SP's added in the recent times.

Can anyone analyse this imbroglio. Thanks for your attention.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-17 : 20:10:20

If you didn't have a trace running, then there's really nothing that can be checked to see what was going on. It's likely you had a performance condition occur which resulted in blocking.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 03:08:58

UPDATE STATISTICS;
RECOMPILE ALL STORED PROCEDURES;
CHECKDB;
REINDEX

I would suggest this order:

REINDEX (will also rebuild stats - in most cases)

UPDATE STATS - this will include but index and non-index statistics, but I think you run the risk that the FULL SCAN that REINDEX already did may be substituted by the lesser default sampling of UPDATE STATS - unless you explicitly specify WITH FULL SCAN (which takes longer ...)

RECOMPILE ...

DBCC CHECKDB - doing this last makes sure that no problems were introduced by any of the above; shouldn't be any bugs there, but someone has to be the first to find a bug, and an index rebuild may have caused some next part of the database files to be used, which may have an I/O fault.

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 03:14:18

Actually, I now wonder whether your order of

UDPATE STATS
REINDEX

isn't better because the quicker / smaller sampling can be used in UPDATE STATS (assuming that is sufficient for the non-index statistics?) and REINDEX will then use FULL SCAN anyway.

I'd be interested to know what others think. Is there anything "extra" that UPDATE STATS will do on non-index stats that would benefit from REINDEX having run first?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-18 : 04:10:14

No need for recompile. Updating statistics and rebuilding indexes will invalidate all plans that refer to them. If you rebuild all indexes and update all stats, that means that all procs will be recompiled anyway.

I'd put CheckDB first, as if there's any corruption it's highly likely that the index rebuilds would fail because of it.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 04:22:21

"Updating statistics and rebuilding indexes will invalidate all plans that refer to them"

That wasn't the case, for us, in SQL 2000 ... so I assume that is "properly fixed" now?

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 04:24:27

Actually, I wonder if it makes a case for not doing UPDATE STATS on tables where it is not necessary. ALL our Sproc query plans being invalidated at once would cause a significant hit on performance for a while (until new plans were cached)

Perhaps we should EXEC common SProcs after Update Stats to force query plans to be available ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources