Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51

    Unanswered: Query Performance Slow in 2000 - not 2005

    Hello,

    I have a query that is poorly designed, running in a legacy system that I am not impressed with. The query is generated by a reporting tool. Recently the performance of this report has been very slow in SQL 2000.

    I took a backup of the database and restored it onto my local 2005 instance to isolate it for further testing. When I ran the query it only took 3 minutes to complete, despite fragmented indexes and questionable design logic. On the 2000 system it doesn't complete in over an hour, and sometimes fails reporting a deadlock error.

    I have run several maintenence tasks on the 2000 instance to defragment the indexes, shrinking the files, defragmenting the system drive, and making sure overall system health was ok.

    What am I missing here? I've tried looking into profiler and cannot find a definitive problem.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Have you run update statistics?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51
    i did, but ill try it again

    i guess the clue could be, what is the difference between restoring 2000 to 2005 that would cause the performance to change. Are statistics automatically updated, files defragged, etc...

    i was thinking about taking a 2005 backup and putting it back on 2000

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by xarfox
    i was thinking about taking a 2005 backup and putting it back on 2000
    Fraid you can't do that. SQL 2005 has different requirements for file header information, and SQL 2000 won't be able to understand those.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Both Optimizer and Query Processor have been re-written in 2K5, so I would see how you're getting different performance on the 2K and 2K5. In a sense you're comparing apples to oranges here.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by xarfox
    Hello,

    I have a query that is poorly designed, running in a legacy system that I am not impressed with. The query is generated by a reporting tool. Recently the performance of this report has been very slow in SQL 2000.

    I took a backup of the database and restored it onto my local 2005 instance to isolate it for further testing. When I ran the query it only took 3 minutes to complete, despite fragmented indexes and questionable design logic. On the 2000 system it doesn't complete in over an hour, and sometimes fails reporting a deadlock error.

    I have run several maintenence tasks on the 2000 instance to defragment the indexes, shrinking the files, defragmenting the system drive, and making sure overall system health was ok.

    What am I missing here? I've tried looking into profiler and cannot find a definitive problem.
    I've had the opposite problem, and stale stats and/or parameter sniffing were the causes.

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by PMASchmed
    I've had the opposite problem, and stale stats and/or parameter sniffing were the causes.

    I've also seen a huge slowdown with 2000 SP4 (I/O waits), appears as self blocking in sp_who2.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •