Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

2005 Unused indexes v2 Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2008 11:12 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:51 AM
Points: 928, Visits: 1,112
Comments posted to this topic are about the item 2005 Unused indexes v2

Wilfred
The best things in life are the simple things
Post #443416
Posted Tuesday, February 12, 2008 1:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 1, 2008 8:01 AM
Points: 369, 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)
Post #454258
Posted Thursday, February 21, 2008 5:55 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, 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.
Post #458499
Posted Thursday, February 21, 2008 7:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 3:06 PM
Points: 7, 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'.



Post #458535
Posted Thursday, February 21, 2008 7:32 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, 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


Post #458543
Posted Thursday, February 21, 2008 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 3:06 PM
Points: 7, Visits: 36
This worked. Thanks for your help.


Post #458748
Posted Friday, May 16, 2008 4:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 21, 2009 2:01 AM
Points: 4, Visits: 21
Thanks a lot ! I didn't thought about that !
Post #501904
Posted Friday, May 16, 2008 5:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:51 AM
Points: 928, Visits: 1,112
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
Post #501943
Posted Wednesday, June 24, 2009 8:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:19 AM
Points: 2,826, Visits: 8,462
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




Post #741087
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse