1

I have one of my production servers that has a windows page file of over 32 GB.

The server in question has 32 GB of RAM, windows server 2008.

It is exclusively used for Sql Server. Nothing else.

The lock pages in memory setting has not been set up for any of the sql server services accounts and I think this might be related to the big size of the page file.

I am already seeing if I can add more memory to this box, but I would like to find out what is causing memory pressure (if anything inside sql server).

what could be some options as how I can do that?

I kind of like, in this case, to monitor Paging File % Usage and if something goes off threshold, I would collect all that is running on sql server at that time. Hopefully the culprits would be all there.

any ideas?

select @@Version

Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Mar 25 2011 13:33:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

enter image description here

dbcc memorystatus

enter image description here

Understanding pagefile.sys % Usage performance monitor (perfmon) counter correctly

  • 1
    Can you please add output of select @@Version and select * from sys.dm_os_process_memory – Shanky Aug 16 '16 at 18:28
  • 1
    What is the SQL Server memory capped at? Please also post the output from sp_configure 'max server memory'. – wBob Aug 16 '16 at 19:05
  • @Shanky I have added. sys.dm_os_process_memory does not work on sql 2005 though. Is there any alternative? – Marcello Miorelli Aug 17 '16 at 14:29
  • @wBob I have added that! – Marcello Miorelli Aug 17 '16 at 14:30
  • @marcellomiorelli Yes correct it would not work in SQL Server 2005 we need to see output of dbcc memorystatus in that under memory manager we have to see VM Committed. – Shanky Aug 17 '16 at 14:49
2

what is causing my windows page file to grow?

That's a good question. If you want to know what applications are causing this in a physical environment and even to a good extent a virtual environment then the following performance counters can be used:

  1. Process()\Page File Bytes
  2. Process()\Page File Bytes Peak

Additionally I'd capture:

  1. Paging File\%Usage
  2. Paging File\%Usage Peak

In a virtual environment the memory pressure is normally done within the guest os by a balloon driver which artificially inflates to grab back memory for other uses. This generally won't show as using paged memory in the file.

The lock pages in memory setting has not been set up for any of the sql server services accounts and I think this might be related to the big size of the page file.

I get what you're saying, but it shouldn't have a bearing on the size of the page file. The system settings for page file have the bearing on the size of the page file. It may be just set to 32GB... who knows. Check out why it's set that way in the system properties.

I am already seeing if I can add more memory to this box, but I would like to find out what is causing memory pressure (if anything inside sql server).

If your min/max memory settings are set properly then SQL Server shouldn't be the main cause of memory pressure according to Windows, especially since you said it's dedicated with nothing else running. It could be internal memory pressure, where clock hands ae sweeping caches (inside or outside hands) and that would be due to workload/amount of memory available. That should be investigated.

How do you know there is pressure? Could you update your post with what you've found?

| improve this answer | |
  • good points. If I find something relevant I will update the post but maybe the server is no longer under pressure now, it could be that it was under memory pressure in the past. – Marcello Miorelli Aug 17 '16 at 7:23
  • 1
    If paging is from SQL Server side output of DMV sys.dm_os_process_memory would show you. You have to look at the difference of columns physical_memory_in_use_kb and virtual_address_space_committed_kb the difference in value is current SQL Server paged out to disk. – Shanky Aug 17 '16 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.