February 29, 2012 at 5:27 am
Hi, hoping to get some advice on an 'issue' I'm currently facing with regard to memory usage.
Quick bit of background would probably help. My company has recently implemented a new 2-node SQL Server 2008 R2 cluster running 5 separate SQL Server clustered instances in a Windows 2008 R2 o/s. The people who commissioned the cluster and who hired a third-party to build it completely ingored the in-house DBA expertise until it came time to 'hand it over'. In other words dump it in the laps of myself and a colleague who knew nothing about the resource requirements of the databases and associated applications and are both 'common-or-garden' DBAs rather than experts. Sound familiar? Anyway, gripe over.
One instance in particular - Enterprise Vault in case you're interested - has eaten up most of the memory on the node on which it's currently running (48GB available) and is sitting at about 42GB. The other instances have pretty much eaten up all the memory on the other node (again 48GB available) . Both nodes are now at about 96% memory usage.
Prior to that active/active configuration all instances were running on a single node in active/passive mode. However that is no longer possible given the total memory requirements of all instances is currently 74GB, somethat more than the 48GB available on each node. In that mode performance went through the floor and 'naturally' the DBAs were given dog's abuse and 'asked' to fix it.
I've therefore had to take evasive action in balancing the load across both nodes. I've also gone through all the usual checks and looked at all the various metrics to tell me if there was something causing the memory requirements to be over and above the normal and natural one utilised by Buffer Cache and Procedure Cache for an instance whose databases total nearly 100GB in size.
All the ususal suspects are normal. Memory usage from sys.dm_os_sys_memory tells me the Cached Page Count is 2471715 and 1116275 for the biggest databases. (Is that high?) and The 'clean' page count for those databases, from sys.dm_os_process_memory, are 2471715 and 1107927 respectively. (Again is that high?)
Buffer Cache hit Ratio does seem suspiciously high though at 100%. Shouldn't it be 90%+? So too is Page Life Expectancy at 458307ms. I know it should ideally be over 300-400. Wasn't expecting it to be quite as big as that though. Is that normal, or rather not abnormal, for databases of this size
There are certainly some large objects in buffer cache and a number of SPs have high Total Logical Read counts but basically, as far as I can see, the simple fact of the matter is that Enterprise Vault has some big databases and they need a lot of memory. However I thought I'd get some advice anyway to see if there's anything obvious I've missed checking in a configuration that I've only just been introduced to.
Regards,
Gordon.
February 29, 2012 at 7:56 am
YaHozna (2/29/2012)
All the ususal suspects are normal. Memory usage from sys.dm_os_sys_memory tells me the Cached Page Count is 2471715 and 1116275 for the biggest databases. (Is that high?) and The 'clean' page count for those databases, from sys.dm_os_process_memory, are 2471715 and 1107927 respectively. (Again is that high?)
Buffer Cache hit Ratio does seem suspiciously high though at 100%. Shouldn't it be 90%+? So too is Page Life Expectancy at 458307ms. I know it should ideally be over 300-400. Wasn't expecting it to be quite as big as that though. Is that normal, or rather not abnormal, for databases of this size
There are certainly some large objects in buffer cache and a number of SPs have high Total Logical Read counts but basically, as far as I can see, the simple fact of the matter is that Enterprise Vault has some big databases and they need a lot of memory. However I thought I'd get some advice anyway to see if there's anything obvious I've missed checking in a configuration that I've only just been introduced to.
Regards,
Gordon.
Having 100% Buffer Cache Hit Ratio is not strange. Most of the time it remains around 99% if there is no memory pressure & it shifts rarely even in memory pressure times! However, PLE which is a good counter to look for, has good value here & this value is completely normal given you have 48 GB RAM on your server.
The general recommended threshold value of 300 seconds for PLE is old one & it was recommended for the servers having only 4GB memory.
Since you have 48GB memory & if you have specified 45GB as SQL Server Max Memory limit then your PLE should be ~ 45*75 = 3375
February 29, 2012 at 10:28 am
Except that there's an issue where PLE is reported as being the average across NUMA nodes and therefore isn't a great metric to work with any longer (see Paul Randal's blog post on this at http://www.sqlskills.com/BLOGS/PAUL/post/Page-Life-Expectancy-isnt-what-you-think.aspx)
Just out of curiosity, have you set max mem on all the SQL Instances?
February 29, 2012 at 12:59 pm
If I got handed that mess I'd allocate each instance 4G of RAM, and leaving 4G for the OS, that would allow the other node to handle it should there be a fail over.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 29, 2012 at 4:08 pm
YaHozna (2/29/2012)
Hi, hoping to get some advice on an 'issue' I'm currently facing with regard to memory usage.Quick bit of background would probably help. My company has recently implemented a new 2-node SQL Server 2008 R2 cluster running 5 separate SQL Server clustered instances in a Windows 2008 R2 o/s. The people who commissioned the cluster and who hired a third-party to build it completely ingored the in-house DBA expertise until it came time to 'hand it over'. In other words dump it in the laps of myself and a colleague who knew nothing about the resource requirements of the databases and associated applications and are both 'common-or-garden' DBAs rather than experts. Sound familiar? Anyway, gripe over.
One instance in particular - Enterprise Vault in case you're interested - has eaten up most of the memory on the node on which it's currently running (48GB available) and is sitting at about 42GB. The other instances have pretty much eaten up all the memory on the other node (again 48GB available) . Both nodes are now at about 96% memory usage.
Prior to that active/active configuration all instances were running on a single node in active/passive mode. However that is no longer possible given the total memory requirements of all instances is currently 74GB, somethat more than the 48GB available on each node. In that mode performance went through the floor and 'naturally' the DBAs were given dog's abuse and 'asked' to fix it.
I've therefore had to take evasive action in balancing the load across both nodes. I've also gone through all the usual checks and looked at all the various metrics to tell me if there was something causing the memory requirements to be over and above the normal and natural one utilised by Buffer Cache and Procedure Cache for an instance whose databases total nearly 100GB in size.
All the ususal suspects are normal. Memory usage from sys.dm_os_sys_memory tells me the Cached Page Count is 2471715 and 1116275 for the biggest databases. (Is that high?) and The 'clean' page count for those databases, from sys.dm_os_process_memory, are 2471715 and 1107927 respectively. (Again is that high?)
Buffer Cache hit Ratio does seem suspiciously high though at 100%. Shouldn't it be 90%+? So too is Page Life Expectancy at 458307ms. I know it should ideally be over 300-400. Wasn't expecting it to be quite as big as that though. Is that normal, or rather not abnormal, for databases of this size
There are certainly some large objects in buffer cache and a number of SPs have high Total Logical Read counts but basically, as far as I can see, the simple fact of the matter is that Enterprise Vault has some big databases and they need a lot of memory. However I thought I'd get some advice anyway to see if there's anything obvious I've missed checking in a configuration that I've only just been introduced to.
Regards,
Gordon.
what value do you have set for max memory for each instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2012 at 5:19 am
Many thanks indeed for the useful responses and links. And apologies for taking so long to reply. Put my back out within a day of posting so have been recuperating since then 🙁
Most of the responses mentioned Max Memory settings. I had forgotten to mention that I was about to set Max Memory ceilings on all the instances according to their current usage and have subsequently done so, although the 'rogue' Enterprise Vault instance continues to soak up additional memory slowly but surely. An upgrade from 48GB to 96GB is also imminent however I want to make sure that I don't end up having the same problem with all the additional memory being 'soaked up' as well, otherwise I will be having my coat handed to me 🙂
I had also forgotten to mention that all instances were left on their original installed RTM versions so an upgrade to SP3 is also imminent. Were there any memory-leak issues with RTM? I seem to recall a Linked Server problem and although these exist they are not currently used.
Regards,
Gordon.
March 5, 2012 at 7:40 am
You must set max memory otherwise SQL server will grab everything, or at least try to.
What values do you have set for the instances installed?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2012 at 10:10 am
YaHozna (3/5/2012)
I had also forgotten to mention that all instances were left on their original installed RTM versions so an upgrade to SP3 is also imminent. Were there any memory-leak issues with RTM? I seem to recall a Linked Server problem and although these exist they are not currently used.
SP3? There is no SP3 for SQL 2008R2.
Also, SQL will take all the memory that you allow it to take, so it you have 48GB in the system as data is moved into the cache it will remain until it needs to be flushed. More and more memory will be taken until it reaches the MAXMEM threshold. Be sure to set hard caps and then take a look at performance.
March 5, 2012 at 10:13 am
I've set different Max Memory values for the different instances according to what they are currently 'using'. This varies from less than 1GB up to 43GB for the most memory hungry instance (Enterprise Vault).
Gordon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply