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 1234»»»

These indexes are unused, right? Expand / Collapse
Author
Message
Posted Tuesday, November 15, 2011 7:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 6:20 AM
Points: 271, Visits: 415
select o.name as TableName, i.name as IndexName, p.reserved_page_count * 8.0 / 1024 as SizeInMB, s.*
from sys.dm_db_index_usage_stats s
inner join sys.objects o on s.object_id = o.object_id
inner join sys.indexes i on i.index_id = s.index_id and i.object_id = o.object_id
inner join sys.dm_db_partition_stats p on i.index_id = p.index_id and o.object_id = p.object_id
where o.name = 'TableName'
--AND last_user_seek is null and last_user_scan is null and last_user_lookup is null

If last_user_seek, last_user_scan, and last_user_lookup are null that (and the last server restart was weeks ago) that basically means the index is never used, yeah? I know there are system_scans and system_lookups but I'm not exactly sure what they are and why they happen.

Same question on stackexchange

http://dba.stackexchange.com/questions/7906/determining-that-indexes-on-a-table-are-unused
Post #1205983
Posted Tuesday, November 15, 2011 7:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
I'd wait at least 1 month before choosing to drop anything. End of month or anything might need those.

System usage is still usage BTW.
Post #1205986
Posted Tuesday, November 15, 2011 7:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 6:20 AM
Points: 271, Visits: 415
I'm trying to figure out what a system process would want with it. If it's something like a consistency check then it's usage that isn't important because it doesn't need to exist.

And I can check with our IT Support people to find out the last time it was rebooted.
Post #1205990
Posted Tuesday, November 15, 2011 7:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 6:20 AM
Points: 271, Visits: 415
I also posted the wrong version of the query. Should be an s.* at the end of that SELECT list.
Post #1205993
Posted Tuesday, November 15, 2011 7:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
aurato (11/15/2011)
I'm trying to figure out what a system process would want with it. If it's something like a consistency check then it's usage that isn't important because it doesn't need to exist.

And I can check with our IT Support people to find out the last time it was rebooted.



I don't know for sure why it's used but it's being used. Might be FK check or something similar, but it's used. That's all I care about.


You can check the create date of tempdb to get that as well. Just make sure your period covers end of months and if possible end of <relevant periods>.


I'm not a big fan of dropping indexes unless I can really prove there's a gain involved. Which you don't do in your script.
Post #1206007
Posted Tuesday, November 15, 2011 7:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 6:20 AM
Points: 271, Visits: 415
Well, the create date of tempdb is September 7, 2011 and of the five indexes that don't have any user seeks/scans/lookups 4 of them don't have any system scans/lookups either.

The table I'm looking at is a replicated table on the subscriber side of transactional replication. I'm guessing that those four existed on the publication side and are used there (confirmed just now for several of them) and that whoever set up replication just scripted out the table for the subscriber DB.
Post #1206014
Posted Tuesday, November 15, 2011 7:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 6:20 AM
Points: 271, Visits: 415
As for proving a benefit, there's 9 indexes on this table and if four never get used they're just dead weight objects.
Post #1206018
Posted Tuesday, November 15, 2011 8:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
aurato (11/15/2011)
As for proving a benefit, there's 9 indexes on this table and if four never get used they're just dead weight objects.


I can't make that call for you.

Also I don't know replication so I don't know how & what consequences it could have.



Just a P.S. 4 unused indexes that are never written to and never read to are not THAT harmful.

Are they filtered? => maybe a wrong plan is ignoring them

Are they unique keys? => can't drop

What size are they taking? => 1mb, 1 tb? Anywhere in between?

When's the last time you had to maintain them?

Are you really that short on HD space that you need to worry about 10 MB <insert your real figures> of indexes.


Why are you focussing on that table rather than the script on the whole DB and finding the biggest possible dead weight that has to most space used & writes without reads?
Post #1206026
Posted Tuesday, November 15, 2011 8:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 6:20 AM
Points: 271, Visits: 415
Ninja's_RGR'us (11/15/2011)
aurato (11/15/2011)
As for proving a benefit, there's 9 indexes on this table and if four never get used they're just dead weight objects.


I can't make that call for you.

Also I don't know replication so I don't know how & what consequences it could have.



Just a P.S. 4 unused indexes that are never written to and never read to are not THAT harmful.

Are they filtered? => maybe a wrong plan is ignoring them

Are they unique keys? => can't drop

What size are they taking? => 1mb, 1 tb? Anywhere in between?

When's the last time you had to maintain them?

Are you really that short on HD space that you need to worry about 10 MB <insert your real figures> of indexes.


Why are you focussing on that table rather than the script on the whole DB and finding the biggest possible dead weight that has to most space used & writes without reads?



It was just something I came across by accident and out of curiosity. And I must have been unclear, these things are being updated all the time. Constantly being written to, never being used for searches.

EDIT: I wanted to add some indexes to this table but it just seems like the list of them is already so long. Something about it irks me, I don't know.
Post #1206030
Posted Tuesday, November 15, 2011 8:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
If you need the new index then you need the new index.


Short of massive space shortage I wouldn't start with "what index can I drop?".


I would start with is there a useful index I can extend with this 1-2 columns (or included columns).
Post #1206033
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse