SQL Server – How to Automatically Clear or Restart Virtual Memory Creep

memorysql serversql-server-2005

I know it's not good practice to do this, but my company cannot afford me trying to find a proper solution to our problem. I've read this and I could do the same with SQL Server 2005, but I was hoping there could be a better (quicker?) way.

The problem is as follows:

We have an ASP.net website (ASP2) running on Windows Server 2003 which also hosts our SQL Server 2005 database and install.

Lately we've noticed the VM Committed memory space for our SQL Server 2005 instance grows and it's almost at the point of VM Reserved space which we've set at 1.7 GB (hmm too much?).

The PC server our SQL Server is running on has 4 GB of RAM and up to 3.5 GB of free disc space for the SQL Server 2005 installation. The database itself is on a 350 GB share (there are two partitions for this: C and D drive respectively).

So in terms of VM space, I would have thought we have enough space and having set our VM limit to 1.7 GB (ideally 2 GB) would have sufficed.

The database itself is under 500 MB in size.

The service slows down considerably when our VM Committed value gets close to the VM Reserved value. The only way I know to bring this down is to stop all SQL Service services and re-start them again. But within 24 hours, the VM Committed still creeps up!

Is there a way I can just bugger trying to find the cause of the problem, but simply do a re-start of all the services overnight automatically? Or should I really be trying to find the cause of our problem?

I did some investigation (but as I said we're time poor at the moment), and found this SQL query (see below), which tells me what actual query seems to hog the CPU the most (not sure if this has anything to do with my original VM problem above).

It shows one query that just so happens to run overnight, and it could be that this query is simply hogging memory and not releasing it back to the pool?

Is there a way to "close" threads/memory allocation etc in SQL Server so that memory is freed back?

Here is the CPU "hog search" query (courtesy of this)

select  
    highest_cpu_queries.plan_handle,  
    highest_cpu_queries.total_worker_time, 
    q.dbid, 
    q.objectid, 
    q.number, 
    q.encrypted, 
    q.[text] 
from  
    (select top 50  
        qs.plan_handle,  
        qs.total_worker_time 
    from  
        sys.dm_exec_query_stats qs 
    order by qs.total_worker_time desc) as highest_cpu_queries 
    cross apply sys.dm_exec_sql_text(plan_handle) as q 
order by highest_cpu_queries.total_worker_time desc

Edit, answers to Pathum's questions:

Results from running the query:

---------------------------------------------------------------------------------------------------- 
Memory usage details for SQL Server instance SERVER9 (9.00.4053.00 - X86) - Standard Edition)
----------------------------------------------------------------------------------------------------

Memory visible to the Operating System
Physical Memory_MB|Physical Memory_GB|Virtual Memory GB 4091|4|2
Buffer Pool Usage at the Moment
BPool_Committed_MB|BPool_Commit_Tgt_MB|BPool_Visible_MB
519.250000|1599.875000|1599.875000

Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters
Mem_KB|Mem_MB|Mem_GB 531712|519.250000|0.507080078

Memory needed as per current Workload for SQL Server instance    
Mem_KB|Mem_MB|Mem_GB 1638272|1599.875000|1.562377929

Total amount of dynamic memory the server is using for maintaining connections
Mem_KB|Mem_MB|Mem_GB 560|0.546875|0.000534057

Total amount of dynamic memory the server is using for locks
Mem_KB|Mem_MB|Mem_GB 968|0.945312|0.000923156

Total amount of dynamic memory the server is using for the dynamic SQL cache
Mem_KB|Mem_MB|Mem_GB 1120|1.093750|0.001068115

Total amount of dynamic memory the server is using for query optimization
Mem_KB|Mem_MB|Mem_GB 552|0.539062|0.000526428

Total amount of dynamic memory used for hash, sort and create index operations.
Mem_KB|Mem_MB|Mem_GB 0|0.000000|0.000000000

Total Amount of memory consumed by cursors
Mem_KB|Mem_MB|Mem_GB 480|0.468750|0.000457763

Number of pages in the buffer pool (includes database, free, and stolen).
8KB_Pages|Pages_in_KB|Pages_in_MB 66464|531712.000000|519.250000000

Number of Data pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 50135|401080.000000|391.679687500

Number of Free pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 895|7160.000000|6.992187500

Number of Reserved pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 0|0.000000|0.000000000

Number of Stolen pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 15434|123472.000000|120.578125000

Number of Plan Cache pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 11953|95624.000000|93.382812500

Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references
Page Life in seconds|PLE Status 47011|PLE is Healthy

Number of requests per second that had to wait for a free page
Free list stalls/sec 0


Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed
Checkpoint pages/sec 572

Number of buffers written per second by the buffer manager"s lazy writer
Lazy writes/sec 0

Total number of processes waiting for a workspace memory grant
Memory Grants Pending 0

Total number of processes that have successfully acquired a workspace memory grant
Memory Grants Outstanding 0

Second query, checking for memory pressure:

SELECT object_name, cntr_value 
FROM sys.dm_os_performance_counters 
WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

Results:

object_name|cntr_value
SQLServer:Memory Manager        |1638272
SQLServer:Memory Manager        |531712
(2 row(s) affected)

My server is 32 bit. We will not be moving to 64 bit for any reason.
Values from sp_configure information cannot be provide, that's confidential. 🙂

Best Answer

First whenever you post question related to SQL Server please post 'version and edition of SQL Server', you wont believe answer might change completely with different versions.

Lately we've noticed the VM Committed memory space for our SQL Server 2005 instance grows and it's almost at the point of VM Reserved space which we've set at 1.7 GB (hmm too much?).

Why are you looking at VM committed and VM reserved, do you actually know what it tells. What are you trying to deduce from VM committed/Reserved. VM reserved is total amount of Virtual address space(VAS) SQL Server has reserved. SQL Server may reserve VAS as per its VAS limit which is 8 TB on 64 bit and by default 2 G on 32 Bit. So there is no problem when VM reserved is high. This is normal behavior. Reserved memory is actually which SQL Server "thinks" it might use in future. And since VM reserved is 1.7 G I don't think there is problem with this

VM committed means total amount of Virtual Address Space (VAS) or virtual memory SQL Server has committed or memory backed by physical RAM on the system. A committed memory has physical memory associated with it. Committed means total Virtual memory SQL Server is currently using.

The service slows down considerably when our VM Committed value gets close to the VM Reserved value. The only way I know to bring this down is to stop all SQL Service services and re-start them again. But within 24 hours, the VM Committed still creeps up!

On SQL Server VM committed can become equal to VM Reserved, it's not necessarily an issue. And please don't develop a habit of restarting SQL Server IMO this is worst you can do. Now what I think is you are facing performance issue in SQL Server and you need to find out first what actually is causing your Server slow. To get started with you can refer to How to analyze SQL Server Performance

This would actually help you to find out where the problem is. Believe me from what you posted it does not seems to me like its a memory issue, again you gave limited information about SQL Server version so my answer is limited.

Please add output of below in your question

Select @@Version

Is your SQL Server patched to SQL Server 2005 SP4. It must be. Can you read errorlog using below command and post its content on some shared location and attach link in question I would like to see what is there in errorlog.

sp_readerrorlog

You said you have only 4 G for SQL Server have you set max server memory for SQL Server ?

Is windows server 2003 patched to latest Service pack. Make sure it is there was bug in windows server which trimmed SQL Server memory excessively.

Edit:

.Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Your SQL Server is 32 bit and windows server is standard edition 32 bit. You cannot in anyway force windows server to use RAM more than 4 G and hence there is no point in enabling AWE on SQL Server which could have allowed data and index pages to see memory beyond there VAS limitation which is 2 G. I can only suggest you to upgrade both SQL Server and windows OS to 64 bit. I would recommend SQL Server 2012 with SP2 and windows Server 2012 or windows server 2008 r2 but make sure both are 64 bit.

If this is not possible can you upgrade Windows Server 2003 from standard to enterprise then you can enable PAE and window server can see more than 4 G. After that enable AWE to allow SQL Server data and index pages to see memory greater than 2 G. As of now in any case SQL Server VAS is limited to 2 G and hence it cannot access memory beyond 2G

Please read this article if you want to understand about 32 bit Memory configuration

Related Solutions

Sql-server – How to determine when to reduce physical memory for SQL Server

Your Page life expectancy counter is at 874 seconds. That isn't a lot. I'm guessing that the server isn't configured correctly, or something on the OS is taking the memory. Odds are this server could use more RAM to up the PLE.

SQL Server 2012 – dbcc memorystatus Buffer Pool Target on VM Higher Than Max Memory

Value Buffer pool target is not in amount of 8 KB pages but the Is actually number of pages which you were correct to assume . So as per output the target buffer pool memory is would be 8*32751616 which comes to 249G. Which is really absurd

Reason:

Now for reason why Bpool target is showing value 250 G in DBCC MEMORYSTATUS and memory manager is showing 12 G as Target committed is because the Counter SQL Server Buffer Manager: target Pages is DEPRECATED from SQL Server 2012 onwards. Microsoft did not specifically mentioned this anywhere but I found it from SQL Server Buffer Manager Object BOL Document. If you read this article you wont find counter target pages because its deprecated. You would also see the same counter present in SQL Server 2008 R2.

Other reason may be I assume your system is NUMA system with NUMA nodes. I am sure if you see MEMORYSTATUS output it would have memory allocation breakdown for various nodes like Node0, Node 1...Node 64. So target includes target memory allocation for all NUMA nodes. If you note Buffer pool in MEMORYSTATUS output, it has single entry and is not restricted to particular node. Plus this value is calculated during startup and does not actually shows correct value after a period of time so you should not refer to it. Instead refer to memory manager output

enter image description here

Edit:

As per question regarding buffer pool target pages on NUMA node believe me its not showing correct value for buffer pool. For my system with 4 G of RAM target pages are 7979008 with almost nothing running on the system. This is totally absurd and I would request you not to look at this counter. I had a chat with one of the experts on this field to confirm the behavior and he told me you should avoid looking at buffer pool output and target pages specially on NUMA system its calculation( way of calculation) is not correct. I can raise a connect Item but it would take few months to get response from MS team.

Not every thing in DBCC MEMORYSTATUS output matters to normal users many things are internal to MS and they would just not give any information regarding the same. If you want to monitor SQL Server memory usage please use below query.

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If you really want to monitor SQL Server target memory I would suggest you to look at

SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.

SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

There are other counters as well you can get from This Link.

Few other interesting counters are

Page reads/sec – Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design

Free List Stalls/sec – Number of requests per second that had to wait for a free page

PS: Please let me know if I have missed anything or if something is not clear

Edit: Here is the Connect Item for your issue lets wait what MS folks have to say about it. Please vote it

Best Answer

Related Solutions

Sql-server – How to determine when to reduce physical memory for SQL Server

SQL Server 2012 – dbcc memorystatus Buffer Pool Target on VM Higher Than Max Memory

Related Question