SQL Server 2005 includes some great new dynamic management views and functions to help find out what is going on. These three in particular work quite well together.
|sys.dm_exec_requests||Dynamic Management View||Returns one row for each request executing within SQL Server|
|sys.dm_exec_sessions||Dynamic Management View||shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. Use sys.dm_exec_sessions to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.|
|sys.dm_exec_sql_text||Dynamic Management Function||Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.|
|sys.dm_exec_text_query_plan||Dynamic Management Function||Return Query plan this is identified by an sql_plan_handle.|
dm_exec_sessions can be joined to the dm_exec_requests to obtain data on who is currently executing what. This will return a sql_handle for which the function dm_exec_sql_test can be used to get actual sql text. In order to get the sql text fro every session returned we can use a new type of join CROSS APPLY. This will execute a UDF for every row.
Putting it all together, here is a stored proc "sp_whassup" that will list who is currently online, what stored proc they are executing and the sql for the stored proc. This is kinda like sp_who.
CREATE PROCEDURE [dbo].[sp_whassup]
select object_name(objectid,db_id()) as proc_name, st.text, login_name,r.session_id, r.request_id,e.status, r.start_time, host_name,program_name
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions e
In my next blog I will show how to use these new views to convert a stored procedure into a "singleton", so that only one occurrence of a the stored procedure can be run at one time.