A way to clear/restart virtual memory creep automatically?

Question

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. :)

Answer 1

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

Answer 2

Start by checking max memory. Instructions here: http://technet.microsoft.com/en-us/library/ms191144(v=sql.90).aspx

You should set the max to be low enough to leave space for the os and any other apps. Which in your case is going to be very low. Maybe 500mb-1gb

Answer 3

Could you post the following result from the Query?These points I have provided below may help you diagnose the issue

Script to report Memory usage details of a SQL Server instance Original Author Sakthivel Chidambaram This Script will help you to diagnose the issue

            /*============================================================================
               Script to report Memory usage details of a SQL Server instance
               Author: Sakthivel Chidambaram, Microsoft http://blogs.msdn.com/b/sqlsakthi 

               Date: June 2012
               Version: V2
               
               V1: Initial Release
               V2: Added PLE, Memory grants pending, Checkpoint, Lazy write,Free list counters

               THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
               ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
               TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
               PARTICULAR PURPOSE. 

            ============================================================================*/ 
            -- We don't need the row count
            SET NOCOUNT ON

            -- Get size of SQL Server Page in bytes
            DECLARE @pg_size INT, @Instancename varchar(50) 
            SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E' 

            -- Extract perfmon counters to a temporary table
            IF OBJECT_ID('tempdb..#perfmon_counters') is not null DROP TABLE #perfmon_counters
            SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters

            -- Get SQL Server instance name
            SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM #perfmon_counters WHERE counter_name = 'Buffer cache hit ratio' 

            -- Print Memory usage details
            PRINT '----------------------------------------------------------------------------------------------------' 
            PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4) + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')' 
            PRINT '----------------------------------------------------------------------------------------------------' 
            SELECT 'Memory visible to the Operating System' 
            SELECT CEILING(physical_memory_in_bytes/1048576.0) as [Physical Memory_MB], CEILING(physical_memory_in_bytes/1073741824.0) as [Physical Memory_GB], CEILING(virtual_memory_in_bytes/1073741824.0) as [Virtual Memory GB] FROM sys.dm_os_sys_info 
            SELECT 'Buffer Pool Usage at the Moment' 
            SELECT (bpool_committed*8)/1024.0 as BPool_Committed_MB, (bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,(bpool_visible*8)/1024.0 as BPool_Visible_MB FROM sys.dm_os_sys_info 
            SELECT 'Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Total Server Memory (KB)' 
            SELECT 'Memory needed as per current Workload for SQL Server instance' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Target Server Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for maintaining connections' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Connection Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for locks' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Lock Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for the dynamic SQL cache' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'SQL Cache Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for query optimization' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Optimizer Memory (KB) ' 
            SELECT 'Total amount of dynamic memory used for hash, sort and create index operations.' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Granted Workspace Memory (KB) ' 
            SELECT 'Total Amount of memory consumed by cursors' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total' 
            SELECT 'Number of pages in the buffer pool (includes database, free, and stolen).' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name= @Instancename+'Buffer Manager' and counter_name = 'Total pages' 
            SELECT 'Number of Data pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Database pages' 
            SELECT 'Number of Free pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free pages' 
            SELECT 'Number of Reserved pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Reserved pages' 
            SELECT 'Number of Stolen pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Stolen pages' 
            SELECT 'Number of Plan Cache pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Plan Cache' and counter_name = 'Cache Pages' and instance_name = '_Total'
            SELECT 'Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references' 
            SELECT cntr_value as [Page Life in seconds],CASE WHEN (cntr_value > 300) THEN 'PLE is Healthy' ELSE 'PLE is not Healthy' END as 'PLE Status'  FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Page life expectancy'
            SELECT 'Number of requests per second that had to wait for a free page' 
            SELECT cntr_value as [Free list stalls/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free list stalls/sec'
            SELECT 'Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed' 
            SELECT cntr_value as [Checkpoint pages/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Checkpoint pages/sec'
            SELECT 'Number of buffers written per second by the buffer manager"s lazy writer'
            SELECT cntr_value as [Lazy writes/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Lazy writes/sec'
            SELECT 'Total number of processes waiting for a workspace memory grant'
            SELECT cntr_value as [Memory Grants Pending] FROM #perfmon_counters WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Pending'
            SELECT 'Total number of processes that have successfully acquired a workspace memory grant'
            SELECT cntr_value as [Memory Grants Outstanding] FROM #perfmon_counters WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Outstanding'

---

• Using this you can check 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)');

---

• If your SQL Server deployment on a 32bit OS. Consider moving it to a 64bit OS.
• Also better if you could provide the values from sp_configure information

Hope my points will help you :)

Update on 2014-DEC-10

@Fernando68 Hi have you enabled AWE(Address Windowing Extensions ) in your SQL Server ? and note that this is not necessary on a 64-bit machine, as the operating system’s memory limits (a 64 bit memory address),SQL Server will always try to use AWE if it is enabled, and SQL Server can dynamically manage its AWE memory usage. Upon startup, it will allocate the amount of memory needed for the startup workload.

You can refer the below article

How It Works: SQL Server 32 bit PAE/AWE on (SQL 2005, 2008, and 2008 R2) – Not Using As Much RAM As Expected!