Solved

Recommended SQL Server 2008 Memory and Processor Settings

Posted on 2016-09-25
6
46 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

863 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

22 Experts available now in Live!

Get 1:1 Help Now