SQL Server Performance

2000->2005 Performance Issue

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by eastcoastsurfer, Sep 26, 2006.

  1. eastcoastsurfer New Member

    We recently moved from 2000 to 2005 and everything is working great except for 1 stored procedure. This procedure involves a lot of dynamic sql, cursors, and temp tables. On 2000 it takes &lt; 20secs to run while on 2005 it always takes nearly a minute.<br /><br />The query is basically building a tree of data based on the parameters passed in by running about 800 inserts into a temp table. I would post the 500 line stored proc, but it wouldn't mean much w/o understanding the surrounding application. The basic idea of the query though is to first get the root record, then run an insert for the level 2 records using the root, then level 3 using level 2, repeat... All these inserts are dynamic sql since the tree is a pivot and can be built with any hierarchy of levels.<br /><br />If you're curious, I didn't write it, and am about to rewrite the query plus the process that uses the data to something a bit more efficient <img src='/community/emoticons/emotion-5.gif' alt=';)' /> I am wondering though why 2005 would be slower than 2000 even on better hardware? I already updated the indexes and the statistics on the tables it uses. Tempdb is a single database on its own drive.<br /><br />Thanks
  2. joechang New Member

    there is no assumption in going from 1 major version to the next that each code segment runs faster.
    usually there is much more code, this does not any given operation faster,
    but rather, there is usually new code to handle special operations which will run faster

    so it looks like this code got no benefit from the new features of 2005, and incurred a few of the negatives
  3. eastcoastsurfer New Member

    I would agree, that there should never be an assumption about a performance increase from one version to another. In fact, similar performance is all I might expect when going from one major version to the next.

    I am just trying to figure why in this case the query slowed down so I can understand and avoid future slow downs from the same issue(s).
  4. joechang New Member

    just one guess, look in the execution plan and see if there are any hash joins
  5. Adriaan New Member

    As part of the upgrade, did you also move from a single CPU server to a multi-CPU server? The unwanted parallellism bug is supposedly still unsolved in SQL 2005.
  6. eastcoastsurfer New Member

    We did go from a 2 CPU box to a 4 CPU box. I'll do some checking about this unwanted parallelism bug...

    The thing about the joins is that each query run is actually pretty simple. Think of a star type schema with 900ish rows in the fact table and at most a couple hundred in the largest dimension table. Each query just pulls a few rows from this table and inserts it into a temp table, building out a tree structure one level at a time.

    Thanks for the ideas so far though.
  7. Twan New Member

    Hi ya,

    when you say temp table do you mean #table temp table or @table table variable? SQL2005 may have changed the recompile levels for data changes in a #table? @table may give you some significant performance improvements...?

    Cheers
    Twan
  8. eastcoastsurfer New Member

    # temp tables were used b/c table vars can't be inserted to from an exec() statement.
  9. Adriaan New Member

    If it is the parallellism bug, then your problem would disappear if you added OPTION (MAXDOP 1) at the end of each (dynamic) query. Shouldn't be too hard to test.
  10. eastcoastsurfer New Member

    I Was just in the process of doing that <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Adding the OPTION (MAXDOP 1) to the end of each dynamic query produced no changes. I'm starting to look at each set of dynamic queries now to see if something particular about one of the dynamic queries is causing the slow down from 2000-&gt;2005.
  11. hrishi_des New Member

    Connect to SQL SERVER 2005 database using Query Analyzer of 2000 and execute stored procedure and compare readings.

  12. eastcoastsurfer New Member

    Tried that too with no help. We never figured out the problem and ended up implementing a caching mechanism to avoid having to run many of they dynamic queries. Having something slow down this much from a version upgrade is still a bit disconcerting though. Thanks for everyones suggestions.

Share This Page