May 22, 2018 at 9:04 am
I am investigating a slowness issue with one of our database servers. The 3rd party application that created this database is pretty inefficient, so the size of the database is north of 1.5 TB. The server is allocated 120 GB of memory. Based on my findings in SQL Server performance metrics, the buffer pool is around 7.3 million pages (59 GB). The Page Life Expectancy is constantly under 300 seconds, which makes me thing that there is not enough memory on this server.
I have searched MSDN for anything that describes what Microsoft recommends for memory but it doesn't give any figures except to say to increase memory as database size increases. I found an unofficial SQL Server memory calculator which recommended increasing memory to 342 GB of ram. Can anyone make a recommendation on how much memory to allocate vs database size?
Thanks in advance
May 22, 2018 at 9:27 am
theit8514 - Tuesday, May 22, 2018 9:04 AMI am investigating a slowness issue with one of our database servers. The 3rd party application that created this database is pretty inefficient, so the size of the database is north of 1.5 TB. The server is allocated 120 GB of memory. Based on my findings in SQL Server performance metrics, the buffer pool is around 7.3 million pages (59 GB). The Page Life Expectancy is constantly under 300 seconds, which makes me thing that there is not enough memory on this server.I have searched MSDN for anything that describes what Microsoft recommends for memory but it doesn't give any figures except to say to increase memory as database size increases. I found an unofficial SQL Server memory calculator which recommended increasing memory to 342 GB of ram. Can anyone make a recommendation on how much memory to allocate vs database size?
Thanks in advance
My first recommendation would be to run Adam Machanic's sp_WhoIsActive and start looking for performance challenged code and poor use of indexes. Also, start looking for junk that constantly recompiles because of bad or no parameterization. You might also want to look into the sp_Blitz collection of tools on Brent Ozar's site. They can provide some incredible info as to what is wrong. Whatever you do, don't start making any changes until you understand whether or not there's actually any ROI on doing so.
For example, in a knee jerk reaction, one of the companies that I do work for had a serious blocking problem on their 256GB 32 core box. They decided to throw hardware at the problem and upped the server to 384 GB and added another 12 core (and, yes.... they forgot what that was going to also cause them in licensing the Enterprise Edition). It actually made the problem worse because it was caused by MARS enabled connections and it only increased the number of connections that were stuck in doing KILLED/ROLLBACKs. They changed the connection strings to forcibly disable MARS (rather than leave anything to chance) and all the problems with blocking suddenly went away.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2018 at 2:31 pm
theit8514 - Tuesday, May 22, 2018 9:04 AMI am investigating a slowness issue with one of our database servers. The 3rd party application that created this database is pretty inefficient, so the size of the database is north of 1.5 TB. The server is allocated 120 GB of memory. Based on my findings in SQL Server performance metrics, the buffer pool is around 7.3 million pages (59 GB). The Page Life Expectancy is constantly under 300 seconds, which makes me thing that there is not enough memory on this server.I have searched MSDN for anything that describes what Microsoft recommends for memory but it doesn't give any figures except to say to increase memory as database size increases. I found an unofficial SQL Server memory calculator which recommended increasing memory to 342 GB of ram. Can anyone make a recommendation on how much memory to allocate vs database size?
Thanks in advance
59GB for the buffer pool - with 120GB on the server seems very low. Can you confirm the settings for max memory? Is this a dedicated server - or are there other applications/instances? Are you running Integration Services on this system - or any other SQL services (SSRS, SSAS, etc...)?
Is this instance configured with a domain service account - or is it running under local system? If using a domain account - did you set the locked pages in memory policy?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy