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

Effect of server memory on use of CLR functions Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 5:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:12 AM
Points: 395, Visits: 703
We're trying to assess the effects of server memory on the performance of one of our databases. So we have run the following test on the same server; when it has 8GB and when it has 32GB:

1) Restore from the same backup.
2) Run the same set of read and read-write stored procedures, in a single connection.
3) See how long they take.

Agreed, it's not a great test, but we were astounded to find that they took:

~12 minutes with 8GB of memory.
~100 minutes with 32GB of memory.

As far as we can see, the data created in the database are the same in each case.

Obviously, we've repeated several times and got approximately the same timings.

Further investigations have suggested that the extra time (i.e. ~90 minutes) is entirely taken up by a single statement which is using a CLR function to populate a column of some table.

I understand that memory for CLR is managed outside of that allocated to SQL to some extent. Does anybody have any idea why limiting the amount of physical memory in the server might result in a massively reduced execution time? It seems so counterintuitive!
Post #1505142
Posted Friday, October 25, 2013 1:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 4:44 PM
Points: 256, Visits: 1,520
How are you adjusting the memory the server has?
Post #1508586
Posted Friday, October 25, 2013 2:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 292, Visits: 1,206
Does this help?
http://www.sqldba.co.nz/blog/?p=30
Post #1508591
Posted Monday, October 28, 2013 3:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:12 AM
Points: 395, Visits: 703
We're adjusting the server's memory by turning it off, adding or removing memory chips and turning it back on again. We're trying to make it as realistic a test as possible, so have chosen not to limit memory by some other means.
Post #1508783
Posted Monday, October 28, 2013 4:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
Does the testing script has similar exec plan in both the environment ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1508787
Posted Monday, October 28, 2013 8:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 4:44 PM
Points: 256, Visits: 1,520
Here are some of the things I'd look at.

What do your typical performance and counters look like?

What's the target server memory, and total server memory under the sql memory manager counter?

What's the page life expectancy look like when this process is running and not running? What's the pagefile usage look like?

Is there anything in the error logs? Do you have an exceptional amount of faults in the memory counters that may indicate a bad (or several) sticks of ram?

Have you benchmarked the ram, and veified they are the exact same model, speed, size and timings? Is it the correct ram for the motherboard?

What does the cpu look like when the process is happening? What's the cpu temp look like?

What are your wait types looking like?

Does the operation you're running have any type of query hints or plan guides that may be effecting it? What's the nature of the CLR? Are you sure the CLR doesn't have a memory leak?

Have you tested it on a completely separate machine?
Post #1508902
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse