SQL with Manoj

Well-designed indexes on tables/views improves the performance of queries run against a database by reducing disk I/O operations and consume fewer system resources. Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, DELETE, or MERGE statements.

SQL Server Database Engine automatically maintains indexes whenever INSERT, UPDATE, or DELETE operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered/fragmented in the database. Fragmentation exists 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.

SQL Server has provided ways to reduce/remedy fragmentation by Reorganizing or Rebuilding an Index.

1. Reorganize Index: uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

2. Rebuild Index: drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

So, which approach to go with, Reorganize or Rebuild?

First of all we’ll check the fragmentation status of a particular table or an index, by using sys.dm_db_index_physical_stats function.

Here we will check the status of all indexes in [HumanResources].[Employee] table of [AdventureWorks2012] database.

select 
	x.name as Index_Name,
	s.database_id, s.object_id, s.index_id, s.index_type_desc, -- General info columns
	s.avg_fragmentation_in_percent, s.fragment_count, s.avg_fragment_size_in_pages -- stats we need
from sys.indexes x
cross apply sys.dm_db_index_physical_stats (
	DB_ID(N'AdventureWorks2012'), -- database_id
	x.object_id, -- object_id
	x.index_id, -- index_id
	NULL, -- partition_number
	NULL) as s -- mode
where s.object_id = object_id('HumanResources.Employee')

Output:

We will use the following criteria setup by Microsoft to detirmine the best method to correct the fragmentation:

avg_fragmentation_in_percent value | Corrective statement
> 5% and <= 30%			     ALTER INDEX REORGANIZE
> 30%				     ALTER INDEX REBUILD WITH (ONLINE = ON)*

The above results shows that the Indexes [AK_Employee_LoginID] and [AK_Employee_NationalIDNumber] requires Rebuild and rest of them are good.

–> TO REBUILD:

--// To Rebuild [AK_Employee_LoginID] Index, run the following query:

USE AdventureWorks2012;
GO

ALTER INDEX AK_Employee_LoginID
	ON HumanResources.Employee
REBUILD;
GO

--// To Rebuild All indexes, use following query:

USE AdventureWorks2012;
GO

ALTER INDEX ALL
	ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
GO

–> TO REORGANIZE:

--// To Reorganize [AK_Employee_NationalIDNumber] Index, run the following query:

USE AdventureWorks2012;
GO

ALTER INDEX AK_Employee_NationalIDNumber
	ON HumanResources.Employee
REORGANIZE;
GO

--// To Reorganize All indexes on [HumanResources].[Employee], use following query:

USE AdventureWorks2012;
GO

ALTER INDEX ALL
	ON HumanResources.Employee
REORGANIZE;
GO

So, check the fragmentation status by using the DM function sys.dm_db_index_physical_stats and then decide to do either REORGANIZE or a REBUILD on an Index.