April 13, 2016 at 1:04 am
I have a very strange issue with one of the production servers. It is a very small server with only 12 GB of data, 16GB RAM and 12 core processors with hyper-threading enabled. Since, there is not a lot of customer activity on the box, the application server is also running from the box. The box has been in use for over 2 years and all of a sudden we started experiencing the box running slow and applications crashing. When we investigated, we saw that SQL Server was paging memory requests and the maximum page file size was only 8 GB. So, I increased the page file size to 30 GB. However, SQL server went ahead and took all of 30 GB and there were errors related to virtual memory in the windows event log. Then, we decided the increase the page file size again, and slowly the page file size went up to 110 GB and SQL Server still took all of it causing the server to crash.
The strangest thing I noticed during the course of the events is the fact that only 30% of RAM is being utilized with SQL Server only taking about 500 MB of RAM.
After spending a day working on this issue, we have now opened a ticket with Microsoft and their team has been working on it for 2 days, but no real improvement so far.
Has anyone ever faced similar issues? If yes, then what could be causing this issue and how can I fix it?
April 13, 2016 at 3:00 am
SQL doesn't use the page file. Windows may swap SQL's memory out to the page file is the OS is under memory pressure, but SQL doesn't use it directly.
What counter/monitor showed you SQL paging memory?
What are you using to see the amount of memory that SQL's using? If task manager, then the problem is probably in the tool. http://sqlinthewild.co.za/index.php/2016/01/19/stop-using-task-manager-to-check-sqls-memory-usage/
What are the errors you're seeing in the event log/error log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply