T-SQL Query Script to monitor Memory Usage of a SQL Server Instance

 

 

 

 

 

 

T-SQL Query Script to monitor Memory Usage of a SQL Server Instance

Rate This
  • Comments 27

If you are using Task Manager to monitor the Memory Usage of SQL Server, please note that Working set shown in Task Manager for SQL Server does not include memory committed by SQL Server using AWE allocations and Large pages. Also "Total Server Memory" Performance Counter is also not the total memory used by SQL Server because it shows only the memory allocation through Buffer Pool. The more accurate way to monitor memory usage of SQL Server is to use DBCC MEMORYSTATUS. Refer KB 271624, KB 907877, BOL for more details on this.

Now, let's come to the topic of this blog. The script below utilizes performance monitor counters and it works for SQL Server of version > 2005. There are lot of new memory related DMV's in SQL Server 2008 which can give the same data so I will rewrite this script for SQL Server 2008 later using those DMV's.

Feel free to pitch-in if you have any recommendations to modify this script: Run this query in Management Studio with "Results to Text" mode for better viewing. I have attached t-sql script for quick download.

/*============================================================================ 
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'
Attachment: Monitor Memory Usage of a SQL Server Instance.sql
Leave a Comment
  • Please add 2 and 8 and type the answer here:
  • Post
  • awsome script thank you sir for writing such kind of brilliant script (mkumarvashisht@gmail.com)

  • Great help and save lot of manual work.

  • great tool, thanks

  • Hi Sakthivel,

    I think for counters like Stolen Pages,Reserved Pages the (cntr_value*@pg_size)/1024.0 as Pages_in_KB should be Pages_in_MB.

    Because we are already converting it to KB when doing cntr_value*@pg_size.

    Please correct me I am missing something here.

    Reply from Sakthi:
    cntr_value*@pg_size reports value in Bytes

  • Cool Script was of great help to me... thanks man :)

  • Great Script Sakthivel.  You have lot of blessings from many DBAs for this.  Keep up the good work!!!

  • Thank you very much.

  • Awesome Script My friend :)

  • How would you calculate the "Available RAM"?

    Can it be done via your script?

    Thanks

    Reply from Sakthi:
    Graham, you may need to add this

    SELECT (available_physical_memory_kb/1024.0) AS [Available RAM] FROM sys.dm_os_sys_memory

  • Good one..Thanks

  • Thanks for the script, troubleshooting a maxmem setting on sql 2005 64 Bit, very helpful.

  • For SQL 2012 a few columns have been renamed

    physical_memory_in_bytes      physical_memory_kb  

    virtual_memory_in_bytes       virtual_memory_kb    

    bpool_commit_target           committed_target_kb  

    bpool_visible                 visible_target_kb    

    bpool_commited                committed_kb

    Note that calculations may need to be modifed.

  • The global system variable @@SERVERNAME is not accurate

    (i.e. when machine name was changed without dropping server).

    You should use CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(255)).

  • A wonderful post! Thank you! Am a beginner to SQL Server usage. What is the difference between using DBCC MEMORYSTATUS to get the memory usage detail (i.e. % of memory usage), and using columns from sys.dm_os_sys_info ? Sakthi says that the former is more accurate. why? Can someone please elaborate ? (am not going to use Memory Monitor, I want to do it through queries/SPs i.e T-SQL)?

  • Am a beginner to SQL Server usage. What is the difference between using DBCC MEMORYSTATUS to get the memory usage detail (i.e. % of memory usage), and using columns from sys.dm_os_sys_info ? Sakthi says that the former is more accurate. Is it true? If yes, why? Can you please elaborate from T-SQL perspective (am not going to use Memory Monitor, I want to do it through queries/SPs)?

Page 1 of 2 (27 items) 12