Solved

Recommended SQL Server 2008 Memory and Processor Settings

Posted on 2016-09-25
6
47 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 125 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 79

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 125 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 250 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 79

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 125 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now