· 8 years ago · Mar 29, 2018, 03:56 PM
1IF OBJECT_ID('dbo.sp_BlitzWho') IS NULL
2 EXEC ('CREATE PROCEDURE dbo.sp_BlitzWho AS RETURN 0;')
3GO
4
5ALTER PROCEDURE dbo.sp_BlitzWho
6 @Help TINYINT = 0 ,
7 @ShowSleepingSPIDs TINYINT = 0,
8 @ExpertMode BIT = 0,
9 @Debug BIT = 0,
10 @VersionDate DATETIME = NULL OUTPUT
11AS
12BEGIN
13 SET NOCOUNT ON;
14 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
15 DECLARE @Version VARCHAR(30);
16 SET @Version = '6.2';
17 SET @VersionDate = '20180201';
18
19
20 IF @Help = 1
21 PRINT '
22sp_BlitzWho from http://FirstResponderKit.org
23
24This script gives you a snapshot of everything currently executing on your SQL Server.
25
26To learn more, visit http://FirstResponderKit.org where you can download new
27versions for free, watch training videos on how it works, get more info on
28the findings, contribute your own code, and more.
29
30Known limitations of this version:
31 - Only Microsoft-supported versions of SQL Server. Sorry, 2005 and 2000.
32
33MIT License
34
35Copyright (c) 2017 Brent Ozar Unlimited
36
37Permission is hereby granted, free of charge, to any person obtaining a copy
38of this software and associated documentation files (the "Software"), to deal
39in the Software without restriction, including without limitation the rights
40to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
41copies of the Software, and to permit persons to whom the Software is
42furnished to do so, subject to the following conditions:
43
44The above copyright notice and this permission notice shall be included in all
45copies or substantial portions of the Software.
46
47THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
48IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
49FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
50AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
51LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
52OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
53SOFTWARE.
54';
55
56/* Get the major and minor build numbers */
57DECLARE @ProductVersion NVARCHAR(128)
58 ,@ProductVersionMajor DECIMAL(10,2)
59 ,@ProductVersionMinor DECIMAL(10,2)
60 ,@EnhanceFlag BIT = 0
61 ,@StringToExecute NVARCHAR(MAX)
62 ,@EnhanceSQL NVARCHAR(MAX) =
63 N'query_stats.last_dop,
64 query_stats.min_dop,
65 query_stats.max_dop,
66 query_stats.last_grant_kb,
67 query_stats.min_grant_kb,
68 query_stats.max_grant_kb,
69 query_stats.last_used_grant_kb,
70 query_stats.min_used_grant_kb,
71 query_stats.max_used_grant_kb,
72 query_stats.last_ideal_grant_kb,
73 query_stats.min_ideal_grant_kb,
74 query_stats.max_ideal_grant_kb,
75 query_stats.last_reserved_threads,
76 query_stats.min_reserved_threads,
77 query_stats.max_reserved_threads,
78 query_stats.last_used_threads,
79 query_stats.min_used_threads,
80 query_stats.max_used_threads,'
81 ,@SessionWaits BIT = 0
82 ,@SessionWaitsSQL NVARCHAR(MAX) =
83 N'LEFT JOIN ( SELECT DISTINCT
84 wait.session_id ,
85 ( SELECT TOP 5 waitwait.wait_type + N'' (''
86 + CAST(SUM(waitwait.wait_time_ms) AS NVARCHAR(128))
87 + N'' ms), ''
88 FROM sys.dm_exec_session_wait_stats AS waitwait
89 WHERE waitwait.session_id = wait.session_id
90 GROUP BY waitwait.wait_type
91 HAVING SUM(waitwait.wait_time_ms) > 5
92 ORDER BY SUM(waitwait.wait_time_ms) DESC
93 FOR
94 XML PATH('''') ) AS session_wait_info
95 FROM sys.dm_exec_session_wait_stats AS wait ) AS wt2
96 ON s.session_id = wt2.session_id
97 LEFT JOIN sys.dm_exec_query_stats AS session_stats
98 ON r.sql_handle = session_stats.sql_handle
99 AND r.plan_handle = session_stats.plan_handle
100 AND r.statement_start_offset = session_stats.statement_start_offset
101 AND r.statement_end_offset = session_stats.statement_end_offset'
102 ,@QueryStatsXML BIT = 0
103 ,@QueryStatsXMLselect NVARCHAR(MAX) = N' CAST(COALESCE(qs_live.query_plan, ''<?No live query plan available. To turn on live plans, see https://www.BrentOzar.com/go/liveplans ?>'') AS XML) AS live_query_plan , '
104 ,@QueryStatsXMLSQL NVARCHAR(MAX) = N'OUTER APPLY sys.dm_exec_query_statistics_xml(s.session_id) qs_live'
105
106
107SET @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
108SELECT @ProductVersionMajor = SUBSTRING(@ProductVersion, 1,CHARINDEX('.', @ProductVersion) + 1 ),
109 @ProductVersionMinor = PARSENAME(CONVERT(VARCHAR(32), @ProductVersion), 2)
110IF EXISTS (SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_exec_query_statistics_xml') AND name = 'query_plan')
111 SET @QueryStatsXML = 1;
112
113
114IF @ProductVersionMajor > 9 and @ProductVersionMajor < 11
115BEGIN
116SET @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
117
118
119 DECLARE @blocked TABLE
120 (
121 dbid SMALLINT NOT NULL,
122 last_batch DATETIME NOT NULL,
123 open_tran SMALLINT NOT NULL,
124 sql_handle BINARY(20) NOT NULL,
125 session_id SMALLINT NOT NULL,
126 blocking_session_id SMALLINT NOT NULL,
127 lastwaittype NCHAR(32) NOT NULL,
128 waittime BIGINT NOT NULL,
129 cpu INT NOT NULL,
130 physical_io BIGINT NOT NULL,
131 memusage INT NOT NULL
132 );
133
134 INSERT @blocked ( dbid, last_batch, open_tran, sql_handle, session_id, blocking_session_id, lastwaittype, waittime, cpu, physical_io, memusage )
135 SELECT
136 sys1.dbid, sys1.last_batch, sys1.open_tran, sys1.sql_handle,
137 sys2.spid AS session_id, sys2.blocked AS blocking_session_id, sys2.lastwaittype, sys2.waittime, sys2.cpu, sys2.physical_io, sys2.memusage
138 FROM sys.sysprocesses AS sys1
139 JOIN sys.sysprocesses AS sys2
140 ON sys1.spid = sys2.blocked;
141
142 SELECT GETDATE() AS run_date ,
143 COALESCE(
144 CONVERT(VARCHAR(20), (ABS(r.total_elapsed_time) / 1000) / 86400) + '':'' + CONVERT(VARCHAR(20), DATEADD(SECOND, (r.total_elapsed_time / 1000), 0), 114) ,
145 CONVERT(VARCHAR(20), DATEDIFF(SECOND, s.last_request_start_time, GETDATE()) / 86400) + '':''
146 + CONVERT(VARCHAR(20), DATEADD(SECOND, DATEDIFF(SECOND, s.last_request_start_time, GETDATE()), 0), 114)
147 ) AS [elapsed_time] ,
148 s.session_id ,
149 COALESCE(DB_NAME(r.database_id), DB_NAME(blocked.dbid), ''N/A'') AS database_name,
150 ISNULL(SUBSTRING(dest.text,
151 ( query_stats.statement_start_offset / 2 ) + 1,
152 ( ( CASE query_stats.statement_end_offset
153 WHEN -1 THEN DATALENGTH(dest.text)
154 ELSE query_stats.statement_end_offset
155 END - query_stats.statement_start_offset )
156 / 2 ) + 1), dest.text) AS query_text ,
157 derp.query_plan ,
158 qmg.query_cost ,
159 s.status ,
160 COALESCE(wt.wait_info, RTRIM(blocked.lastwaittype) + '' ('' + CONVERT(VARCHAR(10),
161 blocked.waittime) + '')'' ) AS wait_info ,
162 CASE WHEN r.blocking_session_id <> 0 AND blocked.session_id IS NULL
163 THEN r.blocking_session_id
164 WHEN r.blocking_session_id <> 0 AND s.session_id <> blocked.blocking_session_id
165 THEN blocked.blocking_session_id
166 ELSE NULL
167 END AS blocking_session_id,
168 COALESCE(r.open_transaction_count, blocked.open_tran) AS open_transaction_count ,
169 s.nt_domain ,
170 s.host_name ,
171 s.login_name ,
172 s.nt_user_name ,
173 s.program_name
174 '
175
176 IF @ExpertMode = 1
177 BEGIN
178 SET @StringToExecute +=
179 N',
180 s.client_interface_name ,
181 s.login_time ,
182 r.start_time ,
183 qmg.request_time ,
184 COALESCE(r.cpu_time, s.cpu_time) AS request_cpu_time,
185 COALESCE(r.logical_reads, s.logical_reads) AS request_logical_reads,
186 COALESCE(r.writes, s.writes) AS request_writes,
187 COALESCE(r.reads, s.reads) AS request_physical_reads ,
188 s.cpu_time AS session_cpu,
189 s.logical_reads AS session_logical_reads,
190 s.reads AS session_physical_reads ,
191 s.writes AS session_writes,
192 tempdb_allocations.tempdb_allocations_mb,
193 s.memory_usage ,
194 r.estimated_completion_time ,
195 r.percent_complete ,
196 r.deadlock_priority ,
197 CASE
198 WHEN s.transaction_isolation_level = 0 THEN ''Unspecified''
199 WHEN s.transaction_isolation_level = 1 THEN ''Read Uncommitted''
200 WHEN s.transaction_isolation_level = 2 AND EXISTS (SELECT 1 FROM sys.dm_tran_active_snapshot_database_transactions AS trn WHERE s.session_id = trn.session_id AND is_snapshot = 0 ) THEN ''Read Committed Snapshot Isolation''
201 WHEN s.transaction_isolation_level = 2 AND NOT EXISTS (SELECT 1 FROM sys.dm_tran_active_snapshot_database_transactions AS trn WHERE s.session_id = trn.session_id AND is_snapshot = 0 ) THEN ''Read Committed''
202 WHEN s.transaction_isolation_level = 3 THEN ''Repeatable Read''
203 WHEN s.transaction_isolation_level = 4 THEN ''Serializable''
204 WHEN s.transaction_isolation_level = 5 THEN ''Snapshot''
205 ELSE ''WHAT HAVE YOU DONE?''
206 END AS transaction_isolation_level ,
207 qmg.dop AS degree_of_parallelism ,
208 COALESCE(CAST(qmg.grant_time AS VARCHAR(20)), ''N/A'') AS grant_time ,
209 qmg.requested_memory_kb ,
210 qmg.granted_memory_kb AS grant_memory_kb,
211 CASE WHEN qmg.grant_time IS NULL THEN ''N/A''
212 WHEN qmg.requested_memory_kb < qmg.granted_memory_kb
213 THEN ''Query Granted Less Than Query Requested''
214 ELSE ''Memory Request Granted''
215 END AS is_request_granted ,
216 qmg.required_memory_kb ,
217 qmg.used_memory_kb AS query_memory_grant_used_memory_kb,
218 qmg.ideal_memory_kb ,
219 qmg.is_small ,
220 qmg.timeout_sec ,
221 qmg.resource_semaphore_id ,
222 COALESCE(CAST(qmg.wait_order AS VARCHAR(20)), ''N/A'') AS wait_order ,
223 COALESCE(CAST(qmg.wait_time_ms AS VARCHAR(20)),
224 ''N/A'') AS wait_time_ms ,
225 CASE qmg.is_next_candidate
226 WHEN 0 THEN ''No''
227 WHEN 1 THEN ''Yes''
228 ELSE ''N/A''
229 END AS next_candidate_for_memory_grant ,
230 qrs.target_memory_kb ,
231 COALESCE(CAST(qrs.max_target_memory_kb AS VARCHAR(20)),
232 ''Small Query Resource Semaphore'') AS max_target_memory_kb ,
233 qrs.total_memory_kb ,
234 qrs.available_memory_kb ,
235 qrs.granted_memory_kb ,
236 qrs.used_memory_kb AS query_resource_semaphore_used_memory_kb,
237 qrs.grantee_count ,
238 qrs.waiter_count ,
239 qrs.timeout_error_count ,
240 COALESCE(CAST(qrs.forced_grant_count AS VARCHAR(20)),
241 ''Small Query Resource Semaphore'') AS forced_grant_count,
242 wg.name AS workload_group_name ,
243 rp.name AS resource_pool_name,
244 CONVERT(VARCHAR(128), r.context_info) AS context_info
245 '
246 END
247
248 SET @StringToExecute +=
249 N'FROM sys.dm_exec_sessions AS s
250 LEFT JOIN sys.dm_exec_requests AS r
251 ON r.session_id = s.session_id
252 LEFT JOIN ( SELECT DISTINCT
253 wait.session_id ,
254 ( SELECT waitwait.wait_type + N'' (''
255 + CAST(SUM(waitwait.wait_duration_ms) AS NVARCHAR(128))
256 + N'' ms) ''
257 FROM sys.dm_os_waiting_tasks AS waitwait
258 WHERE waitwait.session_id = wait.session_id
259 GROUP BY waitwait.wait_type
260 ORDER BY SUM(waitwait.wait_duration_ms) DESC
261 FOR
262 XML PATH('''') ) AS wait_info
263 FROM sys.dm_os_waiting_tasks AS wait ) AS wt
264 ON s.session_id = wt.session_id
265 LEFT JOIN sys.dm_exec_query_stats AS query_stats
266 ON r.sql_handle = query_stats.sql_handle
267 AND r.plan_handle = query_stats.plan_handle
268 AND r.statement_start_offset = query_stats.statement_start_offset
269 AND r.statement_end_offset = query_stats.statement_end_offset
270 LEFT JOIN sys.dm_exec_query_memory_grants qmg
271 ON r.session_id = qmg.session_id
272 AND r.request_id = qmg.request_id
273 LEFT JOIN sys.dm_exec_query_resource_semaphores qrs
274 ON qmg.resource_semaphore_id = qrs.resource_semaphore_id
275 AND qmg.pool_id = qrs.pool_id
276 LEFT JOIN sys.resource_governor_workload_groups wg
277 ON s.group_id = wg.group_id
278 LEFT JOIN sys.resource_governor_resource_pools rp
279 ON wg.pool_id = rp.pool_id
280 OUTER APPLY (
281 SELECT TOP 1
282 b.dbid, b.last_batch, b.open_tran, b.sql_handle,
283 b.session_id, b.blocking_session_id, b.lastwaittype, b.waittime
284 FROM @blocked b
285 WHERE (s.session_id = b.session_id
286 OR s.session_id = b.blocking_session_id)
287 ) AS blocked
288 OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, blocked.sql_handle)) AS dest
289 OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS derp
290 OUTER APPLY (
291 SELECT CONVERT(DECIMAL(38,2), SUM( (((tsu.user_objects_alloc_page_count - user_objects_dealloc_page_count) * 8) / 1024.)) ) AS tempdb_allocations_mb
292 FROM sys.dm_db_task_space_usage tsu
293 WHERE tsu.request_id = r.request_id
294 AND tsu.session_id = r.session_id
295 AND tsu.session_id = s.session_id
296 ) as tempdb_allocations
297 WHERE s.session_id <> @@SPID
298 AND s.host_name IS NOT NULL
299 '
300 + CASE WHEN @ShowSleepingSPIDs = 0 THEN
301 N' AND COALESCE(DB_NAME(r.database_id), DB_NAME(blocked.dbid)) IS NOT NULL'
302 WHEN @ShowSleepingSPIDs = 1 THEN
303 N' OR COALESCE(r.open_transaction_count, blocked.open_tran) >= 1'
304 ELSE N'' END
305 +
306 ' ORDER BY 2 DESC;
307 '
308END
309IF @ProductVersionMajor >= 11
310BEGIN
311SELECT @EnhanceFlag =
312 CASE WHEN @ProductVersionMajor = 11 AND @ProductVersionMinor >= 6020 THEN 1
313 WHEN @ProductVersionMajor = 12 AND @ProductVersionMinor >= 5000 THEN 1
314 WHEN @ProductVersionMajor = 13 AND @ProductVersionMinor >= 1601 THEN 1
315 ELSE 0
316 END
317
318
319IF OBJECT_ID('sys.dm_exec_session_wait_stats') IS NOT NULL
320BEGIN
321 SET @SessionWaits = 1
322END
323
324
325
326SELECT @StringToExecute = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
327
328 DECLARE @blocked TABLE
329 (
330 dbid SMALLINT NOT NULL,
331 last_batch DATETIME NOT NULL,
332 open_tran SMALLINT NOT NULL,
333 sql_handle BINARY(20) NOT NULL,
334 session_id SMALLINT NOT NULL,
335 blocking_session_id SMALLINT NOT NULL,
336 lastwaittype NCHAR(32) NOT NULL,
337 waittime BIGINT NOT NULL,
338 cpu INT NOT NULL,
339 physical_io BIGINT NOT NULL,
340 memusage INT NOT NULL
341 );
342
343 INSERT @blocked ( dbid, last_batch, open_tran, sql_handle, session_id, blocking_session_id, lastwaittype, waittime, cpu, physical_io, memusage )
344 SELECT
345 sys1.dbid, sys1.last_batch, sys1.open_tran, sys1.sql_handle,
346 sys2.spid AS session_id, sys2.blocked AS blocking_session_id, sys2.lastwaittype, sys2.waittime, sys2.cpu, sys2.physical_io, sys2.memusage
347 FROM sys.sysprocesses AS sys1
348 JOIN sys.sysprocesses AS sys2
349 ON sys1.spid = sys2.blocked;
350
351 SELECT GETDATE() AS run_date ,
352 COALESCE(
353 CONVERT(VARCHAR(20), (ABS(r.total_elapsed_time) / 1000) / 86400) + '':'' + CONVERT(VARCHAR(20), DATEADD(SECOND, (r.total_elapsed_time / 1000), 0), 114) ,
354 CONVERT(VARCHAR(20), DATEDIFF(SECOND, s.last_request_start_time, GETDATE()) / 86400) + '':''
355 + CONVERT(VARCHAR(20), DATEADD(SECOND, DATEDIFF(SECOND, s.last_request_start_time, GETDATE()), 0), 114)
356 ) AS [elapsed_time] ,
357 s.session_id ,
358 COALESCE(DB_NAME(r.database_id), DB_NAME(blocked.dbid), ''N/A'') AS database_name,
359 ISNULL(SUBSTRING(dest.text,
360 ( query_stats.statement_start_offset / 2 ) + 1,
361 ( ( CASE query_stats.statement_end_offset
362 WHEN -1 THEN DATALENGTH(dest.text)
363 ELSE query_stats.statement_end_offset
364 END - query_stats.statement_start_offset )
365 / 2 ) + 1), dest.text) AS query_text ,
366 derp.query_plan ,'
367 +
368 CASE @QueryStatsXML
369 WHEN 1 THEN + @QueryStatsXMLselect
370 ELSE N''
371 END
372 +'
373 qmg.query_cost ,
374 s.status ,
375 COALESCE(wt.wait_info, RTRIM(blocked.lastwaittype) + '' ('' + CONVERT(VARCHAR(10), blocked.waittime) + '')'' ) AS wait_info ,'
376 +
377 CASE @SessionWaits
378 WHEN 1 THEN + N'SUBSTRING(wt2.session_wait_info, 0, LEN(wt2.session_wait_info) ) AS top_session_waits ,'
379 ELSE N''
380 END
381 +
382 N'CASE WHEN r.blocking_session_id <> 0 AND blocked.session_id IS NULL
383 THEN r.blocking_session_id
384 WHEN r.blocking_session_id <> 0 AND s.session_id <> blocked.blocking_session_id
385 THEN blocked.blocking_session_id
386 ELSE NULL
387 END AS blocking_session_id,
388 COALESCE(r.open_transaction_count, blocked.open_tran) AS open_transaction_count ,
389 s.nt_domain ,
390 s.host_name ,
391 s.login_name ,
392 s.nt_user_name ,
393 s.program_name
394 '
395 IF @ExpertMode = 1
396 BEGIN
397 SET @StringToExecute +=
398 N',
399 s.client_interface_name ,
400 s.login_time ,
401 r.start_time ,
402 qmg.request_time ,
403 COALESCE(r.cpu_time, s.cpu_time) AS request_cpu_time,
404 COALESCE(r.logical_reads, s.logical_reads) AS request_logical_reads,
405 COALESCE(r.writes, s.writes) AS request_writes,
406 COALESCE(r.reads, s.reads) AS request_physical_reads ,
407 s.cpu_time AS session_cpu,
408 s.logical_reads AS session_logical_reads,
409 s.reads AS session_physical_reads ,
410 s.writes AS session_writes,
411 tempdb_allocations.tempdb_allocations_mb,
412 s.memory_usage ,
413 r.estimated_completion_time ,
414 r.percent_complete ,
415 r.deadlock_priority ,
416 CASE
417 WHEN s.transaction_isolation_level = 0 THEN ''Unspecified''
418 WHEN s.transaction_isolation_level = 1 THEN ''Read Uncommitted''
419 WHEN s.transaction_isolation_level = 2 AND EXISTS (SELECT 1 FROM sys.dm_tran_active_snapshot_database_transactions AS trn WHERE s.session_id = trn.session_id AND is_snapshot = 0 ) THEN ''Read Committed Snapshot Isolation''
420 WHEN s.transaction_isolation_level = 2 AND NOT EXISTS (SELECT 1 FROM sys.dm_tran_active_snapshot_database_transactions AS trn WHERE s.session_id = trn.session_id AND is_snapshot = 0 ) THEN ''Read Committed''
421 WHEN s.transaction_isolation_level = 3 THEN ''Repeatable Read''
422 WHEN s.transaction_isolation_level = 4 THEN ''Serializable''
423 WHEN s.transaction_isolation_level = 5 THEN ''Snapshot''
424 ELSE ''WHAT HAVE YOU DONE?''
425 END AS transaction_isolation_level ,
426 qmg.dop AS degree_of_parallelism , '
427 +
428 CASE @EnhanceFlag
429 WHEN 1 THEN @EnhanceSQL
430 ELSE N''
431 END
432 +
433 N'
434 COALESCE(CAST(qmg.grant_time AS VARCHAR(20)), ''Memory Not Granted'') AS grant_time ,
435 qmg.requested_memory_kb ,
436 qmg.granted_memory_kb AS grant_memory_kb,
437 CASE WHEN qmg.grant_time IS NULL THEN ''N/A''
438 WHEN qmg.requested_memory_kb < qmg.granted_memory_kb
439 THEN ''Query Granted Less Than Query Requested''
440 ELSE ''Memory Request Granted''
441 END AS is_request_granted ,
442 qmg.required_memory_kb ,
443 qmg.used_memory_kb AS query_memory_grant_used_memory_kb,
444 qmg.ideal_memory_kb ,
445 qmg.is_small ,
446 qmg.timeout_sec ,
447 qmg.resource_semaphore_id ,
448 COALESCE(CAST(qmg.wait_order AS VARCHAR(20)), ''N/A'') AS wait_order ,
449 COALESCE(CAST(qmg.wait_time_ms AS VARCHAR(20)),
450 ''N/A'') AS wait_time_ms ,
451 CASE qmg.is_next_candidate
452 WHEN 0 THEN ''No''
453 WHEN 1 THEN ''Yes''
454 ELSE ''N/A''
455 END AS next_candidate_for_memory_grant ,
456 qrs.target_memory_kb ,
457 COALESCE(CAST(qrs.max_target_memory_kb AS VARCHAR(20)),
458 ''Small Query Resource Semaphore'') AS max_target_memory_kb ,
459 qrs.total_memory_kb ,
460 qrs.available_memory_kb ,
461 qrs.granted_memory_kb ,
462 qrs.used_memory_kb AS query_resource_semaphore_used_memory_kb,
463 qrs.grantee_count ,
464 qrs.waiter_count ,
465 qrs.timeout_error_count ,
466 COALESCE(CAST(qrs.forced_grant_count AS VARCHAR(20)),
467 ''Small Query Resource Semaphore'') AS forced_grant_count,
468 wg.name AS workload_group_name,
469 rp.name AS resource_pool_name,
470 CONVERT(VARCHAR(128), r.context_info) AS context_info
471 '
472 END
473
474 SET @StringToExecute +=
475 N'FROM sys.dm_exec_sessions AS s
476 LEFT JOIN sys.dm_exec_requests AS r
477 ON r.session_id = s.session_id
478 LEFT JOIN ( SELECT DISTINCT
479 wait.session_id ,
480 ( SELECT waitwait.wait_type + N'' (''
481 + CAST(SUM(waitwait.wait_duration_ms) AS NVARCHAR(128))
482 + N'' ms) ''
483 FROM sys.dm_os_waiting_tasks AS waitwait
484 WHERE waitwait.session_id = wait.session_id
485 GROUP BY waitwait.wait_type
486 ORDER BY SUM(waitwait.wait_duration_ms) DESC
487 FOR
488 XML PATH('''') ) AS wait_info
489 FROM sys.dm_os_waiting_tasks AS wait ) AS wt
490 ON s.session_id = wt.session_id
491 LEFT JOIN sys.dm_exec_query_stats AS query_stats
492 ON r.sql_handle = query_stats.sql_handle
493 AND r.plan_handle = query_stats.plan_handle
494 AND r.statement_start_offset = query_stats.statement_start_offset
495 AND r.statement_end_offset = query_stats.statement_end_offset
496 '
497 +
498 CASE @SessionWaits
499 WHEN 1 THEN @SessionWaitsSQL
500 ELSE N''
501 END
502 +
503 '
504 LEFT JOIN sys.dm_exec_query_memory_grants qmg
505 ON r.session_id = qmg.session_id
506 AND r.request_id = qmg.request_id
507 LEFT JOIN sys.dm_exec_query_resource_semaphores qrs
508 ON qmg.resource_semaphore_id = qrs.resource_semaphore_id
509 AND qmg.pool_id = qrs.pool_id
510 LEFT JOIN sys.resource_governor_workload_groups wg
511 ON s.group_id = wg.group_id
512 LEFT JOIN sys.resource_governor_resource_pools rp
513 ON wg.pool_id = rp.pool_id
514 OUTER APPLY (
515 SELECT TOP 1
516 b.dbid, b.last_batch, b.open_tran, b.sql_handle,
517 b.session_id, b.blocking_session_id, b.lastwaittype, b.waittime
518 FROM @blocked b
519 WHERE (s.session_id = b.session_id
520 OR s.session_id = b.blocking_session_id)
521 ) AS blocked
522 OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, blocked.sql_handle)) AS dest
523 OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS derp
524 OUTER APPLY (
525 SELECT CONVERT(DECIMAL(38,2), SUM( (((tsu.user_objects_alloc_page_count - user_objects_dealloc_page_count) * 8) / 1024.)) ) AS tempdb_allocations_mb
526 FROM sys.dm_db_task_space_usage tsu
527 WHERE tsu.request_id = r.request_id
528 AND tsu.session_id = r.session_id
529 AND tsu.session_id = s.session_id
530 ) as tempdb_allocations
531 '
532 +
533 CASE @QueryStatsXML
534 WHEN 1 THEN @QueryStatsXMLSQL
535 ELSE N''
536 END
537 +
538 '
539 WHERE s.session_id <> @@SPID
540 AND s.host_name IS NOT NULL
541 '
542 + CASE WHEN @ShowSleepingSPIDs = 0 THEN
543 N' AND COALESCE(DB_NAME(r.database_id), DB_NAME(blocked.dbid)) IS NOT NULL'
544 WHEN @ShowSleepingSPIDs = 1 THEN
545 N' OR COALESCE(r.open_transaction_count, blocked.open_tran) >= 1'
546 ELSE N'' END
547 +
548 ' ORDER BY 2 DESC;
549 '
550
551END
552
553IF @Debug = 1
554 BEGIN
555 PRINT CONVERT(VARCHAR(8000), SUBSTRING(@StringToExecute, 0, 8000))
556 PRINT CONVERT(VARCHAR(8000), SUBSTRING(@StringToExecute, 8000, 160000))
557 END
558
559EXEC(@StringToExecute);
560
561END
562GO