Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Recommended SQL Server 2008 Memory and Processor Settings

Posted on 2016-09-25
6
Medium Priority
?
56 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 83

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 83

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

721 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