SQL Server 2005 Maintenance Plan (too long!)

[former_member578055]

Hi all,

I have a SQL Maint. Plan on MS SQL2005, but it takes to long to be executed.

What is the normal Maint. Plan?

Mine is like:

Check DB Integrity

Shrink

Rebuild index

Update statistics

Clean Up History

Maint. Clean Up task

All for P01 Db.

Thanks!

[former_member578055]

After some investigation, I've done a new plan to solve this.

Thanks to all!

[Former Member]

Pedro,

you have taken a shotgun approach. Usually i prefer to have CheckDB,Rebuild Indexes and update statistics as separate maintenance plans and clean history can be part of backup plan.

1.) CheckDB- Best approach is restore the database onto another server and run checkDB on it. So that you are taking out this high I/O intensive operation from your production server. This should be scheduled job after full backup job.

2.) Rebuild indexes- If you rebuild indexes from maintenance plans it will take forever, as SAP DB's have thousands of indexes. I prefer rebuilding few indexes every week, like top used, top growing tables,indexes with high usuage. you can come up with what suits your needs.

3.) Update statistics- Again instead of maintenance plan is use "sp_updatestats" command, this will update only tables which require statistics update.

4.) Shrink- What are shrinking in your maintenance plan? i hope you are not shrinking Data files, Although you can shrink your log file.

I hope it helps.

Thanks

Mushtaq

[former_member578055]

Hi,

By point:

1- No questions

2- How to put the rebuild only for those tables?

3- No Questions

4- Why not?

Thanks!

[Former Member]

>

> 2- How to put the rebuild only for those tables?
> Thanks!

you do it by using T-SQL scripts. for example from this[script|http://2pttechnology.com/web/forums/thread/18.aspx], you get top n largest tables and i rebuild all the indexes on them by using temporary table and looping over it.

To start with, i refer this [script site |http://ola.hallengren.com/] , it has scripts for each step of your maintenance plan.

> 4- Why not?
> 

Never shrink your data files on SQL Server. It has lot of negative impact on your database, and to fix it you have rebuild every index on the database which may be painful task on large DB's. Like i said you can shrink T-logs.

check dis out: http://blogs.msdn.com/b/temenosonsql/archive/2010/03/16/why-you-should-not-shrink-your-database.aspx

I hope it helps

Mushtaq

[former_member578055]

Hi,

Ive read some more info and I'm confused.

SAP does not recomend rebuild index, just on rare ocasions

Ok.

But, How to detect those ocasions?

One other questions and statistics? when to run? I've tried but it is always canceled and takes too long (DB with 300gb; 200Gb allocated; 100GB free of disk; 32Gb of RAM; 8 cores)

When to run, and how to run?

Using the normal steps of maintenance of MS SQL 2005?

Regards,

Pedro

[Former Member]

Hi,

But, How to detect those ocasions?

After archiving a lot of data for example.

One other questions and statistics?  When to run, and how to run?

Well, first of all you should have 'Auto Update Statistics Asychronously' turned on (check note 879941). Use SQL Server Management Studio and try to run:

use <SID>

go

exec sp_updatestats

go

That should do the job in a timely fashion. You could run the statistcs job on a weekly basis using a maintenance plan.

Regards,

Sven

[former_member578055]

Hi,

Thank you for the answer.

One other question, the actual data files are like this:

Dataflie Size Free % Next growth

P01DATA1 107.838 26,00 16.175

P01DATA2 102.019 27,00 15.302

P01DATA3 102.019 36,00 15.302

As you can see, the space is not evenly spread by the data files.

I've saw that this should be done.

My question is, how?

Thanks!

[Former Member]

>

> SAP does not recomend rebuild index, just on rare occasions

Well i don't buy on this recommendation, if your indexes are heavily fragmented (>80% atleast) mostly they are hurting the performance. Please check this [article|http://sqlcat.com/top10lists/archive/2007/09/12/top-10-best-practices-for-sql-server-maintenance-for-sap.aspx]. Please go through all the points and especially point no.9

Regarding you data file sizes, keep all your data files sizes equal and next growth value for all three files to be equal. Don't auto grow by %, rather use some fix value such as 100mb for all data files. over the time, you will see %Free space will be evenly distributed. In the mean while this not going to hurt you much.

Thanks

Mushtaq