Memory

Understanding Memory Pressure – Are you serious?

In early days, (SQL ver.2000 & before), it used to be very difficult to understand if SQL is running with lack of memory.

SQL 2005 & above there are verious ways and its easy to analyse if there is need of Memory to SQL instance.

Lets discuss.

There are two division, first of all we need to check if its INTERNAL or EXTERNAL memory pressure.

Below is the query that can provide information of external and internal memory pressure. It will return the data how much portion in the existing memory is assigned to differemt 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

We can also find the Memory Pressure by total & target Memory through performance counters.

Please check out “MEMtoLeave” section in MSDN.

Step 1. Buffer Cache Hit Ratio – This value should be above 95% always or close to 100%.

Be Carefull, as little spike in the buffer cache hit ratio over several hours or for a day does not mean that there is a something wrong. However, it gives an idea to proceed further analysis.

Query to find buffer cache hit ratio (SQL server 2005 & above)


SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value,OBJECT_NAME
    FROM sys.dm_os_performance_counters 
    WHERE counter_name = 'Buffer cache hit ratio base'
        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager' 

We can check the behaviour under base test load with read-ahead disabled, here is the article

https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/

Step 2. Page Life Expectancy
“Duration, in seconds, that a page resides in the buffer pool”

Value should be atleast 300 seconds, and if its consistantly goes less than that, there is something wrong.

Here is the query to check

 

--- PLE

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

3. Total & Target Memory.

We need to consistently monitor these values, does not mean that only one time if we find something goes up, we simply increase the memory.

And if only once a day eg. 5-7pm, we find the issue, we can plan finding route cause/query to tune.

I have found very good image from other blog to explain this perfectly.

TotalAndTarget

Here is the link of that blog for detail

http://www.sqlshack.com/sql-server-memory-performance-metrics-part-2-available-bytes-total-server-target-server-memory/


--To query these two counters run the following: The value should be equal in normal scenario.

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)')

--From here you can calculate the ratio in percentages of these two counters

--by running the following query:

SELECT ROUND(100.0 * (

 SELECT CAST([cntr_value] AS FLOAT)

 FROM sys.dm_os_performance_counters

WHERE  

  [object_name] LIKE '%Memory Manager%'

  AND [counter_name] = 'Total Server Memory (KB)'

 ) /

(

  SELECT CAST([cntr_value] AS FLOAT)

 FROM sys.dm_os_performance_counters

 WHERE

  [object_name]

LIKE '%Memory Manager%'

  AND [counter_name] = 'Target Server Memory (KB)')

 , 2)AS [Ratio]

As indicated above this measurement should be at around 100% once the steady state is reached…

 

To configure max memory here is the code


-- To check the existing value
SELECT value_in_use AS original_max_server_memory
FROM sys.configurations
WHERE name = 'max server memory (MB)'
GO

-- Increase/reset the Buffer Pool/Max Memory settings

EXEC sys. sp_configure N'show advanced options' , N'1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys. sp_configure N'max server memory (MB)' , N'1024'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys. sp_configure N'show advanced options' , N'0'
RECONFIGURE WITH OVERRIDE
GO

-- Clear all caches on the server
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ( 'ALL' )
GO

Important Queries


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

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

-- Get Buffer Cache size
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

------We can also check memory used by objects in database of your choice

USE MyDatabase;

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

Add these Performance Monitor counters

Memory – Available MBytes
SQLServer: Buffer Manager – Page Life Expectancy
SQLServer: Memory Manager – Memory Grants Pending
SQLServer: Memory Manager – Target Server Memory
SQLServer: Memory Manager – Total Server Memory
SQLServer: SQL Statistics – Batch Requests/sec
SQLServer: SQL Statistics – Compilations/sec

 

Query to find Performance counters current values

 


/*============================================================================

Author: Amit Vaid

Date: January 2013

Please check the query on test server before running on Production.

============================================================================*/

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..#check_performance') is not null DROP TABLE #check_performance

SELECT * INTO #check_performance FROM sys.dm_os_performance_counters

-- Get SQL Server instance name
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance 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 #check_performance WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Outstanding'

Great Articles

http://www.sqlpassion.at/archive/2011/10/19/query-memory-spills/

http://blog.apexsql.com/sql-server-memory-pressure-counters/

http://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/

http://www.brentozar.com/archive/2013/09/how-to-prove-your-sql-server-needs-more-memory-video/

http://www.sqlshack.com/sql-server-memory-performance-metrics-part-2-available-bytes-total-server-target-server-memory/

http://www.mssqltips.com/sqlservertip/2304/how-to-identify-microsoft-sql-server-memory-bottlenecks/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: