Strange SQL Server 2005 Maintenance Plans

[fuzzyocelot]

Hi everyone,

I was recently asked to maintain a new server that is running SQL Server 2005 (no service packs) which was setup by a vendor. So I started looking through it and found a few interesting things that I thought I'd ask about. Note: I already told them that it needs SP2.

They had set up some maintenance plans which was good. However, I thought they were a bit strange. Maybe it's just me. Does anyone know of a good reason why someone would want to reorganize the indexes and then rebuild them in the same plan for the same databases? The rebuild is set to occur right after the reorganization. This just seems strange to me.

They set up a separate plan for integrity checks per database, a separate plan for the optimizations per database, and a separate plan for backing up each database. There are two user databases. So there are 6 total maintenance plans. Plus, the jobs for each plan are scheduled 5 minutes apart. These 6 jobs run every single night. Maybe it's just not clear to me, but wouldn't it have been easier to just put them in one or two plans instead of six? I will probably end up asking them why they set it up this way because I don't understand.

By the way, this server hasn't gone into production yet so I don't know how high the transactions will be or what the fragmentation level will be. Maybe they're assuming it will be high which is why they scheduled the maintenance plans to run every night. Actually, they run around 2:00 in the morning with the backups running last.

Which brings me to another question. Would it be a good idea to do a backup before the maintenance plans run (i.e. integrity checks and optimization) in case something breaks while the maintenance plan is running and then do a separate backup afterwards in case it fixed something? Just asking.

Thanks!

 

[olahallengren]

It does not make any sense to do both a reorganize and a rebuild on the same index. If you're using the Maintenance Plans you normally do either a rebuild or a reorganize.

You could also do this dynamically by checking the index fragmentation levels with sys.dm_db_index_physical_stats. This way you only have to do something about the indexes with a high fragmentation level. You could then do a rebuild or a reorganize based on the fragmentation level. Microsoft has some recommendations on this.

http://msdn2.microsoft.com/en-us/library/ms189858.aspx

I have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size.

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html

Ola Hallengren

http://ola.hallengren.com

 

[fuzzyocelot]

Thanks for replying and thanks for the links. Actually, I've used your stored procedure before on another server and it's been working great.

Since this particular server isn't even in production or even being tested heavily, I don't see how the indexes could need to be reorganized or rebuilt every day yet. I plan on checking the fragmentation levels on a regular basis once it does go into production. You confirmed what I was thinking.

The main purpose of my post was to find out if there's a reason to reorganize and rebuild the indexes in the same plan. I didn't think it made sense either but still wanted another opinion just in case. Thanks for confirming my suspicions.