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!

  • Question: #1: Reorganized indexes have the stats updated for a rebuild. You will need to update stats on reorganized but not rebuilt indexes. #2: No the server will not need restarting. (Corrected my error on the reorganize.) – RLF Oct 16 '14 at 17:57
  • I thought only rebuild have the stats updated and for reorganize we have to manually run the update stats task – Info23 Oct 16 '14 at 17:59

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.

  • Yes, I will start analyzing olla's script and as always before implementing it if I have any questions, I will always get back to this forum...Thanks a lot, really appreciate your response! – Info23 Oct 16 '14 at 18:39
  • 1
    I just finished moving off of maintenance plans to Ola's scripts. You won't regret it. You get much more control over what's happening. For the record, SQL shouldn't be restarted often (I'd recommend only restarting for patching or system issues). When you restart SQL you clear all the buffers and plan cache, not to mention many of the DMVs that can help you troubleshoot performance issues. Just an additional item to consider. – Kris Gruttemeyer Oct 16 '14 at 18:52
  • how about the application servers? should it be restarted? – Info23 Oct 16 '14 at 19:21
  • 1
    If you have enterprise edition you ca use ONLINE=ON option to rebuild index while database is online but remember this option also takes 2 short term locks. If you have standard edition during rebuild index wont be available to yes application should not access database. Reorganize is always online operation no matter whatever be Edition – Shanky Oct 16 '14 at 19:25
  • 1
    Here's a great article about getting started with Ola's scripts, by the way. smattie.com/2012/09/04/ola-recommendations-for-his-script The application servers, if they are SQL, shouldn't be restarted unless necessary. I know that some companies stick to the old 'reboot weekly' mentality and that's all well and good but it's important to know that restarting the sql server service does have a cost. The server has to go to disk for every single query for it's first execution until the caches are built and that can make things a bit sluggish until the caches are 'warmed up'. – Kris Gruttemeyer Oct 16 '14 at 19:28

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.