Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. After I reorganized index it worked perfectly fine. Here is the quick script I wrote to find fragmentation of the database for all the indexes. It returns index fragmentation details for every index in the current database.
SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, b.name, ps.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id WHERE ps.database_id = DB_ID() ORDER BY ps.OBJECT_ID GO
You can REBUILD or REORGANIZE Index and improve performance. Here is article SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script for how to do it.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
28 Comments. Leave new
thank you, that’s helpful.
i got a script that automates the index maintenance (if 10% fragmented use reorganize, and if 30% or more use rebuild).. however after performing the reindex, the database queries somehow take longer to executed (lots of users got timeouts)… if i stop the job (no reindex is performed), the queries go back to normal time… do you know what happened? is it due to the statistics? thx
what is index id 0 and name NULL in the result of this query????
I can see for the same object id,it has entry for each index(which is fine) but it also have few entries with 0 index id and name is NULL. what is this???
Thanks Pinal. Here is another query which returns very quickly.
SELECT dbschemas.[name] as ‘Schema’,
dbtables.[name] as ‘TableName’,
dbindexes.[name] as ‘Index’,
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