SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


2005 Unused indexes v2


2005 Unused indexes v2

Author
Message
Wilfred van Dijk
Wilfred van Dijk
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2458 Visits: 1363
Comments posted to this topic are about the item 2005 Unused indexes v2

Wilfred
The best things in life are the simple things
blank
blank
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 77
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 and b.[id]=c.[object_id]) '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)
SuperDBA-207096
SuperDBA-207096
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2959 Visits: 711
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.
mgaert
mgaert
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 36
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'.



SuperDBA-207096
SuperDBA-207096
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2959 Visits: 711
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
mgaert
mgaert
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 36
This worked. Thanks for your help.



Kuzco-207164
Kuzco-207164
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 21
Thanks a lot ! I didn't thought about that !
Wilfred van Dijk
Wilfred van Dijk
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2458 Visits: 1363
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
homebrew01
homebrew01
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12144 Visits: 9222
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





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search