On Apr 22, 11:15 am, DA Morgan <damor...@psoug.org> wrote:
> euan.gar...@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...@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...@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.