March 17, 2014 at 12:26 pm
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.
March 17, 2014 at 3:41 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply