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.
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
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:
SELECT * FROM WhatIsGoingOn
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?
Pingback: sys.dm_exec_requests and sys.dm_exec_sessions to find who are logged in to your SQL Server
Pingback: Find Blocking in SQL Server and use it to send an alert
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.
Pingback: Performance Counter in SQL Server
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.
Pingback: Capture long running query in SQL Server
Pingback: Monitor user login connected to SQL Server