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!