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