Check SQL Server Index Fragmentation
What Is Index Fragmentation?
Fragmentation is a common problem in SQL Server databases, as it can lead to slow query performance, long transaction times, and an increase in data maintenance. Understanding and managing index fragmentation is an important part of maintaining your database. This article will discuss how to find index fragmentation and how to measure and monitor fragmentation, and how to defragment indexes.
Generally, to monitor for fragmentation, we should use the sys.dm_db_index_physical_stats DMV (Dynamic Management View).
Here are few simple queries using the above-mentioned DMV and some additional conditions that will list every index on every table in your database, ordered by the percentage of index fragmentation. This query will work on SQL2K5 or newer.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.alloc_unit_type_desc, indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID() ORDER BY indexstats.avg_fragmentation_in_percent desc |
The output will look like this:
Source: myadventuresincoding.wordpress.com
===== Or ======
The same above-mentioned DMV and some additional conditions will list every index on every table in your database about the fragmentation level. Depending on the size of the database it may take some time to execute.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE AdventureWorks -- Database Name GO 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(db_id(N'AdventureWorks'), -- Database Name 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 GO |
The output will look like this:
With the above-mentioned DMV and some additional conditions, we can find only the fragmentation level of a table, we may also use the below query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<span class="SQLKeyword">DECLARE</span> @db_id <span class="SQLKeyword">SMALLINT</span><span class="SQLOperator">;</span> <span class="SQLKeyword">DECLARE</span> @object_id <span class="SQLKeyword">INT</span><span class="SQLOperator">;</span> <span class="SQLKeyword">SET</span> @db_id <span class="SQLOperator">=</span> <span class="SQLFunction">DB_ID</span><span class="SQLOperator">(</span><span class="SQLString">N'AdventureWorks'</span><span class="SQLOperator">)</span><span class="SQLOperator">; -- Database Name</span> <span class="SQLKeyword">SET</span> @object_id <span class="SQLOperator">=</span> <span class="SQLFunction">OBJECT_ID</span><span class="SQLOperator">(</span><span class="SQLString">N'Production.BillOfMaterials'</span><span class="SQLOperator">)</span><span class="SQLOperator">; -- Table Name</span> <span class="SQLKeyword">IF</span> @object_id <span class="SQLKeyword">IS</span> <span class="SQLKeyword">NULL</span> <span class="SQLKeyword">BEGIN</span> <span class="SQLKeyword">PRINT</span> <span class="SQLString">N'Invalid object'</span><span class="SQLOperator">;</span> <span class="SQLKeyword">END</span> <span class="SQLKeyword">ELSE</span> <span class="SQLKeyword">BEGIN</span> <span class="SQLKeyword">SELECT</span> IPS<span class="SQLOperator">.</span>Index_type_desc<span class="SQLOperator">,</span> IPS<span class="SQLOperator">.</span>avg_fragmentation_in_percent<span class="SQLOperator">,</span> IPS<span class="SQLOperator">.</span>avg_fragment_size_in_pages<span class="SQLOperator">,</span> IPS<span class="SQLOperator">.</span>avg_page_space_used_in_percent<span class="SQLOperator">,</span> IPS<span class="SQLOperator">.</span>record_count<span class="SQLOperator">,</span> IPS<span class="SQLOperator">.</span>ghost_record_count<span class="SQLOperator">,</span> IPS<span class="SQLOperator">.</span>fragment_count<span class="SQLOperator">,</span> IPS<span class="SQLOperator">.</span>avg_fragment_size_in_pages <span class="SQLKeyword">FROM</span> sys<span class="SQLOperator">.</span>dm_db_index_physical_stats<span class="SQLOperator">(</span>@db_id<span class="SQLOperator">,</span> @object_id<span class="SQLOperator">,</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span> <span class="SQLString">'DETAILED'</span><span class="SQLOperator">)</span> <span class="SQLKeyword">AS</span> IPS<span class="SQLOperator">;</span> <span class="SQLKeyword">END</span> <span class="SQLKeyword">GO</span> |
The output will look like this:
