--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;
---------------------------------------------------------------------------------------------------------------------------
-- 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;