December 12, 2017 at 3:58 am
Is there any recommended chart from where I can setup up the Server Memory for the size of database or total running databases?
I have one database of size 550+ GB with 10 GB dedicated to SQL Server, remaining 6 GB for application and OS. Total 16 on that VM.
We are facing performance issue gradually as the data is growing, it was already tuned a year ago for schedule maintenance etc but now with these all client is complaining for performance.
For 550+ GB DB size, I have recommended to provided 32 GB memory as we can't immediately move to application on another server. Incase of 32, I will be giving 28 GB to SQL and remaining to application and OS. Would this be OK? I need some sort of chart if available.
Shamshad Ali
December 12, 2017 at 4:22 am
I don't think that 32GB is going to get you out of trouble, not with 550+ GB database. Can you get anymore memory made available... about 224GB would be a good start!
You need to plan to move the application, or the database on to separate servers as soon as possible, as the application running locally is also likely to be hindering your performance.
In theory you want enough memory to store the whole database in memory (and then some!).
One thing you may also want to consider, if possible, is partitioning your data. I would very much doubt that all of your data is "current". Therefore, partition off your legacy/archive data, and keep current data in separate file(s). With an appropriate petitioning schema you could potentially improve performance. This may help you with the limited memory resource you have available.
December 12, 2017 at 5:43 am
There is no magic chart, it all comes down to profiling, base lining and tuning.
The 32GB your suggesting and leaving 4 GB to the OS and Application is only going to cripple your application even more. Doubling up to 32 giving 20 to SQL would be better, remember the more RAM you have in the box the more RAM needs to be free to allow Windows to manage itself effectively.
What edition of SQL are you on? Standard? Enterprise? If standard you have a hard memory limit of 128GB for the Buffer Cache, 32GB for In Memory Tables and 32GB for Column Store, once you hit those limits you cant tell SQL to use more. If your on Enterprise, then knock yourself out with as much RAM as you can afford. In this day and age RAM is cheap so get as much as you can to support what you need, but it all comes down to doing your base line and detecting fluctuations.
December 12, 2017 at 8:16 am
I'm just going to leave this here... https://www.brentozar.com/archive/2015/03/ten-ways-tell-sql-server-clown-car/
Seriously, though - that server needs more memory. Especially if you have non-SQL Serer processes running on it. Having said that, all the memory in the world will only help up to a point, and you need query optimisation to get the rest of the way. But first - MORE RAM!
Thomas Rushton
blog: https://thelonedba.wordpress.com
December 12, 2017 at 8:28 am
shamshad.ali - Tuesday, December 12, 2017 3:58 AMIs there any recommended chart from where I can setup up the Server Memory for the size of database or total running databases?
I have one database of size 550+ GB with 10 GB dedicated to SQL Server, remaining 6 GB for application and OS. Total 16 on that VM.We are facing performance issue gradually as the data is growing, it was already tuned a year ago for schedule maintenance etc but now with these all client is complaining for performance.
For 550+ GB DB size, I have recommended to provided 32 GB memory as we can't immediately move to application on another server. Incase of 32, I will be giving 28 GB to SQL and remaining to application and OS. Would this be OK? I need some sort of chart if available.
Shamshad Ali
16 GB is certainly not enough. 32 is probably not enough. That's making some assumptions, however. I'm guessing that there are more than a handful of users who are doing more than simple selects.
There are also a number of things that should be done to configure a virtual machine for SQL server.
1. How are the drives configured?
2. Are the memory and CPU hot add disabled?
3. Is the guest memory locked to prevent ballooning?
That's the basic list. Have you taken a look at the articles/white papers published by VMWare? David Klee?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply