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:
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