SQL Server Memory and its overview in the query outputs In Below article am going to cover about Memory and its role in SQL Server.
Series of Queries will be presented below which are prepared for the performance monitoring of SQL Server.
All the queries below will return the output which can help DBA to come to a conclusion about the performance of SQL Server.
All the queries are tested in SQL Server 2008/R2. Revert to me if found any issues in Queries, I will check and get back.
/*Below Query will give details of memory across all the memory nodes */
DBCC MEMORYSTATUS
/* Query to find the memory free in VAS */
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)^0)
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
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size)
SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB]
,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
/*Find top memory clerks occupying space in VAS (RAM) phyical */
SELECT [type],
memory_node_id,
single_pages_kb,
multi_pages_kb,
virtual_memory_reserved_kb,
virtual_memory_committed_kb,
awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY virtual_memory_reserved_kb DESC ;
/* Query to find space occupied by each database in Data cache */
SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC
/* Query will give number of cached plans and its size in cache * /
SELECT count(*) AS ‘Number of Plans’,
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS ‘Plan Cache Size (MB)’
FROM sys.dm_exec_cached_plans
/* Running this script breaks down the plan cache size by cached object type */
SELECT objtype AS ‘Cached Object Type’,
count(*) AS ‘Number of Plans’,
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS ‘Plan Cache Size (MB)’,
avg(usecounts) AS ‘Avg Use Count’
FROM sys.dm_exec_cached_plans
GROUP BY objtype
/* Memory query to give overall allocated and overall free */
select @@servername as [server name],total_physical_memory_kb/1024 as [Overall server Physical memory IN MB],
available_physical_memory_kb/1024 [Overall server Available Physical memory In MB],
(total_physical_memory_kb-available_physical_memory_kb)/1024 [Overall memory allocated in MB] from sys.dm_os_sys_memory
go
select @@servername as [server name],physical_memory_in_use_kb/1024 [Current Instance Physical Memory in Use IN MB] from sys.dm_os_process_memory
GO
select @@servername as [server name],cntr_value/1024 [Target Memory set for Instance in MB] from sys.sysperfinfo where object_name like '%Memory%' and counter_name like '%Target%'
GO
/* Check memory allocated at Clerk Level. Sum of the commited memory is the memory totally utilized by the Instance at the current point of time */
SELECT [type],
memory_node_id,
single_pages_kb,
multi_pages_kb,
virtual_memory_reserved_kb,
virtual_memory_committed_kb,
awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY virtual_memory_reserved_kb DESC ;
/* number of plans in each cache store and its size */
SELECT [name],
[type],
single_pages_kb + multi_pages_kb AS total_kb,
entries_count
FROM sys.dm_os_memory_cache_counters
ORDER BY total_kb DESC ;
/* Top 10 processes running in the server */
select top 10 * from master.dbo.sysprocesses
where
status <> 'background'
AND cmd NOT IN ('signal handler','lock monitor','log writer','lazy writer','checkpoint sleep','awaiting command',
'TASK MANAGER')
and spid > 50
and spid <> @@spid
order by CPU desc
Series of Queries will be presented below which are prepared for the performance monitoring of SQL Server.
All the queries below will return the output which can help DBA to come to a conclusion about the performance of SQL Server.
All the queries are tested in SQL Server 2008/R2. Revert to me if found any issues in Queries, I will check and get back.
/*Below Query will give details of memory across all the memory nodes */
DBCC MEMORYSTATUS
/* Query to find the memory free in VAS */
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)^0)
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
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size)
SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB]
,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
/*Find top memory clerks occupying space in VAS (RAM) phyical */
SELECT [type],
memory_node_id,
single_pages_kb,
multi_pages_kb,
virtual_memory_reserved_kb,
virtual_memory_committed_kb,
awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY virtual_memory_reserved_kb DESC ;
/* Query to find space occupied by each database in Data cache */
SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC
/* Query will give number of cached plans and its size in cache * /
SELECT count(*) AS ‘Number of Plans’,
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS ‘Plan Cache Size (MB)’
FROM sys.dm_exec_cached_plans
/* Running this script breaks down the plan cache size by cached object type */
SELECT objtype AS ‘Cached Object Type’,
count(*) AS ‘Number of Plans’,
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS ‘Plan Cache Size (MB)’,
avg(usecounts) AS ‘Avg Use Count’
FROM sys.dm_exec_cached_plans
GROUP BY objtype
/* Memory query to give overall allocated and overall free */
select @@servername as [server name],total_physical_memory_kb/1024 as [Overall server Physical memory IN MB],
available_physical_memory_kb/1024 [Overall server Available Physical memory In MB],
(total_physical_memory_kb-available_physical_memory_kb)/1024 [Overall memory allocated in MB] from sys.dm_os_sys_memory
go
select @@servername as [server name],physical_memory_in_use_kb/1024 [Current Instance Physical Memory in Use IN MB] from sys.dm_os_process_memory
GO
select @@servername as [server name],cntr_value/1024 [Target Memory set for Instance in MB] from sys.sysperfinfo where object_name like '%Memory%' and counter_name like '%Target%'
GO
/* Check memory allocated at Clerk Level. Sum of the commited memory is the memory totally utilized by the Instance at the current point of time */
SELECT [type],
memory_node_id,
single_pages_kb,
multi_pages_kb,
virtual_memory_reserved_kb,
virtual_memory_committed_kb,
awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY virtual_memory_reserved_kb DESC ;
/* number of plans in each cache store and its size */
SELECT [name],
[type],
single_pages_kb + multi_pages_kb AS total_kb,
entries_count
FROM sys.dm_os_memory_cache_counters
ORDER BY total_kb DESC ;
/* Top 10 processes running in the server */
select top 10 * from master.dbo.sysprocesses
where
status <> 'background'
AND cmd NOT IN ('signal handler','lock monitor','log writer','lazy writer','checkpoint sleep','awaiting command',
'TASK MANAGER')
and spid > 50
and spid <> @@spid
order by CPU desc
/* Buffer manager Specific Query */
SELECT 'Procedure
Cache Allocated',
CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
counter_name = 'Procedure cache pages'
UNION
SELECT 'Buffer Cache database pages',
CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
counter_name = 'Database pages'
UNION
SELECT 'Free pages',
CONVERT(int,((CONVERT(numeric(10,2), cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
counter_name = 'Free pages'
GO
--- To get top 5 processed which are utilising more memory usage in sqlserver
select top 5 * from master..sysprocesses order by memusage desc
Hope the above queries will help in troubleshooting the performance issues due to memory
Thanks
