Running queries on SQL Server

Mấu chốt ở đây là DMV sys.dm_exec_request

The script to find the running queries in SQL Server. We can answer the following questions:

Which database is supposed to handle the request?
Which login is executing the request?
From which computer has the request arrived?
From which application has the request been initiated?
When did the request arrive?
What SQL statements are being executed in the request?
What is the execution plan for the SQL statements?
What is the time duration since the request has been running?
Did the request open any transaction?

SELECT      r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            SUBSTRING(t.text,(r.statement_start_offset/2)+1,
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0 
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1 
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL], 
            Status,command,wait_type,wait_time,wait_resource, 
            last_wait_type
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id != @@SPID -- don't show this query
AND         session_id > 50 -- don't show system queries
ORDER BY    r.start_time

Một câu khác cho kết quả tương tự, nhưng nhiều column hơn:

SELECT
DB_NAME(R.database_id) AS DatabaseName
,S.original_login_name AS LoginName
,S.host_name AS ClientMachine
,S.program_name AS ApplicationName
,R.start_time AS RequestStartTime
,ST.text AS SQLQuery
,QP.query_plan AS ExecutionPlan
,R.cpu_time AS CPUTime
,R.total_elapsed_time AS TotalTimeElapsed
,R.open_transaction_count AS TotalTransactionsOpened
,R.reads
,R.logical_reads
,R.writes AS TotalWrites
,CASE
WHEN R.wait_type IS NULL THEN 'Request Not Blocked'
ELSE 'Request Blocked'
END AS QueryBlockInfo
,blocking_session_id AS RequestBlockedBy
FROM sys.dm_exec_requests AS R
INNER JOIN sys.dm_exec_sessions AS S
ON R.session_id = S.session_id
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST
CROSS APPLY sys.dm_exec_query_plan (R.plan_handle) AS QP
ORDER BY TotalTimeElapsed DESC
GO

Tìm những câu truy vấn đang chạy trên database nào, dựa vào Physical Name ta có thể biết file database, hay file log đang nằm ở ổ đĩa nào, từ đó chẩn đoán ổ cứng có bị thắt cổ chai hay không

SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending 
,r.io_pending_ms_ticks , r.io_type , fs.num_of_reads
,fs.num_of_writes FROM sys.dm_io_pending_io_requests AS r 
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
AND fs.file_id = mf.file_id ORDER BY r.io_pending, r.io_pending_ms_ticks DESC ;

Tham khảo:

Running queries on SQL Server

0 comments:

Post a Comment