I have read the recommended mtce tasks for EV server at http://seer.entsupport.symantec.com/docs/332255.htm.
I must admit I am perplexed at the recommeded order specified in this document which is
It is recommended that the following SQL maintenance procedures be performed weekly for all EV, CA, and DA databases in the order listed:
-
-Rebuild Indexes
-
-Update Statistics
-
-Shrink Databases
The Microsoft site actually recommends the following Best Practice (see http://msdn.microsoft.com/en-us/library/ms190488%28v=SQL.90%29.aspx)
Best Practices
Consider the following information when you plan to shrink a database:
-
A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
-
Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
-
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
-
Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.
Bolding is mine.
Shrink Database actually causes the fragmentation of indexes as can be seen by this reference - so why on earth would one do the maintenance jos in the order specified in the Symantec Doc.
Additionally, in relation to the VAULR STORE Databases in particular I would think that they would be continually growing (unless archives are deleted faster than they are added), particulalrly for thoses databases associated with journalling.
SO shrinking will not really help much at all!!
Secondly the rebilding of indexes actually updates statistics anyway - so why do this after a rebuild index operation.
Lastly Rebuild indexes as a regular weekly job seems to me to be overkill.
Microsoft recommend that rebuild indexes be performed when fragmentation is over 30% (see http://msdn.microsoft.com/en-us/library/ms189858.aspx).
Would it not be more astute to set a fill factor on the indexes for these databases (particulalrly those with heavy inserts) to say 50 - 70%, and then monitor fragmentation and perform rebulid indexes when fragmentaion s in fact over 30%.
Hence would not a better plan be to
regularly reorganize indexes when fragmentation is detected to be over say 15%.
Would anyone care to comment!!!
