We have this great Website about the Stored Procedure : sp_whoisactive - Version 11.30 - December 10, 2017
This version does NOT create a stored procedure, it just run in an ad hoc manner.
1
2-- http://whoisactive.com
3
4SET QUOTED_IDENTIFIER ON;
5SET ANSI_PADDING ON;
6SET CONCAT_NULL_YIELDS_NULL ON;
7SET ANSI_WARNINGS ON;
8SET NUMERIC_ROUNDABORT OFF;
9SET ARITHABORT ON;
10GO
11
12DECLARE
13 --~
14 --Filters--Both inclusive and exclusive
15 --Set either filter to '' to disable
16 --Valid filter types are: session, program, database, login, and host
17 --Session is a session ID, and either 0 or '' can be used to indicate "all" sessions
18 --All other filter types support % or _ as wildcards
19 @filter sysname = ''
20 , @filter_type VARCHAR(10) = 'session'
21 , @not_filter sysname = ''
22 , @not_filter_type VARCHAR(10) = 'session'
23 ,
24
25 --Retrieve data about the calling session?
26 @show_own_spid BIT = 0
27 ,
28
29 --Retrieve data about system sessions?
30 @show_system_spids BIT = 0
31 ,
32
33 --Controls how sleeping SPIDs are handled, based on the idea of levels of interest
34 --0 does not pull any sleeping SPIDs
35 --1 pulls only those sleeping SPIDs that also have an open transaction
36 --2 pulls all sleeping SPIDs
37 @show_sleeping_spids TINYINT = 1
38 ,
39
40 --If 1, gets the full stored procedure or running batch, when available
41 --If 0, gets only the actual statement that is currently running in the batch or procedure
42 @get_full_inner_text BIT = 0
43 ,
44
45 --Get associated query plans for running tasks, if available
46 --If @get_plans = 1, gets the plan based on the request's statement offset
47 --If @get_plans = 2, gets the entire plan based on the request's plan_handle
48 @get_plans TINYINT = 0
49 ,
50
51 --Get the associated outer ad hoc query or stored procedure call, if available
52 @get_outer_command BIT = 0
53 ,
54
55 --Enables pulling transaction log write info and transaction duration
56 @get_transaction_info BIT = 0
57 ,
58
59 --Get information on active tasks, based on three interest levels
60 --Level 0 does not pull any task-related information
61 --Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers
62 --Level 2 pulls all available task-based metrics, including:
63 --number of active tasks, current wait stats, physical I/O, context switches, and blocker information
64 @get_task_info TINYINT = 1
65 ,
66
67 --Gets associated locks for each request, aggregated in an XML format
68 @get_locks BIT = 0
69 ,
70
71 --Get average time for past runs of an active query
72 --(based on the combination of plan handle, sql handle, and offset)
73 @get_avg_time BIT = 0
74 ,
75
76 --Get additional non-performance-related information about the session or request
77 --text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on,
78 --ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null,
79 --transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type
80 --
81 --If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of
82 --the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error)
83 --
84 --If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be
85 --populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id,
86 --applock_hash, metadata_resource, metadata_class_id, object_name, schema_name
87 @get_additional_info BIT = 0
88 ,
89
90 --Walk the blocking chain and count the number of
91 --total SPIDs blocked all the way down by a given session
92 --Also enables task_info Level 1, if @get_task_info is set to 0
93 @find_block_leaders BIT = 0
94 ,
95
96 --Pull deltas on various metrics
97 --Interval in seconds to wait before doing the second data pull
98 @delta_interval TINYINT = 0
99 ,
100
101 --List of desired output columns, in desired order
102 --Note that the final output will be the intersection of all enabled features and all
103 --columns in the list. Therefore, only columns associated with enabled features will
104 --actually appear in the output. Likewise, removing columns from this list may effectively
105 --disable features, even if they are turned on
106 --
107 --Each element in this list must be one of the valid output column names. Names must be
108 --delimited by square brackets. White space, formatting, and additional characters are
109 --allowed, as long as the list contains exact matches of delimited valid column names.
110 @output_column_list VARCHAR(8000) = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]'
111 ,
112
113 --Column(s) by which to sort output, optionally with sort directions.
114 --Valid column choices:
115 --session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,
116 --tempdb_current, CPU, context_switches, used_memory, physical_io_delta, reads_delta,
117 --physical_reads_delta, writes_delta, tempdb_allocations_delta, tempdb_current_delta,
118 --CPU_delta, context_switches_delta, used_memory_delta, tasks, tran_start_time,
119 --open_tran_count, blocking_session_id, blocked_session_count, percent_complete,
120 --host_name, login_name, database_name, start_time, login_time, program_name
121 --
122 --Note that column names in the list must be bracket-delimited. Commas and/or white
123 --space are not required.
124 @sort_order VARCHAR(500) = '[start_time] ASC'
125 ,
126
127 --Formats some of the output columns in a more "human readable" form
128 --0 disables outfput format
129 --1 formats the output for variable-width fonts
130 --2 formats the output for fixed-width fonts
131 @format_output TINYINT = 1
132 ,
133
134 --If set to a non-blank value, the script will attempt to insert into the specified
135 --destination table. Please note that the script will not verify that the table exists,
136 --or that it has the correct schema, before doing the insert.
137 --Table can be specified in one, two, or three-part format
138 @destination_table VARCHAR(4000) = ''
139 ,
140
141 --If set to 1, no data collection will happen and no result set will be returned; instead,
142 --a CREATE TABLE statement will be returned via the @schema parameter, which will match
143 --the schema of the result set that would be returned by using the same collection of the
144 --rest of the parameters. The CREATE TABLE statement will have a placeholder token of
145 --<table_name> in place of an actual table name.
146 @return_schema BIT = 0
147 , @schema VARCHAR(MAX) = NULL
148 ,
149
150 --Help! What do I do?
151 @help BIT = 0
152--~
153
154/*
155OUTPUT COLUMNS
156--------------
157Formatted/Non: [session_id] [smallint] NOT NULL
158Session ID (a.k.a. SPID)
159
160Formatted: [dd hh:mm:ss.mss] [varchar](15) NULL
161Non-Formatted: <not returned>
162For an active request, time the query has been running
163For a sleeping session, time since the last batch completed
164
165Formatted: [dd hh:mm:ss.mss (avg)] [varchar](15) NULL
166Non-Formatted: [avg_elapsed_time] [int] NULL
167(Requires @get_avg_time option)
168How much time has the active portion of the query taken in the past, on average?
169
170Formatted: [physical_io] [varchar](30) NULL
171Non-Formatted: [physical_io] [bigint] NULL
172Shows the number of physical I/Os, for active requests
173
174Formatted: [reads] [varchar](30) NULL
175Non-Formatted: [reads] [bigint] NULL
176For an active request, number of reads done for the current query
177For a sleeping session, total number of reads done over the lifetime of the session
178
179Formatted: [physical_reads] [varchar](30) NULL
180Non-Formatted: [physical_reads] [bigint] NULL
181For an active request, number of physical reads done for the current query
182For a sleeping session, total number of physical reads done over the lifetime of the session
183
184Formatted: [writes] [varchar](30) NULL
185Non-Formatted: [writes] [bigint] NULL
186For an active request, number of writes done for the current query
187For a sleeping session, total number of writes done over the lifetime of the session
188
189Formatted: [tempdb_allocations] [varchar](30) NULL
190Non-Formatted: [tempdb_allocations] [bigint] NULL
191For an active request, number of TempDB writes done for the current query
192For a sleeping session, total number of TempDB writes done over the lifetime of the session
193
194Formatted: [tempdb_current] [varchar](30) NULL
195Non-Formatted: [tempdb_current] [bigint] NULL
196For an active request, number of TempDB pages currently allocated for the query
197For a sleeping session, number of TempDB pages currently allocated for the session
198
199Formatted: [CPU] [varchar](30) NULL
200Non-Formatted: [CPU] [int] NULL
201For an active request, total CPU time consumed by the current query
202For a sleeping session, total CPU time consumed over the lifetime of the session
203
204Formatted: [context_switches] [varchar](30) NULL
205Non-Formatted: [context_switches] [bigint] NULL
206Shows the number of context switches, for active requests
207
208Formatted: [used_memory] [varchar](30) NOT NULL
209Non-Formatted: [used_memory] [bigint] NOT NULL
210For an active request, total memory consumption for the current query
211For a sleeping session, total current memory consumption
212
213Formatted: [physical_io_delta] [varchar](30) NULL
214Non-Formatted: [physical_io_delta] [bigint] NULL
215(Requires @delta_interval option)
216Difference between the number of physical I/Os reported on the first and second collections.
217If the request started after the first collection, the value will be NULL
218
219Formatted: [reads_delta] [varchar](30) NULL
220Non-Formatted: [reads_delta] [bigint] NULL
221(Requires @delta_interval option)
222Difference between the number of reads reported on the first and second collections.
223If the request started after the first collection, the value will be NULL
224
225Formatted: [physical_reads_delta] [varchar](30) NULL
226Non-Formatted: [physical_reads_delta] [bigint] NULL
227(Requires @delta_interval option)
228Difference between the number of physical reads reported on the first and second collections.
229If the request started after the first collection, the value will be NULL
230
231Formatted: [writes_delta] [varchar](30) NULL
232Non-Formatted: [writes_delta] [bigint] NULL
233(Requires @delta_interval option)
234Difference between the number of writes reported on the first and second collections.
235If the request started after the first collection, the value will be NULL
236
237Formatted: [tempdb_allocations_delta] [varchar](30) NULL
238Non-Formatted: [tempdb_allocations_delta] [bigint] NULL
239(Requires @delta_interval option)
240Difference between the number of TempDB writes reported on the first and second collections.
241If the request started after the first collection, the value will be NULL
242
243Formatted: [tempdb_current_delta] [varchar](30) NULL
244Non-Formatted: [tempdb_current_delta] [bigint] NULL
245(Requires @delta_interval option)
246Difference between the number of allocated TempDB pages reported on the first and second
247collections. If the request started after the first collection, the value will be NULL
248
249Formatted: [CPU_delta] [varchar](30) NULL
250Non-Formatted: [CPU_delta] [int] NULL
251(Requires @delta_interval option)
252Difference between the CPU time reported on the first and second collections.
253If the request started after the first collection, the value will be NULL
254
255Formatted: [context_switches_delta] [varchar](30) NULL
256Non-Formatted: [context_switches_delta] [bigint] NULL
257(Requires @delta_interval option)
258Difference between the context switches count reported on the first and second collections
259If the request started after the first collection, the value will be NULL
260
261Formatted: [used_memory_delta] [varchar](30) NULL
262Non-Formatted: [used_memory_delta] [bigint] NULL
263Difference between the memory usage reported on the first and second collections
264If the request started after the first collection, the value will be NULL
265
266Formatted: [tasks] [varchar](30) NULL
267Non-Formatted: [tasks] [smallint] NULL
268Number of worker tasks currently allocated, for active requests
269
270Formatted/Non: [status] [varchar](30) NOT NULL
271Activity status for the session (running, sleeping, etc)
272
273Formatted/Non: [wait_info] [nvarchar](4000) NULL
274Aggregates wait information, in the following format:
275(Ax: Bms/Cms/Dms)E
276A is the number of waiting tasks currently waiting on resource type E. B/C/D are wait
277times, in milliseconds. If only one thread is waiting, its wait time will be shown as B.
278If two tasks are waiting, each of their wait times will be shown (B/C). If three or more
279tasks are waiting, the minimum, average, and maximum wait times will be shown (B/C/D).
280If wait type E is a page latch wait and the page is of a "special" type (e.g. PFS, GAM, SGAM),
281the page type will be identified.
282If wait type E is CXPACKET, the nodeId from the query plan will be identified
283
284Formatted/Non: [locks] [xml] NULL
285(Requires @get_locks option)
286Aggregates lock information, in XML format.
287The lock XML includes the lock mode, locked object, and aggregates the number of requests.
288Attempts are made to identify locked objects by name
289
290Formatted/Non: [tran_start_time] [datetime] NULL
291(Requires @get_transaction_info option)
292Date and time that the first transaction opened by a session caused a transaction log
293write to occur.
294
295Formatted/Non: [tran_log_writes] [nvarchar](4000) NULL
296(Requires @get_transaction_info option)
297Aggregates transaction log write information, in the following format:
298A:wB (C kB)
299A is a database that has been touched by an active transaction
300B is the number of log writes that have been made in the database as a result of the transaction
301C is the number of log kilobytes consumed by the log records
302
303Formatted: [open_tran_count] [varchar](30) NULL
304Non-Formatted: [open_tran_count] [smallint] NULL
305Shows the number of open transactions the session has open
306
307Formatted: [sql_command] [xml] NULL
308Non-Formatted: [sql_command] [nvarchar](max) NULL
309(Requires @get_outer_command option)
310Shows the "outer" SQL command, i.e. the text of the batch or RPC sent to the server,
311if available
312
313Formatted: [sql_text] [xml] NULL
314Non-Formatted: [sql_text] [nvarchar](max) NULL
315Shows the SQL text for active requests or the last statement executed
316for sleeping sessions, if available in either case.
317If @get_full_inner_text option is set, shows the full text of the batch.
318Otherwise, shows only the active statement within the batch.
319If the query text is locked, a special timeout message will be sent, in the following format:
320<timeout_exceeded />
321If an error occurs, an error message will be sent, in the following format:
322<error message="message" />
323
324Formatted/Non: [query_plan] [xml] NULL
325(Requires @get_plans option)
326Shows the query plan for the request, if available.
327If the plan is locked, a special timeout message will be sent, in the following format:
328<timeout_exceeded />
329If an error occurs, an error message will be sent, in the following format:
330<error message="message" />
331
332Formatted/Non: [blocking_session_id] [smallint] NULL
333When applicable, shows the blocking SPID
334
335Formatted: [blocked_session_count] [varchar](30) NULL
336Non-Formatted: [blocked_session_count] [smallint] NULL
337(Requires @find_block_leaders option)
338The total number of SPIDs blocked by this session,
339all the way down the blocking chain.
340
341Formatted: [percent_complete] [varchar](30) NULL
342Non-Formatted: [percent_complete] [real] NULL
343When applicable, shows the percent complete (e.g. for backups, restores, and some rollbacks)
344
345Formatted/Non: [host_name] [sysname] NOT NULL
346Shows the host name for the connection
347
348Formatted/Non: [login_name] [sysname] NOT NULL
349Shows the login name for the connection
350
351Formatted/Non: [database_name] [sysname] NULL
352Shows the connected database
353
354Formatted/Non: [program_name] [sysname] NULL
355Shows the reported program/application name
356
357Formatted/Non: [additional_info] [xml] NULL
358(Requires @get_additional_info option)
359Returns additional non-performance-related session/request information
360If the script finds a SQL Agent job running, the name of the job and job step will be reported
361If @get_task_info = 2 and the script finds a lock wait, the locked object will be reported
362
363Formatted/Non: [start_time] [datetime] NOT NULL
364For active requests, shows the time the request started
365For sleeping sessions, shows the time the last batch completed
366
367Formatted/Non: [login_time] [datetime] NOT NULL
368Shows the time that the session connected
369
370Formatted/Non: [request_id] [int] NULL
371For active requests, shows the request_id
372Should be 0 unless MARS is being used
373
374Formatted/Non: [collection_time] [datetime] NOT NULL
375Time that this script's final SELECT ran
376*/
377
378BEGIN;
379 SET NOCOUNT ON;
380 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
381 SET QUOTED_IDENTIFIER ON;
382 SET ANSI_PADDING ON;
383 SET CONCAT_NULL_YIELDS_NULL ON;
384 SET ANSI_WARNINGS ON;
385 SET NUMERIC_ROUNDABORT OFF;
386 SET ARITHABORT ON;
387
388 IF @filter IS NULL
389 OR @filter_type IS NULL
390 OR @not_filter IS NULL
391 OR @not_filter_type IS NULL
392 OR @show_own_spid IS NULL
393 OR @show_system_spids IS NULL
394 OR @show_sleeping_spids IS NULL
395 OR @get_full_inner_text IS NULL
396 OR @get_plans IS NULL
397 OR @get_outer_command IS NULL
398 OR @get_transaction_info IS NULL
399 OR @get_task_info IS NULL
400 OR @get_locks IS NULL
401 OR @get_avg_time IS NULL
402 OR @get_additional_info IS NULL
403 OR @find_block_leaders IS NULL
404 OR @delta_interval IS NULL
405 OR @format_output IS NULL
406 OR @output_column_list IS NULL
407 OR @sort_order IS NULL
408 OR @return_schema IS NULL
409 OR @destination_table IS NULL
410 OR @help IS NULL
411 BEGIN;
412 RAISERROR('Input parameters cannot be NULL', 16, 1);
413 RETURN;
414 END;
415
416 IF @filter_type NOT IN ('session', 'program', 'database', 'login', 'host')
417 BEGIN;
418 RAISERROR('Valid filter types are: session, program, database, login, host', 16, 1);
419 RETURN;
420 END;
421
422 IF @filter_type = 'session' AND @filter LIKE '%[^0123456789]%'
423 BEGIN;
424 RAISERROR('Session filters must be valid integers', 16, 1);
425 RETURN;
426 END;
427
428 IF @not_filter_type NOT IN ('session', 'program', 'database', 'login', 'host')
429 BEGIN;
430 RAISERROR('Valid filter types are: session, program, database, login, host', 16, 1);
431 RETURN;
432 END;
433
434 IF @not_filter_type = 'session' AND @not_filter LIKE '%[^0123456789]%'
435 BEGIN;
436 RAISERROR('Session filters must be valid integers', 16, 1);
437 RETURN;
438 END;
439
440 IF @show_sleeping_spids NOT IN (0, 1, 2)
441 BEGIN;
442 RAISERROR('Valid values for @show_sleeping_spids are: 0, 1, or 2', 16, 1);
443 RETURN;
444 END;
445
446 IF @get_plans NOT IN (0, 1, 2)
447 BEGIN;
448 RAISERROR('Valid values for @get_plans are: 0, 1, or 2', 16, 1);
449 RETURN;
450 END;
451
452 IF @get_task_info NOT IN (0, 1, 2)
453 BEGIN;
454 RAISERROR('Valid values for @get_task_info are: 0, 1, or 2', 16, 1);
455 RETURN;
456 END;
457
458 IF @format_output NOT IN (0, 1, 2)
459 BEGIN;
460 RAISERROR('Valid values for @format_output are: 0, 1, or 2', 16, 1);
461 RETURN;
462 END;
463
464 IF @help = 1
465 BEGIN;
466 DECLARE @header VARCHAR(MAX)
467 , @params VARCHAR(MAX)
468 , @outputs VARCHAR(MAX);
469
470 SELECT @header =
471 REPLACE
472 (
473 REPLACE
474 (
475 CONVERT
476 (
477 VARCHAR(MAX),
478 SUBSTRING
479 (
480 t.text,
481 CHARINDEX('/' + REPLICATE('*', 93), t.text) + 94,
482 CHARINDEX(REPLICATE('*', 93) + '/', t.text) - (CHARINDEX('/' + REPLICATE('*', 93), t.text) + 94)
483 )
484 ),
485 CHAR(13)+CHAR(10),
486 CHAR(13)
487 ),
488 ' ',
489 ''
490 )
491 , @params =
492 CHAR(13) +
493 REPLACE
494 (
495 REPLACE
496 (
497 CONVERT
498 (
499 VARCHAR(MAX),
500 SUBSTRING
501 (
502 t.text,
503 CHARINDEX('--~', t.text) + 5,
504 CHARINDEX('--~', t.text, CHARINDEX('--~', t.text) + 5) - (CHARINDEX('--~', t.text) + 5)
505 )
506 ),
507 CHAR(13)+CHAR(10),
508 CHAR(13)
509 ),
510 ' ',
511 ''
512 )
513 , @outputs =
514 CHAR(13) +
515 REPLACE
516 (
517 REPLACE
518 (
519 REPLACE
520 (
521 CONVERT
522 (
523 VARCHAR(MAX),
524 SUBSTRING
525 (
526 t.text,
527 CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32,
528 CHARINDEX('*/', t.text, CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32) - (CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32)
529 )
530 ),
531 CHAR(9),
532 CHAR(255)
533 ),
534 CHAR(13)+CHAR(10),
535 CHAR(13)
536 ),
537 ' ',
538 ''
539 ) +
540 CHAR(13)
541 FROM sys.dm_exec_requests AS r
542 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
543 WHERE
544 r.session_id = @@SPID;
545
546 WITH A0
547 AS
548 (
549 SELECT 1 AS n
550 UNION ALL
551 SELECT 1
552 )
553 , A1
554 AS
555 (
556 SELECT 1 AS n
557 FROM a0 AS a
558 , a0 AS b
559 )
560 , A2
561 AS
562 (
563 SELECT 1 AS n
564 FROM a1 AS a
565 , a1 AS b
566 )
567 , A3
568 AS
569 (
570 SELECT 1 AS n
571 FROM a2 AS a
572 , a2 AS b
573 )
574 , A4
575 AS
576 (
577 SELECT 1 AS n
578 FROM a3 AS a
579 , a3 AS b
580 )
581 , NUMBERS
582 AS
583 (
584 SELECT TOP(LEN(@header) - 1) ROW_NUMBER() OVER
585 (
586 ORDER BY (SELECT NULL)
587 ) AS number
588 FROM a4
589 ORDER BY number
590 )
591 SELECT RTRIM(LTRIM(
592 SUBSTRING
593 (
594 @header,
595 number + 1,
596 CHARINDEX(CHAR(13), @header, number + 1) - number - 1
597 )
598 )) AS [------header---------------------------------------------------------------------------------------------------------------]
599 FROM numbers
600 WHERE
601 SUBSTRING(@header, number, 1) = CHAR(13);
602
603 WITH A0
604 AS
605 (
606 SELECT 1 AS n
607 UNION ALL
608 SELECT 1
609 )
610 , A1
611 AS
612 (
613 SELECT 1 AS n
614 FROM a0 AS a
615 , a0 AS b
616 )
617 , A2
618 AS
619 (
620 SELECT 1 AS n
621 FROM a1 AS a
622 , a1 AS b
623 )
624 , A3
625 AS
626 (
627 SELECT 1 AS n
628 FROM a2 AS a
629 , a2 AS b
630 )
631 , A4
632 AS
633 (
634 SELECT 1 AS n
635 FROM a3 AS a
636 , a3 AS b
637 )
638 , NUMBERS
639 AS
640 (
641 SELECT TOP(LEN(@params) - 1) ROW_NUMBER() OVER
642 (
643 ORDER BY (SELECT NULL)
644 ) AS number
645 FROM a4
646 ORDER BY number
647 )
648 , TOKENS
649 AS
650 (
651 SELECT RTRIM(LTRIM(
652 SUBSTRING
653 (
654 @params,
655 number + 1,
656 CHARINDEX(CHAR(13), @params, number + 1) - number - 1
657 )
658 )) AS token
659 , number
660 , CASE WHEN SUBSTRING(@params, number + 1, 1) = CHAR(13) THEN number
661 ELSE COALESCE(NULLIF(CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number), 0), LEN(@params)) END AS param_group
662 , ROW_NUMBER() OVER
663 (
664 PARTITION BY
665 CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number),
666 SUBSTRING(@params, number+1, 1)
667 ORDER BY
668 number
669 ) AS group_order
670 FROM numbers
671 WHERE
672 SUBSTRING(@params, number, 1) = CHAR(13)
673 )
674 , PARSED_TOKENS
675 AS
676 (
677 SELECT MIN
678 (
679 CASE WHEN token LIKE '@%' THEN token
680 ELSE NULL END
681 ) AS parameter
682 , MIN
683 (
684 CASE WHEN token LIKE '--%' THEN RIGHT(token, LEN(token) - 2)
685 ELSE NULL END
686 ) AS description
687 , param_group
688 , group_order
689 FROM tokens
690 WHERE
691 NOT
692 (
693 token = ''
694 AND group_order > 1
695 )
696 GROUP BY param_group
697 , group_order
698 )
699 SELECT CASE WHEN description IS NULL AND parameter IS NULL THEN '-------------------------------------------------------------------------'
700 WHEN param_group = MAX(param_group) OVER() THEN parameter
701 ELSE COALESCE(LEFT(parameter, LEN(parameter) - 1), '') END AS [------parameter----------------------------------------------------------]
702 , CASE WHEN description IS NULL AND parameter IS NULL THEN '----------------------------------------------------------------------------------------------------------------------'
703 ELSE COALESCE(description, '') END AS [------description-----------------------------------------------------------------------------------------------------]
704 FROM parsed_tokens
705 ORDER BY param_group , group_order;
706
707 WITH A0
708 AS
709 (
710 SELECT 1 AS n
711 UNION ALL
712 SELECT 1
713 )
714 , A1
715 AS
716 (
717 SELECT 1 AS n
718 FROM a0 AS a
719 , a0 AS b
720 )
721 , A2
722 AS
723 (
724 SELECT 1 AS n
725 FROM a1 AS a
726 , a1 AS b
727 )
728 , A3
729 AS
730 (
731 SELECT 1 AS n
732 FROM a2 AS a
733 , a2 AS b
734 )
735 , A4
736 AS
737 (
738 SELECT 1 AS n
739 FROM a3 AS a
740 , a3 AS b
741 )
742 , NUMBERS
743 AS
744 (
745 SELECT TOP(LEN(@outputs) - 1) ROW_NUMBER() OVER
746 (
747 ORDER BY (SELECT NULL)
748 ) AS number
749 FROM a4
750 ORDER BY number
751 )
752 , TOKENS
753 AS
754 (
755 SELECT RTRIM(LTRIM(
756 SUBSTRING
757 (
758 @outputs,
759 number + 1,
760 CASE WHEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) <
761 COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) THEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) - number - 1
762 ELSE COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) - number - 1 END
763 )
764 )) AS token
765 , number
766 , COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) AS output_group
767 , ROW_NUMBER() OVER
768 (
769 PARTITION BY
770 COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs))
771 ORDER BY
772 number
773 ) AS output_group_order
774 FROM numbers
775 WHERE
776 SUBSTRING(@outputs, number, 10) = CHAR(13) + 'Formatted'
777 OR SUBSTRING(@outputs, number, 2) = CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2
778 )
779 , OUTPUT_TOKENS
780 AS
781 (
782 SELECT *
783 , CASE output_group_order WHEN 2 THEN MAX(CASE output_group_order WHEN 1 THEN token
784 ELSE NULL END) OVER (PARTITION BY output_group)
785 ELSE '' END COLLATE Latin1_General_Bin2 AS column_info
786 FROM tokens
787 )
788 SELECT CASE output_group_order WHEN 1 THEN '-----------------------------------'
789 WHEN 2 THEN CASE WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+1, CHARINDEX(']', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info))
790 ELSE SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2, CHARINDEX(']', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)-1) END
791 ELSE '' END AS formatted_column_name
792 , CASE output_group_order WHEN 1 THEN '-----------------------------------'
793 WHEN 2 THEN CASE WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN SUBSTRING(column_info, CHARINDEX(']', column_info)+2, LEN(column_info))
794 ELSE SUBSTRING(column_info, CHARINDEX(']', column_info)+2, CHARINDEX('Non-Formatted:', column_info, CHARINDEX(']', column_info)+2) - CHARINDEX(']', column_info)-3) END
795 ELSE '' END AS formatted_column_type
796 , CASE output_group_order WHEN 1 THEN '---------------------------------------'
797 WHEN 2 THEN CASE WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN ''
798 ELSE CASE WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, CHARINDEX('>', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info)))
799 ELSE SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, CHARINDEX(']', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))) END END
800 ELSE '' END AS unformatted_column_name
801 , CASE output_group_order WHEN 1 THEN '---------------------------------------'
802 WHEN 2 THEN CASE WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN ''
803 ELSE CASE WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN ''
804 ELSE SUBSTRING(column_info, CHARINDEX(']', column_info, CHARINDEX('Non-Formatted:', column_info))+2, CHARINDEX('Non-Formatted:', column_info, CHARINDEX(']', column_info)+2) - CHARINDEX(']', column_info)-3) END END
805 ELSE '' END AS unformatted_column_type
806 , CASE output_group_order WHEN 1 THEN '----------------------------------------------------------------------------------------------------------------------'
807 ELSE REPLACE(token, CHAR(255) COLLATE Latin1_General_Bin2, '') END AS [------description-----------------------------------------------------------------------------------------------------]
808 FROM output_tokens
809 WHERE
810 NOT
811 (
812 output_group_order = 1
813 AND output_group = LEN(@outputs)
814 )
815 ORDER BY output_group , CASE output_group_order WHEN 1 THEN 99
816 ELSE output_group_order END;
817
818 RETURN;
819 END;
820
821 WITH A0
822 AS
823 (
824 SELECT 1 AS n
825 UNION ALL
826 SELECT 1
827 )
828 , A1
829 AS
830 (
831 SELECT 1 AS n
832 FROM a0 AS a
833 , a0 AS b
834 )
835 , A2
836 AS
837 (
838 SELECT 1 AS n
839 FROM a1 AS a
840 , a1 AS b
841 )
842 , A3
843 AS
844 (
845 SELECT 1 AS n
846 FROM a2 AS a
847 , a2 AS b
848 )
849 , A4
850 AS
851 (
852 SELECT 1 AS n
853 FROM a3 AS a
854 , a3 AS b
855 )
856 , NUMBERS
857 AS
858 (
859 SELECT TOP(LEN(@output_column_list)) ROW_NUMBER() OVER
860 (
861 ORDER BY (SELECT NULL)
862 ) AS number
863 FROM a4
864 ORDER BY number
865 )
866 , TOKENS
867 AS
868 (
869 SELECT '|[' +
870 SUBSTRING
871 (
872 @output_column_list,
873 number + 1,
874 CHARINDEX(']', @output_column_list, number) - number - 1
875 ) + '|]' AS token
876 , number
877 FROM numbers
878 WHERE
879 SUBSTRING(@output_column_list, number, 1) = '['
880 )
881 , ORDERED_COLUMNS
882 AS
883 (
884 SELECT x.column_name
885 , ROW_NUMBER() OVER
886 (
887 PARTITION BY
888 x.column_name
889 ORDER BY
890 tokens.number,
891 x.default_order
892 ) AS r
893 , ROW_NUMBER() OVER
894 (
895 ORDER BY
896 tokens.number,
897 x.default_order
898 ) AS s
899 FROM tokens
900 JOIN (
901 SELECT '[session_id]' AS column_name
902 , 1 AS default_order
903 UNION ALL
904 SELECT '[dd hh:mm:ss.mss]'
905 , 2
906 WHERE
907 @format_output IN (1, 2)
908 UNION ALL
909 SELECT '[dd hh:mm:ss.mss (avg)]'
910 , 3
911 WHERE
912 @format_output IN (1, 2)
913 AND @get_avg_time = 1
914 UNION ALL
915 SELECT '[avg_elapsed_time]'
916 , 4
917 WHERE
918 @format_output = 0
919 AND @get_avg_time = 1
920 UNION ALL
921 SELECT '[physical_io]'
922 , 5
923 WHERE
924 @get_task_info = 2
925 UNION ALL
926 SELECT '[reads]'
927 , 6
928 UNION ALL
929 SELECT '[physical_reads]'
930 , 7
931 UNION ALL
932 SELECT '[writes]'
933 , 8
934 UNION ALL
935 SELECT '[tempdb_allocations]'
936 , 9
937 UNION ALL
938 SELECT '[tempdb_current]'
939 , 10
940 UNION ALL
941 SELECT '[CPU]'
942 , 11
943 UNION ALL
944 SELECT '[context_switches]'
945 , 12
946 WHERE
947 @get_task_info = 2
948 UNION ALL
949 SELECT '[used_memory]'
950 , 13
951 UNION ALL
952 SELECT '[physical_io_delta]'
953 , 14
954 WHERE
955 @delta_interval > 0
956 AND @get_task_info = 2
957 UNION ALL
958 SELECT '[reads_delta]'
959 , 15
960 WHERE
961 @delta_interval > 0
962 UNION ALL
963 SELECT '[physical_reads_delta]'
964 , 16
965 WHERE
966 @delta_interval > 0
967 UNION ALL
968 SELECT '[writes_delta]'
969 , 17
970 WHERE
971 @delta_interval > 0
972 UNION ALL
973 SELECT '[tempdb_allocations_delta]'
974 , 18
975 WHERE
976 @delta_interval > 0
977 UNION ALL
978 SELECT '[tempdb_current_delta]'
979 , 19
980 WHERE
981 @delta_interval > 0
982 UNION ALL
983 SELECT '[CPU_delta]'
984 , 20
985 WHERE
986 @delta_interval > 0
987 UNION ALL
988 SELECT '[context_switches_delta]'
989 , 21
990 WHERE
991 @delta_interval > 0
992 AND @get_task_info = 2
993 UNION ALL
994 SELECT '[used_memory_delta]'
995 , 22
996 WHERE
997 @delta_interval > 0
998 UNION ALL
999 SELECT '[tasks]'
1000 , 23
1001 WHERE
1002 @get_task_info = 2
1003 UNION ALL
1004 SELECT '[status]'
1005 , 24
1006 UNION ALL
1007 SELECT '[wait_info]'
1008 , 25
1009 WHERE
1010 @get_task_info > 0
1011 OR @find_block_leaders = 1
1012 UNION ALL
1013 SELECT '[locks]'
1014 , 26
1015 WHERE
1016 @get_locks = 1
1017 UNION ALL
1018 SELECT '[tran_start_time]'
1019 , 27
1020 WHERE
1021 @get_transaction_info = 1
1022 UNION ALL
1023 SELECT '[tran_log_writes]'
1024 , 28
1025 WHERE
1026 @get_transaction_info = 1
1027 UNION ALL
1028 SELECT '[open_tran_count]'
1029 , 29
1030 UNION ALL
1031 SELECT '[sql_command]'
1032 , 30
1033 WHERE
1034 @get_outer_command = 1
1035 UNION ALL
1036 SELECT '[sql_text]'
1037 , 31
1038 UNION ALL
1039 SELECT '[query_plan]'
1040 , 32
1041 WHERE
1042 @get_plans >= 1
1043 UNION ALL
1044 SELECT '[blocking_session_id]'
1045 , 33
1046 WHERE
1047 @get_task_info > 0
1048 OR @find_block_leaders = 1
1049 UNION ALL
1050 SELECT '[blocked_session_count]'
1051 , 34
1052 WHERE
1053 @find_block_leaders = 1
1054 UNION ALL
1055 SELECT '[percent_complete]'
1056 , 35
1057 UNION ALL
1058 SELECT '[host_name]'
1059 , 36
1060 UNION ALL
1061 SELECT '[login_name]'
1062 , 37
1063 UNION ALL
1064 SELECT '[database_name]'
1065 , 38
1066 UNION ALL
1067 SELECT '[program_name]'
1068 , 39
1069 UNION ALL
1070 SELECT '[additional_info]'
1071 , 40
1072 WHERE
1073 @get_additional_info = 1
1074 UNION ALL
1075 SELECT '[start_time]'
1076 , 41
1077 UNION ALL
1078 SELECT '[login_time]'
1079 , 42
1080 UNION ALL
1081 SELECT '[request_id]'
1082 , 43
1083 UNION ALL
1084 SELECT '[collection_time]'
1085 , 44
1086 ) AS x ON x.column_name LIKE token ESCAPE '|'
1087 )
1088 SELECT @output_column_list =
1089 STUFF
1090 (
1091 (
1092 SELECT ',' + column_name AS [text()]
1093 FROM ordered_columns
1094 WHERE
1095 r = 1
1096 ORDER BY s
1097 FOR XML
1098 PATH('')
1099 ),
1100 1,
1101 1,
1102 ''
1103 );
1104
1105 IF COALESCE(RTRIM(@output_column_list), '') = ''
1106 BEGIN;
1107 RAISERROR('No valid column matches found in @output_column_list or no columns remain due to selected options.', 16, 1);
1108 RETURN;
1109 END;
1110
1111 IF @destination_table <> ''
1112 BEGIN;
1113 SET @destination_table =
1114 --database
1115 COALESCE(QUOTENAME(PARSENAME(@destination_table, 3)) + '.', '') +
1116 --schema
1117 COALESCE(QUOTENAME(PARSENAME(@destination_table, 2)) + '.', '') +
1118 --table
1119 COALESCE(QUOTENAME(PARSENAME(@destination_table, 1)), '');
1120
1121 IF COALESCE(RTRIM(@destination_table), '') = ''
1122 BEGIN;
1123 RAISERROR('Destination table not properly formatted.', 16, 1);
1124 RETURN;
1125 END;
1126 END;
1127
1128 WITH A0
1129 AS
1130 (
1131 SELECT 1 AS n
1132 UNION ALL
1133 SELECT 1
1134 )
1135 , A1
1136 AS
1137 (
1138 SELECT 1 AS n
1139 FROM a0 AS a
1140 , a0 AS b
1141 )
1142 , A2
1143 AS
1144 (
1145 SELECT 1 AS n
1146 FROM a1 AS a
1147 , a1 AS b
1148 )
1149 , A3
1150 AS
1151 (
1152 SELECT 1 AS n
1153 FROM a2 AS a
1154 , a2 AS b
1155 )
1156 , A4
1157 AS
1158 (
1159 SELECT 1 AS n
1160 FROM a3 AS a
1161 , a3 AS b
1162 )
1163 , NUMBERS
1164 AS
1165 (
1166 SELECT TOP(LEN(@sort_order)) ROW_NUMBER() OVER
1167 (
1168 ORDER BY (SELECT NULL)
1169 ) AS number
1170 FROM a4
1171 ORDER BY number
1172 )
1173 , TOKENS
1174 AS
1175 (
1176 SELECT '|[' +
1177 SUBSTRING
1178 (
1179 @sort_order,
1180 number + 1,
1181 CHARINDEX(']', @sort_order, number) - number - 1
1182 ) + '|]' AS token
1183 , SUBSTRING
1184 (
1185 @sort_order,
1186 CHARINDEX(']', @sort_order, number) + 1,
1187 COALESCE(NULLIF(CHARINDEX('[', @sort_order, CHARINDEX(']', @sort_order, number)), 0), LEN(@sort_order)) - CHARINDEX(']', @sort_order, number)
1188 ) AS next_chunk
1189 , number
1190 FROM numbers
1191 WHERE
1192 SUBSTRING(@sort_order, number, 1) = '['
1193 )
1194 , ORDERED_COLUMNS
1195 AS
1196 (
1197 SELECT x.column_name +
1198 CASE WHEN tokens.next_chunk LIKE '%asc%' THEN ' ASC'
1199 WHEN tokens.next_chunk LIKE '%desc%' THEN ' DESC'
1200 ELSE '' END AS column_name
1201 , ROW_NUMBER() OVER
1202 (
1203 PARTITION BY
1204 x.column_name
1205 ORDER BY
1206 tokens.number
1207 ) AS r
1208 , tokens.number
1209 FROM tokens
1210 JOIN (
1211 SELECT '[session_id]' AS column_name
1212 UNION ALL
1213 SELECT '[physical_io]'
1214 UNION ALL
1215 SELECT '[reads]'
1216 UNION ALL
1217 SELECT '[physical_reads]'
1218 UNION ALL
1219 SELECT '[writes]'
1220 UNION ALL
1221 SELECT '[tempdb_allocations]'
1222 UNION ALL
1223 SELECT '[tempdb_current]'
1224 UNION ALL
1225 SELECT '[CPU]'
1226 UNION ALL
1227 SELECT '[context_switches]'
1228 UNION ALL
1229 SELECT '[used_memory]'
1230 UNION ALL
1231 SELECT '[physical_io_delta]'
1232 UNION ALL
1233 SELECT '[reads_delta]'
1234 UNION ALL
1235 SELECT '[physical_reads_delta]'
1236 UNION ALL
1237 SELECT '[writes_delta]'
1238 UNION ALL
1239 SELECT '[tempdb_allocations_delta]'
1240 UNION ALL
1241 SELECT '[tempdb_current_delta]'
1242 UNION ALL
1243 SELECT '[CPU_delta]'
1244 UNION ALL
1245 SELECT '[context_switches_delta]'
1246 UNION ALL
1247 SELECT '[used_memory_delta]'
1248 UNION ALL
1249 SELECT '[tasks]'
1250 UNION ALL
1251 SELECT '[tran_start_time]'
1252 UNION ALL
1253 SELECT '[open_tran_count]'
1254 UNION ALL
1255 SELECT '[blocking_session_id]'
1256 UNION ALL
1257 SELECT '[blocked_session_count]'
1258 UNION ALL
1259 SELECT '[percent_complete]'
1260 UNION ALL
1261 SELECT '[host_name]'
1262 UNION ALL
1263 SELECT '[login_name]'
1264 UNION ALL
1265 SELECT '[database_name]'
1266 UNION ALL
1267 SELECT '[start_time]'
1268 UNION ALL
1269 SELECT '[login_time]'
1270 UNION ALL
1271 SELECT '[program_name]'
1272 ) AS x ON x.column_name LIKE token ESCAPE '|'
1273 )
1274 SELECT @sort_order = COALESCE(z.sort_order, '')
1275 FROM (
1276 SELECT STUFF
1277 (
1278 (
1279 SELECT ',' + column_name AS [text()]
1280 FROM ordered_columns
1281 WHERE
1282 r = 1
1283 ORDER BY number
1284 FOR XML
1285 PATH('')
1286 ),
1287 1,
1288 1,
1289 ''
1290 ) AS sort_order
1291 ) AS z;
1292
1293 BEGIN TRY DROP TABLE #sessions; END TRY BEGIN CATCH END CATCH;;
1294 CREATE TABLE #sessions (
1295 recursion SMALLINT NOT NULL
1296 , session_id SMALLINT NOT NULL
1297 , request_id INT NOT NULL
1298 , session_number INT NOT NULL
1299 , elapsed_time INT NOT NULL
1300 , avg_elapsed_time INT NULL
1301 , physical_io BIGINT NULL
1302 , reads BIGINT NULL
1303 , physical_reads BIGINT NULL
1304 , writes BIGINT NULL
1305 , tempdb_allocations BIGINT NULL
1306 , tempdb_current BIGINT NULL
1307 , CPU INT NULL
1308 , thread_CPU_snapshot BIGINT NULL
1309 , context_switches BIGINT NULL
1310 , used_memory BIGINT NOT NULL
1311 , tasks SMALLINT NULL
1312 , status VARCHAR(30) NOT NULL
1313 , wait_info NVARCHAR(4000) NULL
1314 , locks XML NULL
1315 , transaction_id BIGINT NULL
1316 , tran_start_time DATETIME NULL
1317 , tran_log_writes NVARCHAR(4000) NULL
1318 , open_tran_count SMALLINT NULL
1319 , sql_command XML NULL
1320 , sql_handle VARBINARY(64) NULL
1321 , statement_start_offset INT NULL
1322 , statement_end_offset INT NULL
1323 , sql_text XML NULL
1324 , plan_handle VARBINARY(64) NULL
1325 , query_plan XML NULL
1326 , blocking_session_id SMALLINT NULL
1327 , blocked_session_count SMALLINT NULL
1328 , percent_complete REAL NULL
1329 , host_name sysname NULL
1330 , login_name sysname NOT NULL
1331 , database_name sysname NULL
1332 , program_name sysname NULL
1333 , additional_info XML NULL
1334 , start_time DATETIME NOT NULL
1335 , login_time DATETIME NULL
1336 , last_request_start_time DATETIME NULL
1337 , PRIMARY KEY CLUSTERED (session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON)
1338 , UNIQUE NONCLUSTERED (transaction_id, session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON) );
1339
1340 IF @return_schema = 0
1341 BEGIN;
1342 --Disable unnecessary autostats on the table
1343 CREATE STATISTICS s_session_id ON #sessions (session_id)
1344 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1345 CREATE STATISTICS s_request_id ON #sessions (request_id)
1346 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1347 CREATE STATISTICS s_transaction_id ON #sessions (transaction_id)
1348 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1349 CREATE STATISTICS s_session_number ON #sessions (session_number)
1350 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1351 CREATE STATISTICS s_status ON #sessions (status)
1352 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1353 CREATE STATISTICS s_start_time ON #sessions (start_time)
1354 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1355 CREATE STATISTICS s_last_request_start_time ON #sessions (last_request_start_time)
1356 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1357 CREATE STATISTICS s_recursion ON #sessions (recursion)
1358 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1359
1360 DECLARE @recursion SMALLINT;
1361 SET @recursion =
1362 CASE @delta_interval WHEN 0 THEN 1
1363 ELSE -1 END;
1364
1365 DECLARE @first_collection_ms_ticks BIGINT;
1366 DECLARE @last_collection_start DATETIME;
1367 DECLARE @sys_info BIT;
1368 SET @sys_info = ISNULL(CONVERT(BIT, SIGN(OBJECT_ID('sys.dm_os_sys_info'))), 0);
1369
1370 --Used for the delta pull
1371 REDO:;
1372
1373 IF @get_locks = 1
1374 AND @recursion = 1
1375 AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'
1376 BEGIN;
1377 SELECT y.resource_type
1378 , y.database_name
1379 , y.object_id
1380 , y.file_id
1381 , y.page_type
1382 , y.hobt_id
1383 , y.allocation_unit_id
1384 , y.index_id
1385 , y.schema_id
1386 , y.principal_id
1387 , y.request_mode
1388 , y.request_status
1389 , y.session_id
1390 , y.resource_description
1391 , y.request_count
1392 , s.request_id
1393 , s.start_time
1394 , CONVERT(sysname, NULL) AS object_name
1395 , CONVERT(sysname, NULL) AS index_name
1396 , CONVERT(sysname, NULL) AS schema_name
1397 , CONVERT(sysname, NULL) AS principal_name
1398 , CONVERT(NVARCHAR(2048), NULL) AS query_error INTO #locks
1399 FROM (
1400 SELECT sp.spid AS session_id
1401 , CASE sp.status WHEN 'sleeping' THEN CONVERT(INT, 0)
1402 ELSE sp.request_id END AS request_id
1403 , CASE sp.status WHEN 'sleeping' THEN sp.last_batch
1404 ELSE COALESCE(req.start_time, sp.last_batch) END AS start_time
1405 , sp.dbid
1406 FROM sys.sysprocesses AS sp
1407 OUTER APPLY (
1408 SELECT TOP(1) CASE WHEN (
1409 sp.hostprocess > ''
1410 OR r.total_elapsed_time < 0
1411 ) THEN r.start_time
1412 ELSE DATEADD
1413 (
1414 ms,
1415 1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
1416 DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
1417 ) END AS start_time
1418 FROM sys.dm_exec_requests AS r
1419 WHERE
1420 r.session_id = sp.spid
1421 AND r.request_id = sp.request_id
1422 ) AS req
1423 WHERE
1424 --Process inclusive filter
1425 1 =
1426 CASE WHEN @filter <> '' THEN CASE @filter_type WHEN 'session' THEN CASE WHEN CONVERT(SMALLINT, @filter) = 0
1427 OR sp.spid = CONVERT(SMALLINT, @filter) THEN 1
1428 ELSE 0 END
1429 WHEN 'program' THEN CASE WHEN sp.program_name LIKE @filter THEN 1
1430 ELSE 0 END
1431 WHEN 'login' THEN CASE WHEN sp.loginame LIKE @filter THEN 1
1432 ELSE 0 END
1433 WHEN 'host' THEN CASE WHEN sp.hostname LIKE @filter THEN 1
1434 ELSE 0 END
1435 WHEN 'database' THEN CASE WHEN DB_NAME(sp.dbid) LIKE @filter THEN 1
1436 ELSE 0 END
1437 ELSE 0 END
1438 ELSE 1 END
1439 --Process exclusive filter
1440 AND 0 =
1441 CASE WHEN @not_filter <> '' THEN CASE @not_filter_type WHEN 'session' THEN CASE WHEN sp.spid = CONVERT(SMALLINT, @not_filter) THEN 1
1442 ELSE 0 END
1443 WHEN 'program' THEN CASE WHEN sp.program_name LIKE @not_filter THEN 1
1444 ELSE 0 END
1445 WHEN 'login' THEN CASE WHEN sp.loginame LIKE @not_filter THEN 1
1446 ELSE 0 END
1447 WHEN 'host' THEN CASE WHEN sp.hostname LIKE @not_filter THEN 1
1448 ELSE 0 END
1449 WHEN 'database' THEN CASE WHEN DB_NAME(sp.dbid) LIKE @not_filter THEN 1
1450 ELSE 0 END
1451 ELSE 0 END
1452 ELSE 0 END
1453 AND
1454 (
1455 @show_own_spid = 1
1456 OR sp.spid <> @@SPID
1457 )
1458 AND
1459 (
1460 @show_system_spids = 1
1461 OR sp.hostprocess > ''
1462 )
1463 AND sp.ecid = 0
1464 ) AS s
1465 INNER HASH JOIN (
1466 SELECT x.resource_type
1467 , x.database_name
1468 , x.object_id
1469 , x.file_id
1470 , CASE WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS'
1471 WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM'
1472 WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM'
1473 WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM'
1474 WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM'
1475 WHEN x.page_no IS NOT NULL THEN '*'
1476 ELSE NULL END AS page_type
1477 , x.hobt_id
1478 , x.allocation_unit_id
1479 , x.index_id
1480 , x.schema_id
1481 , x.principal_id
1482 , x.request_mode
1483 , x.request_status
1484 , x.session_id
1485 , x.request_id
1486 , CASE WHEN COALESCE(x.object_id, x.file_id, x.hobt_id, x.allocation_unit_id, x.index_id, x.schema_id, x.principal_id) IS NULL THEN NULLIF(resource_description, '')
1487 ELSE NULL END AS resource_description
1488 , COUNT(*) AS request_count
1489 FROM (
1490 SELECT tl.resource_type +
1491 CASE WHEN tl.resource_subtype = '' THEN ''
1492 ELSE '.' + tl.resource_subtype END AS resource_type
1493 , COALESCE(DB_NAME(tl.resource_database_id), N'(null)') AS database_name
1494 , CONVERT
1495 (
1496 INT,
1497 CASE WHEN tl.resource_type = 'OBJECT' THEN tl.resource_associated_entity_id
1498 WHEN tl.resource_description LIKE '%object_id = %' THEN (
1499 SUBSTRING
1500 (
1501 tl.resource_description,
1502 (CHARINDEX('object_id = ', tl.resource_description) + 12),
1503 COALESCE
1504 (
1505 NULLIF
1506 (
1507 CHARINDEX(',', tl.resource_description, CHARINDEX('object_id = ', tl.resource_description) + 12),
1508 0
1509 ),
1510 DATALENGTH(tl.resource_description)+1
1511 ) - (CHARINDEX('object_id = ', tl.resource_description) + 12)
1512 )
1513 )
1514 ELSE NULL END
1515 ) AS object_id
1516 , CONVERT
1517 (
1518 INT,
1519 CASE WHEN tl.resource_type = 'FILE' THEN CONVERT(INT, tl.resource_description)
1520 WHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN LEFT(tl.resource_description, CHARINDEX(':', tl.resource_description)-1)
1521 ELSE NULL END
1522 ) AS file_id
1523 , CONVERT
1524 (
1525 INT,
1526 CASE WHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN SUBSTRING
1527 (
1528 tl.resource_description,
1529 CHARINDEX(':', tl.resource_description) + 1,
1530 COALESCE
1531 (
1532 NULLIF
1533 (
1534 CHARINDEX(':', tl.resource_description, CHARINDEX(':', tl.resource_description) + 1),
1535 0
1536 ),
1537 DATALENGTH(tl.resource_description)+1
1538 ) - (CHARINDEX(':', tl.resource_description) + 1)
1539 )
1540 ELSE NULL END
1541 ) AS page_no
1542 , CASE WHEN tl.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') THEN tl.resource_associated_entity_id
1543 ELSE NULL END AS hobt_id
1544 , CASE WHEN tl.resource_type = 'ALLOCATION_UNIT' THEN tl.resource_associated_entity_id
1545 ELSE NULL END AS allocation_unit_id
1546 , CONVERT
1547 (
1548 INT,
1549 CASE WHEN
1550 /*TODO: Deal with server principals*/ tl.resource_subtype <> 'SERVER_PRINCIPAL'
1551 AND tl.resource_description LIKE '%index_id or stats_id = %' THEN (
1552 SUBSTRING
1553 (
1554 tl.resource_description,
1555 (CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23),
1556 COALESCE
1557 (
1558 NULLIF
1559 (
1560 CHARINDEX(',', tl.resource_description, CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23),
1561 0
1562 ),
1563 DATALENGTH(tl.resource_description)+1
1564 ) - (CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23)
1565 )
1566 )
1567 ELSE NULL END
1568 ) AS index_id
1569 , CONVERT
1570 (
1571 INT,
1572 CASE WHEN tl.resource_description LIKE '%schema_id = %' THEN (
1573 SUBSTRING
1574 (
1575 tl.resource_description,
1576 (CHARINDEX('schema_id = ', tl.resource_description) + 12),
1577 COALESCE
1578 (
1579 NULLIF
1580 (
1581 CHARINDEX(',', tl.resource_description, CHARINDEX('schema_id = ', tl.resource_description) + 12),
1582 0
1583 ),
1584 DATALENGTH(tl.resource_description)+1
1585 ) - (CHARINDEX('schema_id = ', tl.resource_description) + 12)
1586 )
1587 )
1588 ELSE NULL END
1589 ) AS schema_id
1590 , CONVERT
1591 (
1592 INT,
1593 CASE WHEN tl.resource_description LIKE '%principal_id = %' THEN (
1594 SUBSTRING
1595 (
1596 tl.resource_description,
1597 (CHARINDEX('principal_id = ', tl.resource_description) + 15),
1598 COALESCE
1599 (
1600 NULLIF
1601 (
1602 CHARINDEX(',', tl.resource_description, CHARINDEX('principal_id = ', tl.resource_description) + 15),
1603 0
1604 ),
1605 DATALENGTH(tl.resource_description)+1
1606 ) - (CHARINDEX('principal_id = ', tl.resource_description) + 15)
1607 )
1608 )
1609 ELSE NULL END
1610 ) AS principal_id
1611 , tl.request_mode
1612 , tl.request_status
1613 , tl.request_session_id AS session_id
1614 , tl.request_request_id AS request_id
1615 ,
1616
1617 /*TODO: Applocks, other resource_descriptions*/
1618 RTRIM(tl.resource_description) AS resource_description
1619 , tl.resource_associated_entity_id
1620 /*********************************************/ FROM (
1621 SELECT request_session_id
1622 , CONVERT(VARCHAR(120), resource_type) COLLATE Latin1_General_Bin2 AS resource_type
1623 , CONVERT(VARCHAR(120), resource_subtype) COLLATE Latin1_General_Bin2 AS resource_subtype
1624 , resource_database_id
1625 , CONVERT(VARCHAR(512), resource_description) COLLATE Latin1_General_Bin2 AS resource_description
1626 , resource_associated_entity_id
1627 , CONVERT(VARCHAR(120), request_mode) COLLATE Latin1_General_Bin2 AS request_mode
1628 , CONVERT(VARCHAR(120), request_status) COLLATE Latin1_General_Bin2 AS request_status
1629 , request_request_id
1630 FROM sys.dm_tran_locks
1631 ) AS tl
1632 ) AS x
1633 GROUP BY x.resource_type
1634 , x.database_name
1635 , x.object_id
1636 , x.file_id
1637 , CASE WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS'
1638 WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM'
1639 WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM'
1640 WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM'
1641 WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM'
1642 WHEN x.page_no IS NOT NULL THEN '*'
1643 ELSE NULL END
1644 , x.hobt_id
1645 , x.allocation_unit_id
1646 , x.index_id
1647 , x.schema_id
1648 , x.principal_id
1649 , x.request_mode
1650 , x.request_status
1651 , x.session_id
1652 , x.request_id
1653 , CASE WHEN COALESCE(x.object_id, x.file_id, x.hobt_id, x.allocation_unit_id, x.index_id, x.schema_id, x.principal_id) IS NULL THEN NULLIF(resource_description, '')
1654 ELSE NULL END
1655 ) AS y ON y.session_id = s.session_id
1656 AND y.request_id = s.request_id
1657 OPTION (HASH GROUP);
1658
1659 --Disable unnecessary autostats on the table
1660 CREATE STATISTICS s_database_name ON #locks (database_name)
1661 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1662 CREATE STATISTICS s_object_id ON #locks (object_id)
1663 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1664 CREATE STATISTICS s_hobt_id ON #locks (hobt_id)
1665 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1666 CREATE STATISTICS s_allocation_unit_id ON #locks (allocation_unit_id)
1667 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1668 CREATE STATISTICS s_index_id ON #locks (index_id)
1669 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1670 CREATE STATISTICS s_schema_id ON #locks (schema_id)
1671 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1672 CREATE STATISTICS s_principal_id ON #locks (principal_id)
1673 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1674 CREATE STATISTICS s_request_id ON #locks (request_id)
1675 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1676 CREATE STATISTICS s_start_time ON #locks (start_time)
1677 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1678 CREATE STATISTICS s_resource_type ON #locks (resource_type)
1679 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1680 CREATE STATISTICS s_object_name ON #locks (object_name)
1681 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1682 CREATE STATISTICS s_schema_name ON #locks (schema_name)
1683 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1684 CREATE STATISTICS s_page_type ON #locks (page_type)
1685 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1686 CREATE STATISTICS s_request_mode ON #locks (request_mode)
1687 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1688 CREATE STATISTICS s_request_status ON #locks (request_status)
1689 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1690 CREATE STATISTICS s_resource_description ON #locks (resource_description)
1691 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1692 CREATE STATISTICS s_index_name ON #locks (index_name)
1693 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1694 CREATE STATISTICS s_principal_name ON #locks (principal_name)
1695 WITH SAMPLE 0 ROWS, NORECOMPUTE;
1696 END;
1697
1698 DECLARE @sql VARCHAR(MAX)
1699 , @sql_n NVARCHAR(MAX);
1700
1701 SET @sql =
1702 CONVERT(VARCHAR(MAX), '') +
1703 'DECLARE @blocker BIT;
1704 SET @blocker = 0;
1705 DECLARE @i INT;
1706 SET @i = 2147483647;
1707
1708 DECLARE @sessions TABLE
1709 (
1710 session_id SMALLINT NOT NULL,
1711 request_id INT NOT NULL,
1712 login_time DATETIME,
1713 last_request_end_time DATETIME,
1714 status VARCHAR(30),
1715 statement_start_offset INT,
1716 statement_end_offset INT,
1717 sql_handle BINARY(20),
1718 host_name NVARCHAR(128),
1719 login_name NVARCHAR(128),
1720 program_name NVARCHAR(128),
1721 database_id SMALLINT,
1722 memory_usage INT,
1723 open_tran_count SMALLINT,
1724 ' +
1725 CASE WHEN (
1726 @get_task_info <> 0
1727 OR @find_block_leaders = 1
1728 ) THEN 'wait_type NVARCHAR(32),
1729 wait_resource NVARCHAR(256),
1730 wait_time BIGINT,
1731 '
1732 ELSE '' END +
1733 'blocked SMALLINT,
1734 is_user_process BIT,
1735 cmd VARCHAR(32),
1736 PRIMARY KEY CLUSTERED (session_id, request_id) WITH (IGNORE_DUP_KEY = ON)
1737 );
1738
1739 DECLARE @blockers TABLE
1740 (
1741 session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON)
1742 );
1743
1744 BLOCKERS:;
1745
1746 INSERT @sessions
1747 (
1748 session_id,
1749 request_id,
1750 login_time,
1751 last_request_end_time,
1752 status,
1753 statement_start_offset,
1754 statement_end_offset,
1755 sql_handle,
1756 host_name,
1757 login_name,
1758 program_name,
1759 database_id,
1760 memory_usage,
1761 open_tran_count,
1762 ' +
1763 CASE WHEN (
1764 @get_task_info <> 0
1765 OR @find_block_leaders = 1
1766 ) THEN 'wait_type,
1767 wait_resource,
1768 wait_time,
1769 '
1770 ELSE '' END +
1771 'blocked,
1772 is_user_process,
1773 cmd
1774 )
1775 SELECT TOP(@i)
1776 spy.session_id,
1777 spy.request_id,
1778 spy.login_time,
1779 spy.last_request_end_time,
1780 spy.status,
1781 spy.statement_start_offset,
1782 spy.statement_end_offset,
1783 spy.sql_handle,
1784 spy.host_name,
1785 spy.login_name,
1786 spy.program_name,
1787 spy.database_id,
1788 spy.memory_usage,
1789 spy.open_tran_count,
1790 ' +
1791 CASE WHEN (
1792 @get_task_info <> 0
1793 OR @find_block_leaders = 1
1794 ) THEN 'spy.wait_type,
1795 CASE
1796 WHEN
1797 spy.wait_type LIKE N''PAGE%LATCH_%''
1798 OR spy.wait_type = N''CXPACKET''
1799 OR spy.wait_type LIKE N''LATCH[_]%''
1800 OR spy.wait_type = N''OLEDB'' THEN
1801 spy.wait_resource
1802 ELSE
1803 NULL
1804 END AS wait_resource,
1805 spy.wait_time,
1806 '
1807 ELSE '' END +
1808 'spy.blocked,
1809 spy.is_user_process,
1810 spy.cmd
1811 FROM
1812 (
1813 SELECT TOP(@i)
1814 spx.*,
1815 ' +
1816 CASE WHEN (
1817 @get_task_info <> 0
1818 OR @find_block_leaders = 1
1819 ) THEN 'ROW_NUMBER() OVER
1820 (
1821 PARTITION BY
1822 spx.session_id,
1823 spx.request_id
1824 ORDER BY
1825 CASE
1826 WHEN spx.wait_type LIKE N''LCK[_]%'' THEN
1827 1
1828 ELSE
1829 99
1830 END,
1831 spx.wait_time DESC,
1832 spx.blocked DESC
1833 ) AS r
1834 '
1835 ELSE '1 AS r
1836 ' END +
1837 'FROM
1838 (
1839 SELECT TOP(@i)
1840 sp0.session_id,
1841 sp0.request_id,
1842 sp0.login_time,
1843 sp0.last_request_end_time,
1844 LOWER(sp0.status) AS status,
1845 CASE
1846 WHEN sp0.cmd = ''CREATE INDEX'' THEN
1847 0
1848 ELSE
1849 sp0.stmt_start
1850 END AS statement_start_offset,
1851 CASE
1852 WHEN sp0.cmd = N''CREATE INDEX'' THEN
1853 -1
1854 ELSE
1855 COALESCE(NULLIF(sp0.stmt_end, 0), -1)
1856 END AS statement_end_offset,
1857 sp0.sql_handle,
1858 sp0.host_name,
1859 sp0.login_name,
1860 sp0.program_name,
1861 sp0.database_id,
1862 sp0.memory_usage,
1863 sp0.open_tran_count,
1864 ' +
1865 CASE WHEN (
1866 @get_task_info <> 0
1867 OR @find_block_leaders = 1
1868 ) THEN 'CASE
1869 WHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN
1870 sp0.wait_type
1871 ELSE
1872 NULL
1873 END AS wait_type,
1874 CASE
1875 WHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN
1876 sp0.wait_resource
1877 ELSE
1878 NULL
1879 END AS wait_resource,
1880 CASE
1881 WHEN sp0.wait_type <> N''CXPACKET'' THEN
1882 sp0.wait_time
1883 ELSE
1884 0
1885 END AS wait_time,
1886 '
1887 ELSE '' END +
1888 'sp0.blocked,
1889 sp0.is_user_process,
1890 sp0.cmd
1891 FROM
1892 (
1893 SELECT TOP(@i)
1894 sp1.session_id,
1895 sp1.request_id,
1896 sp1.login_time,
1897 sp1.last_request_end_time,
1898 sp1.status,
1899 sp1.cmd,
1900 sp1.stmt_start,
1901 sp1.stmt_end,
1902 MAX(NULLIF(sp1.sql_handle, 0x00)) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS sql_handle,
1903 sp1.host_name,
1904 MAX(sp1.login_name) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS login_name,
1905 sp1.program_name,
1906 sp1.database_id,
1907 MAX(sp1.memory_usage) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS memory_usage,
1908 MAX(sp1.open_tran_count) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS open_tran_count,
1909 sp1.wait_type,
1910 sp1.wait_resource,
1911 sp1.wait_time,
1912 sp1.blocked,
1913 sp1.hostprocess,
1914 sp1.is_user_process
1915 FROM
1916 (
1917 SELECT TOP(@i)
1918 sp2.spid AS session_id,
1919 CASE sp2.status
1920 WHEN ''sleeping'' THEN
1921 CONVERT(INT, 0)
1922 ELSE
1923 sp2.request_id
1924 END AS request_id,
1925 MAX(sp2.login_time) AS login_time,
1926 MAX(sp2.last_batch) AS last_request_end_time,
1927 MAX(CONVERT(VARCHAR(30), RTRIM(sp2.status)) COLLATE Latin1_General_Bin2) AS status,
1928 MAX(CONVERT(VARCHAR(32), RTRIM(sp2.cmd)) COLLATE Latin1_General_Bin2) AS cmd,
1929 MAX(sp2.stmt_start) AS stmt_start,
1930 MAX(sp2.stmt_end) AS stmt_end,
1931 MAX(sp2.sql_handle) AS sql_handle,
1932 MAX(CONVERT(sysname, RTRIM(sp2.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS host_name,
1933 MAX(CONVERT(sysname, RTRIM(sp2.loginame)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS login_name,
1934 MAX
1935 (
1936 CASE
1937 WHEN blk.queue_id IS NOT NULL THEN
1938 N''Service Broker
1939 database_id: '' + CONVERT(NVARCHAR, blk.database_id) +
1940 N'' queue_id: '' + CONVERT(NVARCHAR, blk.queue_id)
1941 ELSE
1942 CONVERT
1943 (
1944 sysname,
1945 RTRIM(sp2.program_name)
1946 )
1947 END COLLATE SQL_Latin1_General_CP1_CI_AS
1948 ) AS program_name,
1949 MAX(sp2.dbid) AS database_id,
1950 MAX(sp2.memusage) AS memory_usage,
1951 MAX(sp2.open_tran) AS open_tran_count,
1952 RTRIM(sp2.lastwaittype) AS wait_type,
1953 RTRIM(sp2.waitresource) AS wait_resource,
1954 MAX(sp2.waittime) AS wait_time,
1955 COALESCE(NULLIF(sp2.blocked, sp2.spid), 0) AS blocked,
1956 MAX
1957 (
1958 CASE
1959 WHEN blk.session_id = sp2.spid THEN
1960 ''blocker''
1961 ELSE
1962 RTRIM(sp2.hostprocess)
1963 END
1964 ) AS hostprocess,
1965 CONVERT
1966 (
1967 BIT,
1968 MAX
1969 (
1970 CASE
1971 WHEN sp2.hostprocess > '''' THEN
1972 1
1973 ELSE
1974 0
1975 END
1976 )
1977 ) AS is_user_process
1978 FROM
1979 (
1980 SELECT TOP(@i)
1981 session_id,
1982 CONVERT(INT, NULL) AS queue_id,
1983 CONVERT(INT, NULL) AS database_id
1984 FROM @blockers
1985
1986 UNION ALL
1987
1988 SELECT TOP(@i)
1989 CONVERT(SMALLINT, 0),
1990 CONVERT(INT, NULL) AS queue_id,
1991 CONVERT(INT, NULL) AS database_id
1992 WHERE
1993 @blocker = 0
1994
1995 UNION ALL
1996
1997 SELECT TOP(@i)
1998 CONVERT(SMALLINT, spid),
1999 queue_id,
2000 database_id
2001 FROM sys.dm_broker_activated_tasks
2002 WHERE
2003 @blocker = 0
2004 ) AS blk
2005 INNER JOIN sys.sysprocesses AS sp2 ON
2006 sp2.spid = blk.session_id
2007 OR
2008 (
2009 blk.session_id = 0
2010 AND @blocker = 0
2011 )
2012 ' +
2013 CASE WHEN (
2014 @get_task_info = 0
2015 AND @find_block_leaders = 0
2016 ) THEN 'WHERE
2017 sp2.ecid = 0
2018 '
2019 ELSE '' END +
2020 'GROUP BY
2021 sp2.spid,
2022 CASE sp2.status
2023 WHEN ''sleeping'' THEN
2024 CONVERT(INT, 0)
2025 ELSE
2026 sp2.request_id
2027 END,
2028 RTRIM(sp2.lastwaittype),
2029 RTRIM(sp2.waitresource),
2030 COALESCE(NULLIF(sp2.blocked, sp2.spid), 0)
2031 ) AS sp1
2032 ) AS sp0
2033 WHERE
2034 @blocker = 1
2035 OR
2036 (1=1
2037 ' +
2038 --inclusive filter
2039 CASE WHEN @filter <> '' THEN CASE @filter_type WHEN 'session' THEN CASE WHEN CONVERT(SMALLINT, @filter) <> 0 THEN 'AND sp0.session_id = CONVERT(SMALLINT, @filter)
2040 '
2041 ELSE '' END
2042 WHEN 'program' THEN 'AND sp0.program_name LIKE @filter
2043 '
2044 WHEN 'login' THEN 'AND sp0.login_name LIKE @filter
2045 '
2046 WHEN 'host' THEN 'AND sp0.host_name LIKE @filter
2047 '
2048 WHEN 'database' THEN 'AND DB_NAME(sp0.database_id) LIKE @filter
2049 '
2050 ELSE '' END
2051 ELSE '' END +
2052 --exclusive filter
2053 CASE WHEN @not_filter <> '' THEN CASE @not_filter_type WHEN 'session' THEN CASE WHEN CONVERT(SMALLINT, @not_filter) <> 0 THEN 'AND sp0.session_id <> CONVERT(SMALLINT, @not_filter)
2054 '
2055 ELSE '' END
2056 WHEN 'program' THEN 'AND sp0.program_name NOT LIKE @not_filter
2057 '
2058 WHEN 'login' THEN 'AND sp0.login_name NOT LIKE @not_filter
2059 '
2060 WHEN 'host' THEN 'AND sp0.host_name NOT LIKE @not_filter
2061 '
2062 WHEN 'database' THEN 'AND DB_NAME(sp0.database_id) NOT LIKE @not_filter
2063 '
2064 ELSE '' END
2065 ELSE '' END +
2066 CASE @show_own_spid WHEN 1 THEN ''
2067 ELSE 'AND sp0.session_id <> @@spid
2068 ' END +
2069 CASE WHEN @show_system_spids = 0 THEN 'AND sp0.hostprocess > ''''
2070 '
2071 ELSE '' END +
2072 CASE @show_sleeping_spids WHEN 0 THEN 'AND sp0.status <> ''sleeping''
2073 '
2074 WHEN 1 THEN 'AND
2075 (
2076 sp0.status <> ''sleeping''
2077 OR sp0.open_tran_count > 0
2078 )
2079 '
2080 ELSE '' END +
2081 ')
2082 ) AS spx
2083 ) AS spy
2084 WHERE
2085 spy.r = 1;
2086 ' +
2087 CASE @recursion WHEN 1 THEN 'IF @@ROWCOUNT > 0
2088 BEGIN;
2089 INSERT @blockers
2090 (
2091 session_id
2092 )
2093 SELECT TOP(@i)
2094 blocked
2095 FROM @sessions
2096 WHERE
2097 NULLIF(blocked, 0) IS NOT NULL
2098
2099 EXCEPT
2100
2101 SELECT TOP(@i)
2102 session_id
2103 FROM @sessions;
2104 ' +
2105
2106 CASE WHEN (
2107 @get_task_info > 0
2108 OR @find_block_leaders = 1
2109 ) THEN 'IF @@ROWCOUNT > 0
2110 BEGIN;
2111 SET @blocker = 1;
2112 GOTO BLOCKERS;
2113 END;
2114 '
2115 ELSE '' END +
2116 'END;
2117 '
2118 ELSE '' END +
2119 'SELECT TOP(@i)
2120 @recursion AS recursion,
2121 x.session_id,
2122 x.request_id,
2123 DENSE_RANK() OVER
2124 (
2125 ORDER BY
2126 x.session_id
2127 ) AS session_number,
2128 ' +
2129 CASE WHEN @output_column_list LIKE '%|[dd hh:mm:ss.mss|]%' ESCAPE '|' THEN 'x.elapsed_time '
2130 ELSE '0 ' END +
2131 'AS elapsed_time,
2132 ' +
2133 CASE WHEN (
2134 @output_column_list LIKE '%|[dd hh:mm:ss.mss (avg)|]%' ESCAPE '|' OR
2135 @output_column_list LIKE '%|[avg_elapsed_time|]%' ESCAPE '|'
2136 )
2137 AND @recursion = 1 THEN 'x.avg_elapsed_time / 1000 '
2138 ELSE 'NULL ' END +
2139 'AS avg_elapsed_time,
2140 ' +
2141 CASE WHEN @output_column_list LIKE '%|[physical_io|]%' ESCAPE '|'
2142 OR @output_column_list LIKE '%|[physical_io_delta|]%' ESCAPE '|' THEN 'x.physical_io '
2143 ELSE 'NULL ' END +
2144 'AS physical_io,
2145 ' +
2146 CASE WHEN @output_column_list LIKE '%|[reads|]%' ESCAPE '|'
2147 OR @output_column_list LIKE '%|[reads_delta|]%' ESCAPE '|' THEN 'x.reads '
2148 ELSE '0 ' END +
2149 'AS reads,
2150 ' +
2151 CASE WHEN @output_column_list LIKE '%|[physical_reads|]%' ESCAPE '|'
2152 OR @output_column_list LIKE '%|[physical_reads_delta|]%' ESCAPE '|' THEN 'x.physical_reads '
2153 ELSE '0 ' END +
2154 'AS physical_reads,
2155 ' +
2156 CASE WHEN @output_column_list LIKE '%|[writes|]%' ESCAPE '|'
2157 OR @output_column_list LIKE '%|[writes_delta|]%' ESCAPE '|' THEN 'x.writes '
2158 ELSE '0 ' END +
2159 'AS writes,
2160 ' +
2161 CASE WHEN @output_column_list LIKE '%|[tempdb_allocations|]%' ESCAPE '|'
2162 OR @output_column_list LIKE '%|[tempdb_allocations_delta|]%' ESCAPE '|' THEN 'x.tempdb_allocations '
2163 ELSE '0 ' END +
2164 'AS tempdb_allocations,
2165 ' +
2166 CASE WHEN @output_column_list LIKE '%|[tempdb_current|]%' ESCAPE '|'
2167 OR @output_column_list LIKE '%|[tempdb_current_delta|]%' ESCAPE '|' THEN 'x.tempdb_current '
2168 ELSE '0 ' END +
2169 'AS tempdb_current,
2170 ' +
2171 CASE WHEN @output_column_list LIKE '%|[CPU|]%' ESCAPE '|'
2172 OR @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|' THEN 'x.CPU '
2173 ELSE '0 ' END +
2174 'AS CPU,
2175 ' +
2176 CASE WHEN @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
2177 AND @get_task_info = 2
2178 AND @sys_info = 1 THEN 'x.thread_CPU_snapshot '
2179 ELSE '0 ' END +
2180 'AS thread_CPU_snapshot,
2181 ' +
2182 CASE WHEN @output_column_list LIKE '%|[context_switches|]%' ESCAPE '|'
2183 OR @output_column_list LIKE '%|[context_switches_delta|]%' ESCAPE '|' THEN 'x.context_switches '
2184 ELSE 'NULL ' END +
2185 'AS context_switches,
2186 ' +
2187 CASE WHEN @output_column_list LIKE '%|[used_memory|]%' ESCAPE '|'
2188 OR @output_column_list LIKE '%|[used_memory_delta|]%' ESCAPE '|' THEN 'x.used_memory '
2189 ELSE '0 ' END +
2190 'AS used_memory,
2191 ' +
2192 CASE WHEN @output_column_list LIKE '%|[tasks|]%' ESCAPE '|'
2193 AND @recursion = 1 THEN 'x.tasks '
2194 ELSE 'NULL ' END +
2195 'AS tasks,
2196 ' +
2197 CASE WHEN (
2198 @output_column_list LIKE '%|[status|]%' ESCAPE '|'
2199 OR @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'
2200 )
2201 AND @recursion = 1 THEN 'x.status '
2202 ELSE ''''' ' END +
2203 'AS status,
2204 ' +
2205 CASE WHEN @output_column_list LIKE '%|[wait_info|]%' ESCAPE '|'
2206 AND @recursion = 1 THEN CASE @get_task_info WHEN 2 THEN 'COALESCE(x.task_wait_info, x.sys_wait_info) '
2207 ELSE 'x.sys_wait_info ' END
2208 ELSE 'NULL ' END +
2209 'AS wait_info,
2210 ' +
2211 CASE WHEN (
2212 @output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|'
2213 OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|'
2214 )
2215 AND @recursion = 1 THEN 'x.transaction_id '
2216 ELSE 'NULL ' END +
2217 'AS transaction_id,
2218 ' +
2219 CASE WHEN @output_column_list LIKE '%|[open_tran_count|]%' ESCAPE '|'
2220 AND @recursion = 1 THEN 'x.open_tran_count '
2221 ELSE 'NULL ' END +
2222 'AS open_tran_count,
2223 ' +
2224 CASE WHEN @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
2225 AND @recursion = 1 THEN 'x.sql_handle '
2226 ELSE 'NULL ' END +
2227 'AS sql_handle,
2228 ' +
2229 CASE WHEN (
2230 @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
2231 OR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
2232 )
2233 AND @recursion = 1 THEN 'x.statement_start_offset '
2234 ELSE 'NULL ' END +
2235 'AS statement_start_offset,
2236 ' +
2237 CASE WHEN (
2238 @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
2239 OR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
2240 )
2241 AND @recursion = 1 THEN 'x.statement_end_offset '
2242 ELSE 'NULL ' END +
2243 'AS statement_end_offset,
2244 ' +
2245 'NULL AS sql_text,
2246 ' +
2247 CASE WHEN @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
2248 AND @recursion = 1 THEN 'x.plan_handle '
2249 ELSE 'NULL ' END +
2250 'AS plan_handle,
2251 ' +
2252 CASE WHEN @output_column_list LIKE '%|[blocking_session_id|]%' ESCAPE '|'
2253 AND @recursion = 1 THEN 'NULLIF(x.blocking_session_id, 0) '
2254 ELSE 'NULL ' END +
2255 'AS blocking_session_id,
2256 ' +
2257 CASE WHEN @output_column_list LIKE '%|[percent_complete|]%' ESCAPE '|'
2258 AND @recursion = 1 THEN 'x.percent_complete '
2259 ELSE 'NULL ' END +
2260 'AS percent_complete,
2261 ' +
2262 CASE WHEN @output_column_list LIKE '%|[host_name|]%' ESCAPE '|'
2263 AND @recursion = 1 THEN 'x.host_name '
2264 ELSE ''''' ' END +
2265 'AS host_name,
2266 ' +
2267 CASE WHEN @output_column_list LIKE '%|[login_name|]%' ESCAPE '|'
2268 AND @recursion = 1 THEN 'x.login_name '
2269 ELSE ''''' ' END +
2270 'AS login_name,
2271 ' +
2272 CASE WHEN @output_column_list LIKE '%|[database_name|]%' ESCAPE '|'
2273 AND @recursion = 1 THEN 'DB_NAME(x.database_id) '
2274 ELSE 'NULL ' END +
2275 'AS database_name,
2276 ' +
2277 CASE WHEN @output_column_list LIKE '%|[program_name|]%' ESCAPE '|'
2278 AND @recursion = 1 THEN 'x.program_name '
2279 ELSE ''''' ' END +
2280 'AS program_name,
2281 ' +
2282 CASE WHEN @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
2283 AND @recursion = 1 THEN '(
2284 SELECT TOP(@i)
2285 x.text_size,
2286 x.language,
2287 x.date_format,
2288 x.date_first,
2289 CASE x.quoted_identifier
2290 WHEN 0 THEN ''OFF''
2291 WHEN 1 THEN ''ON''
2292 END AS quoted_identifier,
2293 CASE x.arithabort
2294 WHEN 0 THEN ''OFF''
2295 WHEN 1 THEN ''ON''
2296 END AS arithabort,
2297 CASE x.ansi_null_dflt_on
2298 WHEN 0 THEN ''OFF''
2299 WHEN 1 THEN ''ON''
2300 END AS ansi_null_dflt_on,
2301 CASE x.ansi_defaults
2302 WHEN 0 THEN ''OFF''
2303 WHEN 1 THEN ''ON''
2304 END AS ansi_defaults,
2305 CASE x.ansi_warnings
2306 WHEN 0 THEN ''OFF''
2307 WHEN 1 THEN ''ON''
2308 END AS ansi_warnings,
2309 CASE x.ansi_padding
2310 WHEN 0 THEN ''OFF''
2311 WHEN 1 THEN ''ON''
2312 END AS ansi_padding,
2313 CASE ansi_nulls
2314 WHEN 0 THEN ''OFF''
2315 WHEN 1 THEN ''ON''
2316 END AS ansi_nulls,
2317 CASE x.concat_null_yields_null
2318 WHEN 0 THEN ''OFF''
2319 WHEN 1 THEN ''ON''
2320 END AS concat_null_yields_null,
2321 CASE x.transaction_isolation_level
2322 WHEN 0 THEN ''Unspecified''
2323 WHEN 1 THEN ''ReadUncomitted''
2324 WHEN 2 THEN ''ReadCommitted''
2325 WHEN 3 THEN ''Repeatable''
2326 WHEN 4 THEN ''Serializable''
2327 WHEN 5 THEN ''Snapshot''
2328 END AS transaction_isolation_level,
2329 x.lock_timeout,
2330 x.deadlock_priority,
2331 x.row_count,
2332 x.command_type,
2333 ' +
2334 CASE WHEN OBJECT_ID('master.dbo.fn_varbintohexstr') IS NOT NULL THEN 'master.dbo.fn_varbintohexstr(x.sql_handle) AS sql_handle,
2335 master.dbo.fn_varbintohexstr(x.plan_handle) AS plan_handle,'
2336 ELSE 'CONVERT(VARCHAR(256), x.sql_handle, 1) AS sql_handle,
2337 CONVERT(VARCHAR(256), x.plan_handle, 1) AS plan_handle,' END +
2338 '
2339 ' +
2340 CASE WHEN @output_column_list LIKE '%|[program_name|]%' ESCAPE '|' THEN '(
2341 SELECT TOP(1)
2342 CONVERT(uniqueidentifier, CONVERT(XML, '''').value(''xs:hexBinary( substring(sql:column("agent_info.job_id_string"), 0) )'', ''binary(16)'')) AS job_id,
2343 agent_info.step_id,
2344 (
2345 SELECT TOP(1)
2346 NULL
2347 FOR XML
2348 PATH(''job_name''),
2349 TYPE
2350 ),
2351 (
2352 SELECT TOP(1)
2353 NULL
2354 FOR XML
2355 PATH(''step_name''),
2356 TYPE
2357 )
2358 FROM
2359 (
2360 SELECT TOP(1)
2361 SUBSTRING(x.program_name, CHARINDEX(''0x'', x.program_name) + 2, 32) AS job_id_string,
2362 SUBSTRING(x.program_name, CHARINDEX('': Step '', x.program_name) + 7, CHARINDEX('')'', x.program_name, CHARINDEX('': Step '', x.program_name)) - (CHARINDEX('': Step '', x.program_name) + 7)) AS step_id
2363 WHERE
2364 x.program_name LIKE N''SQLAgent - TSQL JobStep (Job 0x%''
2365 ) AS agent_info
2366 FOR XML
2367 PATH(''agent_job_info''),
2368 TYPE
2369 ),
2370 '
2371 ELSE '' END +
2372 CASE WHEN @get_task_info = 2 THEN 'CONVERT(XML, x.block_info) AS block_info,
2373 '
2374 ELSE '' END + '
2375 x.host_process_id,
2376 x.group_id
2377 FOR XML
2378 PATH(''additional_info''),
2379 TYPE
2380 ) '
2381 ELSE 'NULL ' END +
2382 'AS additional_info,
2383 x.start_time,
2384 ' +
2385 CASE WHEN @output_column_list LIKE '%|[login_time|]%' ESCAPE '|'
2386 AND @recursion = 1 THEN 'x.login_time '
2387 ELSE 'NULL ' END +
2388 'AS login_time,
2389 x.last_request_start_time
2390 FROM
2391 (
2392 SELECT TOP(@i)
2393 y.*,
2394 CASE
2395 WHEN DATEDIFF(hour, y.start_time, GETDATE()) > 576 THEN
2396 DATEDIFF(second, GETDATE(), y.start_time)
2397 ELSE DATEDIFF(ms, y.start_time, GETDATE())
2398 END AS elapsed_time,
2399 COALESCE(tempdb_info.tempdb_allocations, 0) AS tempdb_allocations,
2400 COALESCE
2401 (
2402 CASE
2403 WHEN tempdb_info.tempdb_current < 0 THEN 0
2404 ELSE tempdb_info.tempdb_current
2405 END,
2406 0
2407 ) AS tempdb_current,
2408 ' +
2409 CASE WHEN (
2410 @get_task_info <> 0
2411 OR @find_block_leaders = 1
2412 ) THEN 'N''('' + CONVERT(NVARCHAR, y.wait_duration_ms) + N''ms)'' +
2413 y.wait_type +
2414 CASE
2415 WHEN y.wait_type LIKE N''PAGE%LATCH_%'' THEN
2416 N'':'' +
2417 COALESCE(DB_NAME(CONVERT(INT, LEFT(y.resource_description, CHARINDEX(N'':'', y.resource_description) - 1))), N''(null)'') +
2418 N'':'' +
2419 SUBSTRING(y.resource_description, CHARINDEX(N'':'', y.resource_description) + 1, LEN(y.resource_description) - CHARINDEX(N'':'', REVERSE(y.resource_description)) - CHARINDEX(N'':'', y.resource_description)) +
2420 N''('' +
2421 CASE
2422 WHEN
2423 CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 1 OR
2424 CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 8088 = 0
2425 THEN
2426 N''PFS''
2427 WHEN
2428 CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 2 OR
2429 CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 511232 = 0
2430 THEN
2431 N''GAM''
2432 WHEN
2433 CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 3 OR
2434 (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 1) % 511232 = 0
2435 THEN
2436 N''SGAM''
2437 WHEN
2438 CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 6 OR
2439 (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 6) % 511232 = 0
2440 THEN
2441 N''DCM''
2442 WHEN
2443 CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 7 OR
2444 (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 7) % 511232 = 0
2445 THEN
2446 N''BCM''
2447 ELSE
2448 N''*''
2449 END +
2450 N'')''
2451 WHEN y.wait_type = N''CXPACKET'' THEN
2452 N'':'' + SUBSTRING(y.resource_description, CHARINDEX(N''nodeId'', y.resource_description) + 7, 4)
2453 WHEN y.wait_type LIKE N''LATCH[_]%'' THEN
2454 N'' ['' + LEFT(y.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', y.resource_description), 0), LEN(y.resource_description) + 1) - 1) + N'']''
2455 WHEN
2456 y.wait_type = N''OLEDB''
2457 AND y.resource_description LIKE N''%(SPID=%)'' THEN
2458 N''['' + LEFT(y.resource_description, CHARINDEX(N''(SPID='', y.resource_description) - 2) +
2459 N'':'' + SUBSTRING(y.resource_description, CHARINDEX(N''(SPID='', y.resource_description) + 6, CHARINDEX(N'')'', y.resource_description, (CHARINDEX(N''(SPID='', y.resource_description) + 6)) - (CHARINDEX(N''(SPID='', y.resource_description) + 6)) + '']''
2460 ELSE
2461 N''''
2462 END COLLATE Latin1_General_Bin2 AS sys_wait_info,
2463 '
2464 ELSE '' END +
2465 CASE WHEN @get_task_info = 2 THEN 'tasks.physical_io,
2466 tasks.context_switches,
2467 tasks.tasks,
2468 tasks.block_info,
2469 tasks.wait_info AS task_wait_info,
2470 tasks.thread_CPU_snapshot,
2471 '
2472 ELSE '' END +
2473 CASE WHEN NOT (@get_avg_time = 1 AND @recursion = 1) THEN 'CONVERT(INT, NULL) '
2474 ELSE 'qs.total_elapsed_time / qs.execution_count ' END +
2475 'AS avg_elapsed_time
2476 FROM
2477 (
2478 SELECT TOP(@i)
2479 sp.session_id,
2480 sp.request_id,
2481 COALESCE(r.logical_reads, s.logical_reads) AS reads,
2482 COALESCE(r.reads, s.reads) AS physical_reads,
2483 COALESCE(r.writes, s.writes) AS writes,
2484 COALESCE(r.CPU_time, s.CPU_time) AS CPU,
2485 sp.memory_usage + COALESCE(r.granted_query_memory, 0) AS used_memory,
2486 LOWER(sp.status) AS status,
2487 COALESCE(r.sql_handle, sp.sql_handle) AS sql_handle,
2488 COALESCE(r.statement_start_offset, sp.statement_start_offset) AS statement_start_offset,
2489 COALESCE(r.statement_end_offset, sp.statement_end_offset) AS statement_end_offset,
2490 ' +
2491 CASE WHEN (
2492 @get_task_info <> 0
2493 OR @find_block_leaders = 1
2494 ) THEN 'sp.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
2495 sp.wait_resource COLLATE Latin1_General_Bin2 AS resource_description,
2496 sp.wait_time AS wait_duration_ms,
2497 '
2498 ELSE '' END +
2499 'NULLIF(sp.blocked, 0) AS blocking_session_id,
2500 r.plan_handle,
2501 NULLIF(r.percent_complete, 0) AS percent_complete,
2502 sp.host_name,
2503 sp.login_name,
2504 sp.program_name,
2505 s.host_process_id,
2506 COALESCE(r.text_size, s.text_size) AS text_size,
2507 COALESCE(r.language, s.language) AS language,
2508 COALESCE(r.date_format, s.date_format) AS date_format,
2509 COALESCE(r.date_first, s.date_first) AS date_first,
2510 COALESCE(r.quoted_identifier, s.quoted_identifier) AS quoted_identifier,
2511 COALESCE(r.arithabort, s.arithabort) AS arithabort,
2512 COALESCE(r.ansi_null_dflt_on, s.ansi_null_dflt_on) AS ansi_null_dflt_on,
2513 COALESCE(r.ansi_defaults, s.ansi_defaults) AS ansi_defaults,
2514 COALESCE(r.ansi_warnings, s.ansi_warnings) AS ansi_warnings,
2515 COALESCE(r.ansi_padding, s.ansi_padding) AS ansi_padding,
2516 COALESCE(r.ansi_nulls, s.ansi_nulls) AS ansi_nulls,
2517 COALESCE(r.concat_null_yields_null, s.concat_null_yields_null) AS concat_null_yields_null,
2518 COALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level,
2519 COALESCE(r.lock_timeout, s.lock_timeout) AS lock_timeout,
2520 COALESCE(r.deadlock_priority, s.deadlock_priority) AS deadlock_priority,
2521 COALESCE(r.row_count, s.row_count) AS row_count,
2522 COALESCE(r.command, sp.cmd) AS command_type,
2523 COALESCE
2524 (
2525 CASE
2526 WHEN
2527 (
2528 s.is_user_process = 0
2529 AND r.total_elapsed_time >= 0
2530 ) THEN
2531 DATEADD
2532 (
2533 ms,
2534 1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
2535 DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
2536 )
2537 END,
2538 NULLIF(COALESCE(r.start_time, sp.last_request_end_time), CONVERT(DATETIME, ''19000101'', 112)),
2539 sp.login_time
2540 ) AS start_time,
2541 sp.login_time,
2542 CASE
2543 WHEN s.is_user_process = 1 THEN
2544 s.last_request_start_time
2545 ELSE
2546 COALESCE
2547 (
2548 DATEADD
2549 (
2550 ms,
2551 1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
2552 DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
2553 ),
2554 s.last_request_start_time
2555 )
2556 END AS last_request_start_time,
2557 r.transaction_id,
2558 sp.database_id,
2559 sp.open_tran_count,
2560 ' +
2561 CASE WHEN EXISTS
2562 (
2563 SELECT *
2564 FROM sys.all_columns AS ac
2565 WHERE
2566 ac.object_id = OBJECT_ID('sys.dm_exec_sessions')
2567 AND ac.name = 'group_id'
2568 ) THEN 's.group_id'
2569 ELSE 'CONVERT(INT, NULL) AS group_id' END + '
2570 FROM @sessions AS sp
2571 LEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON
2572 s.session_id = sp.session_id
2573 AND s.login_time = sp.login_time
2574 LEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON
2575 sp.status <> ''sleeping''
2576 AND r.session_id = sp.session_id
2577 AND r.request_id = sp.request_id
2578 AND
2579 (
2580 (
2581 s.is_user_process = 0
2582 AND sp.is_user_process = 0
2583 )
2584 OR
2585 (
2586 r.start_time = s.last_request_start_time
2587 AND s.last_request_end_time <= sp.last_request_end_time
2588 )
2589 )
2590 ) AS y
2591 ' +
2592 CASE WHEN @get_task_info = 2 THEN CONVERT(VARCHAR(MAX), '') +
2593 'LEFT OUTER HASH JOIN
2594 (
2595 SELECT TOP(@i)
2596 task_nodes.task_node.value(''(session_id/text())[1]'', ''SMALLINT'') AS session_id,
2597 task_nodes.task_node.value(''(request_id/text())[1]'', ''INT'') AS request_id,
2598 task_nodes.task_node.value(''(physical_io/text())[1]'', ''BIGINT'') AS physical_io,
2599 task_nodes.task_node.value(''(context_switches/text())[1]'', ''BIGINT'') AS context_switches,
2600 task_nodes.task_node.value(''(tasks/text())[1]'', ''INT'') AS tasks,
2601 task_nodes.task_node.value(''(block_info/text())[1]'', ''NVARCHAR(4000)'') AS block_info,
2602 task_nodes.task_node.value(''(waits/text())[1]'', ''NVARCHAR(4000)'') AS wait_info,
2603 task_nodes.task_node.value(''(thread_CPU_snapshot/text())[1]'', ''BIGINT'') AS thread_CPU_snapshot
2604 FROM
2605 (
2606 SELECT TOP(@i)
2607 CONVERT
2608 (
2609 XML,
2610 REPLACE
2611 (
2612 CONVERT(NVARCHAR(MAX), tasks_raw.task_xml_raw) COLLATE Latin1_General_Bin2,
2613 N''</waits></tasks><tasks><waits>'',
2614 N'', ''
2615 )
2616 ) AS task_xml
2617 FROM
2618 (
2619 SELECT TOP(@i)
2620 CASE waits.r
2621 WHEN 1 THEN
2622 waits.session_id
2623 ELSE
2624 NULL
2625 END AS [session_id],
2626 CASE waits.r
2627 WHEN 1 THEN
2628 waits.request_id
2629 ELSE
2630 NULL
2631 END AS [request_id],
2632 CASE waits.r
2633 WHEN 1 THEN
2634 waits.physical_io
2635 ELSE
2636 NULL
2637 END AS [physical_io],
2638 CASE waits.r
2639 WHEN 1 THEN
2640 waits.context_switches
2641 ELSE
2642 NULL
2643 END AS [context_switches],
2644 CASE waits.r
2645 WHEN 1 THEN
2646 waits.thread_CPU_snapshot
2647 ELSE
2648 NULL
2649 END AS [thread_CPU_snapshot],
2650 CASE waits.r
2651 WHEN 1 THEN
2652 waits.tasks
2653 ELSE
2654 NULL
2655 END AS [tasks],
2656 CASE waits.r
2657 WHEN 1 THEN
2658 waits.block_info
2659 ELSE
2660 NULL
2661 END AS [block_info],
2662 REPLACE
2663 (
2664 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2665 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2666 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2667 CONVERT
2668 (
2669 NVARCHAR(MAX),
2670 N''('' +
2671 CONVERT(NVARCHAR, num_waits) + N''x: '' +
2672 CASE num_waits
2673 WHEN 1 THEN
2674 CONVERT(NVARCHAR, min_wait_time) + N''ms''
2675 WHEN 2 THEN
2676 CASE
2677 WHEN min_wait_time <> max_wait_time THEN
2678 CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''
2679 ELSE
2680 CONVERT(NVARCHAR, max_wait_time) + N''ms''
2681 END
2682 ELSE
2683 CASE
2684 WHEN min_wait_time <> max_wait_time THEN
2685 CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, avg_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''
2686 ELSE
2687 CONVERT(NVARCHAR, max_wait_time) + N''ms''
2688 END
2689 END +
2690 N'')'' + wait_type COLLATE Latin1_General_Bin2
2691 ),
2692 NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''),
2693 NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''),
2694 NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''),
2695 NCHAR(0),
2696 N''''
2697 ) AS [waits]
2698 FROM
2699 (
2700 SELECT TOP(@i)
2701 w1.*,
2702 ROW_NUMBER() OVER
2703 (
2704 PARTITION BY
2705 w1.session_id,
2706 w1.request_id
2707 ORDER BY
2708 w1.block_info DESC,
2709 w1.num_waits DESC,
2710 w1.wait_type
2711 ) AS r
2712 FROM
2713 (
2714 SELECT TOP(@i)
2715 task_info.session_id,
2716 task_info.request_id,
2717 task_info.physical_io,
2718 task_info.context_switches,
2719 task_info.thread_CPU_snapshot,
2720 task_info.num_tasks AS tasks,
2721 CASE
2722 WHEN task_info.runnable_time IS NOT NULL THEN
2723 ''RUNNABLE''
2724 ELSE
2725 wt2.wait_type
2726 END AS wait_type,
2727 NULLIF(COUNT(COALESCE(task_info.runnable_time, wt2.waiting_task_address)), 0) AS num_waits,
2728 MIN(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS min_wait_time,
2729 AVG(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS avg_wait_time,
2730 MAX(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS max_wait_time,
2731 MAX(wt2.block_info) AS block_info
2732 FROM
2733 (
2734 SELECT TOP(@i)
2735 t.session_id,
2736 t.request_id,
2737 SUM(CONVERT(BIGINT, t.pending_io_count)) OVER (PARTITION BY t.session_id, t.request_id) AS physical_io,
2738 SUM(CONVERT(BIGINT, t.context_switches_count)) OVER (PARTITION BY t.session_id, t.request_id) AS context_switches,
2739 ' +
2740 CASE WHEN @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
2741 AND @sys_info = 1 THEN 'SUM(tr.usermode_time + tr.kernel_time) OVER (PARTITION BY t.session_id, t.request_id) '
2742 ELSE 'CONVERT(BIGINT, NULL) ' END +
2743 ' AS thread_CPU_snapshot,
2744 COUNT(*) OVER (PARTITION BY t.session_id, t.request_id) AS num_tasks,
2745 t.task_address,
2746 t.task_state,
2747 CASE
2748 WHEN
2749 t.task_state = ''RUNNABLE''
2750 AND w.runnable_time > 0 THEN
2751 w.runnable_time
2752 ELSE
2753 NULL
2754 END AS runnable_time
2755 FROM sys.dm_os_tasks AS t
2756 CROSS APPLY
2757 (
2758 SELECT TOP(1)
2759 sp2.session_id
2760 FROM @sessions AS sp2
2761 WHERE
2762 sp2.session_id = t.session_id
2763 AND sp2.request_id = t.request_id
2764 AND sp2.status <> ''sleeping''
2765 ) AS sp20
2766 LEFT OUTER HASH JOIN
2767 (
2768 ' +
2769 CASE WHEN @sys_info = 1 THEN 'SELECT TOP(@i)
2770 (
2771 SELECT TOP(@i)
2772 ms_ticks
2773 FROM sys.dm_os_sys_info
2774 ) -
2775 w0.wait_resumed_ms_ticks AS runnable_time,
2776 w0.worker_address,
2777 w0.thread_address,
2778 w0.task_bound_ms_ticks
2779 FROM sys.dm_os_workers AS w0
2780 WHERE
2781 w0.state = ''RUNNABLE''
2782 OR @first_collection_ms_ticks >= w0.task_bound_ms_ticks'
2783 ELSE 'SELECT
2784 CONVERT(BIGINT, NULL) AS runnable_time,
2785 CONVERT(VARBINARY(8), NULL) AS worker_address,
2786 CONVERT(VARBINARY(8), NULL) AS thread_address,
2787 CONVERT(BIGINT, NULL) AS task_bound_ms_ticks
2788 WHERE
2789 1 = 0' END +
2790 '
2791 ) AS w ON
2792 w.worker_address = t.worker_address
2793 ' +
2794 CASE WHEN @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
2795 AND @sys_info = 1 THEN 'LEFT OUTER HASH JOIN sys.dm_os_threads AS tr ON
2796 tr.thread_address = w.thread_address
2797 AND @first_collection_ms_ticks >= w.task_bound_ms_ticks
2798 '
2799 ELSE '' END +
2800 ') AS task_info
2801 LEFT OUTER HASH JOIN
2802 (
2803 SELECT TOP(@i)
2804 wt1.wait_type,
2805 wt1.waiting_task_address,
2806 MAX(wt1.wait_duration_ms) AS wait_duration_ms,
2807 MAX(wt1.block_info) AS block_info
2808 FROM
2809 (
2810 SELECT DISTINCT TOP(@i)
2811 wt.wait_type +
2812 CASE
2813 WHEN wt.wait_type LIKE N''PAGE%LATCH_%'' THEN
2814 '':'' +
2815 COALESCE(DB_NAME(CONVERT(INT, LEFT(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) - 1))), N''(null)'') +
2816 N'':'' +
2817 SUBSTRING(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) + 1, LEN(wt.resource_description) - CHARINDEX(N'':'', REVERSE(wt.resource_description)) - CHARINDEX(N'':'', wt.resource_description)) +
2818 N''('' +
2819 CASE
2820 WHEN
2821 CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 1 OR
2822 CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 8088 = 0
2823 THEN
2824 N''PFS''
2825 WHEN
2826 CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 2 OR
2827 CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 511232 = 0
2828 THEN
2829 N''GAM''
2830 WHEN
2831 CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 3 OR
2832 (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 1) % 511232 = 0
2833 THEN
2834 N''SGAM''
2835 WHEN
2836 CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 6 OR
2837 (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 6) % 511232 = 0
2838 THEN
2839 N''DCM''
2840 WHEN
2841 CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 7 OR
2842 (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 7) % 511232 = 0
2843 THEN
2844 N''BCM''
2845 ELSE
2846 N''*''
2847 END +
2848 N'')''
2849 WHEN wt.wait_type = N''CXPACKET'' THEN
2850 N'':'' + SUBSTRING(wt.resource_description, CHARINDEX(N''nodeId'', wt.resource_description) + 7, 4)
2851 WHEN wt.wait_type LIKE N''LATCH[_]%'' THEN
2852 N'' ['' + LEFT(wt.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description), 0), LEN(wt.resource_description) + 1) - 1) + N'']''
2853 ELSE
2854 N''''
2855 END COLLATE Latin1_General_Bin2 AS wait_type,
2856 CASE
2857 WHEN
2858 (
2859 wt.blocking_session_id IS NOT NULL
2860 AND wt.wait_type LIKE N''LCK[_]%''
2861 ) THEN
2862 (
2863 SELECT TOP(@i)
2864 x.lock_type,
2865 REPLACE
2866 (
2867 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2868 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2869 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
2870 DB_NAME
2871 (
2872 CONVERT
2873 (
2874 INT,
2875 SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''dbid='', wt.resource_description), 0) + 5, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''dbid='', wt.resource_description) + 5), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''dbid='', wt.resource_description) - 5)
2876 )
2877 ),
2878 NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''),
2879 NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''),
2880 NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''),
2881 NCHAR(0),
2882 N''''
2883 ) AS database_name,
2884 CASE x.lock_type
2885 WHEN N''objectlock'' THEN
2886 SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''objid='', wt.resource_description), 0) + 6, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''objid='', wt.resource_description) + 6), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''objid='', wt.resource_description) - 6)
2887 ELSE
2888 NULL
2889 END AS object_id,
2890 CASE x.lock_type
2891 WHEN N''filelock'' THEN
2892 SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''fileid='', wt.resource_description), 0) + 7, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''fileid='', wt.resource_description) + 7), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''fileid='', wt.resource_description) - 7)
2893 ELSE
2894 NULL
2895 END AS file_id,
2896 CASE
2897 WHEN x.lock_type in (N''pagelock'', N''extentlock'', N''ridlock'') THEN
2898 SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''associatedObjectId='', wt.resource_description), 0) + 19, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''associatedObjectId='', wt.resource_description) + 19), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''associatedObjectId='', wt.resource_description) - 19)
2899 WHEN x.lock_type in (N''keylock'', N''hobtlock'', N''allocunitlock'') THEN
2900 SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''hobtid='', wt.resource_description), 0) + 7, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''hobtid='', wt.resource_description) + 7), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''hobtid='', wt.resource_description) - 7)
2901 ELSE
2902 NULL
2903 END AS hobt_id,
2904 CASE x.lock_type
2905 WHEN N''applicationlock'' THEN
2906 SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''hash='', wt.resource_description), 0) + 5, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''hash='', wt.resource_description) + 5), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''hash='', wt.resource_description) - 5)
2907 ELSE
2908 NULL
2909 END AS applock_hash,
2910 CASE x.lock_type
2911 WHEN N''metadatalock'' THEN
2912 SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''subresource='', wt.resource_description), 0) + 12, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''subresource='', wt.resource_description) + 12), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''subresource='', wt.resource_description) - 12)
2913 ELSE
2914 NULL
2915 END AS metadata_resource,
2916 CASE x.lock_type
2917 WHEN N''metadatalock'' THEN
2918 SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''classid='', wt.resource_description), 0) + 8, COALESCE(NULLIF(CHARINDEX(N'' dbid='', wt.resource_description) - CHARINDEX(N''classid='', wt.resource_description), 0), LEN(wt.resource_description) + 1) - 8)
2919 ELSE
2920 NULL
2921 END AS metadata_class_id
2922 FROM
2923 (
2924 SELECT TOP(1)
2925 LEFT(wt.resource_description, CHARINDEX(N'' '', wt.resource_description) - 1) COLLATE Latin1_General_Bin2 AS lock_type
2926 ) AS x
2927 FOR XML
2928 PATH('''')
2929 )
2930 ELSE NULL
2931 END AS block_info,
2932 wt.wait_duration_ms,
2933 wt.waiting_task_address
2934 FROM
2935 (
2936 SELECT TOP(@i)
2937 wt0.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
2938 wt0.resource_description COLLATE Latin1_General_Bin2 AS resource_description,
2939 wt0.wait_duration_ms,
2940 wt0.waiting_task_address,
2941 CASE
2942 WHEN wt0.blocking_session_id = p.blocked THEN
2943 wt0.blocking_session_id
2944 ELSE
2945 NULL
2946 END AS blocking_session_id
2947 FROM sys.dm_os_waiting_tasks AS wt0
2948 CROSS APPLY
2949 (
2950 SELECT TOP(1)
2951 s0.blocked
2952 FROM @sessions AS s0
2953 WHERE
2954 s0.session_id = wt0.session_id
2955 AND COALESCE(s0.wait_type, N'''') <> N''OLEDB''
2956 AND wt0.wait_type <> N''OLEDB''
2957 ) AS p
2958 ) AS wt
2959 ) AS wt1
2960 GROUP BY
2961 wt1.wait_type,
2962 wt1.waiting_task_address
2963 ) AS wt2 ON
2964 wt2.waiting_task_address = task_info.task_address
2965 AND wt2.wait_duration_ms > 0
2966 AND task_info.runnable_time IS NULL
2967 GROUP BY
2968 task_info.session_id,
2969 task_info.request_id,
2970 task_info.physical_io,
2971 task_info.context_switches,
2972 task_info.thread_CPU_snapshot,
2973 task_info.num_tasks,
2974 CASE
2975 WHEN task_info.runnable_time IS NOT NULL THEN
2976 ''RUNNABLE''
2977 ELSE
2978 wt2.wait_type
2979 END
2980 ) AS w1
2981 ) AS waits
2982 ORDER BY
2983 waits.session_id,
2984 waits.request_id,
2985 waits.r
2986 FOR XML
2987 PATH(N''tasks''),
2988 TYPE
2989 ) AS tasks_raw (task_xml_raw)
2990 ) AS tasks_final
2991 CROSS APPLY tasks_final.task_xml.nodes(N''/tasks'') AS task_nodes (task_node)
2992 WHERE
2993 task_nodes.task_node.exist(N''session_id'') = 1
2994 ) AS tasks ON
2995 tasks.session_id = y.session_id
2996 AND tasks.request_id = y.request_id
2997 '
2998 ELSE '' END +
2999 'LEFT OUTER HASH JOIN
3000 (
3001 SELECT TOP(@i)
3002 t_info.session_id,
3003 COALESCE(t_info.request_id, -1) AS request_id,
3004 SUM(t_info.tempdb_allocations) AS tempdb_allocations,
3005 SUM(t_info.tempdb_current) AS tempdb_current
3006 FROM
3007 (
3008 SELECT TOP(@i)
3009 tsu.session_id,
3010 tsu.request_id,
3011 tsu.user_objects_alloc_page_count +
3012 tsu.internal_objects_alloc_page_count AS tempdb_allocations,
3013 tsu.user_objects_alloc_page_count +
3014 tsu.internal_objects_alloc_page_count -
3015 tsu.user_objects_dealloc_page_count -
3016 tsu.internal_objects_dealloc_page_count AS tempdb_current
3017 FROM sys.dm_db_task_space_usage AS tsu
3018 CROSS APPLY
3019 (
3020 SELECT TOP(1)
3021 s0.session_id
3022 FROM @sessions AS s0
3023 WHERE
3024 s0.session_id = tsu.session_id
3025 ) AS p
3026
3027 UNION ALL
3028
3029 SELECT TOP(@i)
3030 ssu.session_id,
3031 NULL AS request_id,
3032 ssu.user_objects_alloc_page_count +
3033 ssu.internal_objects_alloc_page_count AS tempdb_allocations,
3034 ssu.user_objects_alloc_page_count +
3035 ssu.internal_objects_alloc_page_count -
3036 ssu.user_objects_dealloc_page_count -
3037 ssu.internal_objects_dealloc_page_count AS tempdb_current
3038 FROM sys.dm_db_session_space_usage AS ssu
3039 CROSS APPLY
3040 (
3041 SELECT TOP(1)
3042 s0.session_id
3043 FROM @sessions AS s0
3044 WHERE
3045 s0.session_id = ssu.session_id
3046 ) AS p
3047 ) AS t_info
3048 GROUP BY
3049 t_info.session_id,
3050 COALESCE(t_info.request_id, -1)
3051 ) AS tempdb_info ON
3052 tempdb_info.session_id = y.session_id
3053 AND tempdb_info.request_id =
3054 CASE
3055 WHEN y.status = N''sleeping'' THEN
3056 -1
3057 ELSE
3058 y.request_id
3059 END
3060 ' +
3061 CASE WHEN NOT
3062 (
3063 @get_avg_time = 1
3064 AND @recursion = 1
3065 ) THEN ''
3066 ELSE 'LEFT OUTER HASH JOIN
3067 (
3068 SELECT TOP(@i)
3069 *
3070 FROM sys.dm_exec_query_stats
3071 ) AS qs ON
3072 qs.sql_handle = y.sql_handle
3073 AND qs.plan_handle = y.plan_handle
3074 AND qs.statement_start_offset = y.statement_start_offset
3075 AND qs.statement_end_offset = y.statement_end_offset
3076 ' END +
3077 ') AS x
3078 OPTION (KEEPFIXED PLAN, OPTIMIZE FOR (@i = 1)); ';
3079
3080 SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);
3081
3082 SET @last_collection_start = GETDATE();
3083
3084 IF @recursion = -1
3085 AND @sys_info = 1
3086 BEGIN;
3087 SELECT @first_collection_ms_ticks = ms_ticks
3088 FROM sys.dm_os_sys_info;
3089 END;
3090
3091 INSERT #sessions ( recursion, session_id, request_id, session_number, elapsed_time, avg_elapsed_time, physical_io, reads, physical_reads, writes, tempdb_allocations, tempdb_current, CPU, thread_CPU_snapshot, context_switches, used_memory, tasks, status, wait_info, transaction_id, open_tran_count, sql_handle, statement_start_offset, statement_end_offset, sql_text, plan_handle, blocking_session_id, percent_complete, host_name, login_name, database_name, program_name, additional_info, start_time, login_time, last_request_start_time )
3092 EXEC sp_executesql
3093 @sql_n,
3094 N'@recursion SMALLINT, @filter sysname, @not_filter sysname, @first_collection_ms_ticks BIGINT',
3095 @recursion, @filter, @not_filter, @first_collection_ms_ticks;
3096
3097 --Collect transaction information?
3098 IF @recursion = 1
3099 AND
3100 (
3101 @output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|'
3102 OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|'
3103 )
3104 BEGIN;
3105 DECLARE @i INT;
3106 SET @i = 2147483647;
3107
3108 UPDATE s
3109 SET tran_start_time = CONVERT
3110 (
3111 DATETIME,
3112 LEFT
3113 (
3114 x.trans_info,
3115 NULLIF(CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info) - 1, -1)
3116 ),
3117 121
3118 )
3119 , tran_log_writes = RIGHT
3120 (
3121 x.trans_info,
3122 LEN(x.trans_info) - CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info)
3123 )
3124 FROM (
3125 SELECT TOP(@i) trans_nodes.trans_node.value('(session_id/text())[1]', 'SMALLINT') AS session_id
3126 , COALESCE(trans_nodes.trans_node.value('(request_id/text())[1]', 'INT'), 0) AS request_id
3127 , trans_nodes.trans_node.value('(trans_info/text())[1]', 'NVARCHAR(4000)') AS trans_info
3128 FROM (
3129 SELECT TOP(@i) CONVERT
3130 (
3131 XML,
3132 REPLACE
3133 (
3134 CONVERT(NVARCHAR(MAX), trans_raw.trans_xml_raw) COLLATE Latin1_General_Bin2,
3135 N'</trans_info></trans><trans><trans_info>', N''
3136 )
3137 )
3138 FROM (
3139 SELECT TOP(@i) CASE u_trans.r WHEN 1 THEN u_trans.session_id
3140 ELSE NULL END AS [session_id]
3141 , CASE u_trans.r WHEN 1 THEN u_trans.request_id
3142 ELSE NULL END AS [request_id]
3143 , CONVERT
3144 (
3145 NVARCHAR(MAX),
3146 CASE WHEN u_trans.database_id IS NOT NULL THEN CASE u_trans.r WHEN 1 THEN COALESCE(CONVERT(NVARCHAR, u_trans.transaction_start_time, 121) + NCHAR(254), N'')
3147 ELSE N'' END +
3148 REPLACE
3149 (
3150 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3151 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3152 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3153 CONVERT(VARCHAR(128), COALESCE(DB_NAME(u_trans.database_id), N'(null)')),
3154 NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
3155 NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
3156 NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
3157 NCHAR(0),
3158 N'?'
3159 ) +
3160 N': ' +
3161 CONVERT(NVARCHAR, u_trans.log_record_count) + N' (' + CONVERT(NVARCHAR, u_trans.log_kb_used) + N' kB)' +
3162 N','
3163 ELSE N'N/A,' END COLLATE Latin1_General_Bin2
3164 ) AS [trans_info]
3165 FROM (
3166 SELECT TOP(@i) trans.*
3167 , ROW_NUMBER() OVER
3168 (
3169 PARTITION BY
3170 trans.session_id,
3171 trans.request_id
3172 ORDER BY
3173 trans.transaction_start_time DESC
3174 ) AS r
3175 FROM (
3176 SELECT TOP(@i) session_tran_map.session_id
3177 , session_tran_map.request_id
3178 , s_tran.database_id
3179 , COALESCE(SUM(s_tran.database_transaction_log_record_count), 0) AS log_record_count
3180 , COALESCE(SUM(s_tran.database_transaction_log_bytes_used), 0) / 1024 AS log_kb_used
3181 , MIN(s_tran.database_transaction_begin_time) AS transaction_start_time
3182 FROM (
3183 SELECT TOP(@i) *
3184 FROM sys.dm_tran_active_transactions
3185 WHERE
3186 transaction_begin_time <= @last_collection_start
3187 ) AS a_tran
3188 INNER HASH JOIN (
3189 SELECT TOP(@i) *
3190 FROM sys.dm_tran_database_transactions
3191 WHERE
3192 database_id < 32767
3193 ) AS s_tran ON s_tran.transaction_id = a_tran.transaction_id
3194 LEFT OUTER HASH JOIN (
3195 SELECT TOP(@i) *
3196 FROM sys.dm_tran_session_transactions
3197 ) AS tst ON s_tran.transaction_id = tst.transaction_id
3198 CROSS APPLY (
3199 SELECT TOP(1) s3.session_id
3200 , s3.request_id
3201 FROM (
3202 SELECT TOP(1) s1.session_id
3203 , s1.request_id
3204 FROM #sessions AS s1
3205 WHERE
3206 s1.transaction_id = s_tran.transaction_id
3207 AND s1.recursion = 1
3208 UNION ALL
3209 SELECT TOP(1) s2.session_id
3210 , s2.request_id
3211 FROM #sessions AS s2
3212 WHERE
3213 s2.session_id = tst.session_id
3214 AND s2.recursion = 1
3215 ) AS s3
3216 ORDER BY s3.request_id
3217 ) AS session_tran_map
3218 GROUP BY session_tran_map.session_id
3219 , session_tran_map.request_id
3220 , s_tran.database_id
3221 ) AS trans
3222 ) AS
3223 u_trans
3224 FOR XML
3225 PATH('trans'),
3226 TYPE
3227 ) AS trans_raw (trans_xml_raw)
3228 ) AS trans_final (trans_xml)
3229 CROSS APPLY trans_final.trans_xml.nodes('/trans') AS trans_nodes (trans_node)
3230 ) AS x
3231 INNER HASH JOIN #sessions AS s ON s.session_id = x.session_id
3232 AND s.request_id = x.request_id
3233 OPTION (OPTIMIZE FOR (@i = 1));
3234 END;
3235
3236 --Variables for text and plan collection
3237 DECLARE @session_id SMALLINT
3238 , @request_id INT
3239 , @sql_handle VARBINARY(64)
3240 , @plan_handle VARBINARY(64)
3241 , @statement_start_offset INT
3242 , @statement_end_offset INT
3243 , @start_time DATETIME
3244 , @database_name sysname;
3245
3246 IF @recursion = 1
3247 AND @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
3248 BEGIN;
3249 DECLARE sql_cursor
3250 CURSOR LOCAL FAST_FORWARD
3251 FOR
3252 SELECT session_id
3253 , request_id
3254 , sql_handle
3255 , statement_start_offset
3256 , statement_end_offset
3257 FROM #sessions
3258 WHERE
3259 recursion = 1
3260 AND sql_handle IS NOT NULL
3261 OPTION (KEEPFIXED PLAN);
3262
3263 OPEN sql_cursor;
3264
3265 FETCH NEXT FROM sql_cursor
3266 INTO
3267 @session_id,
3268 @request_id,
3269 @sql_handle,
3270 @statement_start_offset,
3271 @statement_end_offset;
3272
3273 --Wait up to 5 ms for the SQL text, then give up
3274 SET LOCK_TIMEOUT 5;
3275
3276 WHILE @@FETCH_STATUS = 0
3277 BEGIN;
3278 BEGIN TRY;
3279 UPDATE s
3280 SET s.sql_text = (
3281 SELECT REPLACE
3282 (
3283 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3284 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3285 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3286 N'--' + NCHAR(13) + NCHAR(10) +
3287 CASE WHEN @get_full_inner_text = 1 THEN est.text
3288 WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN est.text
3289 WHEN SUBSTRING(est.text, (@statement_start_offset/2), 2) LIKE N'[a-zA-Z0-9][a-zA-Z0-9]' THEN est.text
3290 ELSE CASE WHEN @statement_start_offset > 0 THEN SUBSTRING
3291 (
3292 est.text,
3293 ((@statement_start_offset/2) + 1),
3294 (
3295 CASE WHEN @statement_end_offset = -1 THEN 2147483647
3296 ELSE ((@statement_end_offset - @statement_start_offset)/2) + 1
3297 END )
3298 )
3299 ELSE RTRIM(LTRIM(est.text)) END END +
3300 NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2,
3301 NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
3302 NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
3303 NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
3304 NCHAR(0),
3305 N''
3306 ) AS
3307 [processing-instruction(query)]
3308 FOR XML
3309 PATH(''),
3310 TYPE
3311 )
3312 , s.statement_start_offset = CASE WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN 0
3313 WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN 0
3314 ELSE @statement_start_offset END
3315 , s.statement_end_offset = CASE WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN -1
3316 WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN -1
3317 ELSE @statement_end_offset END
3318 FROM #sessions AS s
3319 , (
3320 SELECT TOP(1) text
3321 FROM (
3322 SELECT text
3323 , 0 AS row_num
3324 FROM sys.dm_exec_sql_text(@sql_handle)
3325 UNION ALL
3326 SELECT NULL
3327 , 1 AS row_num
3328 ) AS est0
3329 ORDER BY row_num
3330 ) AS est
3331 WHERE
3332 s.session_id = @session_id
3333 AND s.request_id = @request_id
3334 AND s.recursion = 1
3335 OPTION (KEEPFIXED PLAN);
3336 END TRY
3337 BEGIN CATCH;
3338 UPDATE s
3339 SET s.sql_text = CASE ERROR_NUMBER() WHEN 1222 THEN '<timeout_exceeded />'
3340 ELSE '<error message="' + ERROR_MESSAGE() + '" />' END
3341 FROM #sessions AS s
3342 WHERE
3343 s.session_id = @session_id
3344 AND s.request_id = @request_id
3345 AND s.recursion = 1
3346 OPTION (KEEPFIXED PLAN);
3347 END CATCH;
3348
3349 FETCH NEXT FROM sql_cursor
3350 INTO
3351 @session_id,
3352 @request_id,
3353 @sql_handle,
3354 @statement_start_offset,
3355 @statement_end_offset;
3356 END;
3357
3358 --Return this to the default
3359 SET LOCK_TIMEOUT -1;
3360
3361 CLOSE sql_cursor;
3362 DEALLOCATE sql_cursor;
3363 END;
3364
3365 IF @get_outer_command = 1
3366 AND @recursion = 1
3367 AND @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'
3368 BEGIN;
3369 DECLARE @buffer_results TABLE (
3370 EventType VARCHAR(30)
3371 , Parameters INT
3372 , EventInfo NVARCHAR(4000)
3373 , start_time DATETIME
3374 , session_number INT IDENTITY(1,1) NOT NULL PRIMARY KEY );
3375
3376 DECLARE buffer_cursor
3377 CURSOR LOCAL FAST_FORWARD
3378 FOR
3379 SELECT session_id
3380 , MAX(start_time) AS start_time
3381 FROM #sessions
3382 WHERE
3383 recursion = 1
3384 GROUP BY session_id
3385 ORDER BY session_id
3386 OPTION (KEEPFIXED PLAN);
3387
3388 OPEN buffer_cursor;
3389
3390 FETCH NEXT FROM buffer_cursor
3391 INTO
3392 @session_id,
3393 @start_time;
3394
3395 WHILE @@FETCH_STATUS = 0
3396 BEGIN;
3397 BEGIN TRY;
3398 --In SQL Server 2008, DBCC INPUTBUFFER will throw
3399 --an exception if the session no longer exists
3400 INSERT @buffer_results ( EventType, Parameters, EventInfo )
3401 EXEC sp_executesql
3402 N'DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;',
3403 N'@session_id SMALLINT',
3404 @session_id;
3405
3406 UPDATE br
3407 SET br.start_time = @start_time
3408 FROM @buffer_results AS br
3409 WHERE
3410 br.session_number =
3411 (
3412 SELECT MAX(br2.session_number)
3413 FROM @buffer_results br2
3414 );
3415 END TRY
3416 BEGIN CATCH
3417 END CATCH;
3418
3419 FETCH NEXT FROM buffer_cursor
3420 INTO
3421 @session_id,
3422 @start_time;
3423 END;
3424
3425 UPDATE s
3426 SET sql_command = (
3427 SELECT REPLACE
3428 (
3429 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3430 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3431 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3432 CONVERT
3433 (
3434 NVARCHAR(MAX),
3435 N'--' + NCHAR(13) + NCHAR(10) + br.EventInfo + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2
3436 ),
3437 NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
3438 NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
3439 NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
3440 NCHAR(0),
3441 N''
3442 ) AS [processing-instruction(query)]
3443 FROM @buffer_results AS br
3444 WHERE
3445 br.session_number = s.session_number
3446 AND br.start_time = s.start_time
3447 AND
3448 (
3449 (
3450 s.start_time = s.last_request_start_time
3451 AND EXISTS
3452 (
3453 SELECT *
3454 FROM sys.dm_exec_requests r2
3455 WHERE
3456 r2.session_id = s.session_id
3457 AND r2.request_id = s.request_id
3458 AND r2.start_time = s.start_time
3459 )
3460 )
3461 OR
3462 (
3463 s.request_id = 0
3464 AND EXISTS
3465 (
3466 SELECT *
3467 FROM sys.dm_exec_sessions s2
3468 WHERE
3469 s2.session_id = s.session_id
3470 AND s2.last_request_start_time = s.last_request_start_time
3471 )
3472 )
3473 )
3474 FOR XML
3475 PATH(''),
3476 TYPE
3477 )
3478 FROM #sessions AS s
3479 WHERE
3480 recursion = 1
3481 OPTION (KEEPFIXED PLAN);
3482
3483 CLOSE buffer_cursor;
3484 DEALLOCATE buffer_cursor;
3485 END;
3486
3487 IF @get_plans >= 1
3488 AND @recursion = 1
3489 AND @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
3490 BEGIN;
3491 DECLARE @live_plan BIT;
3492 SET @live_plan = ISNULL(CONVERT(BIT, SIGN(OBJECT_ID('sys.dm_exec_query_statistics_xml'))), 0)
3493
3494 DECLARE plan_cursor
3495 CURSOR LOCAL FAST_FORWARD
3496 FOR
3497 SELECT session_id
3498 , request_id
3499 , plan_handle
3500 , statement_start_offset
3501 , statement_end_offset
3502 FROM #sessions
3503 WHERE
3504 recursion = 1
3505 AND plan_handle IS NOT NULL
3506 OPTION (KEEPFIXED PLAN);
3507
3508 OPEN plan_cursor;
3509
3510 FETCH NEXT FROM plan_cursor
3511 INTO
3512 @session_id,
3513 @request_id,
3514 @plan_handle,
3515 @statement_start_offset,
3516 @statement_end_offset;
3517
3518 --Wait up to 5 ms for a query plan, then give up
3519 SET LOCK_TIMEOUT 5;
3520
3521 WHILE @@FETCH_STATUS = 0
3522 BEGIN;
3523 DECLARE @query_plan XML;
3524 IF @live_plan = 1
3525 BEGIN;
3526 BEGIN TRY;
3527 SELECT @query_plan = x.query_plan
3528 FROM sys.dm_exec_query_statistics_xml(@session_id) AS x;
3529
3530 IF @query_plan IS NOT NULL
3531 AND EXISTS
3532 (
3533 SELECT *
3534 FROM sys.dm_exec_requests AS r
3535 WHERE
3536 r.session_id = @session_id
3537 AND r.request_id = @request_id
3538 AND r.plan_handle = @plan_handle
3539 AND r.statement_start_offset = @statement_start_offset
3540 AND r.statement_end_offset = @statement_end_offset
3541 )
3542 BEGIN;
3543 UPDATE s
3544 SET s.query_plan = @query_plan
3545 FROM #sessions AS s
3546 WHERE
3547 s.session_id = @session_id
3548 AND s.request_id = @request_id
3549 AND s.recursion = 1
3550 OPTION (KEEPFIXED PLAN);
3551 END;
3552 END TRY
3553 BEGIN CATCH;
3554 SET @query_plan = NULL;
3555 END CATCH;
3556 END;
3557
3558 IF @query_plan IS NULL
3559 BEGIN;
3560 BEGIN TRY;
3561 UPDATE s
3562 SET s.query_plan = (
3563 SELECT CONVERT(xml, query_plan)
3564 FROM sys.dm_exec_text_query_plan
3565 (
3566 @plan_handle,
3567 CASE @get_plans WHEN 1 THEN @statement_start_offset
3568 ELSE 0 END,
3569 CASE @get_plans WHEN 1 THEN @statement_end_offset
3570 ELSE -1 END
3571 )
3572 )
3573 FROM #sessions AS s
3574 WHERE
3575 s.session_id = @session_id
3576 AND s.request_id = @request_id
3577 AND s.recursion = 1
3578 OPTION (KEEPFIXED PLAN);
3579 END TRY
3580 BEGIN CATCH;
3581 IF ERROR_NUMBER() = 6335
3582 BEGIN;
3583 UPDATE s
3584 SET s.query_plan = (
3585 SELECT N'--' + NCHAR(13) + NCHAR(10) +
3586 N'-- Could not render showplan due to XML data type limitations. ' + NCHAR(13) + NCHAR(10) +
3587 N'-- To see the graphical plan save the XML below as a .SQLPLAN file and re-open in SSMS.' + NCHAR(13) + NCHAR(10) +
3588 N'--' + NCHAR(13) + NCHAR(10) +
3589 REPLACE(qp.query_plan, N'<RelOp', NCHAR(13)+NCHAR(10)+N'<RelOp') +
3590 NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]
3591 FROM sys.dm_exec_text_query_plan
3592 (
3593 @plan_handle,
3594 CASE @get_plans WHEN 1 THEN @statement_start_offset
3595 ELSE 0 END,
3596 CASE @get_plans WHEN 1 THEN @statement_end_offset
3597 ELSE -1 END
3598 ) AS
3599 qp
3600 FOR XML
3601 PATH(''),
3602 TYPE
3603 )
3604 FROM #sessions AS s
3605 WHERE
3606 s.session_id = @session_id
3607 AND s.request_id = @request_id
3608 AND s.recursion = 1
3609 OPTION (KEEPFIXED PLAN);
3610 END; ELSE BEGIN;
3611 UPDATE s
3612 SET s.query_plan = CASE ERROR_NUMBER() WHEN 1222 THEN '<timeout_exceeded />'
3613 ELSE '<error message="' + ERROR_MESSAGE() + '" />' END
3614 FROM #sessions AS s
3615 WHERE
3616 s.session_id = @session_id
3617 AND s.request_id = @request_id
3618 AND s.recursion = 1
3619 OPTION (KEEPFIXED PLAN);
3620 END;
3621 END CATCH;
3622 END;
3623
3624 FETCH NEXT FROM plan_cursor
3625 INTO
3626 @session_id,
3627 @request_id,
3628 @plan_handle,
3629 @statement_start_offset,
3630 @statement_end_offset;
3631 END;
3632
3633 --Return this to the default
3634 SET LOCK_TIMEOUT -1;
3635
3636 CLOSE plan_cursor;
3637 DEALLOCATE plan_cursor;
3638 END;
3639
3640 IF @get_locks = 1
3641 AND @recursion = 1
3642 AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'
3643 BEGIN;
3644 DECLARE locks_cursor
3645 CURSOR LOCAL FAST_FORWARD
3646 FOR
3647 SELECT DISTINCT database_name
3648 FROM #locks
3649 WHERE
3650 EXISTS
3651 (
3652 SELECT *
3653 FROM #sessions AS s
3654 WHERE
3655 s.session_id = #locks.session_id
3656 AND recursion = 1
3657 )
3658 AND database_name <> '(null)'
3659 OPTION (KEEPFIXED PLAN);
3660
3661 OPEN locks_cursor;
3662
3663 FETCH NEXT FROM locks_cursor
3664 INTO
3665 @database_name;
3666
3667 WHILE @@FETCH_STATUS = 0
3668 BEGIN;
3669 BEGIN TRY;
3670 SET @sql_n = CONVERT(NVARCHAR(MAX), '') +
3671 'UPDATE l ' +
3672 'SET ' +
3673 'object_name = ' +
3674 'REPLACE ' +
3675 '( ' +
3676 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3677 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3678 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3679 'o.name COLLATE Latin1_General_Bin2, ' +
3680 'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
3681 'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
3682 'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
3683 'NCHAR(0), ' +
3684 N''''' ' +
3685 '), ' +
3686 'index_name = ' +
3687 'REPLACE ' +
3688 '( ' +
3689 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3690 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3691 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3692 'i.name COLLATE Latin1_General_Bin2, ' +
3693 'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
3694 'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
3695 'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
3696 'NCHAR(0), ' +
3697 N''''' ' +
3698 '), ' +
3699 'schema_name = ' +
3700 'REPLACE ' +
3701 '( ' +
3702 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3703 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3704 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3705 's.name COLLATE Latin1_General_Bin2, ' +
3706 'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
3707 'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
3708 'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
3709 'NCHAR(0), ' +
3710 N''''' ' +
3711 '), ' +
3712 'principal_name = ' +
3713 'REPLACE ' +
3714 '( ' +
3715 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3716 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3717 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
3718 'dp.name COLLATE Latin1_General_Bin2, ' +
3719 'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
3720 'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
3721 'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
3722 'NCHAR(0), ' +
3723 N''''' ' +
3724 ') ' +
3725 'FROM #locks AS l ' +
3726 'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.allocation_units AS au ON ' +
3727 'au.allocation_unit_id = l.allocation_unit_id ' +
3728 'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +
3729 'p.hobt_id = ' +
3730 'COALESCE ' +
3731 '( ' +
3732 'l.hobt_id, ' +
3733 'CASE ' +
3734 'WHEN au.type IN (1, 3) THEN au.container_id ' +
3735 'ELSE NULL ' +
3736 'END ' +
3737 ') ' +
3738 'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p1 ON ' +
3739 'l.hobt_id IS NULL ' +
3740 'AND au.type = 2 ' +
3741 'AND p1.partition_id = au.container_id ' +
3742 'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +
3743 'o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +
3744 'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.indexes AS i ON ' +
3745 'i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +
3746 'AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) ' +
3747 'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +
3748 's.schema_id = COALESCE(l.schema_id, o.schema_id) ' +
3749 'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.database_principals AS dp ON ' +
3750 'dp.principal_id = l.principal_id ' +
3751 'WHERE ' +
3752 'l.database_name = @database_name ' +
3753 'OPTION (KEEPFIXED PLAN); ';
3754
3755 EXEC sp_executesql @sql_n
3756 , N'@database_name sysname'
3757 , @database_name;
3758 END TRY
3759 BEGIN CATCH;
3760 UPDATE #locks
3761 SET query_error = REPLACE
3762 (
3763 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3764 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3765 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3766 CONVERT
3767 (
3768 NVARCHAR(MAX),
3769 ERROR_MESSAGE() COLLATE Latin1_General_Bin2
3770 ),
3771 NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
3772 NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
3773 NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
3774 NCHAR(0),
3775 N''
3776 )
3777 WHERE
3778 database_name = @database_name
3779 OPTION (KEEPFIXED PLAN);
3780 END CATCH;
3781
3782 FETCH NEXT FROM locks_cursor
3783 INTO
3784 @database_name;
3785 END;
3786
3787 CLOSE locks_cursor;
3788 DEALLOCATE locks_cursor;
3789
3790 CREATE CLUSTERED INDEX IX_SRD ON #locks (session_id, request_id, database_name);
3791
3792 UPDATE s
3793 SET s.locks = (
3794 SELECT REPLACE
3795 (
3796 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3797 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3798 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
3799 CONVERT
3800 (
3801 NVARCHAR(MAX),
3802 l1.database_name COLLATE Latin1_General_Bin2
3803 ),
3804 NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
3805 NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
3806 NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
3807 NCHAR(0),
3808 N''
3809 ) AS [Database/@name]
3810 , MIN(l1.query_error) AS [Database/@query_error]
3811 , (
3812 SELECT l2.request_mode AS [Lock/@request_mode]
3813 , l2.request_status AS [Lock/@request_status]
3814 , COUNT(*) AS [Lock/@request_count]
3815 FROM #locks AS l2
3816 WHERE
3817 l1.session_id = l2.session_id
3818 AND l1.request_id = l2.request_id
3819 AND l2.database_name = l1.database_name
3820 AND l2.resource_type = 'DATABASE'
3821 GROUP BY l2.request_mode
3822 , l2.
3823 request_status
3824 FOR XML
3825 PATH(''),
3826 TYPE
3827 ) AS [Database/Locks]
3828 , (
3829 SELECT COALESCE(l3.object_name, '(null)') AS [Object/@name]
3830 , l3.schema_name AS [Object/@schema_name]
3831 , (
3832 SELECT l4.resource_type AS [Lock/@resource_type]
3833 , l4.page_type AS [Lock/@page_type]
3834 , l4.index_name AS [Lock/@index_name]
3835 , CASE WHEN l4.object_name IS NULL THEN l4.schema_name
3836 ELSE NULL END AS [Lock/@schema_name]
3837 , l4.principal_name AS [Lock/@principal_name]
3838 , l4.resource_description AS [Lock/@resource_description]
3839 , l4.request_mode AS [Lock/@request_mode]
3840 , l4.request_status AS [Lock/@request_status]
3841 , SUM(l4.request_count) AS [Lock/@request_count]
3842 FROM #locks AS l4
3843 WHERE
3844 l4.session_id = l3.session_id
3845 AND l4.request_id = l3.request_id
3846 AND l3.database_name = l4.database_name
3847 AND COALESCE(l3.object_name, '(null)') = COALESCE(l4.object_name, '(null)')
3848 AND COALESCE(l3.schema_name, '') = COALESCE(l4.schema_name, '')
3849 AND l4.resource_type <> 'DATABASE'
3850 GROUP BY l4.resource_type
3851 , l4.page_type
3852 , l4.index_name
3853 , CASE WHEN l4.object_name IS NULL THEN l4.schema_name
3854 ELSE NULL END
3855 , l4.principal_name
3856 , l4.resource_description
3857 , l4.request_mode
3858 , l4.
3859 request_status
3860 FOR XML
3861 PATH(''),
3862 TYPE
3863 ) AS [Object/Locks]
3864 FROM #locks AS l3
3865 WHERE
3866 l3.session_id = l1.session_id
3867 AND l3.request_id = l1.request_id
3868 AND l3.database_name = l1.database_name
3869 AND l3.resource_type <> 'DATABASE'
3870 GROUP BY l3.session_id
3871 , l3.request_id
3872 , l3.database_name
3873 , COALESCE(l3.object_name, '(null)')
3874 , l3.
3875 schema_name
3876 FOR XML
3877 PATH(''),
3878 TYPE
3879 ) AS [Database/Objects]
3880 FROM #locks AS l1
3881 WHERE
3882 l1.session_id = s.session_id
3883 AND l1.request_id = s.request_id
3884 AND l1.start_time IN (s.start_time, s.last_request_start_time)
3885 AND s.recursion = 1
3886 GROUP BY l1.session_id
3887 , l1.request_id
3888 , l1.
3889 database_name
3890 FOR XML
3891 PATH(''),
3892 TYPE
3893 )
3894 FROM #sessions s
3895 OPTION (KEEPFIXED PLAN);
3896 END;
3897
3898 IF @find_block_leaders = 1
3899 AND @recursion = 1
3900 AND @output_column_list LIKE '%|[blocked_session_count|]%' ESCAPE '|'
3901 BEGIN;
3902 WITH BLOCKERS
3903 AS
3904 (
3905 SELECT session_id
3906 , session_id AS top_level_session_id
3907 , CONVERT(VARCHAR(8000), '.' + CONVERT(VARCHAR(8000), session_id) + '.') AS the_path
3908 FROM #sessions
3909 WHERE
3910 recursion = 1
3911 UNION ALL
3912 SELECT s.session_id
3913 , b.top_level_session_id
3914 , CONVERT(VARCHAR(8000), b.the_path + CONVERT(VARCHAR(8000), s.session_id) + '.') AS the_path
3915 FROM blockers AS b
3916 JOIN #sessions AS s ON s.blocking_session_id = b.session_id
3917 AND s.recursion = 1
3918 AND b.the_path NOT LIKE '%.' + CONVERT(VARCHAR(8000), s.session_id) + '.%' COLLATE Latin1_General_Bin2
3919 )
3920 UPDATE s
3921 SET s.blocked_session_count = x.blocked_session_count
3922 FROM #sessions AS s
3923 JOIN (
3924 SELECT b.top_level_session_id AS session_id
3925 , COUNT(*) - 1 AS blocked_session_count
3926 FROM blockers AS b
3927 GROUP BY b.top_level_session_id
3928 ) x ON s.session_id = x.session_id
3929 WHERE
3930 s.recursion = 1;
3931 END;
3932
3933 IF @get_task_info = 2
3934 AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
3935 AND @recursion = 1
3936 BEGIN;
3937 CREATE TABLE #blocked_requests (
3938 session_id SMALLINT NOT NULL
3939 , request_id INT NOT NULL
3940 , database_name sysname NOT NULL
3941 , object_id INT
3942 , hobt_id BIGINT
3943 , schema_id INT
3944 , schema_name sysname NULL
3945 , object_name sysname NULL
3946 , query_error NVARCHAR(2048)
3947 , PRIMARY KEY (database_name, session_id, request_id) );
3948
3949 CREATE STATISTICS s_database_name ON #blocked_requests (database_name)
3950 WITH SAMPLE 0 ROWS, NORECOMPUTE;
3951 CREATE STATISTICS s_schema_name ON #blocked_requests (schema_name)
3952 WITH SAMPLE 0 ROWS, NORECOMPUTE;
3953 CREATE STATISTICS s_object_name ON #blocked_requests (object_name)
3954 WITH SAMPLE 0 ROWS, NORECOMPUTE;
3955 CREATE STATISTICS s_query_error ON #blocked_requests (query_error)
3956 WITH SAMPLE 0 ROWS, NORECOMPUTE;
3957
3958 INSERT #blocked_requests ( session_id, request_id, database_name, object_id, hobt_id, schema_id )
3959 SELECT session_id
3960 , request_id
3961 , database_name
3962 , object_id
3963 , hobt_id
3964 , CONVERT(INT, SUBSTRING(schema_node, CHARINDEX(' = ', schema_node) + 3, LEN(schema_node))) AS schema_id
3965 FROM (
3966 SELECT session_id
3967 , request_id
3968 , agent_nodes.agent_node.value('(database_name/text())[1]', 'sysname') AS database_name
3969 , agent_nodes.agent_node.value('(object_id/text())[1]', 'int') AS object_id
3970 , agent_nodes.agent_node.value('(hobt_id/text())[1]', 'bigint') AS hobt_id
3971 , agent_nodes.agent_node.value('(metadata_resource/text()[.="SCHEMA"]/../../metadata_class_id/text())[1]', 'varchar(100)') AS schema_node
3972 FROM #sessions AS s
3973 CROSS APPLY s.additional_info.nodes('//block_info') AS agent_nodes (agent_node)
3974 WHERE
3975 s.recursion = 1
3976 ) AS t
3977 WHERE
3978 t.database_name IS NOT NULL
3979 AND
3980 (
3981 t.object_id IS NOT NULL
3982 OR t.hobt_id IS NOT NULL
3983 OR t.schema_node IS NOT NULL
3984 );
3985
3986 DECLARE blocks_cursor
3987 CURSOR LOCAL FAST_FORWARD
3988 FOR
3989 SELECT DISTINCT database_name
3990 FROM #blocked_requests;
3991
3992 OPEN blocks_cursor;
3993
3994 FETCH NEXT FROM blocks_cursor
3995 INTO
3996 @database_name;
3997
3998 WHILE @@FETCH_STATUS = 0
3999 BEGIN;
4000 BEGIN TRY;
4001 SET @sql_n =
4002 CONVERT(NVARCHAR(MAX), '') +
4003 'UPDATE b ' +
4004 'SET ' +
4005 'b.schema_name = ' +
4006 'REPLACE ' +
4007 '( ' +
4008 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4009 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4010 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4011 's.name COLLATE Latin1_General_Bin2, ' +
4012 'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
4013 'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
4014 'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
4015 'NCHAR(0), ' +
4016 N''''' ' +
4017 '), ' +
4018 'b.object_name = ' +
4019 'REPLACE ' +
4020 '( ' +
4021 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4022 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4023 'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
4024 'o.name COLLATE Latin1_General_Bin2, ' +
4025 'NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''), ' +
4026 'NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''), ' +
4027 'NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''), ' +
4028 'NCHAR(0), ' +
4029 N''''' ' +
4030 ') ' +
4031 'FROM #blocked_requests AS b ' +
4032 'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +
4033 'p.hobt_id = b.hobt_id ' +
4034 'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +
4035 'o.object_id = COALESCE(p.object_id, b.object_id) ' +
4036 'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +
4037 's.schema_id = COALESCE(o.schema_id, b.schema_id) ' +
4038 'WHERE ' +
4039 'b.database_name = @database_name; ';
4040
4041 EXEC sp_executesql @sql_n
4042 , N'@database_name sysname'
4043 , @database_name;
4044 END TRY
4045 BEGIN CATCH;
4046 UPDATE #blocked_requests
4047 SET query_error = REPLACE
4048 (
4049 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4050 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4051 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4052 CONVERT
4053 (
4054 NVARCHAR(MAX),
4055 ERROR_MESSAGE() COLLATE Latin1_General_Bin2
4056 ),
4057 NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
4058 NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
4059 NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
4060 NCHAR(0),
4061 N''
4062 )
4063 WHERE
4064 database_name = @database_name;
4065 END CATCH;
4066
4067 FETCH NEXT FROM blocks_cursor
4068 INTO
4069 @database_name;
4070 END;
4071
4072 CLOSE blocks_cursor;
4073 DEALLOCATE blocks_cursor;
4074
4075 UPDATE s
4076 SET additional_info.modify
4077 ('
4078 insert <schema_name>{sql:column("b.schema_name")}</schema_name>
4079 as last
4080 into (/additional_info/block_info)[1]
4081 ')
4082 FROM #sessions AS s
4083 INNER JOIN #blocked_requests AS b ON b.session_id = s.session_id
4084 AND b.request_id = s.request_id
4085 AND s.recursion = 1
4086 WHERE
4087 b.schema_name IS NOT NULL;
4088
4089 UPDATE s
4090 SET additional_info.modify
4091 ('
4092 insert <object_name>{sql:column("b.object_name")}</object_name>
4093 as last
4094 into (/additional_info/block_info)[1]
4095 ')
4096 FROM #sessions AS s
4097 INNER JOIN #blocked_requests AS b ON b.session_id = s.session_id
4098 AND b.request_id = s.request_id
4099 AND s.recursion = 1
4100 WHERE
4101 b.object_name IS NOT NULL;
4102
4103 UPDATE s
4104 SET additional_info.modify
4105 ('
4106 insert <query_error>{sql:column("b.query_error")}</query_error>
4107 as last
4108 into (/additional_info/block_info)[1]
4109 ')
4110 FROM #sessions AS s
4111 INNER JOIN #blocked_requests AS b ON b.session_id = s.session_id
4112 AND b.request_id = s.request_id
4113 AND s.recursion = 1
4114 WHERE
4115 b.query_error IS NOT NULL;
4116 END;
4117
4118 IF @output_column_list LIKE '%|[program_name|]%' ESCAPE '|'
4119 AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
4120 AND @recursion = 1
4121 AND DB_ID('msdb') IS NOT NULL
4122 BEGIN;
4123 SET @sql_n =
4124 N'BEGIN TRY;
4125 DECLARE @job_name sysname;
4126 SET @job_name = NULL;
4127 DECLARE @step_name sysname;
4128 SET @step_name = NULL;
4129
4130 SELECT
4131 @job_name =
4132 REPLACE
4133 (
4134 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4135 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4136 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4137 j.name,
4138 NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''),
4139 NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''),
4140 NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''),
4141 NCHAR(0),
4142 N''?''
4143 ),
4144 @step_name =
4145 REPLACE
4146 (
4147 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4148 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4149 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
4150 s.step_name,
4151 NCHAR(31),N''?''),NCHAR(30),N''?''),NCHAR(29),N''?''),NCHAR(28),N''?''),NCHAR(27),N''?''),NCHAR(26),N''?''),NCHAR(25),N''?''),NCHAR(24),N''?''),NCHAR(23),N''?''),NCHAR(22),N''?''),
4152 NCHAR(21),N''?''),NCHAR(20),N''?''),NCHAR(19),N''?''),NCHAR(18),N''?''),NCHAR(17),N''?''),NCHAR(16),N''?''),NCHAR(15),N''?''),NCHAR(14),N''?''),NCHAR(12),N''?''),
4153 NCHAR(11),N''?''),NCHAR(8),N''?''),NCHAR(7),N''?''),NCHAR(6),N''?''),NCHAR(5),N''?''),NCHAR(4),N''?''),NCHAR(3),N''?''),NCHAR(2),N''?''),NCHAR(1),N''?''),
4154 NCHAR(0),
4155 N''?''
4156 )
4157 FROM msdb.dbo.sysjobs AS j
4158 INNER JOIN msdb.dbo.sysjobsteps AS s ON
4159 j.job_id = s.job_id
4160 WHERE
4161 j.job_id = @job_id
4162 AND s.step_id = @step_id;
4163
4164 IF @job_name IS NOT NULL
4165 BEGIN;
4166 UPDATE s
4167 SET
4168 additional_info.modify
4169 (''
4170 insert text{sql:variable("@job_name")}
4171 into (/additional_info/agent_job_info/job_name)[1]
4172 '')
4173 FROM #sessions AS s
4174 WHERE
4175 s.session_id = @session_id
4176 AND s.recursion = 1
4177 OPTION (KEEPFIXED PLAN);
4178
4179 UPDATE s
4180 SET
4181 additional_info.modify
4182 (''
4183 insert text{sql:variable("@step_name")}
4184 into (/additional_info/agent_job_info/step_name)[1]
4185 '')
4186 FROM #sessions AS s
4187 WHERE
4188 s.session_id = @session_id
4189 AND s.recursion = 1
4190 OPTION (KEEPFIXED PLAN);
4191 END;
4192 END TRY
4193 BEGIN CATCH;
4194 DECLARE @msdb_error_message NVARCHAR(256);
4195 SET @msdb_error_message = ERROR_MESSAGE();
4196
4197 UPDATE s
4198 SET
4199 additional_info.modify
4200 (''
4201 insert <msdb_query_error>{sql:variable("@msdb_error_message")}</msdb_query_error>
4202 as last
4203 into (/additional_info/agent_job_info)[1]
4204 '')
4205 FROM #sessions AS s
4206 WHERE
4207 s.session_id = @session_id
4208 AND s.recursion = 1
4209 OPTION (KEEPFIXED PLAN);
4210 END CATCH;'
4211
4212 DECLARE @job_id UNIQUEIDENTIFIER;
4213 DECLARE @step_id INT;
4214
4215 DECLARE agent_cursor
4216 CURSOR LOCAL FAST_FORWARD
4217 FOR
4218 SELECT s.session_id
4219 , agent_nodes.agent_node.value('(job_id/text())[1]', 'uniqueidentifier') AS job_id
4220 , agent_nodes.agent_node.value('(step_id/text())[1]', 'int') AS step_id
4221 FROM #sessions AS s
4222 CROSS APPLY s.additional_info.nodes('//agent_job_info') AS agent_nodes (agent_node)
4223 WHERE
4224 s.recursion = 1
4225 OPTION (KEEPFIXED PLAN);
4226
4227 OPEN agent_cursor;
4228
4229 FETCH NEXT FROM agent_cursor
4230 INTO
4231 @session_id,
4232 @job_id,
4233 @step_id;
4234
4235 WHILE @@FETCH_STATUS = 0
4236 BEGIN;
4237 EXEC sp_executesql @sql_n
4238 , N'@job_id UNIQUEIDENTIFIER, @step_id INT, @session_id SMALLINT'
4239 , @job_id
4240 , @step_id
4241 , @session_id
4242
4243 FETCH NEXT FROM agent_cursor
4244 INTO
4245 @session_id,
4246 @job_id,
4247 @step_id;
4248 END;
4249
4250 CLOSE agent_cursor;
4251 DEALLOCATE agent_cursor;
4252 END;
4253
4254 IF @delta_interval > 0
4255 AND @recursion <> 1
4256 BEGIN;
4257 SET @recursion = 1;
4258
4259 DECLARE @delay_time CHAR(12);
4260 SET @delay_time = CONVERT(VARCHAR, DATEADD(second, @delta_interval, 0), 114);
4261 WAITFOR DELAY @delay_time;
4262
4263 GOTO REDO;
4264 END;
4265 END;
4266
4267 SET @sql =
4268 --Outer column list
4269 CONVERT
4270 (
4271 VARCHAR(MAX),
4272 CASE WHEN @destination_table <> ''
4273 AND @return_schema = 0 THEN 'INSERT ' + @destination_table + ' '
4274 ELSE '' END +
4275 'SELECT ' +
4276 @output_column_list + ' ' +
4277 CASE @return_schema WHEN 1 THEN 'INTO #session_schema '
4278 ELSE '' END
4279 --End outer column list
4280 ) +
4281 --Inner column list
4282 CONVERT
4283 (
4284 VARCHAR(MAX),
4285 'FROM ' +
4286 '( ' +
4287 'SELECT ' +
4288 'session_id, ' +
4289 --[dd hh:mm:ss.mss]
4290 CASE WHEN @format_output IN (1, 2) THEN 'CASE ' +
4291 'WHEN elapsed_time < 0 THEN ' +
4292 'RIGHT ' +
4293 '( ' +
4294 'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, (-1 * elapsed_time) / 86400), ' +
4295 'max_elapsed_length ' +
4296 ') + ' +
4297 'RIGHT ' +
4298 '( ' +
4299 'CONVERT(VARCHAR, DATEADD(second, (-1 * elapsed_time), 0), 120), ' +
4300 '9 ' +
4301 ') + ' +
4302 '''.000'' ' +
4303 'ELSE ' +
4304 'RIGHT ' +
4305 '( ' +
4306 'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, elapsed_time / 86400000), ' +
4307 'max_elapsed_length ' +
4308 ') + ' +
4309 'RIGHT ' +
4310 '( ' +
4311 'CONVERT(VARCHAR, DATEADD(second, elapsed_time / 1000, 0), 120), ' +
4312 '9 ' +
4313 ') + ' +
4314 '''.'' + ' +
4315 'RIGHT(''000'' + CONVERT(VARCHAR, elapsed_time % 1000), 3) ' +
4316 'END AS [dd hh:mm:ss.mss], '
4317 ELSE '' END +
4318 --[dd hh:mm:ss.mss (avg)] / avg_elapsed_time
4319 CASE WHEN @format_output IN (1, 2) THEN 'RIGHT ' +
4320 '( ' +
4321 '''00'' + CONVERT(VARCHAR, avg_elapsed_time / 86400000), ' +
4322 '2 ' +
4323 ') + ' +
4324 'RIGHT ' +
4325 '( ' +
4326 'CONVERT(VARCHAR, DATEADD(second, avg_elapsed_time / 1000, 0), 120), ' +
4327 '9 ' +
4328 ') + ' +
4329 '''.'' + ' +
4330 'RIGHT(''000'' + CONVERT(VARCHAR, avg_elapsed_time % 1000), 3) AS [dd hh:mm:ss.mss (avg)], '
4331 ELSE 'avg_elapsed_time, ' END +
4332 --physical_io
4333 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io))) OVER() - LEN(CONVERT(VARCHAR, physical_io))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS '
4334 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS '
4335 ELSE '' END + 'physical_io, ' +
4336 --reads
4337 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads))) OVER() - LEN(CONVERT(VARCHAR, reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS '
4338 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS '
4339 ELSE '' END + 'reads, ' +
4340 --physical_reads
4341 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads))) OVER() - LEN(CONVERT(VARCHAR, physical_reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS '
4342 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS '
4343 ELSE '' END + 'physical_reads, ' +
4344 --writes
4345 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes))) OVER() - LEN(CONVERT(VARCHAR, writes))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS '
4346 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS '
4347 ELSE '' END + 'writes, ' +
4348 --tempdb_allocations
4349 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS '
4350 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS '
4351 ELSE '' END + 'tempdb_allocations, ' +
4352 --tempdb_current
4353 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS '
4354 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS '
4355 ELSE '' END + 'tempdb_current, ' +
4356 --CPU
4357 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CPU))) OVER() - LEN(CONVERT(VARCHAR, CPU))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS '
4358 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS '
4359 ELSE '' END + 'CPU, ' +
4360 --context_switches
4361 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches))) OVER() - LEN(CONVERT(VARCHAR, context_switches))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS '
4362 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS '
4363 ELSE '' END + 'context_switches, ' +
4364 --used_memory
4365 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory))) OVER() - LEN(CONVERT(VARCHAR, used_memory))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS '
4366 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS '
4367 ELSE '' END + 'used_memory, ' +
4368 CASE WHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN
4369 --physical_io_delta
4370 'CASE ' +
4371 'WHEN ' +
4372 'first_request_start_time = last_request_start_time ' +
4373 'AND num_events = 2 ' +
4374 'AND physical_io_delta >= 0 ' +
4375 'THEN ' +
4376 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io_delta))) OVER() - LEN(CONVERT(VARCHAR, physical_io_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) '
4377 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) '
4378 ELSE 'physical_io_delta ' END +
4379 'ELSE NULL ' +
4380 'END AS physical_io_delta, ' +
4381 --reads_delta
4382 'CASE ' +
4383 'WHEN ' +
4384 'first_request_start_time = last_request_start_time ' +
4385 'AND num_events = 2 ' +
4386 'AND reads_delta >= 0 ' +
4387 'THEN ' +
4388 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads_delta))) OVER() - LEN(CONVERT(VARCHAR, reads_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) '
4389 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) '
4390 ELSE 'reads_delta ' END +
4391 'ELSE NULL ' +
4392 'END AS reads_delta, ' +
4393 --physical_reads_delta
4394 'CASE ' +
4395 'WHEN ' +
4396 'first_request_start_time = last_request_start_time ' +
4397 'AND num_events = 2 ' +
4398 'AND physical_reads_delta >= 0 ' +
4399 'THEN ' +
4400 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads_delta))) OVER() - LEN(CONVERT(VARCHAR, physical_reads_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) '
4401 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) '
4402 ELSE 'physical_reads_delta ' END +
4403 'ELSE NULL ' +
4404 'END AS physical_reads_delta, ' +
4405 --writes_delta
4406 'CASE ' +
4407 'WHEN ' +
4408 'first_request_start_time = last_request_start_time ' +
4409 'AND num_events = 2 ' +
4410 'AND writes_delta >= 0 ' +
4411 'THEN ' +
4412 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes_delta))) OVER() - LEN(CONVERT(VARCHAR, writes_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) '
4413 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) '
4414 ELSE 'writes_delta ' END +
4415 'ELSE NULL ' +
4416 'END AS writes_delta, ' +
4417 --tempdb_allocations_delta
4418 'CASE ' +
4419 'WHEN ' +
4420 'first_request_start_time = last_request_start_time ' +
4421 'AND num_events = 2 ' +
4422 'AND tempdb_allocations_delta >= 0 ' +
4423 'THEN ' +
4424 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations_delta))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) '
4425 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) '
4426 ELSE 'tempdb_allocations_delta ' END +
4427 'ELSE NULL ' +
4428 'END AS tempdb_allocations_delta, ' +
4429 --tempdb_current_delta
4430 --this is the only one that can (legitimately) go negative
4431 'CASE ' +
4432 'WHEN ' +
4433 'first_request_start_time = last_request_start_time ' +
4434 'AND num_events = 2 ' +
4435 'THEN ' +
4436 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current_delta))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) '
4437 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) '
4438 ELSE 'tempdb_current_delta ' END +
4439 'ELSE NULL ' +
4440 'END AS tempdb_current_delta, ' +
4441 --CPU_delta
4442 'CASE ' +
4443 'WHEN ' +
4444 'first_request_start_time = last_request_start_time ' +
4445 'AND num_events = 2 ' +
4446 'THEN ' +
4447 'CASE ' +
4448 'WHEN ' +
4449 'thread_CPU_delta > CPU_delta ' +
4450 'AND thread_CPU_delta > 0 ' +
4451 'THEN ' +
4452 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, thread_CPU_delta + CPU_delta))) OVER() - LEN(CONVERT(VARCHAR, thread_CPU_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) '
4453 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) '
4454 ELSE 'thread_CPU_delta ' END +
4455 'WHEN CPU_delta >= 0 THEN ' +
4456 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, thread_CPU_delta + CPU_delta))) OVER() - LEN(CONVERT(VARCHAR, CPU_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) '
4457 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) '
4458 ELSE 'CPU_delta ' END +
4459 'ELSE NULL ' +
4460 'END ' +
4461 'ELSE ' +
4462 'NULL ' +
4463 'END AS CPU_delta, ' +
4464 --context_switches_delta
4465 'CASE ' +
4466 'WHEN ' +
4467 'first_request_start_time = last_request_start_time ' +
4468 'AND num_events = 2 ' +
4469 'AND context_switches_delta >= 0 ' +
4470 'THEN ' +
4471 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches_delta))) OVER() - LEN(CONVERT(VARCHAR, context_switches_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) '
4472 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) '
4473 ELSE 'context_switches_delta ' END +
4474 'ELSE NULL ' +
4475 'END AS context_switches_delta, ' +
4476 --used_memory_delta
4477 'CASE ' +
4478 'WHEN ' +
4479 'first_request_start_time = last_request_start_time ' +
4480 'AND num_events = 2 ' +
4481 'AND used_memory_delta >= 0 ' +
4482 'THEN ' +
4483 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory_delta))) OVER() - LEN(CONVERT(VARCHAR, used_memory_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) '
4484 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) '
4485 ELSE 'used_memory_delta ' END +
4486 'ELSE NULL ' +
4487 'END AS used_memory_delta, '
4488 ELSE '' END +
4489 --tasks
4490 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tasks))) OVER() - LEN(CONVERT(VARCHAR, tasks))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) AS '
4491 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) '
4492 ELSE '' END + 'tasks, ' +
4493 'status, ' +
4494 'wait_info, ' +
4495 'locks, ' +
4496 'tran_start_time, ' +
4497 'LEFT(tran_log_writes, LEN(tran_log_writes) - 1) AS tran_log_writes, ' +
4498 --open_tran_count
4499 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, open_tran_count))) OVER() - LEN(CONVERT(VARCHAR, open_tran_count))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS '
4500 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS '
4501 ELSE '' END + 'open_tran_count, ' +
4502 --sql_command
4503 CASE @format_output WHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_command), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS '
4504 ELSE '' END + 'sql_command, ' +
4505 --sql_text
4506 CASE @format_output WHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_text), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS '
4507 ELSE '' END + 'sql_text, ' +
4508 'query_plan, ' +
4509 'blocking_session_id, ' +
4510 --blocked_session_count
4511 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, blocked_session_count))) OVER() - LEN(CONVERT(VARCHAR, blocked_session_count))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS '
4512 WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS '
4513 ELSE '' END + 'blocked_session_count, ' +
4514 --percent_complete
4515 CASE @format_output WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CONVERT(MONEY, percent_complete), 2))) OVER() - LEN(CONVERT(VARCHAR, CONVERT(MONEY, percent_complete), 2))) + CONVERT(CHAR(22), CONVERT(MONEY, percent_complete), 2)) AS '
4516 WHEN 2 THEN 'CONVERT(VARCHAR, CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1)) AS '
4517 ELSE '' END + 'percent_complete, ' +
4518 'host_name, ' +
4519 'login_name, ' +
4520 'database_name, ' +
4521 'program_name, ' +
4522 'additional_info, ' +
4523 'start_time, ' +
4524 'login_time, ' +
4525 'CASE ' +
4526 'WHEN status = N''sleeping'' THEN NULL ' +
4527 'ELSE request_id ' +
4528 'END AS request_id, ' +
4529 'GETDATE() AS collection_time '
4530 --End inner column list
4531 ) +
4532 --Derived table and INSERT specification
4533 CONVERT
4534 (
4535 VARCHAR(MAX),
4536 'FROM ' +
4537 '( ' +
4538 'SELECT TOP(2147483647) ' +
4539 '*, ' +
4540 'CASE ' +
4541 'MAX ' +
4542 '( ' +
4543 'LEN ' +
4544 '( ' +
4545 'CONVERT ' +
4546 '( ' +
4547 'VARCHAR, ' +
4548 'CASE ' +
4549 'WHEN elapsed_time < 0 THEN ' +
4550 '(-1 * elapsed_time) / 86400 ' +
4551 'ELSE ' +
4552 'elapsed_time / 86400000 ' +
4553 'END ' +
4554 ') ' +
4555 ') ' +
4556 ') OVER () ' +
4557 'WHEN 1 THEN 2 ' +
4558 'ELSE ' +
4559 'MAX ' +
4560 '( ' +
4561 'LEN ' +
4562 '( ' +
4563 'CONVERT ' +
4564 '( ' +
4565 'VARCHAR, ' +
4566 'CASE ' +
4567 'WHEN elapsed_time < 0 THEN ' +
4568 '(-1 * elapsed_time) / 86400 ' +
4569 'ELSE ' +
4570 'elapsed_time / 86400000 ' +
4571 'END ' +
4572 ') ' +
4573 ') ' +
4574 ') OVER () ' +
4575 'END AS max_elapsed_length, ' +
4576 CASE WHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN 'MAX(physical_io * recursion) OVER (PARTITION BY session_id, request_id) + ' +
4577 'MIN(physical_io * recursion) OVER (PARTITION BY session_id, request_id) AS physical_io_delta, ' +
4578 'MAX(reads * recursion) OVER (PARTITION BY session_id, request_id) + ' +
4579 'MIN(reads * recursion) OVER (PARTITION BY session_id, request_id) AS reads_delta, ' +
4580 'MAX(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) + ' +
4581 'MIN(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) AS physical_reads_delta, ' +
4582 'MAX(writes * recursion) OVER (PARTITION BY session_id, request_id) + ' +
4583 'MIN(writes * recursion) OVER (PARTITION BY session_id, request_id) AS writes_delta, ' +
4584 'MAX(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) + ' +
4585 'MIN(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_allocations_delta, ' +
4586 'MAX(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) + ' +
4587 'MIN(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_current_delta, ' +
4588 'MAX(CPU * recursion) OVER (PARTITION BY session_id, request_id) + ' +
4589 'MIN(CPU * recursion) OVER (PARTITION BY session_id, request_id) AS CPU_delta, ' +
4590 'MAX(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) + ' +
4591 'MIN(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) AS thread_CPU_delta, ' +
4592 'MAX(context_switches * recursion) OVER (PARTITION BY session_id, request_id) + ' +
4593 'MIN(context_switches * recursion) OVER (PARTITION BY session_id, request_id) AS context_switches_delta, ' +
4594 'MAX(used_memory * recursion) OVER (PARTITION BY session_id, request_id) + ' +
4595 'MIN(used_memory * recursion) OVER (PARTITION BY session_id, request_id) AS used_memory_delta, ' +
4596 'MIN(last_request_start_time) OVER (PARTITION BY session_id, request_id) AS first_request_start_time, '
4597 ELSE '' END +
4598 'COUNT(*) OVER (PARTITION BY session_id, request_id) AS num_events ' +
4599 'FROM #sessions AS s1 ' +
4600 CASE WHEN @sort_order = '' THEN ''
4601 ELSE 'ORDER BY ' +
4602 @sort_order END +
4603 ') AS s ' +
4604 'WHERE ' +
4605 's.recursion = 1 ' +
4606 ') x ' +
4607 'OPTION (KEEPFIXED PLAN); ' +
4608 '' +
4609 CASE @return_schema WHEN 1 THEN 'SET @schema = ' +
4610 '''CREATE TABLE <table_name> ( '' + ' +
4611 'STUFF ' +
4612 '( ' +
4613 '( ' +
4614 'SELECT ' +
4615 ''','' + ' +
4616 'QUOTENAME(COLUMN_NAME) + '' '' + ' +
4617 'DATA_TYPE + ' +
4618 'CASE ' +
4619 'WHEN DATA_TYPE LIKE ''%char'' THEN ''('' + COALESCE(NULLIF(CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH), ''-1''), ''max'') + '') '' ' +
4620 'ELSE '' '' ' +
4621 'END + ' +
4622 'CASE IS_NULLABLE ' +
4623 'WHEN ''NO'' THEN ''NOT '' ' +
4624 'ELSE '''' ' +
4625 'END + ''NULL'' AS [text()] ' +
4626 'FROM tempdb.INFORMATION_SCHEMA.COLUMNS ' +
4627 'WHERE ' +
4628 'TABLE_NAME = (SELECT name FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(''tempdb..#session_schema'')) ' +
4629 'ORDER BY ' +
4630 'ORDINAL_POSITION ' +
4631 'FOR XML ' +
4632 'PATH('''') ' +
4633 '), + ' +
4634 '1, ' +
4635 '1, ' +
4636 ''''' ' +
4637 ') + ' +
4638 ''')''; '
4639 ELSE '' END
4640 --End derived table and INSERT specification
4641 );
4642
4643 SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);
4644
4645 EXEC sp_executesql @sql_n
4646 , N'@schema VARCHAR(MAX) OUTPUT'
4647 , @schema OUTPUT;
4648END;
Source : http://whoisactive.com
Comments