Finding what is going on now in SQL Server
Generally being a DBA, we are usually asked with one questions "what is going on" with database server whenever any performance issue or any thing happens in application.
DBA will jump in and try to find out
1. Who all are logged in
2. What all they doing
3. Since how long a session is running or what is the longest running session
4. Who is consuming more memory
5. Who is blocking who
6. If it is system task (dbcc, backup, restore etc) then how long will it run
7. What is the query being fired
8. How many reads or writes being done by a session
9. who owns which session
10.What is the hostname
I generally create a view in the maintenance database so that i don't need waste my time much in finding above parameters.
In panic situation many develop tendency to forget things and we do lot of messy things instead of finding the value in a simple.
Create the below view in any one the maintenance database and just select it whenever demand arises to see the current statistics.
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
,cast(cast(DATEDIFF(second, last_request_start_time, getdate()) as float)/60 as decimal(10,3)) 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
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
,cast(cast(DATEDIFF(second, last_request_start_time, getdate()) as float)/60 as decimal(10,3)) 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
When ever demand arises just query:
Select * from whatisgoingon
Hope it helps.
