What is the request_id in sys.dm_exec_requests and how does it change?

According to MSDN, the request_id in sys.dm_exec_requests is the


"ID of the request. Unique in the context of the session. Is not
nullable."

It always appears to be 0, from casual observation.

Can someone tell me how, for a given session, this id can ever be more than 0? Is it possible to reproduce this with some sql?

Thanks!

Can someone tell me how, for a given session, this id can ever be more
than 0? Is it possible to reproduce this with some sql?

The request_id other than zero indicates a connection with Multiple Active Result Sets (MARS) enabled. With MARS, a single connection may have multiple active statements, with statement execution interleaved.

From SSMS, open a new query window connection specifying MultipleActiveResultSets=Trueunder "Options-->Additional Connection Parameters". Then run the query below in the window. You should see a non-zero request_id value.

SELECT * 
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;

StackExchange Database Administrators Q#131695, answer score: 5