Tag: Reorganising Indexes

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'