Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Memory Consumed By SQLServer

Have you wondered how much memory was being consumed by SQL Server?  Have you wondered if there was a way to find out that information from a tSQL command?

On occasion it would be nice to be able to find this information.  A command was provided for this very purpose.  Have you heard of DBCC MemoryStatus?

As stated in the MSDN documentation for this command, “The DBCC MEMORYSTATUS command provides a snapshot of the current memory status of Microsoft SQL Server.” (MSDN).  There is a grundle of information that is provided through this command.  I recommend reading the documentation on it to better understand the outputs – otherwise you may completely misunderstand the results.

Sometimes, you may just want to find high-level usage information.  For a snapshot of that high-level information, you could try a query such as this:

DECLARE @MemTable TABLE
	( row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), VALUE BIGINT );
INSERT INTO @MemTable EXEC ( 'DBCC MEMORYSTATUS' );
 
SELECT TOP 1 Name,CONVERT(DECIMAL(16,2),VALUE) / 1024/1024 'MBUsed'
	FROM @MemTable
	WHERE RTRIM(LTRIM(name)) = 'Working Set';

I got the idea for this query online (slightly adapted) from the following blog.  Go check it out.  Once you delve into the result set of dbcc memorystatus, you can quickly see how many possibilities there would be for various scripts based on the results.

Comments

Posted by Glenn Berry on 1 March 2011

Good post, Jason.  This query also works for SQL Server 2008 and above.

-- Get SQL Server 2008 memory usage

SELECT physical_memory_in_use_kb, process_physical_memory_low,

      process_virtual_memory_low

FROM sys.dm_os_process_memory;

Posted by Dukagjin Maloku on 6 March 2011

Nice post Jason, thanks for sharing!

Posted by saber_talas on 6 March 2011

I have a server with 16 GB memory, Whenever I run SQLServer

engine it consume all memory , Does any one know how can I reduce memory usage in Sql Server 2008 ?

Posted by Adam Aspin on 6 March 2011

Very informative - thanks for this one!

Posted by olivier.kovacs on 7 March 2011

saber_talas.

when connected through SSMS to your DataBase server, right-click on the Server name from the Object Explorer to get to it's properties.

In the Memory section, you can set a min and max memory consumption value to limit the boundaries of what can be used.

Hope this helps.

Posted by marvin.deoliveira on 10 March 2011

Hi saber_talas.

I´d like to remember you that when you need to do that(set max memory parameter) how olivier.kovacs well said, it means your SQL Server is consuming whatever it´s got.

So, after setting this parameter, I strongly recomend that you check whats causing that amount of memory allocation in order to prevent memory issues.

Leave a Comment

Please register or log in to leave a comment.