July 31, 2015 at 7:26 am
Hello,
I'm looking for some assistance in monitoring page file usage for SQL Server. Ultimately, I'm trying to prove that there is not a problem with a server and that it is not struggling with the workload, but someone has looked at page file usage and suggested there is a problem.
I've set up performance counters running from a separate server, but the counters relating to page file usage seem wildly inaccurate. E.g., I've got (_Total\% Usage), and (Total)\%Usage Peak), with Total Usage showing a constant value of 64, and the peak showing 92. I've also got Process (sqlservr)\Page File Bytes and Page File Bytes Peak, which are showing values like 61,275,688,960 and hardly dropping below this.
If I look at Performance Monitor directly the values shown in here correlate with this, as shown in my first screenshot. However, if I look at resource monitor and look at disk activity, you can see that there is basically nothing going on (screenshot 2).
The server is more than equipped to deal with the workload, and looking at other counters for SQL there is no indication of problems, e.g. page life expectancy has a minimum value showing of 576,258, there is never less than 6GB of free RAM, SQL compilations are at a maximum of 5% of batch requests.
It is a physical server with 2x 8 core multi threaded CPUs, 64GB RAM with 58GB as the SQL server maximum. Hopefully this is enough info, but I'm happy to check anything else.
I'm clearly looking at or doing something wrong. Can anyone please offer some advice?
Thanks.
July 31, 2015 at 7:58 am
Are you utilizing NUMA?
July 31, 2015 at 8:12 am
It looks as though the server supports NUMA, but I haven't configured anything around that. I don't really have any experience of NUMA actually, and it wasn't something that I thought to look at.
It looks like there are 2 NUMA nodes with 8 CPUs each.
July 31, 2015 at 9:04 am
SQL Server doesn't use the page file. The only time SQL will end up in the page file is if the OS is under memory pressure and SQL doesn't release memory as requested. In that situation, the OS will page SQL's memory out to the page file to avoid an OS crash.
If there are bytes written to the page file for the SQL service, it's the OS being proactive, just in case SQL does ever has to be paged out.
You can have a page file almost zero on a SQL server machine, the only thing the page file is needed for are OS mini-dumps.
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
July 31, 2015 at 10:24 am
I am with Gail. Page files on SQL Servers should NEVER be used when things are set up correctly, and I advise my clients to make them just big enough for the mini-dumps too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 31, 2015 at 9:06 pm
Hello.
Thank you, I appreciate the responses, although they don't really address my question.
I'm not expecting to see the page file being used, as I don't believe there is any reason it would need to be used, but the way I would track this would be through perfmon, and the data I am seeing there looks to be quite different from my expectations.
I really need to be able to produce data I can take to the person questioning this, which at the moment I really don't have and what I am seeing doesn't make any sense. Can anyone explain the results I'm getting, or point me in the direction of where I would look to produce evidence that SQL is not using the page file and that it is handling the current workload without issue?
The problem is really that I can't pick and choose which results suit my expectations- if I'm going to say a particular counter shows there is no memory pressure, then I need to be able to explain why another seems to contradict this.
Thanks.
August 1, 2015 at 2:03 am
Honestly, I'd just go back to that person and explain to them how SQL's memory works and why looking at the page file is pretty much a waste of time for diagnosing SQL's memory pressure, rather than digging around in perfmon counters for ages and getting into an argument about what the figures actually mean.
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
August 1, 2015 at 3:12 am
Ok, thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply