How can I quickly detect and resolve SQL Server Index fragmentation for a database?

Question

I've come across a situation where my database operations on many of my SQL Server database's tables have become very slow as more records have been added (5s for single insert to a table with 1 million records).

I've estimated that this may be due to fragmented indexes on tables in my database, because I have many tables that use (and need to use) a uniqueidentifier type for Primary Key clustered indexes.

How can I evaluate whether this is the case or not, and how can I resolve the fragmentation issues (perhaps once per deployment) if there are any fragmentation issues?

I would like a solution that works in SQL Server 2005 and higher (I am specifically working with SQL Server in an Azure database (12.0.2000.8)).

Answer 1

To check the fragmentation percentage on a table

SELECT a.index_id, 
       NAME, 
       avg_fragmentation_in_percent, 
       fragment_count, 
       avg_fragment_size_in_pages 
FROM   sys.Dm_db_index_physical_stats(Db_id('dbName'), Object_id('tableName'), 
       NULL, 
              NULL, NULL) AS a 
       INNER JOIN sys.indexes b 
               ON a.object_id = b.object_id 
                  AND a.index_id = b.index_id 

To fix the fragmentation either rebuild or reorganize the index on the table

ALTER INDEX ALL ON table_name REBUILD 

OR

ALTER INDEX index_name ON table_name REBUILD 

-- REORGANIZE

ALTER INDEX ALL ON table_name REORGANIZE

OR

ALTER INDEX index_name ON table_name REORGANIZE

OR

DBCC DBREINDEX ('table_Name')

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

Answer 2

Since you already know the table in which fragmentation is suspected, you can use the below T-SQL statements to identify Fragmentation.

To get the Database ID of a DB:

select name , database_id 
from sys.databases
where name = 'Database_Name'

Run these queries under the database in which the table belongs to.

To get the object ID of a table:

select * from sys.objects where name = 'Table_name'

To find the fragmentation percentage in a table:

select TableName=object_name(dm.object_id)
       ,IndexName=i.name
       ,IndexType=dm.index_type_desc
       ,[%Fragmented]=avg_fragmentation_in_percent   ,dm.fragment_count      ,dm.page_count      ,dm.avg_fragment_size_in_pages     
,dm.record_count     ,dm.avg_page_space_used_in_percent  from 
sys.dm_db_index_physical_stats(14,420770742,null,null,'SAMPLED') dm 
--Here 14 is the Database ID 
--And 420770742 is the Object ID of the table
join sys.indexes i on dm.object_id=i.object_id and
dm.index_id=i.index_id   order by avg_fragmentation_in_percent desc

If the fragmentation of an index is more than 20% then we can try rebuilding that index:

ALTER INDEX Index_Name 
ON [Database_name].[Table_Name] REBUILD

OR - to rebuild all the indexes in the table

ALTER INDEX ALL ON [Database_name].[Table_Name]
REBUILD WITH (FILLFACTOR = 80)

OR - by using DBCC DBREINDEX

DBCC DBREINDEX ('[Database_name].[ Table_Name]')

DBCC DBREINDEX ('[Database_name].[ Table _Name]', 
'Index_Name, 85)

If Fragmentation count is below 20%, you could do away with an Index rebuild or ReOrg.. instead just update statistics for that Index/Table.

To run update statistics on a table with FULLSCAN:

UPDATE STATISTICS [Database_Name].[Table_Name] 
with FULLSCAN

To Update Stats of an Index

UPDATE STATISTICS [Database_Name].[Table_Name] Index_Name
with FULLSCAN

I have given each of these as separate queries for you to get a better understanding of what is being done. Hope this helps