Today I had a need to find the index fragmentation on one of my sql server database and I found this script buried on my computer, thought I will share it here:<\/p>\n
DECLARE @DatabaseID int<\/p>\n
SET @DatabaseID = DB_ID()<\/p>\n
SELECT DB_NAME(@DatabaseID) AS DatabaseName,
\nschemas.[name] AS SchemaName,
\nobjects.[name] AS ObjectName,
\nindexes.[name] AS IndexName,
\nobjects.type_desc AS ObjectType,
\nindexes.type_desc AS IndexType,
\ndm_db_index_physical_stats.partition_number AS PartitionNumber,
\ndm_db_index_physical_stats.page_count AS [PageCount],
\ndm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
\nFROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, ‘LIMITED’) dm_db_index_physical_stats
\nINNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
\nINNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
\nINNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
\nWHERE objects.[type] IN(‘U’,’V’)
\nAND objects.is_ms_shipped = 0
\nAND indexes.[type] IN(1,2,3,4)
\nAND indexes.is_disabled = 0
\nAND indexes.is_hypothetical = 0
\nAND dm_db_index_physical_stats.alloc_unit_type_desc = ‘IN_ROW_DATA’
\nAND dm_db_index_physical_stats.index_level = 0
\nAND dm_db_index_physical_stats.page_count >= 1000<\/p>\n
<\/p>\n
Best practice, you rebuild the index if the fragmentation is more than 40 % , if not a reorganization will suffice.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today I had a need to find the index fragmentation on one of my sql server database and I found this script buried on my computer, thought I will share it here: DECLARE @DatabaseID int SET @DatabaseID = DB_ID() SELECT DB_NAME(@DatabaseID) AS DatabaseName, schemas.[name] AS SchemaName, objects.[name] AS ObjectName, indexes.[name] AS IndexName, objects.type_desc AS ObjectType, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,358,658],"tags":[933,934,935,306],"class_list":["post-1569","post","type-post","status-publish","format-standard","hentry","category-database","category-how-to","category-sql-server-2","tag-check-index","tag-index-fragmentation","tag-reorganize-index","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1569","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1569"}],"version-history":[{"count":1,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1569\/revisions"}],"predecessor-version":[{"id":1570,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1569\/revisions\/1570"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1569"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1569"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1569"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}