Ok... first of all, you didn't say anywhere else that this was a "UAT" box. I thought it was a prod box because you were talking about licensing costs, etc. Generally speaking, Dev and Test boxes can use the Developer's Edition, which is free as of 2016 sp1 and, generally, they don't need nearly as much in the areas of CPU and RAM.
So, Step 1 is to not worry about making UAT boxes run as fast as production. There's actually an advantage in having Dev and UAT boxes being a bit lame compared to production... if you can make something run really well in Dev or UAT, then it will usually do at least as well in prod and frequently better. In other words, concentrate on fixing bad code.
To wit, my Dev boxes have only a 3rd of the CPUs (and MAXDOP has been limited in a similar fashion) and a 3rd of the RAM that my prod box has. The Dev box continues to run on spinning rust rather than SSDs.
The UAT box only has a 3rd of the CPU resources and only a 6th of the RAM.
And, yeah... I have more than 1 database one Dev and UAT that goes over the 1TB mark and several that approach it.
As for some magic method of determining how much RAM you should have, the answer is as with all else having to do with SQL Server... "It Depends". You can look for things like how much memory is being used at any time and still be wrong because of maintenance routines and large batch code, etc. You'll also need a whole lot less as you fix bad code. There's a whole lot of proof on the internet for that later point.
And I have to ask, why the hell do you have Developers running a bunch of ad hoc queries on the UAT box?
As for a "real life" example, you've already provided one but it'll work against you. You have some bad code running that requires 12.4 GB of mostly not used data (71 rows out of 449 thousand) in RAM. Compared to the 90GB of RAM you have, the query required more than 1/7th of the RAM because the whole heap had to live in RAM in order for it to be scanned. Adding more ram isn't going to fix such code. Unfortunately, that's not a good justification for adding more RAM to a UAT box. The only reason why you want a whole lot of memory in prod is so that when multiple copies of such code mistakenly make it to prod, you're not going to bring the server to its knees.
You can do the following Google search on this question...
... and then realize that it truly does "depend". It mostly depends on the workload and whether or not the box is a prod box or not.
To summarize, my suggestion of adding extra RAM was based on how much bad code I think you may have and it being a prod box. That suggestion also won't make the bad code that you posted run any faster. It'll just help keep the bad code from bringing a prod server to it's knees until you can fix the bad code. Having low amounts of RAM in Dev and UAT will help you find such bad code before it makes it to prod. 😉
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)