Reasoning

Ever had some indexes in your database that just do not make sense?

For example:

Sales_12052017,

okay you might say, now we know when it is created.

But there are extended events or traces to capture these kind of things,  and that is not what naming your indexes is for.

 

The script

This script will check your indexes for non default naming (IX_) and rename them like this:

IX_TableName_ColumnName1_Columname2_Columname3

The script will also cut off the name if the name is too long.

 

DECLARE  @schemaname nvarchar(200),
		 @tablename nvarchar(200), 
		 @indexname nvarchar(200), 
		 @colname nvarchar(200), 
		 @indexcolumnid int, 
		 @cmd nvarchar(max),
		 @length int;

DECLARE c CURSOR FOR 
SELECT  DISTINCT S.name, 
				 T.name, 
				 I.name 
FROM sys.indexes I 
INNER JOIN sys.index_columns IC 
ON I.object_id = IC.object_id 
AND I.index_id = IC.index_id 
INNER JOIN sys.columns C 
ON ic.object_id = C.object_id 
AND IC.column_id = C.column_id 
INNER JOIN sys.tables T 
ON I.object_id = T.object_id 
INNER JOIN sys.schemas S 
on T.schema_id = S.schema_id
WHERE 
	 I.is_unique_constraint = 0 
	 AND I.is_primary_key = 0
     AND T.is_ms_shipped = 0 
     AND I.is_unique = 0 
	 AND (I.name not like '%IX%' and I.name not like'%PK%')
ORDER BY 
     t.name, I.name
	
open c
fetch next from c into @schemaname, @tablename, @indexname;
while @@FETCH_STATUS = 0
BEGIN
	SET @cmd = 'exec sp_rename N'''+@schemaname+'.'+@tablename+'.'+@indexname+'' +CHAR(39);
	set @length = len(@cmd);
	if @length is null
	BEGIN
	set @length = 0
	END
	
	DECLARE d CURSOR FOR
	SELECT  ic.index_column_id, 
			C.name  
	FROM sys.indexes I 
	INNER JOIN sys.index_columns IC 
	ON I.object_id = IC.object_id 
	AND I.index_id = IC.index_id 
	INNER JOIN sys.columns C 
	ON ic.object_id = C.object_id 
	AND IC.column_id = C.column_id 
	INNER JOIN sys.tables T 
	ON I.object_id = T.object_id 
	INNER JOIN sys.schemas S 
	on T.schema_id = S.schema_id
	WHERE 
	I.is_unique_constraint = 0 
	AND I.is_primary_key = 0
    AND T.is_ms_shipped = 0 
    AND I.is_unique = 0 
	AND (I.name not like '%IX%' and I.name not like'%PK%')
	AND I.name = @indexname
	ORDER BY 
	index_column_id ASC;

	OPEN d
	FETCH NEXT FROM d INTO @indexcolumnid,@colname
	WHILE @@FETCH_STATUS = 0
	BEGIN

	IF @indexcolumnid = 1 
	BEGIN
		set @cmd += ' , N''IX_'+@tablename+'_'+@colname+ ''
	END

	else
	BEGIN
		set  @cmd += '_'+@colname
	END

	FETCH NEXT FROM d INTO @indexcolumnid,@colname
	END
	CLOSE d
	DEALLOCATE d
	set @cmd = LEFT(@cmd,128+@length) -- 128 is the maxlength for the index name + prefixes
	SET @cmd += ''', N''INDEX'';'
	PRINT @cmd   
fetch next from c into @schemaname, @tablename, @indexname;
END
CLOSE c;
DEALLOCATE c;

 

 

 

What are forwarded records?

Forwarded records are records that reside on a new page when a heap table (table without clustered index) is updated, and the rows are no longer able to stay on the same page,

which causes a forwarding pointer to point to the new page that the row resides in.

Many forwarded records can cause serious I/O issues, and if this happens a lot, it is advised to create a clustered index on the table.

You can also rebuild the table to remove the forwarded records (high overhead).

 

 

sys.dm_db_index_physical_stats

sys.dm_db_index_physical_stats is the dmv used to find the forwarded records,

but this is a very I/O intensive dmv to query, which is why we will apply filtering in the script.

 

 

The script:

We will use a temp table to apply filtering, and only include the heap tables in our dmv checks.

 

SELECT O.*
INTO #temp
FROM   sys.objects O
inner join sys.tables AS T on o.object_id = T.object_id
INNER JOIN sys.schemas AS S 
ON T.schema_id = S.schema_id 
INNER JOIN sys.indexes AS I 
ON T.object_id = I.object_id 
AND I.type = 0;


SELECT  DB_NAME(db_id()) as DatabaseName,
		t.name as TableName,
		index_id, 
		index_type_desc,
		avg_fragmentation_in_percent,
		page_count,
		record_count, 
		forwarded_record_count 
FROM
#temp t
CROSS APPLY 
sys.dm_db_index_physical_stats
(
   DB_ID()
   ,t.object_id
   ,NULL
   ,NULL
   ,'DETAILED'
)
WHERE forwarded_record_count > 0 and t.is_ms_shipped =0

DROP TABLE #temp

 

 

I hope this script helped you achieve your goal, with the least amount of overhead.