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 Thursday, July 7, 2011 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 123, Visits: 521
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?
Post #1138069
Posted Thursday, July 7, 2011 8:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 26, 2012 7:57 AM
Points: 12, 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.
Post #1138202
Posted Thursday, July 7, 2011 1:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 3,360, Visits: 2,004
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.
Post #1138447
Posted Friday, July 8, 2011 7:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 26, 2012 7:57 AM
Points: 12, 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.

Post #1138893
Posted Friday, July 8, 2011 8:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 24, 2014 6:54 AM
Points: 394, Visits: 1,605
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



Post #1138930
Posted Friday, July 8, 2011 8:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:31 AM
Points: 30, Visits: 736
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.
Post #1138945
Posted Friday, July 8, 2011 9:51 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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.
Post #1139019
Posted Friday, July 8, 2011 11:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:31 AM
Points: 30, Visits: 736
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 ?
Post #1139110
Posted Friday, July 8, 2011 11:57 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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.
Post #1139141
Posted Friday, July 8, 2011 3:27 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
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
Post #1139260
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse