I have a best practices question for you all that is based totally on experience.
I just got a new 2008 R2 SQL Server, running on 2008 R2 Windows Server. I have 4 quad zeon processesors (total of 16 logical processors) and only 8GB of memory in it. (I know.. I see the bottleneck too)
Now, it would not be uncommon for me to have about 50 users or so, logged into the ReportingServices (Same Machine) pulling reports. Plus some general data type functions.
What would be an appropriate amount of memory for this box.. As it stands, there is only about 512 MB of memory per logical processor, which means theres literally no room to do anything quickly after the windows server 2008r2 overhead.
Thanks for your thoughts.
asked Jun 21, 2011 at 11:15 AM in Default
The bulk of RAM is used to cache data from disk. Unless you are implementing some NUMA limitations the cache is available across the processors.
How large are your databases? What else is running on the system?
As a quick set of numbers I would go with 2.5-3 GB for the O/S, 4 GB for the main instance and 1 for the Reporting instance. Baseline from there and adjust as necessary.
answered Jun 21, 2011 at 11:26 AM
8gb might be a bit shy on memory, but the real question is, what kind of disks are you talking about. Yeah, get as much memory as you can because the more stuff you can put into cache and keep there the better, but your performance bottleneck will be, most of the time, I/O. I'd focus more of my efforts there than anywhere else.
answered Jun 21, 2011 at 12:13 PM
Grant Fritchey ♦♦
You can check PerfMon counter, SQL Server Memory Manager -> Target Server Mermory, This indicates the amount of Memory that sql server willing to consume.
Also the counter, Process -> Working Set, This tells you much memory is SQL Server consuming, and if this value exceeds MAX SERVER MEMORY then you need to add more ram for SQL Server.
answered Jun 21, 2011 at 08:42 PM
It is really hard to believe that someone would go through the expense of purchases 4 quad core processors and skimp on memory at 8 GB. I just ordered 64 GB for a server and it cost just over 2k. I would ask to meet the person who put together the spec on that server to meet me in the parking lot after work.
answered Jun 22, 2011 at 06:29 AM
For me, I'd go for 1-1.5GB for the OS if there are no applications running from the server (So setting the SQL Instance to use max 6.5GB) and get the server running. Download a trial version of SQL Monitor from RedGate (http://www.red-gate.com/products/dba/sql-monitor/) and use the 14 days to make sure you have it somewhere close. I'd possibly expect to have to reduce the amount of RAM SQL uses in order to allow more for SSRS if the box is hosting that too. Also get a copy of Confio Ignite Free edition to keep a track on all (up to 25) of your servers.
Note: dont install SQL Monitor or Ignite on the server in question, use a different box so you dont make any problems worse.
answered Jun 22, 2011 at 07:01 AM