Database Maintenance, Optimization and Integrity Tasks Questions
-
(Sorry, but I decided to repost this to see if I can get more detailed responses.)
I am in the process of setting up Maintenance Plans for our Databases. We have 2 User Databases (10 GBs and 20 GBs). There are probably about 15,000 records loaded into each database nightly Monday thru Friday.
My first question is about the options on the Optimization Tab. I would like to select the 'Reorganize data and index pages' and 'Reorganize pages with the original amount of free space' options. (Note: The Fixed Option under Server Properties on my server is cleared. So, for the Index Fill Factor, SQL Server selects the optimal setting for performance.) Does the Fixed Option determines the Free Space for the index pages (verses data pages)? Is this the original FILLFACTOR that was specified when the indexes were first created? Would it be okay to use the 'Reorganize pages with the original amount of free space' option?
I am really not sure if I should use the 'Update the statistics used by the query optimizer' and the 'Remove unused space from database files' options. Would these options be advantageous or would they cause the Maintenance Plan to take too long on databases of this size?
I know what these 2 options are used for but the measurements I am not sure of, for example with the 'Update the statistics used by the query optimizer' it wants to know the 'Percentage of the database to sample'. I know that 100% would produce the most accurate statistics but what about the time it would take?
The 'Remove unused space from database files' option wants to know 'Shrink database when it grows beyond __ MB' and 'Amount of free space to remain after shrink __ % of the data space'.
For the 'Shrink database when it grows beyond __ MB', if my database is 10 GB should I enter the growth target size (for 12 GBs should I enter 12,000 MB) or the actual grouwth (for 2 GBs should I enter 2,000 MB)?
For the 'Amount of free space to remain after shrink __ % of the data space' should I enter 10% or 20%.
I guess what I am looking for is sometype of guideline for the Optimization and Integrity Tabs of the Maintenance Plan Wizard to avoid guessing.
Also, in my research I found where it was suggested to schedule the Integrity Tab Tasks before the Optimizations Tab Tasks. What is your opinion on this?
Thanks in advance, Kevin
-
Kevin,
If you select the first option, reorganize data and index options, you should not be concerned about updating the statistics. The statistics will be updated with the index rebuild.
Regarding the options reorganize pages with original amount of free space, it will rebuild the indexes with the fill factor value with which the index was last refreshed.
If the second option is selected, than indexes will be created with the new fill factor value specified.
Regarding shrinking the database, i won't be concerned in your case. You are adding new data continously to your database. But if you run into a scenario for shrinking files, you might want to shrink after you rebuild your indexes.
-
For databases of this size, I'd be setting up custom maintenance routines. Using the maintenance plans is ok for smaller databases, but their all-or-nothing approach can lead to long maintenance windows.
eg: the "Reorganize pages..." option re-indexes all tables in the database regardless of their fragmentation level. A better solution would be to run DBCC SHOWCONTIG first to determine which table need re-indexing and then just do those few.
--------------------
Colt 45 - the original point and click interface
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply