Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Performance tuning for ORM application for 2008R2 migration Expand / Collapse
Author
Message
Posted Thursday, June 14, 2012 5:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:07 AM
Points: 35, Visits: 322
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
Post #1315780
Posted Thursday, June 14, 2012 6:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:40 AM
Points: 166, Visits: 267
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
Post #1315792
Posted Thursday, June 14, 2012 6:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 15,662, Visits: 28,055
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1315804
Posted Thursday, June 14, 2012 6:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:07 AM
Points: 35, Visits: 322
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.
Post #1315830
Posted Thursday, June 14, 2012 7:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:07 AM
Points: 35, Visits: 322
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?
Post #1315868
Posted Thursday, June 14, 2012 7:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 15,662, Visits: 28,055
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1315876
Posted Thursday, June 14, 2012 7:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 5, 2013 3:58 AM
Points: 123, 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
Post #1315889
Posted Thursday, June 14, 2012 7:57 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 15,662, Visits: 28,055
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1315902
Posted Thursday, June 14, 2012 9:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:07 AM
Points: 35, Visits: 322
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%...

Post #1315974
Posted Thursday, June 14, 2012 11:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 5, 2013 3:58 AM
Points: 123, 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
Post #1316301
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse