Skip to content

Instantly share code, notes, and snippets.

@pfrozi

pfrozi/index_fragmentation.sql

Created February 15, 2024 13:43
Show Gist options
  • Save pfrozi/3620111d23e21eec14183fc9d7fd8b5c to your computer and use it in GitHub Desktop.
Save pfrozi/3620111d23e21eec14183fc9d7fd8b5c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
index_fragmentation.sql
SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(avg_fragmentation_in_percent,2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY [Fragmentation %] DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment