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

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

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:46 AM
Points: 123, Visits: 513
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 07, 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 07, 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: Wednesday, April 16, 2014 9:46 AM
Points: 3,150, Visits: 1,900
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 08, 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 08, 2011 8:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:20 PM
Points: 388, Visits: 1,567
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 08, 2011 8:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 21, 2012 4:09 PM
Points: 30, Visits: 708
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 08, 2011 9:51 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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 08, 2011 11:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 21, 2012 4:09 PM
Points: 30, Visits: 708
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 08, 2011 11:57 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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 08, 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