SQL Server Performance Forum – Threads Archive
Unused Index
In a evaluation product (don’t ask names, please) I captured using profiler the following query in order to know unused indexes. SELECT i.id,i.name,o.crdate FROM sysindexes i LEFT JOIN sysobjects o on i.id=o.id WHERE OBJECTPROPERTY(i.id,’IsMSShipped’)=0order by o.crdate Reading BOL, IsMsshipped is: An object created during installation of Microsoft® SQL Serverâ„¢ 2000.
1 = True
0 = False I can’t believe that simple way to drop unused indexes. What do you think? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
And how IsMsShipped relates to unused indexes?
I think that one way to find unused nonclustered indexes – indexes with the selectivity < 0.1. Optimizer will not use this indexes. WBR, Vlad A. Scherbinin
"And how IsMsShipped relates to unused indexes?" Well, new product (see first post) in market said that. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
Luis, I can’t see how this query will ever find out a unused index. It simply returns all indexes not created during installation. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
Agree with you Frank so, this product is no fair.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
I believe they are working out other way to get around the results. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hopefully… [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
I have a requirement to know unused indexes.Did any one of you get to know that?.
It would be great if any one of you help me out .
If you can read spanish: http://www.sql-server-performance.com/lm_indices_utilizados_inact.asp If not, follow the instructions and the code. Feel free to ask anything about article.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
.. and also you can use ITW (which is not clear way out) to findout unused indexes as per thishttp://www.sql-server-performance.com/sql_server_performance_audit7.asp link. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi guys, Sorry for bring up old thread but is there any easier and faster way to detect any unused index in our db? I’m currently planning to save some server disk space and dropping unused index is one of my priority. Appreciate any input. Thanks. Expect the Unexpected
Check: http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=27 Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
I believe this is one of Luis’s pet to find the solution [8D]. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks guys. Now I got the idea… Expect the Unexpected
]]>