A way to clear/restart virtual memory creep automatically?

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 sear

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

StackExchange Database Administrators Q#84574, answer score: 5