SQL 2005 Optimizer not optimizing

[John Rivers]

Hello

has anybody else noticed

I have queries that SQL 2000 optimizes correctly and they run very
fast

Yet SQL 2005 keeps using a dumb query plan and queries run very slow

The problem seems to stem from the assumption that data in a derived
table / subquery will not stay the same for different output rows

So the query is pointlessly re-evaluating the derived table again and
again
The same problem is also affecting subqueries

(This is how msaccess works - but that is not a real database server)

I have experimented with TABLOCKX but the optimizer still seems to
think data can change between rows

Am I missing something?

I am manually optimizing badly affected queries by rewriting them as
stored procedures with temporary tables
but that is like going back in time 20 years

The whole point in the optimizer is that it should find the most
intelligent way to return the results

John

[Roy Harvey (SQL Server MVP)]

Covering the most basic issue first, have you updated statistics since
the conversion?

Roy Harvey
Beacon Falls, CT

On Mon, 13 Oct 2008 08:43:59 -0700 (PDT), John Rivers
<fi*****@btinte rnet.comwrote:

>Hello

has anybody else noticed

I have queries that SQL 2000 optimizes correctly and they run very
fast

Yet SQL 2005 keeps using a dumb query plan and queries run very slow

The problem seems to stem from the assumption that data in a derived
table / subquery will not stay the same for different output rows

So the query is pointlessly re-evaluating the derived table again and
again
The same problem is also affecting subqueries

(This is how msaccess works - but that is not a real database server)

I have experimented with TABLOCKX but the optimizer still seems to
think data can change between rows

Am I missing something?

I am manually optimizing badly affected queries by rewriting them as
stored procedures with temporary tables
but that is like going back in time 20 years

The whole point in the optimizer is that it should find the most
intelligent way to return the results

John

[John Rivers]

Yes

[Roy Harvey (SQL Server MVP)]

On Mon, 13 Oct 2008 11:24:38 -0700 (PDT), John Rivers
<fi*****@btinte rnet.comwrote:

>Yes

Every time there are major upgrades to the optimizer, there are at
least a few regressions where the execution plans are worse than
before. So far that has been the case with every upgrade, and it
doesn't seem likely to change.

I have found that the only recourse is to look at the queries with a
fresh eye and rewrite them, as you are already doing. From the sort
of problem you are reporting I suggest taking one of your problem
queries and posting it, with DDL so we know what the tables look like,
and explaining the performance problem. There are some sharp people
here and one might have an idea that is less work that the stored
procedures you are changing to now. You probably want to start a new
thread for that.

Roy Harvey
Beacon Falls, CT

[Roy Harvey (SQL Server MVP)]

On Mon, 13 Oct 2008 14:46:00 -0400, "Roy Harvey (SQL Server MVP)"
<ro********@sne t.netwrote:

>From the sort
of problem you are reporting I suggest taking one of your problem
queries and posting it, with DDL so we know what the tables look like,
and explaining the performance problem. There are some sharp people
here and one might have an idea that is less work that the stored
procedures you are changing to now. You probably want to start a new
thread for that.

Also, I think the best group is microsoft.publi c.sqlserver.pro gramming
for this sort of question.

Roy Harvey
Beacon Falls, CT

[Erland Sommarskog]

John Rivers (fi*****@btinte rnet.com) writes:

Yes

And you ran UPDATE STATISTICS WITH FULLSCAN?

Else I agree with Roy. With a new versions of SQL Server, there is
always some query that suddenly does not perform as well as it used to
do. There is never any magic pill to regain the performance but only
hard work.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx