-- 20170320 -- Dmitry Gorchilin www.digger.dp.ua -- Процессы наиболее активно работающие с памятью, для 2005 только с tempdb -- https://technet.microsoft.com/ru-ru/library/ms187938(v=sql.105).aspx select u.session_id, (u.internal_objects_alloc_page_count+u.user_objects_alloc_page_count)/128 AS Mb_alloc, (u.internal_objects_dealloc_page_count+u.user_objects_dealloc_page_count)/128 AS Mb_dealloc, u.user_objects_alloc_page_count, u.user_objects_dealloc_page_count, u.internal_objects_alloc_page_count, u.internal_objects_dealloc_page_count, --u.user_objects_deferred_dealloc_page_count, u.session_id, s.[host_name], s.program_name, s.login_name, s.nt_domain, s.nt_user_name, s.cpu_time, s.memory_usage, s.total_elapsed_time, s.reads, s.writes, s.logical_reads, case when s.transaction_isolation_level=0 then 'Unspecified' when s.transaction_isolation_level=1 then 'ReadUncomitted' when s.transaction_isolation_level=2 then 'ReadCommitted' when s.transaction_isolation_level=3 then 'Repeatable' when s.transaction_isolation_level=4 then 'Serializable' when s.transaction_isolation_level=5 then 'Snapshot' ELSE '?' END AS transaction_isolation_level from sys.dm_db_session_space_usage u INNER JOIN sys.dm_exec_sessions s ON u.session_id=s.session_id WHERE u.user_objects_alloc_page_count+u.user_objects_dealloc_page_count+ u.internal_objects_alloc_page_count+u.internal_objects_dealloc_page_count>0 ORDER BY u.user_objects_alloc_page_count+u.user_objects_dealloc_page_count+ u.internal_objects_alloc_page_count+u.internal_objects_dealloc_page_count DESC