Task 14: Indexes

   Here you can find a link that provide you with detailed information about indexes:

Index Overview, Index Architecture, Clustered Indexes, Nonclustered Indexes, Covering Indexes, How Indexes Improve Performance, Index Uniqueness, Data Pages & Extents, How Indexes Are Stored, The Relationship Between Index Size and Page Size, Other Things Stored in the Index, Retrieving Database Page Metadata, Index Statistics, Best Practices on How to Design Database Indexes, Maintaining Indexes for Top Performance

Index fragmentation is a subject that I would like to cover next. 

The 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 in the database (fragmented). 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.

Detecting Fragmentation

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

The result set returned by the sys.dm_db_index_physical_stats function includes the following columns.

avg_fragmentation_in_percent value > 5% and < = 30%  ALTER INDEX REORGANIZE

avg_fragmentation_in_percent value  > 30% ALTER INDEX REBUILD WITH (ONLINE = ON) 

USE AdventureWorks2012;
GO
-- Find the average fragmentation percentage of all indexes
-- in the HumanResources.Employee table.
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
-- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table.
ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee
REORGANIZE ;
GO
-- Reorganize all indexes on the HumanResources.Employee table.
ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE ;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B-tree structure of a SQL Server index