Maintenance plans

Question

I am an accidental dba..I did post a message a while ago about Index fragmentation and some of you suggested maintenance plan and others suggested custom Olla's scripts. I have downloaded his scripts and it is a little overwhelming now, but I am going to take your advice and implement it as soon as possible. Now, just to feel a little comfortable with the entire database maintenance tasks, I plan to implement it using the maintenance plan wizard. I did go through the book written by Brad McGehee like some of you suggested.

I have this sql cluster that has roughly 18 to 20 database. Right now, I am working with just one. This is a small database, 30GB in size and has roughly 100 users. I queried the database to find the index fragment percent level using the following command

select dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
dbindexes.[fill_factor] as 'Fill Factor',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes as dbindexes on dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id

where indexstats.database_id = DB_ID() and indexstats.page_count > 1000 
and (indexstats.avg_fragmentation_in_percent >= 10 and  
indexstats.avg_fragmentation_in_percent <= 40)
order by dbtables.[name] 

Anywhere between 10 to 40%, I plan to re organize and update stats and greater than 40% I plan to rebuild. I ran the query and chose all the indexes that had above 40% fragment percent and 1000 pages and there were roughly around 15 tables and 21 indexes. I created a maintenance plan and chose the rebuild task for only those 15 tables. For the regorganize task, there were 10 tables with 25 indexes. I created a separate plan for this one too. I know that I have created two different plans, but I have never done this before and want to see how it works before I analyze scripts and make it more customized.

I have thought about the following maintenance plan apart from the back up plans that has already been set:

1) Index maintenance - bi weekly 2) history clean up - weekly 3) consistency checks - weekly

I have a few questions:

1) Update stats has never been done for this database. I did read that after reorganize index, I have to manually do update stats task. The auto stats update is enabled. Do I have to do update stats for only those indexes that get reorganized or update stats for the entire database. I did check how much time it would take by restoring a fresh copy of the database in the test system and running update stats for the entire database takes roughly 2 hrs time.

2) I will run these tasks during our maintenance window which is very limited. I am assuming I don't need to restart any servers after running these maintenance plans.

Your feedback and help will be really appreciated.

Thanks!

Answer 1

Index rebuild will update statistics of column with a equivalent of full scan. Reorganizing index will not update statistics at all.

For more detailed explanation Ben Nevarez has written more in his blog

1) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

2) By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

3) Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update column statistics.

4) Reorganizing an index, for example using ALTER INDEX … REORGANIZE, does not update any statistics.

The same can be verified from Paul Randal Post

2) I will run these tasks during our maintenance window which is very limited. I am assuming I don't need to restart any servers after running these maintenance plans.

NO restart is required after running Index rebuild or update statistics. Why people referred you Ola's script as it has ability to look for columns with outdated stats and ignore the updated one. The script has more benefit like it would minimize the time need to run these maintenance task as well as less logs would be produced.