Update Statistics, Rebuilding Indexes, Reorganising Indexes

There are lots discussions and recommendations on the net around this topic among SQL Server community. What is the difference between index rebuilding and re-organising? When do you choose one over another? What the update stats do to your database? Do you need to update statistics after rebuilding indexes?

I’m pleased to share the findings of my extensive research into this very important topic of SQL Server database administration. Hopefully, the recommendations below will be useful to you. They have all been trialed and tested – I’ve played with my databases a lot over the years.

Update statistics facts

  • Update stats updates query optimisation statistics on a table or indexed view
  • By default, the query optimiser updates statistics as necessary to improve the query plan, however sometimes additional statistics need to be created for best results
  • Updating statistics ensures that queries compile with up-to-date statistics
  • Note that updating statistics causes queries to recompile
  • Take into account performance tradeoff between improving query plans and the time it takes to recompile queries
  • Update stats can use tempdb to sort the sample of rows for building statistics
  • Make sure AUTO_UPDATE_STATISTICS settings on the database is ON so that the query optimiser continues to routinely update statistics
  • Query execution times are slow.
  • Insert operations occur on ascending or descending key columns
  • After maintenance operations that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows

When to run Update Statistics

  • Query execution times are slow.
  • Insert operations occur on ascending or descending key columns
  • After maintenance operations that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows

When not to run Update Statistics

Generally I follow the principles outlined in Technet “Using Statistics to Improve Query Performance

  • Statistics does not to be updated after operations such as rebuilding, defragmenting, or reorganizing an index. Why? Because the distribution of data is not changed after those operations
  • In addition, when index on a table or view is rebuild with ALTER INDEX REBUILD or DBCC DBREINDEX the query optimizer updates statistics. This occurs as a byproduct of the internal implementation of these operations and is not done because statistics need to be updated
  • The query optimiser does not update statistics after defragmentation or reorganization operations because these operations do not drop and recreate the index

How it’s done in T-SQL?

Index Fragmentation

Modifications to the database can cause index fragmentation when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. What are the options?

According to Paul Randal if an index has less than 1000 pages and is in memory, don’t bother removing fragmentation. http://www.sqlskills.com/blogs/paul/post/where-do-the-books-online-index-fragmentation-thresholds-come-from.aspx. Other options are rebuilding indexes and reorganising indexes.

Rebuilding Indexes

  • Rebuilding an index drops and re-creates the index
  • It removes fragmentation
  • It compacts pages and reclaims disk space
  • Fill factor can be set during the operation
  • It reorders the index rows in contiguous pages
  • Rebuilding or reorganizing small indexes often does not reduce fragmentation as pages of small indexes are stored on mixed extents

Reorganising Indexes

  • Index reorganisation defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical
  • Reorganising compacts the index pages
  • Reorganising an index uses minimal system resources

How to rebuild / reorganise indexes?

  1. Run the following T-SQL to obtain size and fragmentation information for the data and indexes
SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

2. Check avg_fragmentation_in_percent value against Microsoft Best practices.

If  avg_fragmentation_in_percent value > 5% and < = 30% run ALTER INDEX statement with the REORGANIZE clause

If avg_fragmentation_in_percent value > 30% run ALTER INDEX statement with REBUILD clause

In my opinion Index and Statistics Maintenance script from Ola’s solution is the best for busy DBA’s. I use the scrips on majority of my databases with execution levels based on best practice guidance

To rebuild or reorganize indexes with fragmentation and update modified statistics on all user databases run the following

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

7 comments for “Update Statistics, Rebuilding Indexes, Reorganising Indexes

  1. vims
    August 23, 2013 at 6:20 am

    hello ,

    why not decrease avg_fragmentation_in_percent value and why show three entry fror same index ?
    I also try drop and recreate and also set fillfactor 70 – 95 but not effect.
    what is reason and how to solve
    below is sample

    IndexName avg_fragmentation_in_percent TotalFrags avg_page_space_used_in_percent NumPages
    ———————————————————————————————-
    IdX_Juriid 0.0120136612490775 2118 90.2693723745985 58267
    IdX_Juriid 2 16 98.7015443538424 350
    IdX_Juriid 83.3333333333333 6 34.5688164072152 6

    More then 100 table show avg_fragmentation_in_percent value more then 70 ( I also drop and recreate and but not any changes ) ,

    How to solve it ?
    Please guid me.

    • dba
      September 16, 2013 at 3:51 am

      Hi vims

      You can ignore fragmentation for indexes with less than 1000 pages.
      See @PageCountLevel = 1000 in my solution

      Cheers

      • Sudhir
        November 21, 2013 at 7:03 am

        Good job.

  2. Manikandan
    August 30, 2016 at 10:40 am

    i have created Maintenance plans for Rebuild index and Reorganize index.
    If i do Updatestatistics, will it affect that rebuild index and reorganize index jobs?

    • dba
      August 30, 2016 at 11:52 am

      REBUILD Index updates index statistics only.
      REORGANIZE Index does not update any statistics.
      UPDATE STATISTICS updates both index and column statistics.

      So if you prefer to keep the statistics from REBUILD statement then use UPDATE STATISTICS with COLUMN option to update column statistics only

  3. Krish
    March 16, 2017 at 10:58 pm

    Hello Felix,

    My name is Krish and i’m currently working for medical client as a DBA. Recently one of our application vendor asked us(me) to update stats on the tables where the fragmentation percentage is below 5%. We have >=5 to 22% reorg and after 22% rebuild jobs set up in PROD DB servers. I never seen this kind of requirement in my experience. Is that really give any benefit? Can we achieve this using ola’s scripts?

    Thanks

    • dba
      March 18, 2017 at 5:13 am

      Hi Krish,

      The fact is when index on a table or view is rebuild with ALTER INDEX REBUILD or DBCC DBREINDEX the query optimizer updates statistics and there is no need to update statistics after defragmentation or reorganization operations because these operations do not drop and recreate the index.

      I don’t believe you would benefit from updating stats when fragmentation is below 5%.
      To improve performance work on what your queries are waiting on – and identify what problem you’re really trying to fix.

Leave a Reply

Your email address will not be published. Required fields are marked *