Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need long running queries from dynamic views Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 12:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:55 AM
Points: 54, Visits: 555
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
Post #1409381
Posted Monday, January 21, 2013 12:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
-- 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
Post #1409383
Posted Monday, January 21, 2013 1:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:55 AM
Points: 54, Visits: 555
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.
Post #1409390
Posted Monday, January 21, 2013 1:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
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


  Post Attachments 
query.txt (2 views, 2.00 KB)
Post #1409398
Posted Monday, January 21, 2013 1:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:55 AM
Points: 54, Visits: 555
Hi Team,

Thanks a lot. The above script is working fine...


Thanks a lot..
Post #1409415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse