Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ron Johnson » Indexes, Indexes, Indexes
Add to briefcase
43 posts, Page 1 of 5
12345»»»

Indexes, Indexes, Indexes Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
GreyBeard
Posted Monday, July 04, 2011 9:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 11, 2013 8:52 AM
Points: 65, Visits: 321
Comments posted to this topic are about the item Indexes, Indexes, Indexes
Post #1136196
henrik staun poulsen
Posted Monday, July 04, 2011 11:47 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 23, 2013 11:39 PM
Points: 1,133, Visits: 856
Hi Ron,

I have seen this situation too.
But I've also found that even if the USER_Scans column was zero, the SYSTEM_scans was not zero.

I'm not sure why the System_Scans column exists. BOL only says: "for operations caused by internally generated queries, such as scans for gathering statistics."
But I suspect that they could also list the situation where it was used for Uniqueness???

Best regards,
Henrik Staun Poulsen
www.stovi.com


Post #1136220
kll
Posted Tuesday, July 05, 2011 1:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 02, 2012 7:53 AM
Points: 10, Visits: 53
While an abundance of unused indices are certainly worth taking care of, ther is an equally important task, namely the missing indices. This topic is covered here (external link).

But thank you very much for the heads up for this important area, and also thanks for showing your setup. I just know that these usage statistics disappear when a server is reset, which might happen from time to time.

Best regards,
Keld Laursen
Knowledge Centre for Agriculture
Denmark
Post #1136243
peter-757102
Posted Tuesday, July 05, 2011 6:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:57 AM
Points: 288, Visits: 1,903
Maybe I am missing something or lack some releveant experience in this area, but i see the following code:

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
 INNER JOIN SYS.INDEXES AS I
 ON I.[OBJECT_ID] = S.[OBJECT_ID]
 AND I.INDEX_ID = S.INDEX_ID


To me, it seems like joining between these two tables is an error.
Where SYS.DM_DB_INDEX_USAGE_STATS works over all databases and has a database_id column, SYS.INDEXES does not.

Without explicit filtering on the database name, you match index IDs from other databases to the current database, and thus gather the wrong usage statistics. Anyway, please check to see if I am right!

PS.

I expected the following where clause to be present and the code executed for each database on a server:

where
  s.database_id = db_id()
Post #1136407
Steven993
Posted Tuesday, July 05, 2011 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 21, 2012 4:09 PM
Points: 30, Visits: 708
Actually object_id in both views sys.dm_db_index_usage_stats and sys.indexes refers to the index's table.
Check MSDN for column description of each views.
So it's fine.
Post #1136442
Ognjen Kovacevic
Posted Tuesday, July 05, 2011 7:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:09 AM
Points: 91, Visits: 493
I think it is better to put "s.database_id = db_id()" in join statement
... INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID and S.database_id = db_id()
Post #1136453
bdavey
Posted Tuesday, July 05, 2011 8:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 10:03 AM
Points: 44, Visits: 236
Isn't the term is Accidental DBA and not volunteer DBA?
Post #1136543
tom.groszko
Posted Tuesday, July 05, 2011 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:19 AM
Points: 9, Visits: 88
The presense of an index is not necessarly a problem even if the index is not used. In your article you failed to state what problem you were trying to solve. If index analaysis were as simple as looking at the numbers SSMS would have a button to delete them or perhaps just delete them on it's own.
Your have apparently not bothered to speak with the develpers to find out why those indexes are there and then taken the opportunity to educate so you won't find the same thing in the next project to hit production.
I suspect those who refused to imediately delete those indexes saved your job.
Post #1136636
SQLRNNR
Posted Tuesday, July 05, 2011 11:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 10:25 AM
Points: 18,754, Visits: 12,337
Nice article.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1136702
Brian O'Leary
Posted Tuesday, July 05, 2011 12:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 01, 2012 8:54 AM
Points: 31, Visits: 115
tom.groszko (7/5/2011)
The presense of an index is not necessarly a problem even if the index is not used. In your article you failed to state what problem you were trying to solve. If index analaysis were as simple as looking at the numbers SSMS would have a button to delete them or perhaps just delete them on it's own.
Your have apparently not bothered to speak with the develpers to find out why those indexes are there and then taken the opportunity to educate so you won't find the same thing in the next project to hit production.
I suspect those who refused to imediately delete those indexes saved your job.



I'm mostly with you on this, however the reply given to the poster "you cant delete them, they MIGHT be used in the future" coupled with the fact there are 1800 tables and only 392 indexes (unless of course 1408 tables are used for ETL or some such purpose.....although I can see some problems there as well) seem to suggest a lack of understanding towards indexing in general. Of course this doesnt mean they can simply be deleted, they might exist for year end reporting etc.

MCITP SQL Server 2005/2008 DBA/DBD
Post #1136745
« Prev Topic | Next Topic »

Add to briefcase
43 posts, Page 1 of 5
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.