• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

Recommended SQL Server 2008 Memory and Processor Settings

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
armgon
Asked:
armgon
4 Solutions
 
funwithdotnetCommented:
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
 
David Johnson, CD, MVPOwnerCommented:
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
 
David ToddSenior DBACommented:
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
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
armgonAuthor Commented:
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
 
David Johnson, CD, MVPOwnerCommented:
set the MAXIMUM memory NOT the minimum.
0
 
armgonAuthor Commented:
Thank you everyone for your input
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now