January 21, 2013 at 12:52 am
Hi Friends,
Is there a query to get long running queries from dynamic views in sql server 2008 R2. The O/P should contain the following:
Login name, Statement executed, CPU, READS, Writes
Thanks in advance
January 21, 2013 at 12:56 am
-- Execute the query inside target database
SELECT TOP 10
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE
qt.dbid = DB_ID()
ORDER BY
average_seconds DESC;
AND
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 21, 2013 at 1:05 am
HI Team,
From the above query am getting the queries hitting in that DB. I need from which sql login that particular statement is running.
Thanks in advance.
January 21, 2013 at 1:28 am
rajkiran.panchagiri (1/21/2013)
I need from which sql login that particular statement is running.
TRy this SELECT
‘SESSION_ID: ‘ + CAST(es.[session_id] AS VARCHAR(4)) + ‘ ‘ +
‘ HOST_NAME: ‘ + es.[host_name] + ‘ ‘ +
‘ PROGRAM_NAME: ‘ + es.[program_name], ‘ ‘ + CHAR(13) + CHAR(10),
‘ LOGIN_NAME: ‘ + es.[login_name] + ‘ ‘ +
‘ PROCESS_OWNER: ‘ + CASE es.[is_user_process]
WHEN 1 THEN ‘User’
ELSE ‘System’ END, ‘ ‘ + CHAR(13) + CHAR(10),
‘ TRANSACTION_START_TIME: ‘ + CAST(tat.[transaction_begin_time] AS VARCHAR) + ‘ ‘ +
‘ LAST_READ_TIME: ‘ + CAST(ec.[last_read] AS VARCHAR) + ‘ ‘ +
‘ LAST_WRITE_TIME: ‘ + CAST(ec.[last_write] AS VARCHAR) + ‘ ‘ +
‘ SESSION_STATUS: ‘ + es.[status], ‘ ‘ + CHAR(13) + CHAR(10),
‘ TRANSACTION_STATE: ‘ + CASE tat.[transaction_state]
WHEN 0 THEN ‘The transaction has not been completely initialized yet.’
WHEN 1 THEN ‘The transaction has been initialized but has not started.’
WHEN 2 THEN ‘The transaction is active.’
WHEN 3 THEN ‘The transaction has ended. This is used for read-only transactions.’
WHEN 4 THEN ‘The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.’
WHEN 5 THEN ‘The transaction is in a prepared state and waiting resolution.’
WHEN 6 THEN ‘The transaction has been committed.’
WHEN 7 THEN ‘The transaction is being rolled back.’
WHEN 8 THEN ‘The transaction has been rolled back.’ END + ‘ ‘ +
‘ TRANSACTION_TYPE: ‘ + CASE CAST(tat.[transaction_type] AS VARCHAR)
WHEN ’1' THEN ‘Read/Write’
WHEN ’2' THEN ‘Read-only’
WHEN ’3' THEN ‘System’ END , ‘ ‘ + CHAR(13) + CHAR(10),
‘ SQL_TEXT: ‘ + est.text, ‘ ‘ + CHAR(13) + CHAR(10) + ‘ ‘ + CHAR(13) + CHAR(10)
FROM sys.dm_tran_active_transactions tat
INNER JOIN sys.dm_tran_session_transactions tst
ON tst.transaction_id = tat.transaction_id
INNER JOIN sys.dm_exec_sessions es
ON es.session_id = tst.session_id
INNER JOIN sys.dm_exec_connections ec
ON ec.session_id = es.session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) est
ORDER BY tat.[transaction_begin_time] ASC
here you need to replace inverted comma in proper way
and see attachment too .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 21, 2013 at 1:48 am
Hi Team,
Thanks a lot. The above script is working fine...
Thanks a lot..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply