memusage and memory_usage on sys.sysprocesses and sys.dm_exec_sessions

sakuraime 2,331

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,891 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,331 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,891 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,331 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,891 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,331 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,891 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,331 Reputation points

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

but use for what???
Sean Gallardy - MSFT 1,891 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

Cris Zhan-MSFT 6,641 Reputation points

2021-02-04T09:13:58.29+00:00

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
Please sign in to rate this answer.
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
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

memusage and memory_usage on sys.sysprocesses and sys.dm_exec_sessions

1 answer

Your answer