June 14, 2012 at 5:34 am
Hi all,
I am responsible for migrating an in house .Net application which was developed with an Object Relational Model Data Access Layer from Windows 2003/SQL 2005 to Windows 2008/SQL 2008. For the most part the DAL will send thousands of single select statements at SQL in a single thread meanwhile the user waits for the screen to load.
The current server is about 4 years old but handles this type of load quite well. The problem is that while the new server has a better CPU with more RAM and faster disks the performance of our application is much slower. The new server just doesn't seem to be able to respond single select statement style traffic.
Before it gets mentioned - yes it is painfully obvious the application needs to be rewritten but this is cost prohibitive at this time. Our goal is just to move this app to new hardware and SQL 2008 AND run at least as good if not faster than previously.
Can anyone suggest OS tweaks, SQL configurations or even hardware that may improve performance?
Thanks in advance
Chris
June 14, 2012 at 6:04 am
Hi ,
You can possibly looks at areas in SQL database
1. If lot reads or updates based on SELECT CONDITION check if the indexe statistics are up to date, if not issue UPDATE STATISTICS on database.
2. Check if the index are not fragmented for indexes having greater than 10K data pages.
3. Is execution plan different than SQL 2005 used for qureies, if yes does step one or two resolve it ?
4. Are you running with compatbility level of older version that is SQL 2005 is 90 or using the new compatibily level in SQL2008 which is 100 , have you tested this ?
Cheer Satish 🙂
June 14, 2012 at 6:27 am
Validate the settings are basically the same between both servers. Enable the 'Optimize for Ad Hoc Workloads' option on your server. That frequently helps ORM apps. Possibly look at using Read Committed Snapshot to reduce the amount of locking on the server. That will lead to a larger tempdb, so be ready there. Make sure all your statistics are updated with full scan after the migration. They will be rebuilt automatically over time, but if you update them as part of the migration, you'll see better behavior out of the box.
That's all I can think of for quick hits. After that, it's a question of what is running slow and why. Monitoring and identifying the bottlenecks then working to resolve them. Standard stuff from that point.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2012 at 6:50 am
Thanks Satish,
1) Statistics were up to date, but I updated them again with FULLSCAN and there was no difference.
2) Indexes have less than 1% fragmentation so it's not that
3) The query plan is very simple - just a Clustered Index Seek
We are talking really basic queries like SELECT c1, c2, c3, c4, c5 FROM table WHERE id=1
4) I upped the compatibility level to 100 and that made no difference.
June 14, 2012 at 7:24 am
Hi Grant,
I have done a cursory check of the settings on both servers and nothing different stands out. I did run a test with 'Optimise for Ad Hoc Workloads', but this doesnt seem to have sped things up at all.
As far as looking at read committed snapshot, the testing I have been performing is on an idle server so there is definately no locking going on - if anything I think this would degrade performance rather than improve it for this particular application.
As mentioned previously, updating statistics did nothing.
Thanks for your suggestions though.
I am starting to think that the newer server is simply just slower at opening and closing connections when compared to SQL 2005? Is there any merit in this?
June 14, 2012 at 7:34 am
Not sure. It's not something I've seen elsewhere. There aren't any huge, general, performance improvements between 2005 & 2008, but there are no huge performance degradations that I've seen either. I have seen queries that performed worse between the two, but those were almost always edge case queries anyway. Most ORM queries that I've seen wouldn't qualify (not all, there are a few doozies there) since they're overly simplistic frequently, not overly complex.
This is a bit of a stumper. What about connection pooling? Is it disabled/enabled on either of the servers different from the other?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2012 at 7:46 am
I would suggest pickup of any simple query and follow the below step
SET STATISTICS IO ON
<YOUR QUERY>
SET STATISTICS IO OFF
just see the if u are getting physical reads if yes and the number is good then you system hardware is been overutiized coz of that more data is been fetched from the hard disk memory to the main memory causing a slow overall system.
Hope this helps
REgards
Asit
June 14, 2012 at 7:57 am
ASITKAUSHIK (6/14/2012)
I would suggest pickup of any simple query and follow the below stepSET STATISTICS IO ON
<YOUR QUERY>
SET STATISTICS IO OFF
just see the if u are getting physical reads if yes and the number is good then you system hardware is been overutiized coz of that more data is been fetched from the hard disk memory to the main memory causing a slow overall system.
Hope this helps
REgards
Asit
Or you can just query the sys.dm_exec_query_stats DMO to see aggregate reads. Not a perfect measure, but quicker than actually executing each individual query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2012 at 9:06 am
I have confirmed that the application is using connection pooling by tracing the logon audit so it doesnt seem to be that.
The database is quite small, only 8 Gb, and the new server has 24Gb ram. There were no physical reads in any of the queries I tested. I checked the cache hit ratio which is at 100%...
June 14, 2012 at 11:39 pm
Chris,
Can you confirm if any other application is running on this server , which may be taking the resources and making your sql server starving for resources.
Also if it possible to monitor the system for some time using sql profiler and see what are the queries which are hogging the resource. Running profiler would be expensive but would give us some data as to what is making the system slow.
Regards
Asit
June 15, 2012 at 1:59 am
Hi Asit,
The server I am testing is brand new with nothing else running on it. There are definately no other processes running on it as I am the only one with access.
To recap - the problem is that I am migrating from an old sql 2005 box to new hardware and a 2008R2 install. We havent migrated yet as the new server is performing slower than the old.
Thanks
Chris
June 15, 2012 at 4:21 am
Hi Chris,
Can you see any bottlenecks on the actual server its self using perfmon?
Are the CPU's in powersaving mode (need to check OS & Bios)?
Thanks
Dan
June 15, 2012 at 4:28 am
Could this be a network traffic issue? If the run the same query on the new and old server do they both return in roughly the same amount of time?
Is the new server on the same switch / sub net etc as the old one?
June 15, 2012 at 4:30 am
Other suggestions are to validate the memory configuration and the disk alignment. I've posted this to #sqlhelp on Twitter.
OK. Stepping back for a minute. What kind of monitoring do you have in place? Can you see where the slow down is occurring based on the metrics?
Another option, can you capture a trace on the old server and then play it back on the new server? How does that perform?
Finally, I ran into a number of application side issues, in and around connections, when moving from 2000 to 2005. Same to 2008 from 2000. Has anyone examined the code to see what connection settings it's using are within best practices for 2008. Also, have you updated the connection DLLs since you're on a new version of SQL Server? It's possible the problem isn't on the server at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2012 at 4:54 am
I would step back a bit and take more of a top down approach. What exactly is slower about the 2008 environment, I mean apart from the overall execution time of course 🙂 Let's get specific about where you are seeing the slowdown. Start by looking at the server Wait Types for the environment and then drill down from there.
As an aside, the suggestion to check the server is using the High Performance Power Plan is a very good one. I have seen this catch people out and restrict server performance on a number of occasions.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply