The following query will provide details of external and internal memory pressure. It will return the data how much portion in the existing memory is assigned to what kind of memory type.
SELECT TYPE, SUM(single_pages_kb) InternalPressure, SUM(multi_pages_kb) ExtermalPressure FROM sys.dm_os_memory_clerks GROUP BY TYPE ORDER BY SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESC GO
What is your method to find memory pressure?
Reference: Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
10 Comments. Leave new
Hi,
We use mainly Zenoss for monitoring our servers. It gives warnings when the system is running out of memory or harddrive is filling up etc. I think it could be set to watch Sql Server memory statistics also via performance counters. I think it’s working rather good.
The real problem comes when Sql Server is under heavy pressure and you need to decide what to do about it. Luckily in my case there’s always been some single query that’s taking a huge amount of resources. I’ve simply issued kill command and the server has continued normally.
run perfmon utility
add counter select Performance object SQLINSTANT:Memory
ex. MSSQL$SQLEXPRESS:Memory and add allcounter
it shows graphical memory pressure
I use this querys to analise the memory (Dashboard memory):
select counter_name ,cntr_value,cast((cntr_value/1024.0)/1024.0 as numeric(8,2)) as Gb
from sys.dm_os_performance_counters
where counter_name like ‘%server_memory%’;
–See code depot for full script
— Page Life Expectancy
SELECT cntr_value AS ‘Page Life Expectancy’
FROM sys.dm_os_performance_counters
WHERE object_name = ‘SQLServer:Buffer Manager’
AND counter_name = ‘Page life expectancy’
— Top 10 consumers of memory from buffer pool
SELECT TOP (10) type, SUM(single_pages_kb)/1024. AS [SPA Mem, MB],SUM(Multi_pages_kb)/1024. AS [MPA Mem,MB]
FROM sys.dm_os_memory_clerks
GROUP BY type
–HAVING SUM(single_pages_kb) + sum(Multi_pages_kb) > 20000
ORDER BY SUM(single_pages_kb) DESC
SELECT SUM(single_pages_kb)/1024. AS [SPA Mem, KB],SUM(Multi_pages_kb)/1024. AS [MPA Mem, KB]
FROM sys.dm_os_memory_clerks
— Get total buffer usage by database
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 ––- exclude system databases
AND database_id 32767– –- exclude ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;
SELECT
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 ––- exclude system databases
AND database_id 32767– –- exclude ResourceDB
1. Look for a message in the Resource Manager Ring buffer
select convert(xml, record) from sys.dm_os_ring_buffers where ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’
2. Look for Free Pages below 640
https://blogs.msdn.microsoft.com/
SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = ‘Free pages’ AND [object_name] LIKE ‘%BUFFER MANAGER%’
3. Look for Lazy Writes/sec above 20
DECLARE @per_second_counter TABLE (point_in_time datetime primary key clustered not null, read_value bigint not null);
INSERT @per_second_counter (point_in_time, read_value)
SELECT GETDATE(), cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Lazy Writes/Sec’ AND [object_name] LIKE ‘%BUFFER MANAGER%’;
while NOT EXISTS (SELECT 0 FROM @per_second_counter WHERE point_in_time DATEADD(SECOND, -11, later.point_in_time) AND earlier.point_in_time < later.point_in_time
4. Look for Page Life Expectancy below 300
SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' AND [object_name] LIKE '%BUFFER MANAGER%'
can any body explain
below query
(select * from table) a
what the purpose of “a” here.
It gives name or alias to the subquery. Check this example:
select a.foo, a.bar
from (
select 1 as foo, 2 as bar
) a
Agree with Pinal. I always thought single pages kb plus multi pages kb is the actual use of memory. This post confirms my belief.
Hello Pinal,
Your query is not working.
Sorry Pinal , I don,t know it was for sql server 2008
When it comes for tuning or pressure on memory we have to find if any query or proc which is taking more memory then required.e.g if a date column can be added on a sub query which is fetch 10 cr of records or using fast forward cursor.many things comes into play theory can’t be implemented as practical different environments means different tuning.