October 16, 2013 at 5:14 am
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!
October 25, 2013 at 1:57 pm
How are you adjusting the memory the server has?
October 25, 2013 at 2:19 pm
Does this help?
October 28, 2013 at 3:35 am
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.
October 28, 2013 at 4:11 am
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;-)
October 28, 2013 at 8:15 am
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?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply