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


Indexes, Indexes, Indexes


Indexes, Indexes, Indexes

Author
Message
GreyBeard
GreyBeard
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 331
Comments posted to this topic are about the item Indexes, Indexes, Indexes
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2030 Visits: 1222
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



kll
kll
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
peter-757102
peter-757102
SSC Eights!
SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)

Group: General Forum Members
Points: 807 Visits: 2559
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()

Steven993
Steven993
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 833
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. ;-)
Ognjen Kovacevic
Ognjen Kovacevic
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 608
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()
bdavey
bdavey
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 295
Isn't the term is Accidental DBA and not volunteer DBA?
tom.groszko
tom.groszko
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 187
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.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32094 Visits: 18551
Nice article.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Brian O'Leary
Brian O'Leary
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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
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