次の方法で共有

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

  • [アーティクル]

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 https://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'

Monitor Memory Usage of a SQL Server Instance.sql

Comments

  • Anonymous
    June 21, 2011
    awsome script thank you sir for writing such kind of brilliant script (mkumarvashisht@gmail.com)

  • Anonymous
    August 01, 2011
    Great help and save lot of manual work.

  • Anonymous
    November 03, 2011
    great tool, thanks

  • Anonymous
    February 09, 2012
    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

  • Anonymous
    February 23, 2012
    Cool Script was of great help to me... thanks man :)

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

  • Anonymous
    May 18, 2012
    Thank you very much.

  • Anonymous
    May 31, 2012
    Awesome Script My friend :)

  • Anonymous
    June 12, 2012
    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

  • Anonymous
    June 12, 2012
    Good one..Thanks

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

  • Anonymous
    October 18, 2012
    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.

  • Anonymous
    April 08, 2013
    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)).

  • Anonymous
    April 27, 2013
    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)?

  • Anonymous
    April 27, 2013
    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)?

  • Anonymous
    July 11, 2013
    Great script - I'm using it frequently :) Recently, I encountered this issue and investigated with your script: normally the "Lock Memory" on a System is about 140 MB - on a 32GB Server - thus not a problem at all. Once in a while a certain user process crashes with an error, telling "not enough space to maintain locks". When this happens, the Lock Memory is 3.5 GB. We don't use ROWLOCK in this case, or TF 1224/1211 or something ... Do you have an idea where this could come from, and maybe how to reset/release this memory space? Thanks & Cheers from Germany!

  • Anonymous
    August 28, 2013
    Could you please let me know, whether this Query creates Blockings or any other performance issue?

  • Anonymous
    August 28, 2013
    Hi Pavan, We are caching the DMV data to a temp table and doing further calculation so I don't expect any Blockings. I would not recommend running this very frequently...

  • Anonymous
    September 10, 2013
    Nice blog one query to give you consolidated info about memory.

  • Anonymous
    September 18, 2013
    Awesome script!! Thank you!!!

  • Anonymous
    November 24, 2013
    Thanks a bunch for this wonderful script. Perfect ...Awesome script.

  • Anonymous
    March 31, 2014
    The comment has been removed

  • Anonymous
    July 15, 2014
    Great script, Thanks for the hard work to put this together! Thanks Aamir Shahzad

  • Anonymous
    July 28, 2014
    The comment has been removed

  • Anonymous
    September 29, 2014
    Use full ..thanks :-)

  • Anonymous
    October 09, 2014
    Wonderful script...Appreciate your work

  • Anonymous
    November 10, 2014
    Hi Shakthivel, This was nice, I have a SQL Server 2005 instance, it has max server memory configured for some value, this script is working great for the instances which has default memory settings, but it is showing the max server memory value for the instances which are configured to use certain max server memory. Please help me on this. Thanks, Kasi

  • Anonymous
    January 21, 2015
    Thanks it is interesting to see all these results at one place. Now I can generate my health check report.

  • Anonymous
    June 22, 2015
    Thank you so much for the above script, I really appreciate you for making easy to understand the internal utilization of SQL memory.

  • Anonymous
    July 02, 2015
    Is there a SQL 2012 version available? Thanks for any info

  • Anonymous
    August 17, 2015
    Thanks  for the  script which  gives  clear  detail, Very much Useful

  • Anonymous
    September 22, 2015
    For anyone have problems with this script and 2012, see Dwayne J. Baldwin's excellent update here in the comments: Dwayne J. Baldwin  19 Oct 2012 12:55 AM   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.