<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
These indexes are unused, right?
37 posts, Page 2 of 4
««
1
2
3
4
»
»»
These indexes are unused, right?
Rate Topic
Display Mode
Topic Options
Author
Message
SQLKnowItAll
SQLKnowItAll
Posted Tuesday, November 15, 2011 8:09 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614,
Visits: 3,130
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
Thanks,
Jared
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Post #1206034
Ninja's_RGR'us
Ninja's_RGR'us
Posted Tuesday, November 15, 2011 8:11 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,359,
Visits: 9,541
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
Post #1206035
SQLKnowItAll
SQLKnowItAll
Posted Tuesday, November 15, 2011 8:20 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614,
Visits: 3,130
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
Thanks,
Jared
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Post #1206050
aurato
aurato
Posted Tuesday, November 15, 2011 8:22 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 20, 2013 8:01 AM
Points: 254,
Visits: 373
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.
Post #1206051
aurato
aurato
Posted Tuesday, November 15, 2011 8:23 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 20, 2013 8:01 AM
Points: 254,
Visits: 373
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.
Post #1206052
Ninja's_RGR'us
Ninja's_RGR'us
Posted Tuesday, November 15, 2011 8:26 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,359,
Visits: 9,541
aurato (11/15/2011)
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.
You've clearly made up your mind, even before coming here.
So why are we still talking about this?
Post #1206053
aurato
aurato
Posted Tuesday, November 15, 2011 8:27 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 20, 2013 8:01 AM
Points: 254,
Visits: 373
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.
Post #1206057
SQLKnowItAll
SQLKnowItAll
Posted Tuesday, November 15, 2011 8:29 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614,
Visits: 3,130
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
Thanks,
Jared
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Post #1206058
aurato
aurato
Posted Tuesday, November 15, 2011 8:29 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 20, 2013 8:01 AM
Points: 254,
Visits: 373
Ninja's_RGR'us (11/15/2011)
aurato (11/15/2011)
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.
You've clearly made up your mind, even before coming here.
So why are we still talking about this?
I really am not understanding all this hostility. If I'd made up my mind they'd be deleted already and I wouldn't be posting here. I made a question on stackexchange too. I'm here because I'm trying to find the flaws in my reasoning. Which you're doing but you're being pretty snippy about it.
Post #1206060
Ninja's_RGR'us
Ninja's_RGR'us
Posted Tuesday, November 15, 2011 8:31 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,359,
Visits: 9,541
Time to see for yourself what we see in your situation.
Got 3 hours?
http://www.youtube.com/watch?v=Kcnj5bQghjY&feature=relmfu
http://www.youtube.com/watch?v=XmE_Utp8CY0
http://www.youtube.com/watch?v=RZ4YTg4S7HI&feature=relmfu
Don't be fooled by the title. They are all equally important and useful.
Post #1206062
« Prev Topic
|
Next Topic »
37 posts, Page 2 of 4
««
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
