August 2, 2011 at 12:56 pm
We are experiencing high and frequent memory paging on our SQL Server 2005 server. Our environment...
Windows Server 2003 Enterprise Edition SP2
2 - 2.33 Ghz quad core processors
32 GB of RAM
SQL 2005 Enterprise Edition SP4
The server is in a Microsoft Cluster and connected to an EMC CX4-120 SAN using Fiber Channel at 4 GBS
The server has 5 sql server instances and the instances are configured as follows in memory...
Instance 1: AWE-Yes, Min Server Mem - 0, Max server mem - 8192, Index creation mem - 0 and min mem per query - 1024
Instance 2: AWE-Yes, Min Server Mem - 0, Max server mem - 6144, Index creation mem - 0 and min mem per query - 1024
Instance 3: AWE-Yes, Min Server Mem - 0, Max server mem - 3072, Index creation mem - 0 and min mem per query - 1024
Instance 4: AWE-Yes, Min Server Mem - 0, Max server mem - 3072, Index creation mem - 0 and min mem per query - 1024
Instance 5: AWE-Yes, Min Server Mem - 0, Max server mem - 8192, Index creation mem - 0 and min mem per query - 1024
This leaves 4 GB for the Operating System
We've been using the Idera SQL Diagnostics tool and the memory resource is showing that Memory Paging is critical. In looking in Windows Task Manager, the processes that are causing the page faults are primarily svchost.exe, Rtvscan.exe and wmiprvse.exe. The sqlservr.exe process is paging but at much lower levels than the other processes mentioned.
Examples:
svchost.exe: Page Faults: 119,000,000 and PF Delta 300 to over 1,000
Rtvscane.exe: Page Faults: 78,000 and PF Delta 0
wmiprvse.exe: Page Faults: 38,000 and PF Delta - 1 to over 1,000
sqlservr.exe: Page Faults: 9,000 and 1 to over 100
We do have symantec installed but it's not scanning NDF, LDF or MDF files.
It's important to note, I think, that the performance issues being reported to us by our user base is intermittent so we are troubleshooting various tasks and processes that are being run to further drill down into the time and duration when we're experiencing performing issues. Having said that the purpose of this post is to make sure we're optimally configured overall and then we can rule out general performance issues.
Thanks
August 3, 2011 at 4:08 pm
I believe I found the culprit. We have a robocopy job that runs on the hour that copies the backup jobs from sql to a disk based backup system. Each time it ran, because of the size of the files - multiple GB's - the paging would go up correspondingly with the duration of the job.
August 4, 2011 at 6:21 am
You may need to change your file cache settings. We've had similiar problems with large file copies causing paging.
check these two registry keys:
HKLM\System\CurrentControlSet\Services\LanmanServer\Parameters\Size
HKLM\System\CurrentControlSet\Control\Session Manager\Memory Management\LargeSystemCache
They should be equal to 3 and 0 respectively.
See http://www.redbooks.ibm.com/redpapers/pdfs/redp3943.pdf starting on page 11 for a good explanation of what they do.
August 4, 2011 at 7:38 am
I will add that you do NOT have 4GB left over for the OS. Those memory settings for each instance are only to limit the BUFFER POOL. SQL Server has many other buckets of memory that are NOT limited by that number. You are over-committing memory for the server - which is a common issue when you have many instances on the same hardware. You wind up 'wasting' LOTS of memory on 'overhead' stuff that is not productive.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 4, 2011 at 4:03 pm
Thanks for the information SpringTownDBA. It was interesting to note, as I was following the documentation you provided, and then additional documentation, that the setting for LargeSystemCache is supposed to be set to 0 Automatically when SQL 2005 is installed - according to the Microsoft documentation I was reading. This was not the case - it was set to 1.
This setting corresponds to "Maximize data throughout for file sharing setting" - 1 and
"Maximize data throughput for network applications" - 0
I changed from file sharing to network applications and it changed the LargeSystemCache setting from 1 to 0.
Thanks again.
August 4, 2011 at 4:16 pm
Thanks for the information TheSQLGuru. Any suggestions/suggested reading on tuning my instances? For example, are there perfmon counters I can look at to see how much BufferPool memory I should reserve for the instance and then set the memory accordingly?
One other thing I was wondering about is if I should set something for minimal memory so as to control how much memory is dynamically moving in and out for the instances. My thinking might not be correct here - but the idea I had here was to find a balance in how much memory is reserved for each instance.
Thanks again.
August 5, 2011 at 1:22 am
micbra (8/4/2011)
Thanks for the information TheSQLGuru. Any suggestions/suggested reading on tuning my instances? For example, are there perfmon counters I can look at to see how much BufferPool memory I should reserve for the instance and then set the memory accordingly?
Check out the MSSQL$<Instance>:BufferManager counters in perfmon, this will give an indication as to whats using the memory. You'll have to monitor/trend the memory usage to get an accurate picture, especially in terms of Page Life Expectancy as that can fluctuate throughout the day.
One other thing I was wondering about is if I should set something for minimal memory so as to control how much memory is dynamically moving in and out for the instances. My thinking might not be correct here - but the idea I had here was to find a balance in how much memory is reserved for each instance.
Thanks again.
As for setting the min memory, be careful with that with many instances and the OS having a low amount to start with, if the OS becomes starved of memory and you've set the min too high then it can't claim back any RAM from SQL if its becoming critical. Some people insist on setting the min to the same as the max, others set it to 50% of the max and others like to leave it as 0 - its dependant on the system setup/usage/requirements and in some cases personal preference.
From my experience setting the min equal to the max has caused issues if the max is set too high and there are other things potentially running on the server during the day eg. Robocopy, AV etc. This has caused OS starvation which is never a good thing, if the OS ain't running then SQL will struggle 😉
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 5, 2011 at 9:21 am
micbra (8/4/2011)
Thanks for the information TheSQLGuru. Any suggestions/suggested reading on tuning my instances? For example, are there perfmon counters I can look at to see how much BufferPool memory I should reserve for the instance and then set the memory accordingly?One other thing I was wondering about is if I should set something for minimal memory so as to control how much memory is dynamically moving in and out for the instances. My thinking might not be correct here - but the idea I had here was to find a balance in how much memory is reserved for each instance.
Thanks again.
Chris Taylor covered lots of good stuff in his last post.
I don't have recommended reading on this stuff, sorry. I just have 15+ years of experience making the SQL Server relational engine spin like a top. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 6, 2011 at 7:31 am
One quick note- Real-time antivirus tends to not make SQL performance real happy. But if you absolutely gotta have it on, at least make sure you also exclude the BAK and TRN file extensions. That could be a significant part of perf hit during both backups and Robocopy.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply