I'm looking for assistance with SQL server 2012 memory settings.

Good morning,

I have a SQL server in our environment that might possibly be contributing to poor performance from our ERP system (we're troubleshooting the performance issues from multiple directions).  The server is a Dell PE 720, running Server 2008 R2 and SQL 2012, with a single Xeon E5-2609 @ 2.4 GHz and 16 GB of RAM.

I was seeing consistent high cpu and memory utilization on the server.  I understand that this can be caused by poorly written queries, but that will require a significant consultation expense (we don't have the requisite SQL expertise in house), so we elected to upgrade hardware first to see if we can alleviate the issue.  Over the weekend, I added a second E5-2609 processor and an additional 32 GB of RAM.

I understand that there are memory configurations within SQL that will need to be tweaked to take advantage of the additional resources; I'm just not familiar enough with SQL to be confident making changes to them.  Can anyone offer assistance with what the best practices settings would be?

As I look at the SQL server properties, I see the following settings (still set as they were with the single proc and 16 GB of RAM):

SQL Version:  11.0.2218.0

MEMORY page

Server Memory Options
Minimum Server Memory (MB)    0
Maximum Server Memory  (MB)   6000

Other Memory Options
Index creation memory (in KB, 0 = dynamic)     0
Minimum memory per query (in KB)     1024

Configured values checked, not running values


Processors Page

Automatically set processor affinity mask for all processors     CHECKED
Automatically set I/O affinity mask for all processors     CHECKED

Maximum worker threads:     0

Boost SQL Server priority     UNCHECKED
Use Windows fibers (lightweight pooling)     UNCHECKED

Configured values checked, not running values


Any assistance you might be able to offer would be appreciated!

Scott
Scott MilnerApplication AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Your SQL Server has only 6GB set for MAX memory.: "Maximum Server Memory  (MB)   6000"
This means that whatever RAM you add it won't use it since is limited to 6GB.

Use the following script to increase the maximum memory to 12GB (you can set any value that you want, just don't forget that the value is set in MB):
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max server memory', 12288;
GO
RECONFIGURE WITH OVERRIDE;
GO

Open in new window

0
Deepak ChauhanSQL Server DBACommented:
You have 32 GB total memory for two SQL server instances.
Generally we should leave 25% of the total memory for OS and other program.

In this case you have 24GB available physical memory for SQL server instances.

now you can configure

SQL 2008r2 > min memory=3GB.
SQL 2012 > min memory=3GB.

SQL 2008r2 > Max memory -- You can configure this value according to the utilisation. (or 50% of 24 GB for this instance and rest for SQL 2012)

But this is depend on the utilisation. Suppose SQL server 2012 has very less databases compare to SQL2008 in this case you have to configure it carefully.

like 60:40 or 70:30
1
Scott MilnerApplication AdministratorAuthor Commented:
thank you both for your responses.  I should clarify that there is only SQL 2012 running (the other reference was to the OS, Server 2008 R2), and that I added 32 GB of ram, bringing the total to 48GB.

If I follow your thinking, deepak, that would mean that I leave 12 GB (25%) of the total allocation to the OS, and set my maximum memory setting to 32768?

Do I need to worry about index creation or minimum memory per query settings?

Scott
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't have the same opinion as Deepak. From my experience I would let 1GB free for 32bit server and 4GB free for 64bit server. Is usually what the operating system needs to work. The rest can be set for the SQL Server instance(s).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott MilnerApplication AdministratorAuthor Commented:
good to know, Vitor!

Does having SS Analysis Services running on the same server change your opinion of how much memory should be set for the SQL max memory?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
That depends on the use of the SSAS. It will process the cubes and dimensions at the same time that the databases are heavily being accessed? Or is the opposite and it will only be used when the databases won't be accessed?
Do you also have SSIS and SSRS installed in the same box? SSIS is a memory eater!
0
Scott MilnerApplication AdministratorAuthor Commented:
ah.. good catch.

As far as I know, the cubes are supposed to be processed during off-hours (I'll need to figure out how to verify this).

SSIS is installed on this box as well.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
SSIS is installed on this box as well.
I hope that the packages are also running during off-hours or you can have performance issues in the future since you can't configure memory usage for SSIS (it will use all the RAM from the server if necessary).

That's a reason why SQL Server engine should be in a dedicated box and that's for have dedicated resources. Sharing resources with other applications can kill a database performance.
0
Scott MilnerApplication AdministratorAuthor Commented:
that makes sense.  Unfortunately, my IT resources are rather limited at this point (I just took this job), but I will dig into it with my long-range plans.  I'll check with our ERP (we use MS Dynamics Axapta) developers to see if they know how much we are utilizing SSIS, and on the timing of it's process runs.

Unless you or Deepak have any other thoughts, I'll start with a 32GB allocation to maximum memory, and monitor the response.

Thank you both for your help!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'll start with a 32GB allocation to maximum memory, and monitor the response.
Seems pretty good for me. Monitoring is what you really need to get the real memory usage from all processes.
Good luck in your new job. Cheers.
0
Scott MilnerApplication AdministratorAuthor Commented:
Thanks!  I'm learning, but it never seems to be fast enough!  :)

Have a great day!
0
Scott MilnerApplication AdministratorAuthor Commented:
One last question, Vitor...

does that script statement that you provided require a reboot?  I ran it, and the values were updated appropriately, but I also received a response that states I need to 'Run the RECONFIGURE statement to install.'

Can you assist me with the next step?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No reboot needed. Not even a SQL Server restart. The RECONFIGURE WITH OVERRIDE command should be enough to set the new configuration immediately.
0
Scott MilnerApplication AdministratorAuthor Commented:
perfect... thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.