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


2005 unused indexes


2005 unused indexes

Author
Message
Wilfred van Dijk
Wilfred van Dijk
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1636 Visits: 1363
Comments posted to this topic are about the item 2005 unused indexes

Wilfred
The best things in life are the simple things
Manie Verster
Manie Verster
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1821 Visits: 1022
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)
Wilfred van Dijk
Wilfred van Dijk
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1636 Visits: 1363
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 Smile
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
Manie Verster
Manie Verster
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1821 Visits: 1022
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)
Wilfred van Dijk
Wilfred van Dijk
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1636 Visits: 1363
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
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