Creating Maintenance Plans - 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)

Creating Maintenance Plans

Author

Topic

jacksja
Starting Member

4 Posts

Posted - 2010-04-22 : 13:13:32

Hi everyone,

I need some guidance on the best practices for creating maintenance plans that will help in optimizing and tuning the database.

I am creating database backups using the maintenance plans tasks drag and drop icons.

The order of my user backup maintenance plan is setup as follows:

1. Backup database task
2. Rebuild Index task
3. Update Statistics task
4. Shrink Database task

I'm not sure if I'm setting up the above steps in the correct order. is there any documentation regarding the order in which these tasks should be performed?

Any advice given will be greatly appreciated

Regards,
James

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-22 : 14:41:46

You should NOT shrink the database on a scheduled basis. There is a reason why the file sizes are at that size. Shrinks should only be done manually and only if you know you don't need that bigger size in the near future. Shrinks cause performance issues while it is running, causes heavy fragmentation of the files, and more performance penalty when the file needs to grow out again.

You also should not rebuild the indexes via the maintenance plan as it doesn't select smart options. Instead you should use custom code to do it. There are lots of options out there for this custom code, but here's mine: http://weblogs.sqlteam.com/tarad/archive/2009/11/03/DefragmentingRebuilding-Indexes-in-SQL-Server-2005-and-2008Again.aspx

Your order is fine.

I don't use any maintenance plans. Instead I only use custom code. In case that's the way you want to do it too (custom code gives you better options), here's all of my code: http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

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

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-22 : 14:42:39

A while back I blogged on what my production systems look like as far as jobs. Here's that blog: http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

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

Subscribe to my blog

jacksja
Starting Member

4 Posts

Posted - 2010-04-22 : 14:57:59

Tara,

I greatly appreciate your response. Thank you very much.

-James

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-22 : 15:32:25

You're welcome, glad to help.

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

Subscribe to my blog

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-04-24 : 21:23:40

One thing you should be aware of - when you rebuild your indexes, the statistics are updated. If you reorganize the index the statistics will not be updated. Instead of relying on the maintenance plan update statistics task, which by default updates all statistics you can use an Execute SQL Task and execute the following:

Use YourDatabase; Execute sp_updatestats @resample = 'RESAMPLE';

The procedure sp_updatestats has logic that will only update statistics that need to be updated. Using the resample option tells the procedure to use the previous sampling rate that was used to create the statistics instead of the default sampling rate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources