Indexes, Indexes, Indexes

  • 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?

  • 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.

  • 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.

  • 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.

  • 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

  • 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.

  • 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.

  • 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 ?

  • 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.

  • 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

  • @ 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.

  • 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)
    Intro to Tally Tables and Functions

  • 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.

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

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