Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance tuning for ORM application for 2008R2 migration


Performance tuning for ORM application for 2008R2 migration

Author
Message
chris-320654
chris-320654
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 364
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
Satish Nagaraja
Satish Nagaraja
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 308
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 :-)
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17553 Visits: 32253
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
chris-320654
chris-320654
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 364
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.
chris-320654
chris-320654
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 364
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?
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17553 Visits: 32253
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ASITKAUSHIK
ASITKAUSHIK
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 88
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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17553 Visits: 32253
ASITKAUSHIK (6/14/2012)
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


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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
chris-320654
chris-320654
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 364
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%...
ASITKAUSHIK
ASITKAUSHIK
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 88
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search