Technorati tags:

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.

View/function Type Description
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]
AS
BEGIN
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
on e.session_id=r.session_id
cross apply
sys.dm_exec_sql_text(sql_handle) st
END

 

What's Next?

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.