Having a sustained value below 640 could mean that new memory requests may stall and performance will suffer. Looking at Buffer Manager Free Pages over a period of time can give a general indication of possible memory pressure. You will also want to look at Page Life Expectancy and Buffer Catch Hit Ratio.
Buffer Manager Free Pages in SQL Server 2012 is not applicable.
SELECT cntr_value ,
GETDATE() AS [CollectionDt]
FROM sys.dm_os_performance_counters WITH ( NOLOCK )
WHERE OBJECT_NAME = N'SQLServer:Buffer Manager'
AND counter_name = N'Free pages'
OPTION (RECOMPILE);
GETDATE() AS [CollectionDt]
FROM sys.dm_os_performance_counters WITH ( NOLOCK )
WHERE OBJECT_NAME = N'SQLServer:Buffer Manager'
AND counter_name = N'Free pages'
OPTION (RECOMPILE);
To learn more about the Buffer Manager and Free Pages and memory monitoring, please take a look at these links:
- http://msdn.microsoft.com/en-us/library/ms189628(v=sql.105).aspx
- http://msdn.microsoft.com/en-us/library/ms187743(v=sql.110).aspx
- http://blogs.msdn.com/b/vsanil/archive/2012/11/21/did-some-performance-counters-like-free-pages-disappear-in-sql-2012.aspx
great post!!!
ReplyDelete