Indexes, Indexes, Indexes

Question

Post reply

Indexes, Indexes, Indexes

Viewing 13 posts - 31 through 42 (of 42 total)

You must be logged in to reply to this topic. Login to reply

peleg SSCrazy Points: 2611 More actions · Answer 1

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 Old Hand Points: 340 More actions · Answer 2

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 SSCoach Points: 17191 More actions · Answer 3

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"> 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 Old Hand Points: 340 More actions · Answer 4

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 SSCarpal Tunnel Points: 4074 More actions · Answer 5

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 Mr or Mrs. 500 Points: 588 More actions · Answer 6

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 SSC Guru Points: 294069 More actions · Answer 7

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 Mr or Mrs. 500 Points: 588 More actions · Answer 8

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 SSC Guru Points: 294069 More actions · Answer 9

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 SSChasing Mays Points: 623 More actions · Answer 10

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

BW_Toro Old Hand Points: 340 More actions · Answer 11

@ Brian O'Leary

Please go back and read what I posted earlier in this thread and run the query I provided. I think I already showed where you may be mistaken.

The data collection query in the article always returns table names from the current database due to how the OBJECT_NAME function is used, and it always returns index names from the current database, but it can associate them with index usage stats for different indexes on different tables in different databases. The association (JOIN) is on object_id and index_id. If object_id and index_id match across databases (I have several cases of this occurring on a lightly loaded SQL Server instance) then the query returns very misleading rows. The names of the tables and indexes in the other databases are irrelevant because the query does not see them.

You have minimized (again) the inaccuracy of the data collection query in the article. Your "observations" are stated authoritatively, but I believe they are wrong.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 12

I know this is an old article but since it was recently republished I have to say that I hope and pray that folks read the discussion before they even think about using the code as is especially when it comes to the code that supposedly returns the correct name of the table and index. To be blunt, the code is incorrect for what it's advertised to do and you could end up deleting incorrect indexes if you use the code as is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scott D. Jacobson SSCertifiable Points: 6089 More actions · Answer 13

Naked Ape (7/5/2011)
Interesting reading. Was that a deliberate reference to Alices Restaurant, Massacre (twenty-eight color graphs with circles and arrows and a paragraph on the bottom of each one)?

Thank you Naked Ape. It took until the 2nd page of posts but I'm glad I wasn't the only one who picked up on the Arlo Guthrie reference.