October 27, 2009 at 3:42 pm
Hi all,
I have some developers who are trying to move to SQL 2005 from SQL 2000. They are finding that a normal job they run daily is taking a few minutes longer than normal on SQL 2005 and they are getting more CPU usage near 100% during the job than they used to.
Could this be something in regards to SQL 2005? Is there a hotfix we can try? etc?
Thanks in advance!
October 28, 2009 at 3:45 am
Can you give more details about the "job"?
October 28, 2009 at 6:51 am
I'm trying...unfortunately i'm just the administrator and the DBA set the job up so let me see what I can get from them
October 28, 2009 at 8:33 am
We had performance issues after upgrading to 2005. Some of them were resolved after closely looking at the joins and finding we needed to modify or add the CONVERT functions between dissimilar data types. Not sure why, but it seemed as though 2000 was more forgiving of data type differences in joins. Look at some executions plans for areas to tune.
October 28, 2009 at 9:46 am
The other thing you might want to look at is usage of tempdb. SQL 2005 placed a significantly increased burden on tempdb and if the job is using a lot of temp tables, table variables etc..., they might be competing for tempdb resources along with whatever else is running on the server. If you're seeing a lot of waittypes where the database id of the resource is 2:, you could be experiencing I/O contention in tempdb. If that's the case, you might want to allocate additional data files, typically one per CPU and make sure they are all the same size. That allows SQL Server to "round robin" the disk I/O resources and reduce contention in tempdb.
October 28, 2009 at 2:59 pm
I've just ran into http://sqlinthewild.co.za/index.php/2007/10/21/memory-and-sql-2005-sp2/ and http://support.microsoft.com/default.aspx/kb/927396 - maybe this will help you...
Which is the last SP you applied?
October 29, 2009 at 5:01 am
The post mentioning data type differences is right. This is a documented feature of SQL 2005 and above.
The best way to find if this affects you is to look at your execution plans on SQL 2000 and SQL 2005 for the queries that are running slower. Anything that goes from a seek in 2000 to a scan in 2005 needs to be looked at further.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply