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

by Mark   Last Updated March 09, 2016 07:02 AM

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!



Answers 1


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;
Dan Guzman
Dan Guzman
March 09, 2016 13:39 PM

Related Questions


creating index on big table(s)

Updated September 03, 2018 18:06 PM

Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM