I thought I would start a short series where I put out a Dynamic Management View (DMV) query every day, for 30 days (or until I get tired of it). So here is query #1:
– How much Physical RAM does the machine have?
SELECT physical_memory_in_bytes/1024 AS physical_memory_in_kb
FROM sys.dm_os_sys_info
Bonus Related Query
This non-dmv query lets you see what the Page Life Expectancy (PLE) is for a SQL Server instance. PLE tells you how long, on average (in seconds) that data pages are staying in the buffer. The general rule of thumb is that a value below 300 is a sign of memory pressure, but I would argue that you should just keep an eye on this value after you have a baseline, to help judge how the system is doing. This is exposed as a performance counter in PerfMon, but you can also get at it with the query below:
– Get PLE from sys.dm_os_performance_counters
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’
– Get PLE from sys.sysperfinfo (old way)
SELECT cntr_value AS ‘Page Life Expectancy’
FROM sys.sysperfinfo
WHERE object_name = ‘SQLServer:Buffer Manager’
AND counter_name = ‘Page life expectancy’
Technorati Tag: SQL Server
