-
Notifications
You must be signed in to change notification settings - Fork 27
/
8-Whats Running Script
118 lines (118 loc) · 4 KB
/
8-Whats Running Script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
--select getdate()
go
select @@servername
go
--======================================================
SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID) -- Ignore this current statement.
ORDER BY 1, 2
--==============================================================================
--https://www.sqlservercentral.com/Forums/Topic619606-360-1.aspx
SELECT
r.session_id
, r.start_time
, TotalElapsedTime_ms = r.total_elapsed_time
, r.[status]
, r.command
, DatabaseName = DB_Name(r.database_id)
, r.wait_type
, r.last_wait_type
, r.wait_resource
, r.cpu_time
, r.reads
, r.writes
, r.logical_reads
, t.[text] AS [executing batch]
, SUBSTRING(
t.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2
) AS [executing statement]
, p.query_plan
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY
sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY
r.total_elapsed_time DESC;
==================================================================================================
--Query to find long running transaction and high memory consumed process
--https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2865d2d4-9d75-4d0e-86f6-08495178a71b/query-to-find-long-running-transaction-and-high-memory-consumed-process?forum=transactsql
SELECT
r.session_id
, r.start_time
, TotalElapsedTime_ms = r.total_elapsed_time
, r.[status]
, r.command
, DatabaseName = DB_Name(r.database_id)
, r.wait_type
, r.last_wait_type
, r.wait_resource
, r.cpu_time
, r.reads
, r.writes
, r.logical_reads
, t.[text] AS [executing batch]
, SUBSTRING(
t.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2
) AS [executing statement]
, p.query_plan
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY
sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY
r.total_elapsed_time DESC;
===================================================================
SELECT r.session_id ,
(Select Top 1 CON.client_net_address From sys.dm_exec_connections AS CON
where CON.session_id = r.session_id) Client_Address,
OBJECT_NAME(qt.objectid, qt.dbid),
r.[status] ,
r.wait_type ,
r.scheduler_id ,
SUBSTRING(qt.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2) AS [statement_executing] ,
DB_NAME(qt.[dbid]) AS [DatabaseName] ,
Object_NAME(qt.objectid) AS [ObjectName] ,
r.cpu_time ,
r.total_elapsed_time ,
r.reads ,
r.writes ,
r.logical_reads ,
r.plan_handle
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
===========================================================================