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


These indexes are unused, right?


These indexes are unused, right?

Author
Message
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4964 Visits: 3694
aurato (11/15/2011)
jared-709193 (11/15/2011)
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


Why stop at a year? Maybe it runs every two years. Maybe it's a decade-ly report. I see your point, but I don't know any way to determine that they're not being used other than finding every single query that accesses this table (i.e. lots) and checking the execution plans of each one.

And I am doing research. This is my research now : /

It seems like the consensus is "don't do it it's not a big deal". But every book I've read says that you suffer performance degradation on inserts for each index that you have. I'm just trying to get to the bottom of this. No need to be hostile and talk about how I'm inches from being justifiably fired.


Now we understand your real concern. You don't want indexes there that are unused because you feel that it may affect performance. Let me ask you this... Are you seeing problems with performance on your inserts?

Jared

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
aurato
aurato
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 415
jared-709193 (11/15/2011)


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.


That's exactly why I'm here. Because I wasn't sure and I was asking if this was enough.


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 Smile

Jared


I sent an email to the VP of IT before I even made this post, explaining all of that.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28775 Visits: 9671
I don't mean to be snippy and that's not my intention.


I guess I'm too tired. Logging off.
aurato
aurato
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 415
jared-709193 (11/15/2011)

Now we understand your real concern. You don't want indexes there that are unused because you feel that it may affect performance. Let me ask you this... Are you seeing problems with performance on your inserts?

Jared


When any large number of inserts or updates happens on the publication server, replication falls hours behind and kills report performance. I don't know if that's due to insert issues, though. I can't say that I've ever gone "Man that insert took way too long."
aurato
aurato
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 415
Ninja's_RGR'us (11/15/2011)
I don't mean to be snippy and that's not my intention.


I guess I'm too tired. Logging off.


It's fine. You've been extremely cordial in all my other threads that you've posted in. I didn't know I was striking a nerve here.
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28775 Visits: 9671
aurato (11/15/2011)
jared-709193 (11/15/2011)

Now we understand your real concern. You don't want indexes there that are unused because you feel that it may affect performance. Let me ask you this... Are you seeing problems with performance on your inserts?

Jared


When any large number of inserts or updates happens on the publication server, replication falls hours behind and kills report performance. I don't know if that's due to insert issues, though. I can't say that I've ever gone "Man that insert took way too long."



Just a PS. Insert can also suffer from LACK of indexes (check the exec plan for table scans for FK checks).
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28775 Visits: 9671
I think you'd be better off starting a new thread with replication getting 1 hour behind as the title.


You'll get better help that way to solve your real issue ;-).

Usually indexes are not the #1 culprit for this but I don't know how to debug this so I'm useless on that problem.

L8r.
aurato
aurato
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 415
Ninja's_RGR'us (11/15/2011)
I think you'd be better off starting a new thread with replication getting 1 hour behind as the title.


You'll get better help that way to solve your real issue ;-).

Usually indexes are not the #1 culprit for this but I don't know how to debug this so I'm useless on that problem.

L8r.


I really didn't come here with replication in mind at all. In my head I just wanted to get rid of something if it wasn't being used. I'm a purist I guess.
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4964 Visits: 3694
aurato (11/15/2011)
jared-709193 (11/15/2011)

Now we understand your real concern. You don't want indexes there that are unused because you feel that it may affect performance. Let me ask you this... Are you seeing problems with performance on your inserts?

Jared


When any large number of inserts or updates happens on the publication server, replication falls hours behind and kills report performance. I don't know if that's due to insert issues, though. I can't say that I've ever gone "Man that insert took way too long."


Well, let's start with the fact that when you insert data into a replicated table in an array of 1000 records it is replicated as 1000 inserts. Not a set of 1000. So replication can get behind fairly quickly. The indexes are maintained on each individual server and should not affect replication. Also, if your servers are in different locations (WAN) you may want to set it up to pull from the subscriber instead of push. Also, where is your distributor? These things can all affect replication.

As far as the indexes... If I was reasonably sure that these are not being used I would drop them and save the script. If at some point someone complains about the performance of a yearly report, you tell them that there is not an index to help performance on that specific report and that you can help them improve performance by building one. I would always protect myself by being ready to say "well, it did not run that slow last time because there is more data in the table this year than last year. I can probably improve performance with this though." Then create the index and document WHY it was created somewhere.

Jared

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
aurato
aurato
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 415
jared-709193 (11/15/2011)
aurato (11/15/2011)
jared-709193 (11/15/2011)

Now we understand your real concern. You don't want indexes there that are unused because you feel that it may affect performance. Let me ask you this... Are you seeing problems with performance on your inserts?

Jared


When any large number of inserts or updates happens on the publication server, replication falls hours behind and kills report performance. I don't know if that's due to insert issues, though. I can't say that I've ever gone "Man that insert took way too long."


Well, let's start with the fact that when you insert data into a replicated table in an array of 1000 records it is replicated as 1000 inserts. Not a set of 1000. So replication can get behind fairly quickly. The indexes are maintained on each individual server and should not affect replication. Also, if your servers are in different locations (WAN) you may want to set it up to pull from the subscriber instead of push. Also, where is your distributor? These things can all affect replication.

As far as the indexes... If I was reasonably sure that these are not being used I would drop them and save the script. If at some point someone complains about the performance of a yearly report, you tell them that there is not an index to help performance on that specific report and that you can help them improve performance by building one. I would always protect myself by being ready to say "well, it did not run that slow last time because there is more data in the table this year than last year. I can probably improve performance with this though." Then create the index and document WHY it was created somewhere.

Jared


I'm pretty bad with replication. So I'll try to explain as best I can. The distributor, which I take to mean the distribution database, is on the subscribing server. The servers are in the same building and LAN. And your second paragraph is what I wanted to do, trying to clear it with my boss.

Also for anyone reading this who's interested in the extra info, I posted the same question on stackexchange at

http://dba.stackexchange.com/questions/7906/determining-that-indexes-on-a-table-are-unused
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