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


Indexes, Indexes, Indexes


Indexes, Indexes, Indexes

Author
Message
peleg
peleg
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 537
Hi Ron, can you share with us the package?
how do you clean the diffrent statistics, so on next run time, you will get only the data from the previous time?
BW_Toro
BW_Toro
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 523
I think the data aggregation query included in the article can produce wildly skewed results because of the bug in the data collection query. I think it could lead one to conclude an index is used very lightly/rarely when it's used very heavily/frequently. I think it could lead one to conclude an index is used very heavily/frequently when it's used very lightly/rarely. Some folks have dismissed the issue and said the data collection query is acceptable. I have to disagree because the query can produce very misleading results.
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6677 Visits: 2399
This may be the link to the PerfStats scripts. This is from the MSDN blog:

http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx

I have not installed these scripts yet so I do not know if this is the same thing referenced in the article or not.
BW_Toro
BW_Toro
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 523
Where is the author? Where are the folks who defended the data collection query as harmless?

Steven993 said the query is fine. I think I have shown it's not fine.

Brian O'Leary pronounced the effect of the bug to be nothing worse than showing duplicate rows. I think I have shown the effect is much worse.

Where is the discussion? Can a few more people weigh in to confirm or refute (with some evidence) the problem? The article is rated 4+ stars, but I'm afraid the core query is fundamentally flawed.
MWise
MWise
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 1808
BW_Toro (7/8/2011)
Where is the author? Where are the folks who defended the data collection query as harmless?


I'm with you on this one. I think the article should be fixed or removed. Unless there is some processing that the author did not share in the article that fixes the issue, you will indeed get incorrect results from the query. I'd hate for someone to use the code to "tune" their systems. They could end up dropping or keeping the wrong indexes.

Can we please get an update from the author or editor here?!?!

Thanks,
Marcy



Steven993
Steven993
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 833
I was wrong.
Even if I think I never saw 2 tables with the same object_id within an instance, Object_id is unique only within a database according to MSDN.
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70229 Visits: 9671
Steven993 (7/8/2011)
I was wrong.
Even if I think I never saw 2 tables with the same object_id within an instance, Object_id is unique only within a database according to MSDN.



It was like that in 2000 and I don't see why it should have changed since.
Steven993
Steven993
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 833
Ninja's_RGR'us (7/8/2011)

It was like that in 2000 and I don't see why it should have changed since.

Did I said it changed ?
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70229 Visits: 9671
Steven993 (7/8/2011)
Ninja's_RGR'us (7/8/2011)

It was like that in 2000 and I don't see why it should have changed since.

Did I said it changed ?


I was merely confirming the information.
Brian O'Leary
Brian O'Leary
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 116
BW_Toro (7/8/2011)
Where is the author? Where are the folks who defended the data collection query as harmless?

Steven993 said the query is fine. I think I have shown it's not fine.

Brian O'Leary pronounced the effect of the bug to be nothing worse than showing duplicate rows. I think I have shown the effect is much worse.

Where is the discussion? Can a few more people weigh in to confirm or refute (with some evidence) the problem? The article is rated 4+ stars, but I'm afraid the core query is fundamentally flawed.



I highlighted in a previous post that the query is incorrect due to the [object_id] being unique only within a database, I am in no way "defending" anything, having said that I'm not trying to rip into anybody either.

I noticed the query that performs the aggregates is grouping on name, so unless there are multiple indexes with the same name AND the same object_id across multiple databases it's likely to give you accurate enough results. (Caveat @ BW_Toro :-)) This statement is indended only as an observation and not an endorsement!)

I personally would not use the data this produces to make any decisions.

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