The quick and easy way to determine how much memory your SQL Server is using is to look at the Task Manager on the server and find “sqlservr.exe” in the process list, and look at the “Mem Usage” column.
But what if you are not on the server, or if you want to grab this information programmatically from an application, it is not so obvious. After 15 different Google queries, and many articles saying to just use DBCC MEMORYSTATUS, I finally tracked it down.
The problem with DBCC MEMORYSTATUS, is that it returns nearly 500 values about every aspect of memory possible on the SQL Server.
I finally discovered that if I take all of the output from DBCC MEMORYSTATUS, and insert it into a temporary table, I can then query for the one line of information that I need. The total memory used, that matches what the task manager shows is called “Working Set”. The following query returns the same memory usage numbers as the task manager shows.
CREATE TABLE #tmp
( row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), value BIGINT );
INSERT INTO #tmp EXEC ( 'DBCC MEMORYSTATUS' );
SELECT top 1 value / 1024 'KBUsed'
FROM #tmp
WHERE RTRIM(LTRIM(name)) = 'Working Set';
DROP TABLE #tmp;From here I was able to call this from my C# code as follows: (conn is of type SqlConnection and was already open and connected).
// get the total
memory used by the SQL Server
SQLStatement = @"CREATE TABLE #tmp
( row_id INT IDENTITY PRIMARY KEY, name
VARCHAR(100), value BIGINT );
INSERT INTO
#tmp EXEC ( 'DBCC MEMORYSTATUS' );
SELECT top 1
value / 1024 'KBUsed'
FROM #tmp
WHERE RTRIM(LTRIM(name)) = 'Working Set';
DROP TABLE
#tmp;";
SQLDataAdapter = new SqlDataAdapter(SQLStatement,
conn);
dtResult = new DataTable();
SQLDataAdapter.Fill(dtResult);
foreach (DataRow drRow in dtResult.Rows)
{
tbServerMemory.Text = drRow["KBUsed"].ToString();
}
SQLDataAdapter.Dispose(); This gives you a quick way to check the amount of memory being used by the SQL Server without being on the server or using the Task Manager.