SQL 2005 Optimizer not optimizing

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • John Rivers
    #1

    SQL 2005 Optimizer not optimizing

    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


    Tags: None
    • Roy Harvey (SQL Server MVP)
      #2
      Re: SQL 2005 Optimizer not optimizing

      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
      <first10@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
      >

        Comment

        • John Rivers
          #3
          Re: SQL 2005 Optimizer not optimizing

          Yes

            Comment

            • Roy Harvey (SQL Server MVP)
              #4
              Re: SQL 2005 Optimizer not optimizing

              On Mon, 13 Oct 2008 11:24:38 -0700 (PDT), John Rivers
              <first10@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

                Comment

                • Roy Harvey (SQL Server MVP)
                  #5
                  Re: SQL 2005 Optimizer not optimizing

                  On Mon, 13 Oct 2008 14:46:00 -0400, "Roy Harvey (SQL Server MVP)"
                  <roy_harvey@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

                    Comment

                    • Erland Sommarskog
                      #6
                      Re: SQL 2005 Optimizer not optimizing

                      John Rivers (first10@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, esquel@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

                        Comment

                        Previous template Next
                        Working...