Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Recommended SQL Server 2008 Memory and Processor Settings

Posted on 2016-09-25
6
Medium Priority
?
58 Views
Last Modified: 2016-10-03
I would like some assistance with recommended Memory and Processor settings for the attached screens.

The goal is to maximize the performance of SQL Server to give application users the fastest response to query and processes.

About the environment:
Windows 2008 Server Standard
MS SQL 2008 Server
on a VM dedicated to only running SQL Server


Running on a VM with the folliwing specifications:
All SSD VMWARE Data Store
VM Memory set at 32GB
VM Processor set at 4 processors x 6 Cores = 24 Total Processor Cores

Your input as to any changes to the settings in the two included screenshots would be greatly appreciated.
ScreenClip1.png
ScreenClip2.png
0
Comment
Question by:armgon
6 Comments
 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 500 total points
ID: 41815002
The more memory available the better. As a rule-of-thumb, I set the max SQL Server memory to total memory -2 GB on a server dedicated to SQL Server. One GB for the OS and one for other processes that run from time to time.

Of course, hardware settings are only part of SQL Server performance. There is often  much that can be done in the database to improve performance (I.e. indexes, keys, etc). Same goes for queries. Optimizing queries can yield great benefits.

Good luck!
0
 
LVL 84

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 500 total points
ID: 41815042
what I use in deployments is
System memory < 16 GB use 50% of memory
System Memory >= 16 GB use 70% of memory

put your .dbf/.log/temp directories on different volumes/LUN's or on a SSD to maximize IOPS
0
 
LVL 35

Accepted Solution

by:
David Todd earned 1000 total points
ID: 41816886
Hi,

What you're showing is the default memory and processor settings.

If the machine is dedicated to SQL and there are no other processes then that should be okay. SQL is fairly sensible about its use of memory. If you wnat to set it to something, try 28GB as a starting figure (28672) and you can adjust up or down from there. If there is available memory then increase it, if available memory is looking a little low decrease it.

If you run Brent Ozar's sp_Blitz it will complain that memory is too high. You could use that as a guide as to what max memory should be set to.

I'd leave the processor screen alone for now.

HTH
  David
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:armgon
ID: 41816896
Would these settings be safe for for a VM with 32GB allocated.

Set SQL Minimum Server Memory to: 16Gb
Set SQL Minimum Server Memory to: 28Gb
0
 
LVL 84

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 500 total points
ID: 41818919
set the MAXIMUM memory NOT the minimum.
0
 

Author Comment

by:armgon
ID: 41825737
Thank you everyone for your input
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question