Find amount of Memory consumed by SPID

Problem: Report Server running extremely slowly, memory possibly a culprit, find the cause

Solution: sys.dm_exec_requests contains a useful column named granted_query_memory which represents the number of pages allocated to each running process. To find the amount of memory allocated to the process multiply this value by 8.

The following query provides this information as well as other useful bits

select er.session_id, es.host_name, db_name(er.database_id) as 'database', er.status,
er.command, er.blocking_session_id as 'blocker',
er.wait_type, er.wait_time, er.wait_resource, er.percent_complete,er.start_time,
er.cpu_time, er.total_elapsed_time, er.total_elapsed_time - er.cpu_time as 'signal time',
er.reads, er.writes, er.logical_reads, er.granted_query_memory * 8 as 'query mem kb',
st.text, qp.query_plan
from sys.dm_exec_requests er
join sys.dm_exec_sessions es
on er.session_id = es.session_id
cross apply sys.dm_exec_sql_text(er.sql_handle) st
cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where er.session_id > 50
and er.session_id @@SPID
order by er.session_id;

Unrelated note – I found an easy way of deterining when the SQL server started

select sqlserver_start_time from sys.dm_os_sys_info


Leave a Reply

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

You are commenting using your 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: