As a DBA, it is mandatory to keep an eye on your database and server too, especially during the business hour. SQL Server providing very helpful Dynamic Management View to help in this situation. Dynamic Management View (DMV) are as follow:
sys.dm_exec_requests
sys.dm_exec_sessions
I have created one SQL Server view with the help of following script which I used to use, often, during business hours to check whether everything is going smooth or not.
[sourcecode language=”sql”]
CREATE VIEW WhatIsGoingOn
AS
SELECT
OBJECT_NAME(objectid) as ObjectName
,SUBSTRING(stateText.text, (statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(stateText.text)
ELSE statement_end_offset
END – statement_start_offset)/2) + 1) AS statement_text
,DB_Name(database_id) as DatabaseName
,req.cpu_time AS CPU_Time
,DATEDIFF(minute, last_request_start_time, getdate()) AS RunningMinutes
,req.Percent_Complete
,sess.HOST_NAME as RunningFrom
,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy
,sess.session_id AS SessionID
,req.blocking_session_id AS BlockingWith
,req.reads
,req.writes
,sess.[program_name]
,sess.login_name
,sess.status
,sess.last_request_start_time
,req.logical_reads
FROM
sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id
AND sess.is_user_process = 1
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS stateText
[/sourcecode]
Now, whenever I want to see the status whether anything is blocking to my query/queries or which query/SP is being executed from which time. How many reads/writes happening by each objects. if there is a task like backup/restore, I can even see, how many percent complete by just executing one simple SELECT statement:
[sourcecode language=”sql”]
SELECT * FROM WhatIsGoingOn
[/sourcecode]
If you wish you can, even, use this VIEW in your .NET web page/windows application so that you don’t even need to connect to database directly to see the status.
Note: Microsoft Books online is a default reference of all articles but examples and explanations
I just such as Article related to Khmer Karaoke Superstars raquo; Somnangblogs Wonderful just what I want!.
I was just seeking this information for a while. After 6 hours of continuous Googleing, at last I got it in your website. I wonder what’s the lack of Google strategy that don’t rank this type of informative websites in top of the list. Normally the top web sites are full of garbage.
I like this article.
I am not getting proper ObjectName, how do I get it?
Thanks for any other fantastic post. The place else could anybody get that kind of information in such a perfect method of writing? I’ve a presentation next week, and I am at the look for such information.
Nice post at SQL Server, sys.dm_exec_requests, sys.dm_exec_Sessions, find currently running process SQL Server database. I was checking continuously this blog and I’m impressed! Extremely useful info specially the last part :) I care for such information a lot. I was looking for this particular info for a very long time. Thank you and best of luck.