Maintenance Plan Best Practice

Question

Maintenance Plan Best Practice

Viewing 11 posts - 16 through 25 (of 25 total)

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

mckinnj1 Mr or Mrs. 500 Points: 548 More actions · Answer 1

GilaMonster (7/1/2008)
Loner (6/26/2008)
Why not shrink the database?
http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

Sorry to resurrect this issue...

Just looking for some general advice I suppose.

We have a database where the vendor has several daily stored procedures that DROP and CREATE tables in order fulfill some jobs. As many db's tend to do.

I have seen another vendor create a maintenance plan that goes in the following order on all the USER databases for their application:

Shrink DB

Reorganize Index

Rebuild Index

Update Statistics

History Cleanup

Maintenance Cleanup (to create space for new backups)

DBCC

Back up Db's

Questions:

1) Given all the table drops/creations that occur daily is it ok to shrink THEN rebuild daily? Or should the shrink be done at all give what the previous article was stating? Or can it be done weekly and still run effectively without chewing up unnecessary space?

2) What is the purpose of Reorganizing THEN Rebuilding THEN Updating Statistics if the Rebuild does both reorganizing AND updating stats?

3) Would it be better to run a shrink log script daily rather than shrink db? Or neither? What is the optimal schedule for shrinking logs and db's?

Sometimes I do not give all the necessary details as I am a relative noob to the DBA role. So, if I am missing some required details, please forgive and let me know what further info you need.

My goal is to come up with somewhat of a relative SOP for all our systems (including the one mentioned above) to have daily and weekly maintenance plans. Obviously some vendor db's are created differently from others, so without going into great specificity, I'd like to come up with something somewhat generic to all that would allow the other DBA's to tweak their to fit their needs.

Any advice would be much appreciated!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Why do you want to shrink at all?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mckinnj1 Mr or Mrs. 500 Points: 548 More actions · Answer 3

GilaMonster (5/20/2010)
Why do you want to shrink at all?

The log files tend to grow rapidly over the course of a few weeks if unchecked. Is it better to run a shrink log file script rather than the db?

Short answer: Space

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It's better to do neither.

Have you identified why the log is growing? Full recovery and no log backups?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Irish Hall of Fame Points: 3958 More actions · Answer 5

mckinnj1 (5/20/2010)
GilaMonster (7/1/2008)
Loner (6/26/2008)
Why not shrink the database?
http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Sorry to resurrect this issue...
Just looking for some general advice I suppose.
We have a database where the vendor has several daily stored procedures that DROP and CREATE tables in order fulfill some jobs. As many db's tend to do.
I have seen another vendor create a maintenance plan that goes in the following order on all the USER databases for their application:
Shrink DB
Reorganize Index
Rebuild Index
Update Statistics
History Cleanup
Maintenance Cleanup (to create space for new backups)
DBCC
Back up Db's
Questions:
1) Given all the table drops/creations that occur daily is it ok to shrink THEN rebuild daily? Or should the shrink be done at all give what the previous article was stating? Or can it be done weekly and still run effectively without chewing up unnecessary space?
2) What is the purpose of Reorganizing THEN Rebuilding THEN Updating Statistics if the Rebuild does both reorganizing AND updating stats?
3) Would it be better to run a shrink log script daily rather than shrink db? Or neither? What is the optimal schedule for shrinking logs and db's?
Sometimes I do not give all the necessary details as I am a relative noob to the DBA role. So, if I am missing some required details, please forgive and let me know what further info you need.
My goal is to come up with somewhat of a relative SOP for all our systems (including the one mentioned above) to have daily and weekly maintenance plans. Obviously some vendor db's are created differently from others, so without going into great specificity, I'd like to come up with something somewhat generic to all that would allow the other DBA's to tweak their to fit their needs.
Any advice would be much appreciated!!

I agree with Gail. I would not be spending time shrinking the Database or the Log files.

I suspect that growth is what you are battling with. I would press the vendor and ask them why they think the Database(s) are growing so quickly.

I'm also not sold on the "order of operations" they have recommended. Perhaps I am naive, but why would you Reorg Indexes and then rebuild them? Also, you mention that all of this runs every day :w00t: ?

I know what I would recommend, but that is specific to our Databases:

Full Backup once per day (minimum)

Transaction Logs every 4 hours (minimum)

Database Statistics Updated Weekly

Index Defragmentation Weekly

Index Rebuilds Monthly

Disk Defragmentation Quarterly (unless SAN Vendor prohibits it)

These are not hard and fast rules by any means, only recommendations. However, they are a framework that we suggest they build on.

Regards, Irish

mckinnj1 Mr or Mrs. 500 Points: 548 More actions · Answer 6

Thank you, that kind of solidified what I thought.

I could not understand the rebuild after the reorg...

Now, with that said, here are some follow up questions...

Would it be better to Reorganize Indexes THEN Update Statistics?

OR

Simply Rebuild?

I understand the Rebuild does page locks and reorg does record locks. But what kind of locks does the update statistics utilize?

If it is record locks as well, then it would seem the "reorg + update" method would seem optimal considering our db is pretty much utilized 24/7.

Thoughts?

Irish Hall of Fame Points: 3958 More actions · Answer 7

mckinnj1 (5/20/2010)
Thank you, that kind of solidified what I thought.
I could not understand the rebuild after the reorg...
Now, with that said, here are some follow up questions...
Would it be better to Reorganize Indexes THEN Update Statistics?
OR
Simply Rebuild?
I understand the Rebuild does page locks and reorg does record locks. But what kind of locks does the update statistics utilize?
If it is record locks as well, then it would seem the "reorg + update" method would seem optimal considering our db is pretty much utilized 24/7.
Thoughts?

According to BOL, an ALTER INDEX REORGANIZE is equivalent to an Index Defrag. (this is not something that I knew before, so it will change my recommendations for SQL 2005 and up). Based on what I am reading, neither a Defrag or a Reorg locks:

SQL BOL

REORGANIZE

Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

So, unless I am mistaken, Rebuilding only would be the best option as that is the same as dropping and recreating the Index. However, there are some Indexes that can be rebuilt with the ONLINE option. This basically allows a new index to be built the same as the current in parallel. When the new one is done, the old one is dropped and the new one takes its place. (It's more technical than that, but that's the gist).

In any case, rebuilding Indexes daily still sounds excessive to me.

Updating Statistics does not seem to have any adverse affects on record/table locks. There is a trade off in that queries have to be recompiled, but that may outweigh the performance gains. Updating statistics to frequently can be bad:

SQL BOL

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application.

Unless someone can come up with a reason, a really good reason, to rebuild indexes or update statistics daily, it just sounds to frequent to me.

Take my opinion for what it is worth.

Regards, Irish

mckinnj1 Mr or Mrs. 500 Points: 548 More actions · Answer 8

You answered everything perfectly!

Thanks for the peace of mind and the guidance!!

+100 to Irish.

😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeffrey Irish (5/20/2010)
In any case, rebuilding Indexes daily still sounds excessive to me.

Rebuilding all indexes, regardless of fragmentation is usually a waste. There are many good index rebuild scripts that just rebuild indexes that need to be rebuilt, often with configurable thresholds for fragmentation and index size. Start with Michelle's one at http://www.sqlfool.com

Updating Statistics does not seem to have any adverse affects on record/table locks.

It runs in read uncommitted isolation and hence takes only schema stability locks

Updating statistics to frequently can be bad:

However updating them too infrequently can be worse. That said, if auto-update stats is enabled at a database level, I don't actually recommend manual updates of all stats, just ones that are prone to going stale long before the auto-update would kick in and hence resulting in very bad execution plans and very slow queries.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 10

Jeffrey Irish (5/20/2010)
So, unless I am mistaken, Rebuilding only would be the best option as that is the same as dropping and recreating the Index. However, there are some Indexes that can be rebuilt with the ONLINE option. This basically allows a new index to be built the same as the current in parallel. When the new one is done, the old one is dropped and the new one takes its place. (It's more technical than that, but that's the gist).

fwiw, "ONLINE index operations are available only in SQL Server 2005 Enterprise Edition"

Irish Hall of Fame Points: 3958 More actions · Answer 11

Homebrew,

I'm aware of that, but clearly neglected to mention it.

Regards, Irish