January 15, 2008 at 11:12 pm
Comments posted to this topic are about the item 2005 Unused indexes v2
Wilfred
The best things in life are the simple things
February 12, 2008 at 1:51 am
In the following line you need to make sure that your index is unique. So you have to check that you are on the right table. Otherwise you can get more indexes returned if they have the same name and index id because the index id is only unique within the table and not within the database. And it is not allowed to get more then one result on that line.
So when you put in the following then it will work.
and b.[id]=c.[object_id]
Select
(select used/128 from sysindexes b where b.name=c.name and c.index_id = b.indid [font="Arial Black"]and b.[id]=c.[object_id]) [/font]'MB',
from sys.dm_db_index_usage_stats a
join sysobjects as o
on (a.object_id = o.id)
join sys.indexes as c
on (a.object_id = c.object_id and a.index_id = c.index_id)
February 21, 2008 at 5:55 am
Pretty good idea. One thing that might make it a bit more useful though, is to turn it into a sproc and allow the dbid to be passed in.
February 21, 2008 at 7:09 am
Call me stupid here but I pasted this into SSMS and it had a bunch of syntax errors (see below). Now you might say my studio is not set up the same as yours, but when you post a script with very little information about it, what I expect is that it will just run. This might be useful, but honestly I don't want to spend the time debugging it to find out.
Msg 102, Level 15, State 1, Procedure vw_index_usage, Line 3
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure vw_index_usage, Line 5
Incorrect syntax near 'MB'.
Msg 102, Level 15, State 1, Procedure vw_index_usage, Line 6
Incorrect syntax near 'cols'.
Msg 102, Level 15, State 1, Procedure vw_index_usage, Line 7
Incorrect syntax near 'included'.
February 21, 2008 at 7:32 am
I ran into the same thing. There were some odd characters in the listing, might be from the web.
At any rate here's my copy
drop view vw_index_usage
go
create view vw_index_usage as
select object_name(a.object_id) 'table',
c.name "index",
(select used/128 from sysindexes b where b.name=c.name and c.index_id = b.indid and b.[id]=c.[object_id]) 'MB',
(select count(*) from sys.index_columns d where a.object_id = d.object_id and a.index_id = d.index_id and d.is_included_column = 0) "cols",
(select count(*) from sys.index_columns d where a.object_id = d.object_id and a.index_id = d.index_id and d.is_included_column = 1) "included",
(a.user_seeks + a.user_scans + a.user_lookups) "hits",
(a.user_updates) "updates",
a.last_user_update "stats_date",
cast(a.user_seeks + a.user_scans + a.user_lookups AS REAL) / cast(case a.user_updates when 0 then 1 else a.user_updates end as REAL) * 100 "ratio",
'alter index [' + c.name + '] on [' + object_name(a.object_id) + '] disable;' "SQLCmd"
from sys.dm_db_index_usage_stats a
join sysobjects as o
on (a.object_id=o.id)
join sys.indexes as c
on (a.object_id = c.object_id and a.index_id = c.index_id)
where o.type = 'U' -- exclude system tables
and c.is_unique = 0 -- no unique indexes
and c.type = 2 -- nonclustered indexes only
and c.is_primary_key = 0 -- no primary keys
and c.is_unique_constraint = 0 -- no unique constraints
and c.is_disabled = 0 -- only active indexes
and a.database_id = DB_ID() -- for current database only
go
February 21, 2008 at 12:08 pm
This worked. Thanks for your help.
May 16, 2008 at 4:59 am
Thanks a lot ! I didn't thought about that !
May 16, 2008 at 5:50 am
Thanks for fixing this bug.
I didn't put this into a stored procedure, because it's already isolating the current database (DB_ID() part)
The incorrect syntax issue is indeed caused by the web.
Wilfred
The best things in life are the simple things
June 24, 2009 at 8:40 am
Since the script is based on sys.dm_db_index_usage_stats, it only returns unused indexes that exist in sys.dm_db_index_usage_stats. Other unused indexes can be found by a different query with "WHERE NOT EXISTS (select ... from sys.dm_db_index_usage_stats ). Something like this:
SELECT DB_NAME() AS DATABASENAME,
--OBJECT_NAME(B.OBJECT_ID) AS 'Table',
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply