SQL Server Performance Counters - Memory Queries

--Bpool stats

select (bpool_committed * 8192)/(1024*1024) as bpool_committed_mb,(cast(bpool_commit_target as bigint) * 8192) / (1024*1024) as bpool_target_mb,(bpool_visible * 8192) / (1024*1024) as bpool_visible_mb from sys.dm_os_sys_info

-- Get me physical RAM installed  and size of user VAS

select physical_memory_in_bytes/(1024*1024) as phys_mem_mb,virtual_memory_in_bytes/(1024*1024)as user_virtual_address_space_size fromsys.dm_os_sys_info  go

-- What about multi_page consumers--

select type, name, sum(multi_pages_kb)/1024 as multi_pages_mb from sys.dm_os_memory_clerks where multi_pages_kb > 0 group by type, nameorder by multi_pages_mb desc go

-- Let's now get the total consumption of virtual allocator--

select SUM(virtual_memory_committed_kb)/1024 as total_virtual_mem_mb from sys.dm_os_memory_clerks go

-- Get me all clerks that take some memory--

select * from sys.dm_os_memory_clerks where (single_pages_kb > 0) or (multi_pages_kb > 0) or (virtual_memory_committed_kb > 0) go

-- Get me stolen pages--

select (SUM(single_pages_kb)*1024)/8192 as total_stolen_pages from sys.dm_os_memory_clerks go

-- Get me the total amount of memory consumed by multi_page consumers

select SUM(multi_pages_kb)/1024 as total_multi_pages_mb fromsys.dm_os_memory_clerks go

-- Is anyone using AWE allocator?-

select SUM(awe_allocated_kb)/1024 as total_awe_allocated_mb

go

 

-- Breakdown clerks with stolen pages

select type, name,sum((single_pages_kb*1024)/8192) as stolen_pages from sys.dm_os_memory_clerks where single_pages_kb > 0 group by type, name order by stolen_pages desc go

-- Breakdown the clerks who use virtual allocator--

select type, name,sum(virtual_memory_committed_kb)/1024 as virtual_mem_mb fromsys.dm_os_memory_clerks where virtual_memory_committed_kb > 0 group bytype, name order by virtual_mem_mb desc go

-- Who are the biggest cache stores?

select name, type,(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024

as cache_size_mb fromsys.dm_os_memory_cache_counters

where type like 'CACHESTORE%'group by name, type order by cache_size_mb desc

go

from sys.dm_os_memory_clerks  go

-- Who are the biggest user stores?

select name, type,(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024

as cache_size_mb fromsys.dm_os_memory_cache_counters where type like'USERSTORE%' group by name, type order by cache_size_mb desc go

-- Get me other information about system memory

select total_physical_memory_kb/(1024)

as phys_mem_mb,available_physical_memory_kb/(1024)

as avail_phys_mem_mb,system_cache_kb/(1024) as sys_cache_mb,

(kernel_paged_pool_kb+kernel_nonpaged_pool_kb)/(1024)

as kernel_pool_mb,total_page_file_kb/(1024) as total_virtual_memory_mb,available_page_file_kb/(1024) as available_virtual_memory_mb,system_memory_state_desc

from sys.dm_os_sys_memory   go

-- Who is the AWE user?

select type, name, sum(awe_allocated_kb)/1024 as awe_allocated_mb 

from sys.dm_os_memory_clerks where awe_allocated_kb > 0 group bytype, name order by awe_allocated_mb desc go

-- What is the total memory used by the clerks?-

select (sum(multi_pages_kb)+SUM(virtual_memory_committed_kb)+

SUM(awe_allocated_kb))/1024 from sys.dm_os_memory_clerks go

-- Does this sync up with what the node thinks?--

select SUM(virtual_address_space_committed_kb)/1024 as total_node_virtual_memory_mb,SUM(locked_page_allocations_kb)/1024 as total_awe_memory_mb,SUM(single_pages_kb)/1024 as total_single_pages_mb,

SUM(multi_pages_kb)/1024 as total_multi_pages_mb

from sys.dm_os_memory_nodes where memory_node_id != 64

go

-- Total memory used by SQL Server through SQLOS memory nodes

-- including DAC node-- What takes up the rest of the space?

select (SUM(virtual_address_space_committed_kb)+

SUM(locked_page_allocations_kb)+

SUM(multi_pages_kb))/1024 as total_sql_memusage_mb fromsys.dm_os_memory_nodes

go

-- Who are the biggest object stores?

select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024 as cache_size_mb

from sys.dm_os_memory_clerks where type like 'OBJECTSTORE%'

group by name, type order by cache_size_mb desc

go

select mc.type, mo.type from sys.dm_os_memory_clerks mc

join sys.dm_os_memory_objects mo

on mc.page_allocator_address = mo.page_allocator_address

group by mc.type, mo.type

order by mc.type, mo.type

go

--------------------------------------------------------------------

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE'%Buffer Manager%' AND [counter_name] = 'Page life expectancy'

 

SELECT [counter_name], [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manager%' AND [counter_name] IN ('Page reads/sec','Page writes/sec', 'Lazy writes/sec')

 

SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE'%Memory Manager%' AND [counter_name] = 'Memory Grants Pending'

 

--To find out which queries are currently waiting on a memory grant, run the following query:

SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL

 

--Total Server Memory and Target Server Memory

SELECT [counter_name], [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] IN ('Total Server Memory (KB)', 'Target Server Memory (KB)')

 

SELECT      CASE counter_name  WHEN 'Target Server Memory (KB)' THEN'MemoryAssignedToSQLServer' WHEN 'Total Server Memory (KB)' THEN'MemoryUsedBySQLServer'  END AS Property,           (cntr_value/1024) AS Value_MB FROM  sys.dm_os_performance_counters

WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')

 

--From here you can calculate the ratio in percentages of these two counters by running the following query: - See more at: http://blog.apexsql.com/sql-server-memory-pressure-counters/#sthash.UOAxm9d0.dpuf

SELECT ROUND(100.0 * ( SELECT CAST([cntr_value] AS FLOAT) FROMsys.dm_os_performance_counters WHERE       [object_name] LIKE '%Memory Manager%'AND [counter_name] = 'Total Server Memory (KB)' ) / ( SELECT CAST([cntr_value] ASFLOAT) FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] = 'Target Server Memory (KB)') , 2)AS [Ratio] 

select Instancename,TargetServerMemoryKB/1024 'TargetServerMemoryMB',TotalServerMemoryKB/1024 as 'TotalServerMemoryMB',PLE,(TargetServerMemoryKB/1024)/PLE as 'ChurnMB/sec' from(select @@SERVERNAME as'Instancename',max(case when counter_name = 'Target Server Memory (KB)' then cntr_value end) as 'TargetServerMemoryKB',max(case when counter_name = 'Total Server Memory (KB)' then cntr_value end) as 'TotalServerMemoryKB',max(case when counter_name = 'Page life expectancy'  then cntr_value end) as 'PLE' fromsys.dm_os_performance_counters) as p

;with cte as (

select @@SERVERNAME as 'Instancename',

max(case when name = 'min server memory (MB)' then value_in_use end)as 'MinServerMemoryMB',

max(case when name = 'max server memory (MB)' then value_in_use end)as 'MaxServerMemoryMB' from sys.configurations)

select p.Instancename,os.PhysicalCPUCount, os.PhysicalMemoryMB,

c.MinServerMemoryMB,c.MaxServerMemoryMB,p.TargetServerMemoryKB/1024 as 'TargetServerMemoryMB',p.TotalServerMemoryKB/1024 as'TotalServerMemoryMB',p.PLE , (p.TotalServerMemoryKB)/p.PLE as'ChurnKB/sec' from( select @@SERVERNAME as 'Instancename',

max(case when counter_name = 'Target Server Memory (KB)' then cntr_value end) as 'TargetServerMemoryKB',

max(case when counter_name = 'Total Server Memory (KB)' then cntr_value end) as 'TotalServerMemoryKB',

max(case when counter_name = 'Page life expectancy'  then cntr_value end) as 'PLE'

from sys.dm_os_performance_counters)

as p join cte c on p.instancename = c.instancename

join (SELECT @@SERVERNAME as 'Instancename', cpu_count AS'LogicalCPUCount', hyperthread_ratio AS 'HyperthreadRatio',

cpu_count/hyperthread_ratio AS 'PhysicalCPUCount',

physical_memory_in_bytes/1048576 AS 'PhysicalMemoryMB'

FROM sys.dm_os_sys_info ) as os

on c.instancename=os.instancename

-- Get me memory information about SQLSERVR.EXE process

-- GetMemoryProcessInfo() API used for this

-- physical_memory_in_use_kb

select physical_memory_in_use_kb/(1024) as sql_physmem_inuse_mb,locked_page_allocations_kb/(1024) as awe_memory_mb,total_virtual_address_space_kb/(1024) as max_vas_mb,virtual_address_space_committed_kb/(1024) as sql_committed_mb,memory_utilization_percentage as working_set_percentage,virtual_address_space_available_kb/(1024) as vas_available_mb,process_physical_memory_low as is_there_external_pressure,process_virtual_memory_low as is_there_vas_pressure from sys.dm_os_process_memory

go

SELECT physical_memory_in_use_kb,

locked_page_allocations_kb,memory_utilization_percentage

FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);

go

select * from sys.dm_os_ring_buffers where ring_buffer_type like'RING_BUFFER_RESOURCE%'

go

select memory_node_id as node, virtual_address_space_reserved_kb/(1024) as VAS_reserved_mb,virtual_address_space_committed_kb/(1024) as virtual_committed_mb,locked_page_allocations_kb/(1024) as locked_pages_mb,single_pages_kb/(1024) as single_pages_mb,multi_pages_kb/(1024) as multi_pages_mb,shared_memory_committed_kb/(1024) as shared_memory_mb from sys.dm_os_memory_nodes where memory_node_id != 64

go

with vasummary(Size,reserved,free) as ( select size = vadump.size,reserved = SUM(case(convert(int, vadump.base) ^ 0)  when 0 then 0 else 1 end),free = SUM(case(convert(int, vadump.base)^ 0x0) when 0 then 1 else 0 end)from (select CONVERT(varbinary,sum(region_size_in_bytes)) as size,region_allocation_base_address as base from sys.dm_os_virtual_address_dump where region_allocation_base_address <> 0x0 group by region_allocation_base_address  UNION(

select CONVERT(varbinary, region_size_in_bytes),region_allocation_base_address fromsys.dm_os_virtual_address_dump where region_allocation_base_address = 0x0))as vadump group by size) select * from vasummary

go

--memory per instance

 SELECT      CASE counter_name  WHEN 'Target Server Memory (KB)'THEN 'MemoryAssignedToSQLServer' WHEN 'Total Server Memory (KB)'THEN 'MemoryUsedBySQLServer'  END AS Property,           (cntr_value/1024) AS Value_MB FROM  sys.dm_os_performance_counters WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')

------------------------------------------------------

----expensive queries Using DMV. here you find an example how to identifiy this queries:

SELECT TOP 10  SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,

 ((CASE qs.statement_end_offset

        WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset

    END - qs.statement_start_offset) / 2) + 1),

    qs.execution_count,    qs.total_logical_reads,

    qs.last_logical_reads,    qs.total_logical_writes,

    qs.last_logical_writes,    qs.total_worker_time,

    qs.last_worker_time, qs.total_elapsed_time / 1000000 total_elapsed_time_in_s,

    qs.last_elapsed_time / 1000000 last_elapsed_time_in_s,

    qs.last_execution_time,   qp.query_plan

FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

--ORDER BY qs.total_logical_reads DESC -- logical reads

--ORDER BY qs.total_logical_writes DESC -- logical writes

 ORDER BY qs.total_worker_time DESC -- CPU time

These queries tell you which databases are consuming most memory in the buffer cache and then you can drill down to which objects in that database are using the most memory, that might help point you in the direction of the queries that use those objects:

--find out how big buffer pool is and determine percentage used by each database

 

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value   FROM sys.dm_os_performance_counters

WHERE RTRIM([object_name]) LIKE '%Buffer Manager'   AND counter_name = 'Total Pages';

;WITH src AS(   SELECT        database_id, db_buffer_pages = COUNT_BIG(*)

FROM sys.dm_os_buffer_descriptors       --WHERE database_id BETWEEN 5 AND 32766      

GROUP BY database_id)SELECT   [db_name] = CASE [database_id] WHEN 32767        THEN 'Resource DB'        ELSE DB_NAME([database_id]) END,   db_buffer_pages,   db_buffer_MB = db_buffer_pages / 128,   db_buffer_percent = CONVERT(DECIMAL(6,3),        db_buffer_pages * 100.0 / @total_buffer)

FROM src

ORDER BY db_buffer_MB DESC;

 

--then drill down into memory used by objects in database of your choice

 

USE db_with_most_memory;

 

WITH src AS(   SELECT       [Object] = o.name,       [Type] = o.type_desc,       [Index] = COALESCE(i.name, ''),       [Index_Type] = i.type_desc,       p.[object_id],       p.index_id,       au.allocation_unit_id  

FROM       sys.partitions AS p   INNER JOIN       sys.allocation_units AS au       ON p.hobt_id = au.container_id   INNER JOIN       sys.objects AS o       ON p.[object_id] = o.[object_id]   INNER JOIN       sys.indexes AS i       ON o.[object_id] = i.[object_id]       AND p.index_id = i.index_id   WHERE       au.[type] IN (1,2,3)       AND o.is_ms_shipped = 0)

SELECT   src.[Object],   src.[Type],   src.[Index],   src.Index_Type,   buffer_pages = COUNT_BIG(b.page_id),   buffer_mb = COUNT_BIG(b.page_id) / 128

FROM   src INNER JOIN   sys.dm_os_buffer_descriptors AS b   ON src.allocation_unit_id = b.allocation_unit_id

WHERE   b.database_id = DB_ID() GROUP BY   src.[Object],   src.[Type],   src.[Index],   src.Index_Type

ORDER BY   buffer_pages DESC;

------------------------------------------------------------------------------------------------------------------------

-- This helps you find the most expensive cached stored procedures from a memory perspective-- You should look at this if you see signs of memory pressure-- Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure

 SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],

qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count

AS [avg_elapsed_time], qs.cached_time

FROM sys.procedures AS p

INNER JOIN sys.dm_exec_procedure_stats AS qs

ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

ORDER BY qs.total_logical_reads DESC;

SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads],

qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count,

qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count

AS [avg_elapsed_time], qs.cached_time

FROM sys.procedures AS p

INNER JOIN sys.dm_exec_procedure_stats AS qs

ON p.[object_id] = qs.[object_id]

WHERE qs.database_id = DB_ID()

ORDER BY qs.total_physical_reads, qs.total_logical_reads DESC;

---------------------------------------------------------------------------------------------------------------------------

Determine SQL Server memory use by database and object

-- Note: querying sys.dm_os_buffer_descriptors -- requires the VIEW_SERVER_STATE permission.  DECLARE @total_buffer INT;  SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters  WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Database Pages';  ;WITH src AS ( SELECT  database_id, db_buffer_pages = COUNT_BIG(*) FROM sys.dm_os_buffer_descriptors --WHERE database_id BETWEEN 5 AND 32766 GROUP BY database_id ) SELECT [db_name] = CASE [database_id] WHEN 32767  THEN 'Resource DB'  ELSE DB_NAME([database_id]) END, db_buffer_pages, db_buffer_MB = db_buffer_pages / 128, db_buffer_percent = CONVERT(DECIMAL(6,3),  db_buffer_pages * 100.0 / @total_buffer) FROM src ORDER BY db_buffer_MB DESC;

So now I know that I can drill into that database specifically if I want to track down the objects that are taking up most of that memory.

use Team ;WITH src AS ( SELECT [Object] = o.name, [Type] = o.type_desc, [Index] = COALESCE(i.name, ''), [Index_Type] = i.type_desc, p.[object_id], p.index_id, au.allocation_unit_id FROM sys.partitions AS p INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id] INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id WHERE au.[type] IN (1,2,3) AND o.is_ms_shipped = 0 ) SELECT src.[Object], src.[Type], src.[Index], src.Index_Type, buffer_pages = COUNT_BIG(b.page_id), buffer_mb = COUNT_BIG(b.page_id) / 128 FROM src INNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id WHERE b.database_id = DB_ID() GROUP BY src.[Object], src.[Type], src.[Index], src.Index_Type ORDER BY buffer_pages DESC;