memusage and memory_usage on sys.sysprocesses and sys.dm_exec_sessions

Question

memusage and memory_usage on sys.sysprocesses and sys.dm_exec_sessions

sakuraime 2,351

https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql?view=sql-server-ver15
there are columns

memusage on sys.sysprocesses sys.dm_exec_sessions

memory_usage on sys.dm_exec_sessions

are they referring the same things ?

is this the actually the size of the execution plan in the cache?

Sean Gallardy - MSFT 1,901 Reputation points Microsoft Employee

2021-01-29T22:46:24.97+00:00

sys.sysprocesses has been deprecated for almost 2 decades and is only around for backwards compatibility and shouldn't continue to be used. I'd go with whatever the official Docs have to say on sys.dm_exec_sessions but would not trust the data in sys.sysprocesses to be accurate or valid. In fact, many of the older system views such as this are not compatible with newer functionality and will not show up at all, please do not use them.
sakuraime 2,351 Reputation points

2021-01-30T07:34:03.98+00:00

so what's memory_usage on sys.dm_exec_sessions??

memory size of the plan ?
Sean Gallardy - MSFT 1,901 Reputation points Microsoft Employee

2021-01-30T13:47:15.663+00:00

There is no "memory_usage" column in sys.dm_exec_requests - there is however a ganted_qquery_memory column.
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-ver15
sakuraime 2,351 Reputation points

2021-01-31T07:54:38.813+00:00

i mean sys.dm_exec_sessions..
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-ver15
Sean Gallardy - MSFT 1,901 Reputation points Microsoft Employee

2021-01-31T12:34:09.923+00:00

The description is in the link you've given, it's documented what that column means.
sakuraime 2,351 Reputation points

2021-01-31T14:01:25.597+00:00

it's unclear... that's I have question ... you really know what's that actually mean ?
Sean Gallardy - MSFT 1,901 Reputation points Microsoft Employee

2021-01-31T15:49:55.237+00:00

It really means what it says, "Number of 8-KB pages of memory used by this session", there isn't some deeper or spiritual meaning to it.
sakuraime 2,351 Reputation points

2021-02-01T01:20:14.057+00:00

but use for what???
Sean Gallardy - MSFT 1,901 Reputation points Microsoft Employee

2021-02-01T01:39:20.527+00:00

Anything the session needs to use memory. You're looking at this extremely simplistic, however a session can use memory from various different areas, so this is just a number - it's not tied to any specific use.

1 answer

Your answer

Sean Gallardy - MSFT 1,901 Reputation points Microsoft Employee

2021-01-29T22:46:24.97+00:00

sys.sysprocesses has been deprecated for almost 2 decades and is only around for backwards compatibility and shouldn't continue to be used. I'd go with whatever the official Docs have to say on sys.dm_exec_sessions but would not trust the data in sys.sysprocesses to be accurate or valid. In fact, many of the older system views such as this are not compatible with newer functionality and will not show up at all, please do not use them.
sakuraime 2,351 Reputation points

2021-01-30T07:34:03.98+00:00

so what's memory_usage on sys.dm_exec_sessions??

memory size of the plan ?
Sean Gallardy - MSFT 1,901 Reputation points Microsoft Employee

2021-01-30T13:47:15.663+00:00

There is no "memory_usage" column in sys.dm_exec_requests - there is however a ganted_qquery_memory column.
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-ver15
sakuraime 2,351 Reputation points

2021-01-31T07:54:38.813+00:00

i mean sys.dm_exec_sessions..
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-ver15
Sean Gallardy - MSFT 1,901 Reputation points Microsoft Employee

2021-01-31T12:34:09.923+00:00

The description is in the link you've given, it's documented what that column means.
sakuraime 2,351 Reputation points

2021-01-31T14:01:25.597+00:00

it's unclear... that's I have question ... you really know what's that actually mean ?
Sean Gallardy - MSFT 1,901 Reputation points Microsoft Employee

2021-01-31T15:49:55.237+00:00

It really means what it says, "Number of 8-KB pages of memory used by this session", there isn't some deeper or spiritual meaning to it.
sakuraime 2,351 Reputation points

2021-02-01T01:20:14.057+00:00

but use for what???
Sean Gallardy - MSFT 1,901 Reputation points Microsoft Employee

2021-02-01T01:39:20.527+00:00

Anything the session needs to use memory. You're looking at this extremely simplistic, however a session can use memory from various different areas, so this is just a number - it's not tied to any specific use.

Answer 1

Cris Zhan-MSFT 6,671

Hi,

>is this the actually the size of the execution plan in the cache?

No, there is no correlation between buffer pool (cached data), procedure cache (execution plans), memory allocation and users (sessions).
https://stackoverflow.com/questions/33669296/determine-amount-of-memory-cached-by-user-in-sql-server

>what's memory_usage on sys.dm_exec_sessions?

Just a guess, but I would assume that this is the number of 8KB pages being used to manage the session.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a61b5a15-9530-451f-bc6d-d52b0b0a8b16/sysdmexecsessions-memoryusage-values-only-0-2-or-3?forum=sqldatabaseengine

Niraj Mitchell 0 Reputation points

2024-10-21T13:34:28.25+00:00

The memory size as it is stated in MS. e.g. if the result is memory_usage = 4, then 8KB x 4= 32KB

Share via

memusage and memory_usage on sys.sysprocesses and sys.dm_exec_sessions

1 answer

Your answer