Queries useful for diagnostics indexes fragmentation in MS SQL Server.
- Using Dynamic Management Fucntion (DMF) to determine index fragmentation level.
sys.dm_db_index_physical_stats
Input parameters:- database_id – Default =0 (for all databases) – database_id can be obtained from sys_databases
- object_id – Default=0 (all tables and views in gicen database)
- index_id – Default=-1 (all indexes for base, table or view)
- partition_number – Default=0 (all partitions from owning object)
- mode – Default=NULL ->’LIMITED’;
- LIMITED – fastest, scans the smallest number of pages. For index – only parent-level pages of B-tree are scanned
- SAMPLED – statistics based on a 1% sample of all pages in the index/heap. For indexes having less then 10k pages it will use DETAILED mode instead of SAMPLED
- DETAILED – Scans all pages and returns all statistics
--on big databases this query might takes long time to execute
SELECT
object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5;
--fragmentation level for object in given db
--db_id:DB_ID(N'name_of_db')
--object_id:OBJECT_ID(N'Object_name')
------
SELECT IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(
--db_id,
--object_id,
NULL, NULL , 'DETAILED') AS IPS;
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
2. Rebuild
When the Fragmentation is greater than 30: REBUILD
--REBUILD--
--OFFLINE--:
ALTER INDEX Index_Name ON Table_Name REBUILD
--ONLINE (enterprise version of mssql server):
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
-----------------------------------------------
--For all indexes in given table:
ALTER INDEX ALL ON Table_Name REBUILD;
3. Reorganize
When the Fragmentation percentage is between 15-30: REORGANIZE
ALTER INDEX ALL ON Table_Name REORGANIZE;