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:
[codesyntax lang=”tsql”]
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';
[/codesyntax]
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.
