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 12345»»»

Indexes, Indexes, Indexes Expand / Collapse
Author
Message
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: Tuesday, February 11, 2014 3:07 PM
Points: 65, Visits: 331
Comments posted to this topic are about the item Indexes, Indexes, Indexes
Post #1136196
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: Saturday, April 12, 2014 10:26 AM
Points: 1,310, Visits: 958
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
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
Posted Tuesday, July 05, 2011 6:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:35 AM
Points: 319, Visits: 2,151
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
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
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: Thursday, April 10, 2014 5:14 AM
Points: 91, Visits: 521
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
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: Tuesday, April 08, 2014 12:35 PM
Points: 48, Visits: 268
Isn't the term is Accidental DBA and not volunteer DBA?
Post #1136543
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: Friday, April 04, 2014 8:06 AM
Points: 9, Visits: 135
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
Posted Tuesday, July 05, 2011 11:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 20,455, Visits: 14,075
Nice article.



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


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
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: Monday, May 27, 2013 3:39 PM
Points: 31, Visits: 116
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 12345»»»

Permissions Expand / Collapse