September 15, 2010 at 1:19 pm
finally got around to writing a simple query to track all unused indexes so i can email it out via SSRS. but for some reason it lists all the indexes in the database for each table
select a.database_id, b.name as database_name, d.name as table_name, a.index_id, c.name as index_name, a.user_seeks, a.user_scans, a.user_lookups, a.user_updates,
a.last_user_seek, a.last_user_scan, a.last_user_lookup, a.last_user_update,
a.system_seeks, a.system_scans, a.system_lookups, a.system_updates,
a.last_system_seek, a.last_system_scan, a.last_system_lookup, a.last_system_update
from sys.dm_db_index_usage_stats a,sys.databases b, sysindexes c, sysobjects d
where a.database_id in (10)
and a.database_id = b.database_id
and c.indid = a.index_id
and d.id = a.object_id
and c.name is not null
and d.id > 97
and c.rows > 0
and d.id not in(53575229,637245325, 669245439, 685245496, 701245553,2073058421,2105058535 )
order by d.name, c.name
September 15, 2010 at 1:28 pm
edit: there are a few syntax issues with your script, such as cartesian products when you select multiple tables without any join criteria.
Here is a script I've been using for unused indexes across all databases..I'm not sure where I got it from, but kudos to whoever wrote it (Glenn Berry maybe?)
SELECT TOP 1
DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
,user_updates
,system_updates
-- Useful fields below:
--, *
INTO #TempUnusedIndexes
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
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
AND user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND s.[object_id] = -999 -- Dummy value to get table structure.
;
-- Loop around all the databases on the server.
EXEC sp_MSForEachDB 'USE [?];
-- Table already exists.
INSERT INTO #TempUnusedIndexes
SELECT TOP 10
DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
,user_updates
,system_updates
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
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND i.name IS NOT NULL -- Ignore HEAP indexes.
ORDER BY user_updates DESC
;
'
-- Select records.
SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
-- Tidy up.
DROP TABLE #TempUnusedIndexes
September 15, 2010 at 1:53 pm
thx for your help. originally had the inner joins in there but used ands to see if it would change anything after it wasn't working. with your help i also found that i was joining the wrong columns together.
a lot of times i forget about the little shortcut functions like db_name() and use a join to get the data
plan is to dump these daily for selected databases into a central database and then run reports off it
select a.database_id, database_name = db_name(), table_name = OBJECT_NAME(a.[object_id]), a.index_id, c.name as index_name, a.user_seeks, a.user_scans, a.user_lookups, a.user_updates,
a.last_user_seek, a.last_user_scan, a.last_user_lookup, a.last_user_update,
a.system_seeks, a.system_scans, a.system_lookups, a.system_updates,
a.last_system_seek, a.last_system_scan, a.last_system_lookup, a.last_system_update
from sys.dm_db_index_usage_stats a
--inner join sys.databases b on a.database_id = b.database_id
inner join sys.indexes c on c.object_id = a.object_id and a.index_id = c.index_id
--inner join sys.objects d on d.object_id = c.object_id
where a.database_id in (10)
--and c.name is not null
--and d.object_id > 97
--and c.rows > 0
AND OBJECTPROPERTY(a.[object_id], 'IsMsShipped') = 0
--and d.object_id not in(53575229,637245325, 669245439, 685245496, 701245553, 2073058421,2105058535,1993058139,1993058136,2025058250,2057058364 )
--order by d c.name
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply