Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

2005 unused indexes Expand / Collapse
Author
Message
Posted Thursday, October 18, 2007 4:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, December 6, 2014 5:36 AM
Points: 938, Visits: 1,159
Comments posted to this topic are about the item 2005 unused indexes

Wilfred
The best things in life are the simple things
Post #412465
Posted Friday, September 10, 2010 2:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:58 PM
Points: 1,207, Visits: 928
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.


Manie 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)
Post #983590
Posted Friday, September 10, 2010 3:17 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, December 6, 2014 5:36 AM
Points: 938, Visits: 1,159
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
Post #983615
Posted Friday, September 10, 2010 3:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:58 PM
Points: 1,207, Visits: 928
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.


Manie 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)
Post #983624
Posted Friday, September 10, 2010 4:20 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, December 6, 2014 5:36 AM
Points: 938, Visits: 1,159
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
Post #983628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse