October 18, 2007 at 4:24 pm
Comments posted to this topic are about the item 2005 unused indexes
Wilfred
The best things in life are the simple things
September 10, 2010 at 2:21 am
Hi Wilfred,
I was searching the net for articles/scripts on unused indexes and before I found yours, I came upon this website that gave a script on unused indexes. I modified it a bit to exclude PK's and my record count was 1222. Oh yes, I was naughty and did not administer my indexes as I should have but the problem I have here is I am not a DBA, I am a developer and there is no DBA here and I have been self training for close to 4 years now so unfortunately my developer work always took precedence to the DBA work and that's not quite so bad because I have put maintenance plans in place to do all sorts of things to protect and check the database. I also get some emails if anything goes wrong but I am worried now because the database has become bloated. Back to my query. I added theother query below and link to the website.
select object_schema_name(i.object_id) schemaname, object_name(i.object_id) tablename,
i.name indexname
from sys.indexes i
where objectproperty(i.object_id, 'IsUserTable') = 1 and isnull(i.name,'') <> '' and
not exists(select ne.index_id from sys.dm_db_index_usage_stats ne
where ne.object_id = i.object_id and ne.index_id = i.index_id and database_id = db_id()) and
left(i.name,2) <> 'PK'
ORDER BY schemaname, tablename, indexname
Now my problem is this: I ran your query and only got only 109 rows for my database. Wow!!! from 1222 to 109 is a great difference. Won't you please have a look and tell me what the other query do that yours don't.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
September 10, 2010 at 3:17 am
The answer was already given on the SQLTeam site by Saggi Neumann:
Since the DMV sys.dm_db_index_usage_stats also tracks changes in indexes (not only seeks and scans) in the user_updates column, it might list some indexes that aren't used for querying but are updated. Your query would just get you indexes on tables that are not touched
To summarize: the query with the large results reports indexes which are not used, because the related table was not used (so far). Bad assumption, especially if you run this script directly after a SLQ restart 🙂
My script (and the suggested modification from Saggi) reports indexes which are not optimal or poor used (hits compared to updates); the table itselves is being used, but the related indexes are not optimal.
Note: unless you have a very strict naming convention, I would not rely on the 'PK' exclusion of indexes. instead query the column [is_primary_key] on sys.indexes
Wilfred
The best things in life are the simple things
September 10, 2010 at 3:56 am
I was stupid and could have saved myself a lof of typing. Your query looks at the index usage and according to that decide what is unused. The other query looks at all the indexes and get those that has no stats on. Now, my question is: Is both queries good or if one is good which one is it. To everyone that reads this, please I am calling all boffins to please tell me.
Before you now fly off to answer this I first have some confessions to make because I have sinned. When I took responsibility of this database, now remember I am a developer and have never had the necessity to DBA a database, I had learn, and learn very fast. Someone told me about DTA (database tuning advisor) and I ran it and I applied all of it's recommendations. Now my database is bloated and I need to get rid of unused (not rarely used) indexes. I will come to the rarely used later. According to the query posted in my first comment I have 1222 unused indexes and if I am right according to Wilfred's query 109 rarely used indexes.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
September 10, 2010 at 4:20 am
You can only judge if an index is well-used if it has some statistics (about hits and updates). If an index doen't exists in the DM view, it doesn't say anything about it's design. It's just telling you the related table was not referenced in a query since the startup of SQL.
Try this (but not on a live environment!): restart sql and run the "large" unused index query, you'll see almost every index will be reported (because nothing happened on the database so far). If you run the query again after some time, the number of unused indexes will decrease. Does this tell you something about how usefull an index is? No.
To conclude: regardless which script you use, you'll have to compare the hits against writes to determine if an index is useful. Make sure every batch has run against the database in order to get a complete picture of your database activities.
Wilfred
The best things in life are the simple things
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply