January 13, 2010 at 2:15 pm
I am upgrading a SQL 2000 server 2.33 ghz quad processor 4 gb ram running windows 2000 to SQL 2005 Windows 2003 same processors and ram. Both servers are run on VM on the same server and same set of disks. After the upgrade is done there is significantly more activity on the processor. With only 3 – 4 users hitting the server all 4 processors are pegged at 100% and the users are seeing degraded performance. The SQL 2000 box had no issue with processors pegging. When I upgraded I set the databases to 2005, updated the statistics and reindexed them. I am just looking for some help on what could be causing the system performance issues I am seeing with 2005. Let me know if there is additional information you need and Thanks in advance for any help you can provide.
January 13, 2010 at 2:22 pm
What services of SQL Server 2005 did you install on the server? Is it fully patched?
What version of Window Server 2003? Is it updated and patched?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 13, 2010 at 2:58 pm
All of the services were intitally installed with sql 2005 running service pack 3. Windows is 2003 Standard addition is fully patched as well. After the inital testing we turned off everything but the SQL Server Service but that made no difference.
January 13, 2010 at 4:13 pm
If you stop SQL Server Service does CPU drop below 100%?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 14, 2010 at 1:06 pm
When we turned off SQL Server Service the server did drop down to minimal CPU ussage(under 10% max).
January 14, 2010 at 1:23 pm
Jason Manthei-309857 (1/13/2010)
Both servers are run on VM on the same server and same set of disks. After the upgrade is done there is significantly more activity on the processor. With only 3 – 4 users hitting the server all 4 processors are pegged at 100% and the users are seeing degraded performance.
Can you post more specific information about your VM host and what amount of memory and number of processors you dedicated to the SQL 2005 VM itself?
The 4 processors that are pegged at 100%, is this with the VM host you are seeing this from or from within the VM itself?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 14, 2010 at 1:44 pm
There are 4 cpus and 4 gigs of ram assigned to the VM. The VM Host is a Quad Dual Core with 32 gig of ram. The 100% CPU ussage was seen within the VM on all 4 processors. The VM host was showing not showing any CPU's pegged. If that isn't what you are looking for let me know and I will try and get more info for you.
Thanks,
Jason
January 14, 2010 at 2:22 pm
Jason Manthei-309857 (1/14/2010)
The VM host was showing not showing any CPU's pegged.
It was or it was not?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 15, 2010 at 8:15 am
Did you UPDATE_STATS after the upgrade? This can make a big difference when moving from SQL 2000 to 2005
January 15, 2010 at 10:26 am
The VM server which hosts the SQL VM did not show any processors at 100% while the SQL VM was showing all 4 processors at 100%
I did update stats and reindex all the databases.
January 15, 2010 at 11:41 am
I would suggest starting up a trace file in Profiler and seeing if anything stands out to you, mainly look for excessive durations on stored procedures or T-SQL queries being run. Some of the links below reference doing the same thing:
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply