A DMV a day keeps the Doctor away

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

About these ads
This entry was posted in SQL Server 2005. Bookmark the permalink.

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