SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Effect of server memory on use of CLR functions


Effect of server memory on use of CLR functions

Author
Message
Julian Fletcher
Julian Fletcher
SSC Eights!
SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)

Group: General Forum Members
Points: 942 Visits: 1092
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!
benjamin.reyes
benjamin.reyes
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1261 Visits: 2286
How are you adjusting the memory the server has?
Sreekanth B
Sreekanth B
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1168 Visits: 2155
Does this help?
http://www.sqldba.co.nz/blog/?p=30
Julian Fletcher
Julian Fletcher
SSC Eights!
SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)

Group: General Forum Members
Points: 942 Visits: 1092
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.
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13908 Visits: 4077
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;-)
benjamin.reyes
benjamin.reyes
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1261 Visits: 2286
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?
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