Hello Readers,

Today, I am trying to put down few but very useful index related scripts. I have used these scripts many times with different versions of SQL Server.

1. Missing Index : Missing indexes are one reason why an SQL query takes longer (much longer) to complete. Here’s how to find out about them and fix the problem.

SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage], 
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC OPTION (RECOMPILE);

2. Unused Index : Once SQL Server system has been in use for a while you might find there are a number of indexes that exist on the system that aren’t being used. Fortunately, SQL Server provides a Dynamic Management View (DMV) named sys.dm_db_index_usage_stats that tracks your index usage.

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
 I.[NAME] AS [INDEX NAME], type_desc,
 coalesce(last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup) as LastUsed,
 USER_SEEKS, 
 USER_SCANS, 
 USER_LOOKUPS, 
 USER_UPDATES ,
 last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup,
 'drop index ['+I.[NAME]+'] on ['+OBJECT_NAME(S.[OBJECT_ID])+'];' as DropStatement
 FROM SYS.DM_DB_INDEX_USAGE_STATS AS S 
 INNER JOIN SYS.INDEXES AS I 
 ON I.[OBJECT_ID] = S.[OBJECT_ID] 
 AND I.INDEX_ID = S.INDEX_ID 
 WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 
 order by type_desc,coalesce(last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup) desc

3. Index Dependencies, size and Descriptions

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
set nocount on
declare @db varchar(2000)
declare @cmd varchar(8000)
declare @TabName varchar(100)
create table #T (Dbname varchar(500), TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), IndexKeys varchar(200), IndexSize int)
declare cur cursor for
select ltrim(upper('['+name+']'))
from master .. sysdatabases
where status & 512 <> 512
and dbid > 4
and name ='admin' ----------------------------------------------->> set database name here
order by name
open cur
fetch next from cur into @db
while @@fetch_status = 0 
begin
select @cmd = 'Use'+space(1)+@db
select @cmd = @cmd + '
DECLARE @TabName varchar(100)
DECLARE TCursor CURSOR FAST_FORWARD LOCAL FOR
SELECT ist.table_schema+''.''+ist.table_name 
FROM sys.sysobjects sys inner join INFORMATION_SCHEMA.TABLES ist on ist.table_name = sys.name
WHERE sys.xtype = ''U''
OPEN TCursor
FETCH NEXT FROM TCursor INTO @TabName
WHILE @@FETCH_STATUS = 0
 BEGIN
 INSERT INTO #T (IndexName, IndexDescr, IndexKeys)
 EXEC sp_helpindex @TabName
 UPDATE #T SET TabName = @TabName WHERE TabName IS NULL
 UPDATE #T SET DbName = db_name() where dbname is null
 FETCH NEXT FROM TCursor INTO @TabName
 END
CLOSE TCursor
DEALLOCATE TCursor
DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N''E''
UPDATE #T SET IndexSize = 
 ((CAST(sysindexes.used AS bigint) * @ValueCoef )/1024)/1024 
 from
 sys.sysobjects INNER JOIN sys.sysindexes ON sysobjects.id = sysindexes.id
 INNER JOIN #T T ON substring(T.tabname, patindex(''%.%'', T.tabname)+1, len (T.tabname)) collate SQL_Latin1_General_CP1_CI_AS = sysobjects.name 
 AND T.IndexName collate SQL_Latin1_General_CP1_CI_AS = sysindexes.name
'
exec(@cmd)
fetch next from cur into @db
end
close cur 
deallocate cur
SELECT @@servername, dbname, Tabname, indexname, indexdescr, indexkeys, convert(varchar(20), indexSize)+space(1)+'MB' as IndexSize
FROM #T
group by dbname, Tabname, indexname, indexdescr, indexkeys, indexSize
ORDER BY 2, len(indexsize) desc, replace(convert(varchar(20), indexsize), 'MB','') desc
DROP TABLE #T

There are a few more important scripts posted earlier

1. How to create Drop and Recreate Script of all Indexes in a database?

2. How to move Indexes (Cluster and Non Cluster) from Primary File group to New File group?

 

I hope this helps…

 

Thanks