Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2008R2 Memory & Performance Issues

Posted on 2014-02-19
4
Medium Priority
?
868 Views
Last Modified: 2014-02-25
I am running SQL Server 2008R2 on a virtual Windows Server 2008R2, hosted on a VMware host running ESX 4.0.  The problem I am having is one of slow responsiveness on the server.  The server was originally configured with 8GB of memory and the SQL server was set at a maximum memory of 3GB.  

When checking the memory usage on the Performance tab in Task Manager, I found that 98% of available physical memory was being used. On the Resource Monitor I found that sqlservr.exe was utilizing the most with Working memory of 1.2GB.  This has resulted in slow response time to the users connecting to the SQL database, ase well as slow response when logged onto the server itself.

I decided to bump the physical memory on the VM to 16GB and reset the SQL memory to 4GB.
vm configuration
SQL max memory
Our current situation is that even with these changes, memory is now maxed out again around 98-99% utilized.
Performance
The SQL server appears to be using the bulk of the memory, but adding up all the running processes only nets me about 6GB in use. I’ve no idea what is using the remaining 10GB
resource monitor

Any thoughts on how I can improve performance or decrease the amount of memory that is being used?
0
Comment
Question by:Pancomp60
[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
  • 3
4 Comments
 

Author Comment

by:Pancomp60
ID: 39870731
Update:

I ran a Sysinterals App named RamMap and found that "Driver Locked" has 10,906.096K allocated to it.  From what I can find online, Driver Locked corresponds to locked pages in memory.  Since the SQL service is not configured to utilize locked pages, why would I have 10GB in locked pages?
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 1500 total points
ID: 39871041
HI,

I suggest that if your VM has 16GB of memory, then 4GB for SQL is too low. Suggest that a better figure would be in the 10-14GB range.

HTH
  David
0
 

Accepted Solution

by:
Pancomp60 earned 0 total points
ID: 39874951
Thanks David, I suspected as much too but when I adjusted it, the whole system bogged down.  But...that turned out not to be the issue.

Apparently whoever originally setup this particular VM configured the reserved memory limit to 4GB!  Have no idea why, but once I set it unlimited, my memory issues went away.  I will leave the SQL memory setting at 12GB per David's suggestion as it makes sense to me.  So far the system is running better than it ever has and since the VM was ported over from a bare-metal server 3 years ago, I kick myself for not discovering the memory configuration error earlier.
0
 

Author Closing Comment

by:Pancomp60
ID: 39885114
Lesson learned: Thoroughly check over your memory configuration and settings on the VM before addressing them in SQL or Windows.
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article explains how to install and use the NTBackup utility that comes with Windows Server.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
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 …

604 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