April 22, 2010 at 2:48 pm
The views sys.dm_db_index_usage_stats and sys.indexes SEEM to relate on the index_id attribute, but such is not actually the case.
While the index_id attribute shows the id of the index in the sys.indexes view, in sys.dm_db_index_usage_stats it shows the TYPE of index (HEAP, CLUSTERED etc). You cannot relate these 2 attributes to reveal detailed information about an indexes usage.
Does anyone have another approach?
thanks for your help everyone.
April 22, 2010 at 2:54 pm
This is what I use to find unused indexes on my SQL instances:
If Object_ID('dbo.Indexes_Unused_Select') is Null
Exec ( 'Create Proc dbo.Indexes_Unused_Select as Select 0')
Go
Alter Proc dbo.Indexes_Unused_Select
@Database sysname = 'ALL'
,@Index sysname = 'ALL'
,@Min_Row_Count int = 5000
,@Table sysname = 'ALL'
,@User_scans int = 5
,@User_Seeks int = 5
as
Set nocount on
Select @Database = Replace(@Database,'''','''''')
,@Index = Replace(@Index,'''','''''')
,@Table = Replace(@Table,'''','''''')
Declare @SQL Varchar(Max)
Declare @Unused_Indexes Table (
[DB_Name] sysname
,Table_name sysname
,[Index_Name] sysname null
,User_Seeks int
,User_Scans int
,User_Lookups int
,Row_Count int
,Used_Page_Count int
,Space_Reserved Varchar(50)
,[Type] sysname
)
DECLARE cr_db CURSOR static FOR
SELECT [name]
FROM Master.sys.Databases SD
WHERE SD.User_Access = 0 --MultiUser
AND SD.[State] = 0 --Online
And ([Name] = @Database or @Database = 'ALL')
open cr_db
While 0=0
Begin
Fetch Next
From cr_db
into @Database
If @@Fetch_Status <> 0
Break
begin Try
Set @SQL = 'Use ' + @Database + '
Select Db_Name() [DB_Name]
,Object_name(ius.Object_id) [Name]
,si.name [Index_Name]
,ius.User_Seeks
,ius.User_Scans
,ius.User_Lookups
,ps.row_count
,ps.used_page_count
,Case
When in_row_reserved_page_count * 8 < 2000 Then Cast(in_row_reserved_page_count * 8 as Varchar(20)) + ''KB''
When in_row_reserved_page_count * 8 / 512 < 5000 Then Cast(in_row_reserved_page_count * 8 / 1024 as Varchar(20)) + ''MB''
Else Cast(in_row_reserved_page_count * 8 / 1024 /1024 as Varchar(20)) + ''GB''
End As Space_Reserved
,si.type_desc [Type]
From sys.dm_db_index_usage_stats ius
Join sys.indexes si
on ius.Object_id = si.Object_id
and ius.index_id = si.Index_id
join sys.dm_db_partition_stats ps
on si.object_id = ps.object_id
and si.index_id = ps.index_id
Where ius.Database_ID = db_id()
and ( Object_Name(ius.Object_ID) = ''' + @Table + ''' or ''' + @Table + ''' = ''ALL'' )
and ( si.Name = ''' + @index + ''' or ''' + @index + ''' = ''ALL'' )
and row_count > ' + Cast(@Min_Row_count as Varchar(20)) + '
--and object_name(ius.object_id) =
and (User_scans < ' + Cast(@User_Scans as Varchar(10)) + '
and user_seeks < ' + Cast(@User_Seeks as Varchar(10)) + ')
order by DB_Name
,[Name]
,[Index_Name]'
Insert @Unused_Indexes
Exec (@SQL)
End Try
Begin Catch
Print Error_Message()
Print @Database
End Catch
End
April 22, 2010 at 3:05 pm
Hi Toby,
Thank you for the quick reply.
I was using a similar approach, but during testing turned up some weird results...
Here is a little sample which shows my confusion about index_id in which I created a dummy idx, called "idx_never_used"
select object_id('TAJS_ALERT')
2105058535
select name, index_id, type, type_desc from sys.indexes where object_id=2105058535
name index_id type type_desc
----------------------------------------------
NULL 00HEAP
PK_TAJS_ALERT 22NONCLUSTERED
alert 32NONCLUSTERED
idx_never_used682NONCLUSTERED
select database_id, index_id from sys.dm_db_index_usage_stats where object_id=2105058535
database_id index_id
--------------------
8 0
April 22, 2010 at 3:08 pm
As samples of joining the two tables together, here is a script by Glenn Barry from his DMV a day series.
-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;
Here is a script I use to determine Index statistic duplication in sys.dm_db_index_usage_stats
select db_name(s.database_id) as DBNAME, object_name(s.object_id) as ObjectName
, i.name as IndexName
,user_seeks, user_scans,user_lookups,user_updates,system_seeks, system_scans
,Row_number() over (partition by s.object_id,s.index_id order by s.index_id) as RowNum
From sys.dm_db_index_usage_stats s
Inner Join sys.indexes i
on i.object_id = s.object_id
And i.index_id = s.index_id
And s.database_id = db_id()
Where objectproperty(s.object_id,'ISMSShipped') = 0
Group by s.database_id,s.object_id,i.name,s.index_id, user_seeks, user_scans
,user_lookups,user_updates,system_seeks, system_scans
And then another script by Glenn that shows how to find unused indexes.
-- List unused indexes
SELECT OBJECT_NAME(i.[object_id]) AS [Table Name], i.name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.[object_id] = o.[object_id]
WHERE i.index_id
NOT IN (SELECT s.index_id
FROM sys.dm_db_index_usage_stats AS s
WHERE s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
AND database_id = DB_ID())
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC;
You can read more about my script here[/url].
You can read more about Glenns unused indexes script here.
And finally the Bad NC Index script here.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2010 at 3:11 pm
tew (4/22/2010)
Hi Toby,Thank you for the quick reply.
I was using a similar approach, but during testing turned up some weird results...
Here is a little sample which shows my confusion about index_id in which I created a dummy idx, called "idx_never_used"
select object_id('TAJS_ALERT')
2105058535
select name, index_id, type, type_desc from sys.indexes where object_id=2105058535
name index_id type type_desc
----------------------------------------------
NULL 00HEAP
PK_TAJS_ALERT 22NONCLUSTERED
alert 32NONCLUSTERED
idx_never_used682NONCLUSTERED
select database_id, index_id from sys.dm_db_index_usage_stats where object_id=2105058535
database_id index_id
--------------------
8 0
That means that your table is in a heap and has no index on it. Index_id of 0 is the table heap without Clustered Index. It typically does not have a name.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2010 at 3:24 pm
Hi Jason,
But there are 4 indexes, including the heap, defined on the table (please see the middle query). Yet when I query the dmv with the same object name, information for only one of the indexes on the same object is returned.
Sorry, the formatting didn't turn out very well.
I know I didn't run a query that used the index that I defined (idx_never_used), so why isn't it being returned when the dmv is queried?
thanks again,
Tim
April 22, 2010 at 3:27 pm
An entry is only created in the metadata, of that view, for an index after the index has been used. If the index has not been used - it won't appear (it's not supposed to appear anyway).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2010 at 3:39 pm
eureka.... after updating statistics on the table, all expected rows appear in sys.dm_db_index_usage_stats
Nice to have a breakthrough from time to time.
Thanks everyone for your help!
Tim
April 22, 2010 at 3:48 pm
Congrats and you're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply