Whassup with my SQL Server


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.


Comments (1)
  1. ghozan says:

    Hi, this might be not related to your post here, but I don’t know where to put a question. So I’ll just ask here, hope that’s ok with you. I have put a string (the string itself is a create trigger function) in a temporary table. Now I want to execute what’s in that temporary table. I have tried this:

    EXEC(SELECT qry FROM Temp_Table), but failed because it said :

    Msg 156, Level 15, State 1, Line 123

    Incorrect syntax near the keyword ‘SELECT’.

    Msg 102, Level 15, State 1, Line 123

    Incorrect syntax near ‘)’.

    Can you tell me how to do this? The reason I use this way is because if I put it in a variable with varchar type it only allows me to put 8000 characters, whilst the create trigger statement I’ve put in the temp table is much longer than that. Thank you in advance. Hopefully there is an answer to it.

    Regards,

    Ghozan

Comments are closed.

Skip to main content