SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2012 x64 is not using free memory


SQL Server 2012 x64 is not using free memory

Author
Message
Bennersj
Bennersj
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 11
I have a SQL Server 2012 x64 instance running on a server with 32GB of RAM. The server is a workhorse for our organization with over 100 databases and is used by several different departments (roughly 500GB of data between all the databases). It ran fine until three days ago when we started getting complaints of timeouts and queries taking 5-10x longer to run than normal. Looking at the server, Processor% is low, but disk IO is high for that server (50-100MB/sec with 50-100 Disk Queue Length). We cannot identify anything that has changed recently in how the server is being used. We have restarted it with no improvement.

The OS has allocated 24GB of RAM to the server, but it appears SQL Server is using less than 1GB for data caching. At this point, I think the server is not caching as it should and is doing much more physical data reads than it should. However, I have not been able to find any suggestions on getting SQL Server to use more RAM for the data cache. Some details are below:

SQL Server Version:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

PerfMon Counters (This is what I am seeing right now, but I have been watching them throughout the day and have not noticed much change)
Page life expectancy: 4.03 (I have not seen a value above 12 today)
Page lookups/sec: 190,324.857
Page reads/sec: 4,799.761
Readahead pages/sec: 4,490.515
Database pages: 70,456.490
Database Cache Memory (KB): 563,652
Buffer cache hit ratio: 98.826
Total Server Memory (KB): 24,649,755
Free Memory (KB): 22,467,926


The low page life expectancy, along with a low database cache memory and high free memory has me concerned. I think this is the problem, but I have not been able to find any ideas on getting SQL Server to use more data for caching. This is a production envrionment, so troubleshooting options are limited, but with the problems we are experiencing a little downtime is preferable to continued performance problems. I appreciate any suggestions you may have. Let me know if there is any other data I should also be looking at with troubleshooting.
Bennersj
Bennersj
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 11
Our server admin noticed that an External HDD we were using for backups was not showing up and Windows was giving an error that the device could not be installed. He unplugged the drive and restarted (again), and now the server is running MUCH better. Database Cache Memory is now over 20GB and disk IO for the server is averaging 5MB/sec and below.

He said that we had a problem with some of our file servers last year with external drives (USB 3.0 drives plugged into a USB 2.0 port) with very similar symptoms - the servers ran very sluggish with no noticeable reason.

I have no idea how an external hard drive could cause memory issues like this, and it is possible that the fix was related more to restarting again. However, at this point all I can do is wait and see if it happens again.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search