SQL Server Memory Related Queries

http://glennberrysqlperformance.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3dSQL%2520Server%25202008%2520R2

What is using all of my memory in SQL Server?  If you ever believe that you are seeing signs of memory pressure in a SQL Server instance, there are a number of queries you can run to help confirm that you are under memory pressure and to help determine what is using the most memory.

I have labeled each query as to which versions of SQL Server it will run on. The idea is to run the instance level queries to confirm memory pressure, and then to switch to the database that is using the most memory and run the database level queries to identify what is using the most memory in that database.

-- SQL Server 2008 and R2 Memory Related Queries

-- Glenn Berry 

-- October 2010

-- http://glennberrysqlperformance.spaces.live.com/

-- Twitter: GlennAlanBerry



-- Instance Level queries



-- Good basic information about memory amounts and state (SQL 2008 and 2008 R2)


SELECT 
total_physical_memory_kb, 
available_physical_memory_kb, 

       
total_page_file_kb, 
available_page_file_kb, 

       
system_memory_state_desc

FROM 
sys
.
dm_os_sys_memory
;




-- You want to see "Available physical memory is high"





-- SQL Server Process Address space info (SQL 2008 and 2008 R2)

--(shows whether locked pages is enabled, among other things)


SELECT 
physical_memory_in_use_kb,
locked_page_allocations_kb, 

       
page_fault_count, 
memory_utilization_percentage, 

       
available_commit_limit_kb, 
process_physical_memory_low, 

       
process_virtual_memory_low

FROM 
sys
.
dm_os_process_memory
;




-- You want to see 0 for process_physical_memory_low

-- You want to see 0 for process_virtual_memory_low





-- Page Life Expectancy (PLE) value for default instance (SQL 2005, 2008 and 2008 R2)


SELECT 
cntr_value AS 
[Page Life Expectancy]

FROM 
sys
.
dm_os_performance_counters


WHERE 
OBJECT_NAME 
= 
N'SQLServer:Buffer Manager' 
-- Modify this if you have named instances


AND 
counter_name = 
N'Page life expectancy'
;




-- PLE is a good measurement of memory pressure.

-- Higher PLE is better. Below 300 is generally bad.

-- Watch the trend, not the absolute value.





-- Get total buffer usage by database for current instance (SQL 2005, 2008 and 2008 R2)

-- Note: This is a fairly expensive query


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


AND 
database_id <> 
32767 -- ResourceDB


GROUP BY 
DB_NAME
(
database_id)


ORDER BY 
[Cached Size (MB)] DESC
;




-- Helps determine which databases are using the most memory on an instance





-- Memory Clerk Usage for instance

-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)

-- (SQL 2005, 2008 and 2008 R2)


SELECT TOP
(
20) 
[type], 
[name], 
SUM
(
single_pages_kb) 
AS 
[SPA Mem, Kb] 

FROM 
sys
.
dm_os_memory_clerks 


GROUP BY 
[type], 
[name]  

ORDER BY 
SUM
(
single_pages_kb) 
DESC
;




-- CACHESTORE_SQLCP  SQL Plans         These are cached SQL statements or batches that aren't in 

--                                     stored procedures, functions and triggers

-- CACHESTORE_OBJCP  Object Plans      These are compiled plans for stored procedures, 

--                                     functions and triggers

-- CACHESTORE_PHDR   Algebrizer Trees  An algebrizer tree is the parsed SQL text that 

--                                     resolves the table and column names





-- Find single-use, ad-hoc queries that are bloating the plan cache


SELECT TOP
(
100) 
[text], 
cp.
size_in_bytes

FROM 
sys
.
dm_exec_cached_plans 
AS 
cp

CROSS APPLY 
sys
.
dm_exec_sql_text
(
plan_handle) 


WHERE 
cp.
cacheobjtype = 
N'Compiled Plan' 


AND 
cp.
objtype = 
N'Adhoc' 


AND 
cp.
usecounts = 
1

ORDER BY 
cp.
size_in_bytes DESC
;




-- Gives you the text and size of single-use ad-hoc queries that waste space in the plan cache

-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and 2008 R2 only)

-- Enabling forced parameterization for the database can help, but test first!





-- Database level queries (switch to your database)

--USE YourDatabaseName;

--GO



-- Breaks down buffers used by current database by object (table, index) in the buffer cache

-- (SQL 2008 and 2008 R2) Note: This is a fairly expensive query


SELECT 
OBJECT_NAME
(
p.
[object_id]) 
AS 
[ObjectName], 


p.
index_id, 
COUNT
(*)/
128 AS 
[Buffer size(MB)],  
COUNT
(*) 
AS 
[BufferCount], 


p.
data_compression_desc AS 
[CompressionType]

FROM 
sys
.
allocation_units 
AS 
a

INNER JOIN 
sys
.
dm_os_buffer_descriptors 
AS 
b

ON 
a.
allocation_unit_id = 
b.
allocation_unit_id

INNER JOIN 
sys
.
partitions 
AS 
p

ON 
a.
container_id = 
p.
hobt_id

WHERE 
b.
database_id = 
CONVERT
(
int
,
DB_ID
())

AND 
p.
[object_id] > 
100

GROUP BY 
p.
[object_id], 
p.
index_id, 
p.
data_compression_desc

ORDER BY 
[BufferCount] DESC
;






-- Top Cached SPs By Total Logical Reads (SQL 2008 and 2008 R2). Logical reads relate to memory pressure


SELECT TOP
(
25) 
p.
name AS 
[SP Name], 
qs.
total_logical_reads AS 
[TotalLogicalReads], 


qs.
total_logical_reads/
qs.
execution_count AS 
[AvgLogicalReads],
qs.
execution_count, 


ISNULL
(
qs.
execution_count/
DATEDIFF
(
Second
, 
qs.
cached_time, 
GETDATE
()), 
0) 
AS 
[Calls/Second], 


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

What causes memory pressure, and what can you do about it? 

Well, first try to make sure you are running a 64-bit version of SQL Server. Try to make sure you are running SQL Server 2008 R2 on top of Windows Server 2008 R2. Of course, it may be that you are stuck on an older version of SQL Server, but you should always be pushing to upgrade to a newer version (IMHO).

Regardless of what version of SQL Server you are running, you should be on the lookout for poorly written queries, that return too many columns or too many rows. Always push back on your developers to see if they really need to return every column in a table. Always question whether they need to return every row in a table. Be on the lookout for missing indexes and implicit conversions that cause SQL server to do table or index scans on large tables.