What Is Index Fragmentation?
Over time, as records are inserted, updated, and deleted, your tables and indexes become fragmented. This fragmentation can lead to poor performance of not only your SELECT queries, but also your INSERT, UPDATE, and DELETE operations.
How Do I Find Index Fragmentation?
Index fragmentation can be found by querying the built in sys.dm_db_index_physical_stats DMV. To get readable, useful information you’ll also need to join your query to other DMVs such as sys.indexes and sys.tables. Below is a simple query that will provide a list of indexes, fragmentation percentage, and record counts for each table in a database.
USE YourDatabase
GO
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, indexstats.page_count,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC
No comments:
Post a Comment