Databases Reference
In-Depth Information
If we just restart the server or SQL Server instance and look for the statistics, it will show that
no index is used and will suggest dropping (
DROP
) all indexes; this is not right. So, keep your
SQL Server running for at least one business cycle, and then check for the statistics.
Business cycles differ from case tocase. Some tables and queries could
be in use every day, but some queries for some reports may execute once
in a month or year, may be by HR to see the performance of an employee,
especially at the time of yearly appraisal. So, let everything run, and finish
your business cycle, and then we will have an appropriate and correct picture.
How to do it...
There is one simple T-SQL query to execute for getting the information regarding unused
indexes. The query is given as follows:
--following query will show you which index is never used
SELECT
ind.Index_id,
obj.Name as TableName,
ind.Name as IndexName,
ind.Type_Desc,
indUsage.user_seeks,
indUsage.user_scans,
indUsage.user_lookups,
indUsage.user_updates,
indUsage.last_user_seek,
indUsage.last_user_scan,
'drop index [' + ind.name + '] ON [' + obj.name + ']' as
DropIndexCommand
FROM
Sys.Indexes as ind
JOIN
Sys.Objects as obj
ON
ind.object_id=obj.Object_ID
LEFT JOIN
sys.dm_db_index_usage_stats indUsage
ON
ind.object_id = indUsage.object_id
AND
ind.Index_id=indUsage.Index_id
Search WWH ::
Custom Search