Re: Differences between Oracle,DB2,SQL2005 in cost estimation

On Apr 22, 11:15 am, DA Morgan <damor..._at_psoug.org> wrote:
> euan.gar..._at_gmail.com wrote:
> >> I am not aware of person or persons that have done the hard work of
> >> Oracle experts such as Jonathan Lewis and Julian Dyke with either DB2
> >> orSQL Server.
>
> >> InSQL Server, likely, the tools to do so don't even exist in the
> >> product.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor..._at_x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org
>
> > Exist to do what? There is lots of info on the SQL Server QP,
> > especially in SQL 2005. I'd start with the Inside SQL Server T-SQL
> > Querying, Any docs on the dynamic management views, this blog:
> >http://blogs.msdn.com/craigfr/and there is a new book in the inside
> > SQL Server series coming on query optimisation?
>
> > -Euan
>
> Pick up a copy of Jonathan Lewis' Cost-Based Oracle Fundamentals.
>
> I don't believe the tools exist in the product to allow this type
> of research.
>
> Not to mention there is no equivalent to X$ anything, GV$ anything,
> ASH or AWR. The metrics are just are not there to analyze.
>
> Is there a SQL Server equivalent of a 10053 trace?
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

You know Danel if you ever want to have a meaningful conversation about this stuff then you need to stop thinking in Oracle technology terms and start thinking in customer solution terms, that way we can actually compare Oracle to SQL Server, DB2, MySQL etc.

Does SQL Server have a feature called AWR? No but if I try and abstract it out (feel free to correct me its been a while since I used 10g) AWR feels like an analytical/DW store that maintains history for a bunch of "operational" metrics about the database and the systems associated with it. That is then used as the source for some rules( is this where ADDM comes into play).

Assuming for now my interpretation is somewhat close then SQl Server does expose internal structures and state via Dynamic Management Views, some of those views actuqlly maintain history (although by no means would I call them a DW). You can see this by using the management reports in management studio as these are mostly based off DMVs.

There are several different samples that are shipped by MS and the community ( the latest of which is a performance dashboard shipped by the customer support team) that are much more analytical.

Now I have no doubt that your first response is going to be that these are not as complete as AWR, that may or may not be true, but again thats not the issue. SQL Server does not have as many switches and knobs as Oracle (and yes we can argue over this for ever but right now the 2 companies have different philosphies on this sort of stuff, lets agree they are different and that has implications in terms of how much info is needed in an operations analytics tool) and hence it may be ok that there is less info in the SQL Server solution.

>From what I can glean it looks like the a 10053 trace is a debug of
the optimiser, I presume used to debug cost based optimisation issues in terms of plan generation. Seems like a good tool to have, there are ways of debugging plans in SQL Server they may or may not be as good as the 10053 trace, without spending time debugging both on real systems I couldn't say.

Seems like ASH and x$ are related to diagnostics and history of wait events, as these are different in SQL Server and Oracle again I am not sure there needs to be a match. There was a lot of effort put into documenting the wait states in SQL Server 2005 and there have been plenty of articles written about them. I am pretty sure there are DMVs related to this but I don't remember them all off the top of my head.

However the point here is that I don't hear of a lot of people asking for ASH like functionality relating to wait states in SQL Server so perhaps they are less of an issue given the different architectures. Received on Sun May 27 2007 - 03:15:18 CDT