This is the first post in an ongoing SQL Scripts series, in which I list useful queries that I use in my day to day database administration.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE YourDatabase GO SELECT s.[name] +'.'+t.[name] AS table_name ,i.NAME AS index_name ,index_type_desc ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent ,record_count AS table_record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips INNER JOIN sys.tables t on t.[object_id] = ips.[object_id] INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id] INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id) ORDER BY avg_fragmentation_in_percent DESC |
The above query is database specific. If you want to run it on every user database on your server, simply replace DB_ID() with NULL in the call to sys.dm_db_index_physical_stats.
i made bite ahead query to get all DB fragmentation details in one go.
Exec sp_MSforeachdb ‘USE ?; ‘select sys.databases.name AS DBname, sys.tables.name As tablename, sys.tables.object_id, sys.dm_db_index_physical_stats.avg_fragmentation_in_percent, sys.dm_db_index_physical_stats.index_type_desc from sys.dm_db_index_physical_stats (DB_ID(N’?’), Null, Null, Null, ‘SAMPLED’)
JOIN sys.tables ON sys.tables.object_id=sys.dm_db_index_physical_stats.object_id
JOIN sys.databases ON sys.databases.database_id=sys.dm_db_index_physical_stats.database_id
where avg_fragmentation_in_percent >=10
order by DBname,avg_fragmentation_in_percent DESC
hope you got result.