sys.dm_db_index_usage_stats:
Returns counts of different types of index operations and the time each type of operation was last performed.
user_updates:
Number of updates by user queries. This includes Insert, Delete, and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count increments by 1
Considered a cost since the system has to spend time to keep Indexes Updated due to Updates/Inserted/Deletes of underlying data. Thus if these Indexes are Un-used then why have them in the databases and take up resources to keep them updated.
Solution:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UnUsedIndexesByUser](
[LogDate] [datetime] NULL DEFAULT (getdate()),
[ServerName] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[SchemaName] [nvarchar](128) NULL,
[TableName] [nvarchar](128) NULL,
[IndexName] [sysname] NULL,
[CostFactor_NumUpdateOperationsToUnusedUndexes] [bigint] NULL,
[UserSeeks] [bigint] NULL,
[UserScans] [bigint] NULL,
[UserLookups] [bigint] NULL,
[UserUsageTotal] [bigint] NULL,
[SystemUsage] [bigint] NULL,
[LastUserUpdate] [datetime] NULL,
[LastUserSeek] [datetime] NULL,
[LastUserScan] [datetime] NULL,
[LastUserLookup] [datetime] NULL,
) ON [PRIMARY]
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
SET TRAN ISOLATION LEVEL READ UNCOMMITTED
EXEC sp_MSForEachDB
"IF '?' IN ('')
BEGIN
USE ?;
Declare @SQL Varchar(max)
Set @SQL=
'
INSERT INTO msdb..UnUsedIndexesByUser
(
[ServerName],
[DatabaseName],
[SchemaName],
[TableName] ,
[IndexName] ,
[CostFactor_NumUpdateOperationsToUnusedUndexes] ,
[UserSeeks] ,
[UserScans],
[UserLookups],
[UserUsageTotal],
[SystemUsage],
[LastUserUpdate],
[LastUserSeek] ,
[LastUserScan] ,
[LastUserLookup]
)
SELECT TOP 100
@@servername as ServerName
, DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, s.User_Updates as [UserUpdates]
, s.user_seeks as [UserSeeks]
, s.user_scans as [UserScans]
, s.user_lookups as [UserLookups]
, s.user_seeks + s.user_scans + s.user_lookups AS [UserUsageTotal]
, s.system_seeks + s.system_scans + s.system_lookups AS [System Usage]
, s.last_user_update as [LastUserUpdate]
, s.last_user_seek as [LastUserSeek]
, s.last_user_scan as [LastUserScan]
, s.last_user_lookup as [LastUserLookup]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE
OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND s.user_seeks < 100
AND s.user_scans < 100
AND s.user_lookups < 100
AND i.name IS NOT NULL
AND s.user_updates > 0
ORDER BY s.user_updates DESC'
USE ?;
Exec(@SQL)
End"
Select * from [msdb].[dbo].[UnUsedIndexesByUser] order by [TableName] ASC, [IndexName] ASC
|
|
|
|
|
|