InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 17:32:11 GMT20http://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxThanks everybody for your comments and insight.Tue, 15 Nov 2011 09:29:12 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxCheck out this:[url]http://msdn.microsoft.com/en-us/library/ms151706.aspx[/url]I couldn't find anything really about placing the distribution database on the subscriber, but that's because it is not really done. You see, when you create a publication, it creates a log reader job on the distributor along with a distribution job. If these are on the subscriber, it still has to go back and read the info from the log on the publisher. This is basically a "remote distributor" without its own dedicated box. If you post this question in another thread, I'm sure you will have many people telling you this is a bad idea and they may have better explanations for it.Thanks,JaredTue, 15 Nov 2011 09:27:11 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxOooops.... Pretty Long thread (was almost lost :-))...[b]Few recommendations:[/b]• [u]Extra indexes are not always bad[/u]. And they are not burden on server also. For Example DW schema, you may have [b]N[/b] heavy indexes on huge table, NOT all are used for frequently but [u]NOT causing any problem as well because Data Load happens ones a month.[/u]• Index utilization guess based on query may not be a good idea. It should be [u]justified by Developers / Architects[/u].• If you have any [u]Data Dictionary[/u] for the application, please dig in & try to identify their usage. (It’s time consuming but will give you a firm base for your decision)Tue, 15 Nov 2011 09:18:37 GMTDevhttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]aurato (11/15/2011)[/b][hr]What's the reason why the distributor should be on the publication side? I'm sorry to admit I know next to nothing about the inner workings of replication.[/quote]I will look for an article, but from a practical sense think of it this way... If I insert 10,000 rows from table A to table B on the publisher, this goes into the distributor as 10,000 insert statements because replication actually reads the transaction log, not the SQL statement directly. Which means that the distributor has to run these at the subscriber. If the distributor is doing its work and the subscriber is doing its work, that is a lot of resources. However, the work is done as 1 statement on the publisher, so housing the distributor is not going to take up as much resources. Plus, the distributor has to read from the log of the publisher, so it makes sense to have it on the same machine in the situation that you do not have a dedicated distributor box. This is extremely simplified, but I will search for an article.Thanks,JaredTue, 15 Nov 2011 09:15:47 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxWhat's the reason why the distributor should be on the publication side? I'm sorry to admit I know next to nothing about the inner workings of replication.Tue, 15 Nov 2011 09:09:11 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxEasiest test is to script them, drop 'em all and see how much and if it helped.I still feel there's a bigger issue here.Tue, 15 Nov 2011 09:07:54 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]aurato (11/15/2011)[/b][hr][quote][b]jared-709193 (11/15/2011)[/b][hr][quote][b]aurato (11/15/2011)[/b][hr][quote][b]jared-709193 (11/15/2011)[/b][hr]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[/quote]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."[/quote]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[/quote]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 [url=http://dba.stackexchange.com/questions/7906/determining-that-indexes-on-a-table-are-unused]http://dba.stackexchange.com/questions/7906/determining-that-indexes-on-a-table-are-unused[/url][/quote]Well, that could certainly be an issue as I think the distribution database is best kept on the publisher if you do not have a separate box. For your indexes, one final thing you can do is search all stored procedures for the columns that are in the indexes. Sure, they will be queried sometimes and not filtered on, but at least if you know which SPs contain column names it is a big help. Then you just have to worry about the ad hoc queries.Thanks,JaredTue, 15 Nov 2011 09:03:57 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]jared-709193 (11/15/2011)[/b][hr][quote][b]aurato (11/15/2011)[/b][hr][quote][b]jared-709193 (11/15/2011)[/b][hr]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[/quote]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."[/quote]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[/quote]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 [url=http://dba.stackexchange.com/questions/7906/determining-that-indexes-on-a-table-are-unused]http://dba.stackexchange.com/questions/7906/determining-that-indexes-on-a-table-are-unused[/url]Tue, 15 Nov 2011 08:57:43 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]aurato (11/15/2011)[/b][hr][quote][b]jared-709193 (11/15/2011)[/b][hr]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[/quote]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."[/quote]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.JaredTue, 15 Nov 2011 08:43:22 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]Ninja's_RGR'us (11/15/2011)[/b][hr]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.[/quote]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.Tue, 15 Nov 2011 08:42:44 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxI 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.Tue, 15 Nov 2011 08:40:01 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]aurato (11/15/2011)[/b][hr][quote][b]jared-709193 (11/15/2011)[/b][hr]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[/quote]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."[/quote]Just a PS. Insert can also suffer from LACK of indexes (check the exec plan for table scans for FK checks).Tue, 15 Nov 2011 08:37:52 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]Ninja's_RGR'us (11/15/2011)[/b][hr]I don't mean to be snippy and that's not my intention.I guess I'm too tired. Logging off.[/quote]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.Tue, 15 Nov 2011 08:35:36 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]jared-709193 (11/15/2011)[/b][hr]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[/quote]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."Tue, 15 Nov 2011 08:34:36 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxI don't mean to be snippy and that's not my intention.I guess I'm too tired. Logging off.Tue, 15 Nov 2011 08:33:04 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]jared-709193 (11/15/2011)[/b][hr]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. [/quote]That's exactly why I'm here. Because I wasn't sure and I was asking if this was enough.[quote]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[/quote]I sent an email to the VP of IT before I even made this post, explaining all of that.Tue, 15 Nov 2011 08:32:46 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]aurato (11/15/2011)[/b][hr][quote][b]jared-709193 (11/15/2011)[/b][hr]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[/quote]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.[/quote]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?JaredTue, 15 Nov 2011 08:32:14 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxTime to see for yourself what we see in your situation.Got 3 hours?[url]http://www.youtube.com/watch?v=Kcnj5bQghjY&feature=relmfu[/url][url]http://www.youtube.com/watch?v=XmE_Utp8CY0[/url][url]http://www.youtube.com/watch?v=RZ4YTg4S7HI&feature=relmfu[/url]Don't be fooled by the title. They are all equally important and useful.Tue, 15 Nov 2011 08:31:56 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]Ninja's_RGR'us (11/15/2011)[/b][hr][quote][b]aurato (11/15/2011)[/b][hr][quote][b]Ninja's_RGR'us (11/15/2011)[/b][hr]P.S. Check out those survey results...9 index is "nothing"[url]http://sqlskills.com/BLOGS/PAUL/post/Survey-nonclustered-index-counts-(code-to-run).aspx[/url][/quote]I still wouldn't want them there if they're not being used.[/quote]You've clearly made up your mind, even before coming here.So why are we still talking about this?[/quote]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.Tue, 15 Nov 2011 08:29:37 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]aurato (11/15/2011)[/b][hr][quote][b]jared-709193 (11/15/2011)[/b][hr][quote][b]aurato (11/15/2011)[/b][hr]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.[/quote]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[/quote]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.[/quote]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 :)JaredTue, 15 Nov 2011 08:29:11 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]jared-709193 (11/15/2011)[/b][hr]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[/quote]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.Tue, 15 Nov 2011 08:27:56 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]aurato (11/15/2011)[/b][hr][quote][b]Ninja's_RGR'us (11/15/2011)[/b][hr]P.S. Check out those survey results...9 index is "nothing"[url]http://sqlskills.com/BLOGS/PAUL/post/Survey-nonclustered-index-counts-(code-to-run).aspx[/url][/quote]I still wouldn't want them there if they're not being used.[/quote]You've clearly made up your mind, even before coming here.So why are we still talking about this?Tue, 15 Nov 2011 08:26:10 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]Ninja's_RGR'us (11/15/2011)[/b][hr]P.S. Check out those survey results...9 index is "nothing"[url]http://sqlskills.com/BLOGS/PAUL/post/Survey-nonclustered-index-counts-(code-to-run).aspx[/url][/quote]I still wouldn't want them there if they're not being used.Tue, 15 Nov 2011 08:23:29 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]jared-709193 (11/15/2011)[/b][hr][quote][b]aurato (11/15/2011)[/b][hr]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.[/quote]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[/quote]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.Tue, 15 Nov 2011 08:22:34 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxCan 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.JaredTue, 15 Nov 2011 08:20:27 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxP.S. Check out those survey results...9 index is "nothing"[url]http://sqlskills.com/BLOGS/PAUL/post/Survey-nonclustered-index-counts-(code-to-run).aspx[/url]Tue, 15 Nov 2011 08:11:21 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]aurato (11/15/2011)[/b][hr]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.[/quote]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. JaredTue, 15 Nov 2011 08:09:21 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxIf 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).Tue, 15 Nov 2011 08:08:58 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]Ninja's_RGR'us (11/15/2011)[/b][hr][quote][b]aurato (11/15/2011)[/b][hr]As for proving a benefit, there's 9 indexes on this table and if four never get used they're just dead weight objects.[/quote]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 themAre they unique keys? => can't dropWhat 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?[/quote]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.Tue, 15 Nov 2011 08:05:10 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]aurato (11/15/2011)[/b][hr]As for proving a benefit, there's 9 indexes on this table and if four never get used they're just dead weight objects.[/quote]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 themAre they unique keys? => can't dropWhat 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?Tue, 15 Nov 2011 08:01:15 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxAs for proving a benefit, there's 9 indexes on this table and if four never get used they're just dead weight objects.Tue, 15 Nov 2011 07:57:27 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxWell, 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.Tue, 15 Nov 2011 07:55:48 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspx[quote][b]aurato (11/15/2011)[/b][hr]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.[/quote]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.Tue, 15 Nov 2011 07:50:05 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxI also posted the wrong version of the query. Should be an s.* at the end of that SELECT list.Tue, 15 Nov 2011 07:42:55 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxI'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.Tue, 15 Nov 2011 07:42:06 GMTauratohttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxI'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.Tue, 15 Nov 2011 07:39:40 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1205983-391-1.aspxselect 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 sinner join sys.objects o on s.object_id = o.object_idinner join sys.indexes i on i.index_id = s.index_id and i.object_id = o.object_idinner join sys.dm_db_partition_stats p on i.index_id = p.index_id and o.object_id = p.object_idwhere o.name = 'TableName' --AND last_user_seek is null and last_user_scan is null and last_user_lookup is nullIf 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[url=http://dba.stackexchange.com/questions/7906/determining-that-indexes-on-a-table-are-unused]http://dba.stackexchange.com/questions/7906/determining-that-indexes-on-a-table-are-unused [/url]Tue, 15 Nov 2011 07:37:33 GMTaurato