aurato (11/15/2011)
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.
Ok, so how do you know those indexes are not being used. The query you gave us is not going to tell you if these are used or not. It only tells you if they have been accessed since the last restart. What if someone used to run a report that they have forgotten about. All of the sudden they remember it and start running it again. My CEO used to ask for a specific report only when the Board of Directors asked for it. Sometimes it was monthly, sometimes it was over a year.
The only way to tell if these indexes are used is to determine what accesses the columns that are indexed. Ask developers, search through report code, stored procedures. Nobody here can give you the "ok" to drop these. If you are that concerned about it, talk to a superior and tell them that you are convinced that they are not being used. Then explain that dropping them may affect performance if they are in fact used on something. Save the script for the index, and put it back when someone complains. If your business tolerates that (which one of my previous employers did), then do that. Otherwise, don't try to fix something that isn't broken 🙂
Jared
Jared
CE - Microsoft