Re: Poor performance after upgrading to sql server 2005

The optimizer has changed between 2000 and 2005 so there will certainly be
some types of queries that are better optimized and some that are not. If
either of you can provide the exact code and the execution plans for both
(preferably xml for 2005) I can give them to the dev team to look at. If
there is a problem with how the optimizer is dealing with these it they will
want to know about it and there will be a better chance of fixing it. That
is if it is broke to begin with. It is quite possible a matter of
implementation.

--
Andrew J. Kelly SQL MVP


"Damian" <damiank@xxxxxxxxxxxxxxx> wrote in message
news:1141354358.202211.278990@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
We have just upgraded our development server to sql sever 2005. Dual
processor, 4GB RAM, 64 bit etc.

We also noticed an incredible difference in the execution of stored
procedures that are using correlated subqueries. An interesing note is
that while rewriting one of these stored procedures I created an outer
join to a derived table (the replacement) and while verifying the
results, my performance was back, and the data was correct in both
instances. Comment out the derived table, performance deserts us.

Another interesting thing was pointed out to me today by a co-developer
(as I was going through and re-writing a stack of these) is that an ad
hoc query built and then executed with the EXEC command did not suffer
from the same performance problem.

Do these differences have to do with the statement level compilation
introduced in 2005?

Any thoughts would be appreciated.



.

Relevant Pages

  • Re: index bloat?
    ... execution plan is, how can I get the query optimizer to generate it - *without the hints*? ... Because some users of the DB are using report designing software that will just generate the join query with no hints, and I don't want them sitting there for hours when they don't have to. ... Does the optimizer not choose the merge join because it requires a bookmark lookup? ...
    (comp.databases.ms-sqlserver)
  • Re: A new proof of the superiority of set oriented approaches: numerical/time serie linear interpola
    ... while Brian merely states his claim. ... cursors are the way to go. ... I think you are greatly underestimating what a good optimizer can do. ... the execution time dropped to just over an hour. ...
    (comp.databases.theory)
  • Re: tuning stored procedure, variables and different optimisers
    ... available to the optimizer at compile time ... declare @var int ... execution time (since the whole batch is compiled at once, ...
    (comp.databases.ms-sqlserver)
  • Re: Need Help, fast
    ... For a parameter to a stored procedure, the optimizer picks up the value for when the execution plan ... The first> query gets the value of the int based on a date, @minsess. ...
    (microsoft.public.sqlserver.programming)
  • Re: Poor performance after upgrading to sql server 2005
    ... processor, 4GB RAM, 64 bit etc. ... We also noticed an incredible difference in the execution of stored ... that while rewriting one of these stored procedures I created an outer ...
    (microsoft.public.sqlserver.setup)