Whassup with my SQL Server
Technorati tags: SQL
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.