November 15, 2011 at 7:37 am
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
November 15, 2011 at 7:39 am
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.
November 15, 2011 at 7:42 am
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.
November 15, 2011 at 7:42 am
I also posted the wrong version of the query. Should be an s.* at the end of that SELECT list.
November 15, 2011 at 7:50 am
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.
November 15, 2011 at 7:55 am
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.
November 15, 2011 at 7:57 am
As for proving a benefit, there's 9 indexes on this table and if four never get used they're just dead weight objects.
November 15, 2011 at 8:01 am
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?
November 15, 2011 at 8:05 am
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.
November 15, 2011 at 8:08 am
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).
November 15, 2011 at 8:09 am
aurato (11/15/2011)
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.
Are you saying that the indexes no longer reside on the publisher? If they do reside on the publisher, why would you want to remove them from the subscriber? If your subscriber is being used as a failover, of course those indexes aren't used. They WILL be when there is a disaster scenario.
Jared
Jared
CE - Microsoft
November 15, 2011 at 8:11 am
P.S. Check out those survey results...
9 index is "nothing"
http://sqlskills.com/BLOGS/PAUL/post/Survey-nonclustered-index-counts-(code-to-run).aspx
November 15, 2011 at 8:20 am
Can you tell us the primary reason for you wanting to drop these? Are you annoyed with the amount of indexes or are you running out of space? Obviously these indexes were put there for a reason, so one of us cannot give you justification for removing them. If you told us that you know for a fact that these are not required for any queries, then we could say to go ahead and drop them. However, just because they have not been used in 2 months does not mean that that they are not used at all. Many reports from my previous company were run quarterly or yearly. I can guarantee that the indexes required for these reports were not used more than 4 or 1 time a year, respectively. If someone came in there and dropped them with no research into which queries referenced these tables and columns and assumed they were not used at all because it had not been accessed in 11 months, I would have fired him/her on the spot.
Jared
Jared
CE - Microsoft
November 15, 2011 at 8:22 am
jared-709193 (11/15/2011)
aurato (11/15/2011)
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.
Are you saying that the indexes no longer reside on the publisher? If they do reside on the publisher, why would you want to remove them from the subscriber? If your subscriber is being used as a failover, of course those indexes aren't used. They WILL be when there is a disaster scenario.
Jared
It's not used as a failover. Before I got here they had issues with running reports against the publisher, so they set up replication to move the data to another server so that they could query against that instead. It's there to support reporting copies of the data.
November 15, 2011 at 8:23 am
Ninja's_RGR'us (11/15/2011)
P.S. Check out those survey results...9 index is "nothing"
http://sqlskills.com/BLOGS/PAUL/post/Survey-nonclustered-index-counts-(code-to-run).aspx
I still wouldn't want them there if they're not being used.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply