June 17, 2009 at 12:56 pm
I am trying to set up system and databases Maintance plan Order and don't know if I need all of it and which order,can some one help me?Thank you
CheckDatabaseIntegrity
ShrinkDatabase
Reorginize Index
Rebulding Index
Update Statistics
Cleanup History
Maintance Cleanup Task
Is this right order and do I need all of it?
June 20, 2009 at 10:22 am
Krasavita (6/17/2009)
I am trying to set up system and databases Maintance plan Order and don't know if I need all of it and which order,can some one help me?Thank you1.CheckDatabaseIntegrity
2.ShrinkDatabase
3.Reorginize Index
4.Rebulding Index
5.Update Statistics
6.Cleanup History
7.Maintance Cleanup Task
Is this right order and do I need all of it?
I've numbered ur requests. I suppose you want to remove point 2 from your maintainance plan list. Why do you want to shrink your database? this causes lots of fragmentation and may lead to db performance issues.
Keep ur clean up tasks at the end as u've rightly put.
Also why do you want to reorganize and rebuild indexes both? refer BOL on when to do index reorganization and when to perform index rebuilding.
update statistics and integrity checks has been put at right places.
EDIT: placed /quote at its right place
June 20, 2009 at 2:16 pm
Updating stats after an index rebuild is a poor idea. A rebuild of an index automatically updates stats on that index with full scan. If you then do a sampled update of the stats (which is what update stats does) you'll decrease the accuracy of the stats.
Update stats should go before the index rebuild. That way all stats get a sampled update and then the index rebuild updates index-based stats with full scan.
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
June 20, 2009 at 2:36 pm
Don't shrink. Manage your space, make sure you have enough free space to handle operations, and leave it at that. Shrink is for emergencies, not maintenance.
Also, the checks should happen before backups, if there's an issue, page someone so they can handle it and you don't end up with bad backups.
June 20, 2009 at 2:57 pm
GilaMonster (6/20/2009)
Updating stats after an index rebuild is a poor idea. A rebuild of an index automatically updates stats on that index with full scan. If you then do a sampled update of the stats (which is what update stats does) you'll decrease the accuracy of the stats.Update stats should go before the index rebuild. That way all stats get a sampled update and then the index rebuild updates index-based stats with full scan.
If you use sp_updatestats, it only selects statistics that need to be updated. Running this after you rebuild your indexes can reduce the amount of time it takes to perform the update. If you run this prior to rebuilding your indexes, you will definitely be updating statistics that you are going to later update when you rebuild the index - which to me is just a waste of time. YMMV.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2009 at 3:10 pm
Do you know offhand if maint plans use sp_updatestats or UPDATE STATISTICS?
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
June 20, 2009 at 3:15 pm
The maintenance plan plug-in uses UPDATE STATISTICS. I get around this by using an Execute SQL Task and executing:
Use db1; Execute sp_updatestats @resample = 'RESAMPLE';
Use db2; Execute sp_updatestats @resample = 'RESAMPLE';
...
This step follows the rebuild index step that only touches on those indexes that are more than 10% fragmented. In that procedure, if I reorganize the index - I have a step that performs a full update statistics also.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2009 at 3:45 pm
Jeffrey Williams (6/20/2009)
The maintenance plan plug-in uses UPDATE STATISTICS.
So if you're using the default maint plans, updating stats after rebuilding indexes will (I think) lead to a sampled update.
Need to test that.
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
June 21, 2009 at 11:16 am
Just to be clear on another point... it's not the shrinking of a database that causes fragmentation and performance problems. It's the regrowth that causes all of that. Agreed that the shrinking of a database is what usually necessitates regrowth, though.
Steve is absolutely correct in saying that database sizes should be (must be) managed. Even if you haven't shrunk your databases, automatic regrowth should never occur because it will always occur when you can least afford it... when you're doing something big. It's so easy to identify what the growth pattern of (especially old, big) databases will be. Plan on scheduling for predicted necessary growth at "quiet times" on the system.
As a side bar, if TempDB autogrows after a reboot, then you seriously need to make a change in it's boot size. If TempDB is setup to autogrow by 10% and starts out at 1MB, there will be 73 growths (and the resulting fragmentation) just to get to 1GB. TempDB should be large enough on boot up to never need to autogrow unless something went dreadfully wrong.
One other thing... I don't use the maintenance plan to rebuild indexes on very large databases that also have a lot of static lookup tables. I'll use a tweaked version of the rebuild script found when you lookup DBCC ShowContig (bottom of the listing) because a fair amount of time can be wasted rebuilding indexes that don't need it. The script allows you to set what the level of fragmentation must be before you rebuild an index.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2009 at 11:20 am
Jeff Moden (6/21/2009)
Just to be clear on another point... it's not the shrinking of a database that causes fragmentation and performance problems. It's the regrowth that causes all of that. Agreed that the shrinking of a database is what usually necessitates regrowth, though.Steve is absolutely correct in saying that database sizes should be (must be) managed. Even if you haven't shrunk your databases, automatic regrowth should never occur because it will always occur when you can least afford it... when you're doing something big. It's so easy to identify what the growth pattern of (especially old, big) databases will be. Plan on scheduling for predicted necessary growth at "quiet times" on the system.
As a side bar, if TempDB autogrows after a reboot, then you seriously need to make a change in it's boot size. If TempDB is setup to autogrow by 10% and starts out at 1MB, there will be 73 growths (and the resulting fragmentation) just to get to 1GB. TempDB should be large enough on boot up to never need to autogrow unless something went dreadfully wrong.
Don't be real shy about the size of TempDB... it's a critical resource and correct sizing is nearly as critical. On databases as small as 500 GB, I'll frequently need to set the boot size of TempDB to something like 10 GB and it's not always because of just bad code being present.
One other thing... I don't use the maintenance plan to rebuild indexes on very large databases that also have a lot of static lookup tables. I'll use a tweaked version of the rebuild script found when you lookup DBCC ShowContig (bottom of the listing) because a fair amount of time can be wasted rebuilding indexes that don't need it. The script allows you to set what the level of fragmentation must be before you rebuild an index.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2009 at 11:31 am
GilaMonster (6/20/2009)
Jeffrey Williams (6/20/2009)
The maintenance plan plug-in uses UPDATE STATISTICS.So if you're using the default maint plans, updating stats after rebuilding indexes will (I think) lead to a sampled update.
Need to test that.
The plug-in gives you the options for how you want to update the statistics. You can define the sampling rate or select a full scan.
Either way - performing that update after you rebuild indexes is just wasting time updating the index statistics on the indexes that have just been rebuilt.
Using sp_updatestats without the resample option will use the default sampling rate as well as auto update statistics. Using the resample option does not guarantee that you will get a full scan, but if the last time it was updated was due to an index rebuild or a full scan, that is what you are going to get.
I do wish the plug-in would be updated so it worked the same way as sp_updatestats, at least have that option available.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 21, 2009 at 11:39 am
Jeff Moden (6/21/2009)
One other thing... I don't use the maintenance plan to rebuild indexes on very large databases that also have a lot of static lookup tables. I'll use a tweaked version of the rebuild script found when you lookup DBCC ShowContig (bottom of the listing) because a fair amount of time can be wasted rebuilding indexes that don't need it. The script allows you to set what the level of fragmentation must be before you rebuild an index.
I also don't use the maintenance plan plug-in for this. I use an Execute SQL Task in the maintenance plan to execute a custom procedure. I have a maintenance plan on one of my systems where all of the steps except the Notify Operator task is just Execute SQL Tasks.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 21, 2009 at 12:30 pm
Jeff Moden (6/21/2009)
Just to be clear on another point... it's not the shrinking of a database that causes fragmentation and performance problems. It's the regrowth that causes all of that. Agreed that the shrinking of a database is what usually necessitates regrowth, though.
It's the shrink operation that causes index fragmentation. Shrink works by taking pages that are towards the end of the file and moving them as far forward as it can. Because it does that page by page, if can completely reverse the order of index pages on disk.
Shrink/grow combinations can (depending on the disk layout) cause file-level fragmentation.
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
June 21, 2009 at 12:58 pm
GilaMonster (6/21/2009)
Jeff Moden (6/21/2009)
Just to be clear on another point... it's not the shrinking of a database that causes fragmentation and performance problems. It's the regrowth that causes all of that. Agreed that the shrinking of a database is what usually necessitates regrowth, though.It's the shrink operation that causes index fragmentation. Shrink works by taking pages that are towards the end of the file and moving them as far forward as it can. Because it does that page by page, if can completely reverse the order of index pages on disk.
Shrink/grow combinations can (depending on the disk layout) cause file-level fragmentation.
I was thinking only of the file-level fragmentation. Learned something new. Thanks Gail.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2009 at 7:10 am
Thank you everyone, so just to be cleared, Here is the order:
1.CheckDatabaseIntegrity
2.Update Statistics
3.Reorginize Index
4.Rebulding Index
5.Cleanup History
6.Maintance Cleanup Task
(Is this order ok for system db and user db?)
My back up runs at 2:00 a.m should I ran this before the backup?
I am confused in reference
Reorginize Index and Rebulding Index, I thought this is part of optimization process, but which one should I include in the process is it rebuild index right?
Thank you
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply