August 9, 2009 at 9:40 am
I have a number of indexes set up on our production database server and I'm sure that alot of them are no longer needed, is there an easy way (or difficult way) to tell which indexes are not being used any more
August 9, 2009 at 10:25 am
Best is to capture this content for a while (to have persistent data)
select db_name(database_id) as DbName
, case db_id() when database_id then object_name(object_id)
else cast(object_id as varchar(128))
end as ObjectName
, *
from sys.dm_db_index_usage_stats
where db_name(database_id) not like '[_]%'
order by DbName, last_user_seek, last_user_scan, last_user_lookup, ObjectName
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 9, 2009 at 11:52 am
Very cool, Johan...
--Jeff Moden
August 9, 2009 at 12:12 pm
Remember that the values in sys.dm_db_index_usage_stats are reset whenever SQL is restarted, so an index that shows unused is only since the last restart.
BOL: "The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed."
August 9, 2009 at 12:31 pm
Also note that indexes that have not been used at all (no seeks, no scans, no lookups, no updates) will not appear in index_usage_stats. An index has to be used in some way, even if it's just updated, to appear in that DMV.
Be very careful when using this DMV to decide what indexes can be dropped that you don't drop indexes that are only used at specific times (month ned reports been a classic example)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2009 at 8:58 am
Here's a short SQLServerPedia article about it: http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use
August 10, 2009 at 12:01 pm
The best way is to create a job which calls the stored procedure and schedule every week. Here is the stored procedure. This SP will store unused indexes in the table and then you can analyze based on the data.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROC [dbo].[SP_UNUSEDINDEXES]
AS
/*
The results of this will show you how many times each index has been used,
and how often it's been updated. If you have 0 seeks, scans, and lookups
but a ton of updates, it's a good bet that the index in question is a
waste of time and can be deleted
*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MNT_UNUSEDINDEXES]') AND type in (N'U'))
--DROP TABLE [dbo].[MNT_UNUSEDINDEXES]
CREATE TABLE dbo.MNT_UNUSEDINDEXES(
[database_name] [sysname] NOT NULL,
[object_name] [sysname] NOT NULL,
[index_name] [sysname] NULL,
[type_desc] [nvarchar](60) NULL,
[user_seeks] [bigint] NULL,
[user_scans] [bigint] NULL,
[user_lookups] [bigint] NULL,
[user_updates] [bigint] NULL,
[last_user_seek] [datetime] NULL,
[last_user_scan] [datetime] NULL,
[DropIndexStatement] [nvarchar](271) NULL,
[CurrentDate] datetime default getdate()
) ON [PRIMARY]
DECLARE @SQLSTR VARCHAR(1000)
DECLARE @NAME VARCHAR(50)
DECLARE DBCURSOR CURSOR FOR
SELECT NAME FROM SYS.SYSDATABASES WHERE NAME NOT IN ('MASTER','TEMPDB','MODEL','MSDB')
OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLSTR =
'USE '+@NAME+
' INSERT INTO SC_ADMINDB.DBO.MNT_UNUSEDINDEXES
SELECT '''+@NAME +''', o.name AS object_name, i.name AS index_name
, i.type_desc, u.user_seeks, u.user_scans, u.user_lookups
, u.user_updates, u.last_user_seek, u.last_user_scan
, ''Drop index '' + i.name + '' on '' + o.name as DropIndexStatement
,getdate()
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
AND i.index_id = u.index_id
AND u.database_id = DB_ID()
WHERE o.type ''S''
and isnull(u.user_updates,0) > 0
and i.type_desc NOT IN(''HEAP'',''CLUSTERED'')
ORDER BY (convert(decimal(19,4),ISNULL(u.user_seeks, 0))
+ ISNULL(u.user_scans, 0)
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc
, user_updates desc, o.name, i.name'
EXEC(@SQLSTR)
--PRINT @SQLSTR
FETCH NEXT FROM DBCURSOR INTO @NAME
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
August 10, 2009 at 12:03 pm
Back in my AS/400 days, I wrote my own "Where Used - Last Used" tool to capture similar information. I could do the same thing here and put the results into a table so that when SQL is restarted I have the historical usage information and can look at it over time. Then it will be safer to make decisions about dropping indexes.
And here's another script.
-- 1) stats exist, but never used
select db.name AS DATABASENAME, obj.name as 'Table'
, idx.name as 'Index', idx.rowcnt, idx.used/128 'MB', substring(ix.type_desc,1,20), 'Unused Stats', is_unique as 'unique', is_primary_key as 'primary'
from sys.dm_db_index_usage_stats ixu
join sys.databases db on db.database_id = ixu.database_id
join sys.objects obj on ixu.object_id = obj.object_id
join sys.sysindexes idx on ixu.object_id = idx.id and ixu.index_id = idx.indid
join sys.indexes ix on ixu.object_id = ix.object_id and ixu.index_id = ix.index_id
where last_user_seek is null and last_user_scan is null and last_user_lookup is null
and ixu.index_id > 0 and db.database_id = db_id() and obj.type 'S' -- and ix.type_desc = 'NONCLUSTERED'
and ix.is_unique = 0 and ix.is_primary_key = 0 -- don't include unique or primary keys
UNION
-- 2) Stats were never created, so index is not used
SELECT DB_NAME() AS DATABASENAME,
obj.name as 'Table',
B.NAME AS 'Index',
idx.rowcnt, idx.used/128 'MB', substring(b.type_desc,1,20), 'No Stats', is_unique as 'unique', is_primary_key as 'primary'
FROM SYS.OBJECTS obj
INNER JOIN SYS.INDEXES B ON obj.OBJECT_ID = B.OBJECT_ID
join SYS.SYSINDEXES idx ON B.OBJECT_ID = idx.ID and B.index_id = idx.indid
WHERE NOT EXISTS (SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID)
AND obj.TYPE 'S'
and B.NAME is not NULL
and b.is_unique = 0 and b.is_primary_key = 0 -- don't include unique or primary keys
ORDER BY obj.name , idx.NAME
August 11, 2009 at 1:40 am
Thakyou all for your useful suggestions, I shouldn'y have any problems now 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply