SQL Server Performance Forum – Threads Archive
2000->2005 Performance Issue
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 < 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
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
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).
just one guess, look in the execution plan and see if there are any hash joins
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.
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.
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
# temp tables were used b/c table vars can’t be inserted to from an exec() statement.
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.
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->2005.
Connect to SQL SERVER 2005 database using Query Analyzer of 2000 and execute stored procedure and compare readings.
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.
]]>