index fragmentation – Run SQL Server BLOG

I do it for registered servers:

Declare @TableIndex table (ServerName sql_variant, DBName nvarchar(128), SchemaName varchar(128), TableName nvarchar (128), IndexName nvarchar (128), index_type_desc nvarchar (60),FragPercent float, record_count bigint, create_date datetime, modify_date datetime);

INSERT into @TableIndex 
exec SP_MSforeachdb @command1 = 
            'use [?];
			 IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'',''SQLMonitor'')
			 BEGIN

                SELECT 
					
					SERVERPROPERTY(''ServerName'')  
					,DB_NAME() as DBName
					,s.[name]  AS SchemaName
					,t.[name] AS TableName
					 ,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
					 ,create_date
					 ,modify_date
					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)
					where record_count>1000
					ORDER BY record_count desc, avg_fragmentation_in_percent DESC
				END'
select * from @TableIndex 
go

Declare @TableIndex table (ServerName sql_variant, DBName nvarchar(128), SchemaName varchar(128), TableName nvarchar (128), IndexName nvarchar (128), index_type_desc nvarchar (60),FragPercent float, record_count bigint, create_date datetime, modify_date datetime);

INSERT into @TableIndex

exec SP_MSforeachdb @command1 =

'use [?];

IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'',''SQLMonitor'')

BEGIN

SELECT

SERVERPROPERTY(''ServerName'')

,DB_NAME() as DBName

,s.[name] AS SchemaName

,t.[name] AS TableName

,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

,create_date

,modify_date

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)

where record_count>1000

ORDER BY record_count desc, avg_fragmentation_in_percent DESC

END'

select * from @TableIndex

You can often check the index defragmentation. It is useful if you start checking with the biggest table, because the biggest effect is there.
I like to check the indexes with the creation and modification date. If you rebuild the index your index modification date will be updated.

USE UserDB     -- with modifydate -order by rowcount
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
 ,create_date
 ,modify_date
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 record_count desc, avg_fragmentation_in_percent DESC

USE UserDB -- with modifydate -order by rowcount

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

,create_date

,modify_date

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 record_count desc, avg_fragmentation_in_percent DESC

If you want to see for all database you can replace the

DB_ID()

with

NULL

in the script.

Tag: index fragmentation

Collect Index defragmentation

Index fragmentation with table row count and index creation and modification date