Monitor SQL Server Memory Usage

Reference: http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/28/t-sql-script-to-monitor-memory-usage-by-sql-server-instance.aspx


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'

MSDTC and sys.dm_tran_active_transactions

dbcc opentran


go

dbcc inputbuffer(spidnumber)

go


select distinct(request_owner_guid) as [UoW ID] from sys.dm_tran_locks where request_session_id = -1

--- SQL Server 2005 onwards
select * from sys.dm_tran_active_transactions where name = 'DTCXact'
go
select * from sys.dm_tran_active_transactions where Transaction_state = 2 and name ='DTCXact'


Reference:

http://www.sqlserver-dba.com/2011/11/ms-dtc-and-sysdm_tran_active_transactions.html

http://www.sqlserver-dba.com/2011/07/sql-server-detected-a-dtcktm-in-doubt-transaction-with-uow.html

SQL Server Books Online has full details only all columns and values returned > For analysis purposes , I’ll highlight a few:


Transaction_type = “4” means it’s a distributed transaction
Transaction_state =”2” means the transaction is active

As a follow up I execute:
select * from sys.dm_tran_locks where request_owner_guid = '922D5B60-A5CA-4C96-8891-703021B40AD8'

This returns transactions related to this owner guid.

If you see -2 in the request_session_id column , investigate further for orphaned distributed transactions.