k1153eML

1
2--CREATE PROC dbo.sp_WhoIsActive 
3
4--( 
5
6--~ 
7
8 --Filters--Both inclusive and exclusive 
9
10 --Set either filter to '' to disable 
11
12 --Valid filter types are: session, program, database, login, and host 
13
14 --Session is a session ID, and either 0 or '' can be used to indicate "all" sessions 
15
16 --All other filter types support % or _ as wildcards 
17
18 declare @filter sysname = '', 
19
20 @filter_type VARCHAR(10) = 'session', 
21
22 @not_filter sysname = '', 
23
24 @not_filter_type VARCHAR(10) = 'session', 
25
26  
27
28 --Retrieve data about the calling session? 
29
30 @show_own_spid BIT = 0, 
31
32  
33
34 --Retrieve data about system sessions? 
35
36 @show_system_spids BIT = 0, 
37
38  
39
40 --Controls how sleeping SPIDs are handled, based on the idea of levels of interest 
41
42 --0 does not pull any sleeping SPIDs 
43
44 --1 pulls only those sleeping SPIDs that also have an open transaction 
45
46 --2 pulls all sleeping SPIDs 
47
48 @show_sleeping_spids TINYINT = 1, 
49
50  
51
52 --If 1, gets the full stored procedure or running batch, when available 
53
54 --If 0, gets only the actual statement that is currently running in the batch or procedure 
55
56 @get_full_inner_text BIT = 0, 
57
58  
59
60 --Get associated query plans for running tasks, if available 
61
62 --If @get_plans = 1, gets the plan based on the request's statement offset 
63
64 --If @get_plans = 2, gets the entire plan based on the request's plan_handle 
65
66 @get_plans TINYINT = 0, 
67
68  
69
70 --Get the associated outer ad hoc query or stored procedure call, if available 
71
72 @get_outer_command BIT = 0, 
73
74  
75
76 --Enables pulling transaction log write info and transaction duration 
77
78 @get_transaction_info BIT = 0, 
79
80  
81
82 --Get information on active tasks, based on three interest levels 
83
84 --Level 0 does not pull any task-related information 
85
86 --Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers 
87
88 --Level 2 pulls all available task-based metrics, including:  
89
90 --number of active tasks, current wait stats, physical I/O, context switches, and blocker information 
91
92 @get_task_info TINYINT = 1, 
93
94  
95
96 --Gets associated locks for each request, aggregated in an XML format 
97
98 @get_locks BIT = 0, 
99
100  
101
102 --Get average time for past runs of an active query 
103
104 --(based on the combination of plan handle, sql handle, and offset) 
105
106 @get_avg_time BIT = 0, 
107
108  
109
110 --Get additional non-performance-related information about the session or request 
111
112 --text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on,  
113
114 --ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null,  
115
116 --transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type 
117
118 -- 
119
120 --If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of 
121
122 --the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error) 
123
124 -- 
125
126 --If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be 
127
128 --populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id,  
129
130 --applock_hash, metadata_resource, metadata_class_id, object_name, schema_name 
131
132 @get_additional_info BIT = 0, 
133
134  
135
136 --Walk the blocking chain and count the number of  
137
138 --total SPIDs blocked all the way down by a given session 
139
140 --Also enables task_info Level 1, if @get_task_info is set to 0 
141
142 @find_block_leaders BIT = 0, 
143
144  
145
146 --Pull deltas on various metrics 
147
148 --Interval in seconds to wait before doing the second data pull 
149
150 @delta_interval TINYINT = 0, 
151
152  
153
154 --List of desired output columns, in desired order 
155
156 --Note that the final output will be the intersection of all enabled features and all  
157
158 --columns in the list. Therefore, only columns associated with enabled features will  
159
160 --actually appear in the output. Likewise, removing columns from this list may effectively 
161
162 --disable features, even if they are turned on 
163
164 -- 
165
166 --Each element in this list must be one of the valid output column names. Names must be 
167
168 --delimited by square brackets. White space, formatting, and additional characters are 
169
170 --allowed, as long as the list contains exact matches of delimited valid column names. 
171
172 @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][%]',  
173
174  
175
176 --Column(s) by which to sort output, optionally with sort directions.  
177
178  --Valid column choices: 
179
180  --session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,  
181
182  --tempdb_current, CPU, context_switches, used_memory, physical_io_delta, reads_delta,  
183
184  --physical_reads_delta, writes_delta, tempdb_allocations_delta, tempdb_current_delta,  
185
186  --CPU_delta, context_switches_delta, used_memory_delta, tasks, tran_start_time,  
187
188  --open_tran_count, blocking_session_id, blocked_session_count, percent_complete,  
189
190  --host_name, login_name, database_name, start_time, login_time, program_name 
191
192  -- 
193
194  --Note that column names in the list must be bracket-delimited. Commas and/or white 
195
196  --space are not required.  
197
198 @sort_order VARCHAR(500) = '[start_time] ASC', 
199
200  
201
202 --Formats some of the output columns in a more "human readable" form 
203
204 --0 disables outfput format 
205
206 --1 formats the output for variable-width fonts 
207
208 --2 formats the output for fixed-width fonts 
209
210 @format_output TINYINT = 1, 
211
212  
213
214 --If set to a non-blank value, the script will attempt to insert into the specified  
215
216 --destination table. Please note that the script will not verify that the table exists,  
217
218 --or that it has the correct schema, before doing the insert. 
219
220 --Table can be specified in one, two, or three-part format 
221
222 @destination_table VARCHAR(4000) = '', 
223
224  
225
226 --If set to 1, no data collection will happen and no result set will be returned; instead, 
227
228 --a CREATE TABLE statement will be returned via the @schema parameter, which will match  
229
230 --the schema of the result set that would be returned by using the same collection of the 
231
232 --rest of the parameters. The CREATE TABLE statement will have a placeholder token of  
233
234 --<table_name> in place of an actual table name. 
235
236 @return_schema BIT = 0, 
237
238 @schema VARCHAR(MAX) = NULL, 
239
240  
241
242 --Help! What do I do? 
243
244 @help BIT = 0 
245
246--~ 
247
248--) 
249
250/* 
251
252OUTPUT COLUMNS 
253
254-------------- 
255
256Formatted/Non: [session_id] [smallint] NOT NULL 
257
258 Session ID (a.k.a. SPID) 
259
260  
261
262Formatted:  [dd hh:mm:ss.mss] [varchar](15) NULL 
263
264Non-Formatted: <not returned> 
265
266 For an active request, time the query has been running 
267
268 For a sleeping session, time since the last batch completed 
269
270  
271
272Formatted:  [dd hh:mm:ss.mss (avg)] [varchar](15) NULL 
273
274Non-Formatted: [avg_elapsed_time] [int] NULL 
275
276 (Requires @get_avg_time option) 
277
278 How much time has the active portion of the query taken in the past, on average? 
279
280  
281
282Formatted:  [physical_io] [varchar](30) NULL 
283
284Non-Formatted: [physical_io] [bigint] NULL 
285
286 Shows the number of physical I/Os, for active requests 
287
288  
289
290Formatted:  [reads] [varchar](30) NULL 
291
292Non-Formatted: [reads] [bigint] NULL 
293
294 For an active request, number of reads done for the current query 
295
296 For a sleeping session, total number of reads done over the lifetime of the session 
297
298  
299
300Formatted:  [physical_reads] [varchar](30) NULL 
301
302Non-Formatted: [physical_reads] [bigint] NULL 
303
304 For an active request, number of physical reads done for the current query 
305
306 For a sleeping session, total number of physical reads done over the lifetime of the session 
307
308  
309
310Formatted:  [writes] [varchar](30) NULL 
311
312Non-Formatted: [writes] [bigint] NULL 
313
314 For an active request, number of writes done for the current query 
315
316 For a sleeping session, total number of writes done over the lifetime of the session 
317
318  
319
320Formatted:  [tempdb_allocations] [varchar](30) NULL 
321
322Non-Formatted: [tempdb_allocations] [bigint] NULL 
323
324 For an active request, number of TempDB writes done for the current query 
325
326 For a sleeping session, total number of TempDB writes done over the lifetime of the session 
327
328  
329
330Formatted:  [tempdb_current] [varchar](30) NULL 
331
332Non-Formatted: [tempdb_current] [bigint] NULL 
333
334 For an active request, number of TempDB pages currently allocated for the query 
335
336 For a sleeping session, number of TempDB pages currently allocated for the session 
337
338  
339
340Formatted:  [CPU] [varchar](30) NULL 
341
342Non-Formatted: [CPU] [int] NULL 
343
344 For an active request, total CPU time consumed by the current query 
345
346 For a sleeping session, total CPU time consumed over the lifetime of the session 
347
348  
349
350Formatted:  [context_switches] [varchar](30) NULL 
351
352Non-Formatted: [context_switches] [bigint] NULL 
353
354 Shows the number of context switches, for active requests 
355
356  
357
358Formatted:  [used_memory] [varchar](30) NOT NULL 
359
360Non-Formatted: [used_memory] [bigint] NOT NULL 
361
362 For an active request, total memory consumption for the current query 
363
364 For a sleeping session, total current memory consumption 
365
366  
367
368Formatted:  [physical_io_delta] [varchar](30) NULL 
369
370Non-Formatted: [physical_io_delta] [bigint] NULL 
371
372 (Requires @delta_interval option) 
373
374 Difference between the number of physical I/Os reported on the first and second collections.  
375
376 If the request started after the first collection, the value will be NULL 
377
378  
379
380Formatted:  [reads_delta] [varchar](30) NULL 
381
382Non-Formatted: [reads_delta] [bigint] NULL 
383
384 (Requires @delta_interval option) 
385
386 Difference between the number of reads reported on the first and second collections.  
387
388 If the request started after the first collection, the value will be NULL 
389
390  
391
392Formatted:  [physical_reads_delta] [varchar](30) NULL 
393
394Non-Formatted: [physical_reads_delta] [bigint] NULL 
395
396 (Requires @delta_interval option) 
397
398 Difference between the number of physical reads reported on the first and second collections.  
399
400 If the request started after the first collection, the value will be NULL 
401
402  
403
404Formatted:  [writes_delta] [varchar](30) NULL 
405
406Non-Formatted: [writes_delta] [bigint] NULL 
407
408 (Requires @delta_interval option) 
409
410 Difference between the number of writes reported on the first and second collections.  
411
412 If the request started after the first collection, the value will be NULL 
413
414  
415
416Formatted:  [tempdb_allocations_delta] [varchar](30) NULL 
417
418Non-Formatted: [tempdb_allocations_delta] [bigint] NULL 
419
420 (Requires @delta_interval option) 
421
422 Difference between the number of TempDB writes reported on the first and second collections.  
423
424 If the request started after the first collection, the value will be NULL 
425
426  
427
428Formatted:  [tempdb_current_delta] [varchar](30) NULL 
429
430Non-Formatted: [tempdb_current_delta] [bigint] NULL 
431
432 (Requires @delta_interval option) 
433
434 Difference between the number of allocated TempDB pages reported on the first and second  
435
436 collections. If the request started after the first collection, the value will be NULL 
437
438  
439
440Formatted:  [CPU_delta] [varchar](30) NULL 
441
442Non-Formatted: [CPU_delta] [int] NULL 
443
444 (Requires @delta_interval option) 
445
446 Difference between the CPU time reported on the first and second collections.  
447
448 If the request started after the first collection, the value will be NULL 
449
450  
451
452Formatted:  [context_switches_delta] [varchar](30) NULL 
453
454Non-Formatted: [context_switches_delta] [bigint] NULL 
455
456 (Requires @delta_interval option) 
457
458 Difference between the context switches count reported on the first and second collections 
459
460 If the request started after the first collection, the value will be NULL 
461
462  
463
464Formatted:  [used_memory_delta] [varchar](30) NULL 
465
466Non-Formatted: [used_memory_delta] [bigint] NULL 
467
468 Difference between the memory usage reported on the first and second collections 
469
470 If the request started after the first collection, the value will be NULL 
471
472  
473
474Formatted:  [tasks] [varchar](30) NULL 
475
476Non-Formatted: [tasks] [smallint] NULL 
477
478 Number of worker tasks currently allocated, for active requests 
479
480  
481
482Formatted/Non: [status] [varchar](30) NOT NULL 
483
484 Activity status for the session (running, sleeping, etc) 
485
486  
487
488Formatted/Non: [wait_info] [nvarchar](4000) NULL 
489
490 Aggregates wait information, in the following format: 
491
492  (Ax: Bms/Cms/Dms)E 
493
494 A is the number of waiting tasks currently waiting on resource type E. B/C/D are wait 
495
496 times, in milliseconds. If only one thread is waiting, its wait time will be shown as B. 
497
498 If two tasks are waiting, each of their wait times will be shown (B/C). If three or more  
499
500 tasks are waiting, the minimum, average, and maximum wait times will be shown (B/C/D). 
501
502 If wait type E is a page latch wait and the page is of a "special" type (e.g. PFS, GAM, SGAM),  
503
504 the page type will be identified. 
505
506 If wait type E is CXPACKET, the nodeId from the query plan will be identified 
507
508  
509
510Formatted/Non: [locks] [xml] NULL 
511
512 (Requires @get_locks option) 
513
514 Aggregates lock information, in XML format. 
515
516 The lock XML includes the lock mode, locked object, and aggregates the number of requests.  
517
518 Attempts are made to identify locked objects by name 
519
520  
521
522Formatted/Non: [tran_start_time] [datetime] NULL 
523
524 (Requires @get_transaction_info option) 
525
526 Date and time that the first transaction opened by a session caused a transaction log  
527
528 write to occur. 
529
530  
531
532Formatted/Non: [tran_log_writes] [nvarchar](4000) NULL 
533
534 (Requires @get_transaction_info option) 
535
536 Aggregates transaction log write information, in the following format: 
537
538 A:wB (C kB) 
539
540 A is a database that has been touched by an active transaction 
541
542 B is the number of log writes that have been made in the database as a result of the transaction 
543
544 C is the number of log kilobytes consumed by the log records 
545
546  
547
548Formatted:  [open_tran_count] [varchar](30) NULL 
549
550Non-Formatted: [open_tran_count] [smallint] NULL 
551
552 Shows the number of open transactions the session has open 
553
554  
555
556Formatted:  [sql_command] [xml] NULL 
557
558Non-Formatted: [sql_command] [nvarchar](max) NULL 
559
560 (Requires @get_outer_command option) 
561
562 Shows the "outer" SQL command, i.e. the text of the batch or RPC sent to the server,  
563
564 if available 
565
566  
567
568Formatted:  [sql_text] [xml] NULL 
569
570Non-Formatted: [sql_text] [nvarchar](max) NULL 
571
572 Shows the SQL text for active requests or the last statement executed 
573
574 for sleeping sessions, if available in either case. 
575
576 If @get_full_inner_text option is set, shows the full text of the batch. 
577
578 Otherwise, shows only the active statement within the batch. 
579
580 If the query text is locked, a special timeout message will be sent, in the following format: 
581
582  <timeout_exceeded /> 
583
584 If an error occurs, an error message will be sent, in the following format: 
585
586  <error message="message" /> 
587
588  
589
590Formatted/Non: [query_plan] [xml] NULL 
591
592 (Requires @get_plans option) 
593
594 Shows the query plan for the request, if available. 
595
596 If the plan is locked, a special timeout message will be sent, in the following format: 
597
598  <timeout_exceeded /> 
599
600 If an error occurs, an error message will be sent, in the following format: 
601
602  <error message="message" /> 
603
604  
605
606Formatted/Non: [blocking_session_id] [smallint] NULL 
607
608 When applicable, shows the blocking SPID 
609
610  
611
612Formatted:  [blocked_session_count] [varchar](30) NULL 
613
614Non-Formatted: [blocked_session_count] [smallint] NULL 
615
616 (Requires @find_block_leaders option) 
617
618 The total number of SPIDs blocked by this session, 
619
620 all the way down the blocking chain. 
621
622  
623
624Formatted:  [percent_complete] [varchar](30) NULL 
625
626Non-Formatted: [percent_complete] [real] NULL 
627
628 When applicable, shows the percent complete (e.g. for backups, restores, and some rollbacks) 
629
630  
631
632Formatted/Non: [host_name] [sysname] NOT NULL 
633
634 Shows the host name for the connection 
635
636  
637
638Formatted/Non: [login_name] [sysname] NOT NULL 
639
640 Shows the login name for the connection 
641
642  
643
644Formatted/Non: [database_name] [sysname] NULL 
645
646 Shows the connected database 
647
648  
649
650Formatted/Non: [program_name] [sysname] NULL 
651
652 Shows the reported program/application name 
653
654  
655
656Formatted/Non: [additional_info] [xml] NULL 
657
658 (Requires @get_additional_info option) 
659
660 Returns additional non-performance-related session/request information 
661
662 If the script finds a SQL Agent job running, the name of the job and job step will be reported 
663
664 If @get_task_info = 2 and the script finds a lock wait, the locked object will be reported 
665
666  
667
668Formatted/Non: [start_time] [datetime] NOT NULL 
669
670 For active requests, shows the time the request started 
671
672 For sleeping sessions, shows the time the last batch completed 
673
674  
675
676Formatted/Non: [login_time] [datetime] NOT NULL 
677
678 Shows the time that the session connected 
679
680  
681
682Formatted/Non: [request_id] [int] NULL 
683
684 For active requests, shows the request_id 
685
686 Should be 0 unless MARS is being used 
687
688  
689
690Formatted/Non: [collection_time] [datetime] NOT NULL 
691
692 Time that this script's final SELECT ran 
693
694*/ 
695
696--AS 
697
698BEGIN; 
699
700 SET NOCOUNT ON;  
701
702 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
703
704 SET QUOTED_IDENTIFIER ON; 
705
706 SET ANSI_PADDING ON; 
707
708 SET CONCAT_NULL_YIELDS_NULL ON; 
709
710 SET ANSI_WARNINGS ON; 
711
712 SET NUMERIC_ROUNDABORT OFF; 
713
714 SET ARITHABORT ON; 
715
716  
717
718 IF 
719
720  @filter IS NULL 
721
722  OR @filter_type IS NULL 
723
724  OR @not_filter IS NULL 
725
726  OR @not_filter_type IS NULL 
727
728  OR @show_own_spid IS NULL 
729
730  OR @show_system_spids IS NULL 
731
732  OR @show_sleeping_spids IS NULL 
733
734  OR @get_full_inner_text IS NULL 
735
736  OR @get_plans IS NULL 
737
738  OR @get_outer_command IS NULL 
739
740  OR @get_transaction_info IS NULL 
741
742  OR @get_task_info IS NULL 
743
744  OR @get_locks IS NULL 
745
746  OR @get_avg_time IS NULL 
747
748  OR @get_additional_info IS NULL 
749
750  OR @find_block_leaders IS NULL 
751
752  OR @delta_interval IS NULL 
753
754  OR @format_output IS NULL 
755
756  OR @output_column_list IS NULL 
757
758  OR @sort_order IS NULL 
759
760  OR @return_schema IS NULL 
761
762  OR @destination_table IS NULL 
763
764  OR @help IS NULL 
765
766 BEGIN; 
767
768  RAISERROR('Input parameters cannot be NULL', 16, 1); 
769
770  RETURN; 
771
772 END; 
773
774   
775
776 IF @filter_type NOT IN ('session', 'program', 'database', 'login', 'host') 
777
778 BEGIN; 
779
780  RAISERROR('Valid filter types are: session, program, database, login, host', 16, 1); 
781
782  RETURN; 
783
784 END; 
785
786   
787
788 IF @filter_type = 'session' AND @filter LIKE '%[^0123456789]%' 
789
790 BEGIN; 
791
792  RAISERROR('Session filters must be valid integers', 16, 1); 
793
794  RETURN; 
795
796 END; 
797
798   
799
800 IF @not_filter_type NOT IN ('session', 'program', 'database', 'login', 'host') 
801
802 BEGIN; 
803
804  RAISERROR('Valid filter types are: session, program, database, login, host', 16, 1); 
805
806  RETURN; 
807
808 END; 
809
810   
811
812 IF @not_filter_type = 'session' AND @not_filter LIKE '%[^0123456789]%' 
813
814 BEGIN; 
815
816  RAISERROR('Session filters must be valid integers', 16, 1); 
817
818  RETURN; 
819
820 END; 
821
822   
823
824 IF @show_sleeping_spids NOT IN (0, 1, 2) 
825
826 BEGIN; 
827
828  RAISERROR('Valid values for @show_sleeping_spids are: 0, 1, or 2', 16, 1); 
829
830  RETURN; 
831
832 END; 
833
834   
835
836 IF @get_plans NOT IN (0, 1, 2) 
837
838 BEGIN; 
839
840  RAISERROR('Valid values for @get_plans are: 0, 1, or 2', 16, 1); 
841
842  RETURN; 
843
844 END; 
845
846  
847
848 IF @get_task_info NOT IN (0, 1, 2) 
849
850 BEGIN; 
851
852  RAISERROR('Valid values for @get_task_info are: 0, 1, or 2', 16, 1); 
853
854  RETURN; 
855
856 END; 
857
858  
859
860 IF @format_output NOT IN (0, 1, 2) 
861
862 BEGIN; 
863
864  RAISERROR('Valid values for @format_output are: 0, 1, or 2', 16, 1); 
865
866  RETURN; 
867
868 END; 
869
870   
871
872 IF @help = 1 
873
874 BEGIN; 
875
876  DECLARE  
877
878   @header VARCHAR(MAX), 
879
880   @params VARCHAR(MAX), 
881
882   @outputs VARCHAR(MAX); 
883
884  
885
886  SELECT  
887
888   @header = 
889
890    REPLACE 
891
892    ( 
893
894     REPLACE 
895
896     ( 
897
898      CONVERT 
899
900      ( 
901
902       VARCHAR(MAX), 
903
904       SUBSTRING 
905
906       ( 
907
908        t.text,  
909
910        CHARINDEX('/' + REPLICATE('*', 93), t.text) + 94, 
911
912        CHARINDEX(REPLICATE('*', 93) + '/', t.text) - (CHARINDEX('/' + REPLICATE('*', 93), t.text) + 94) 
913
914       ) 
915
916      ), 
917
918      CHAR(13)+CHAR(10), 
919
920      CHAR(13) 
921
922     ), 
923
924     ' ', 
925
926     '' 
927
928    ), 
929
930   @params = 
931
932    CHAR(13) + 
933
934     REPLACE 
935
936     ( 
937
938      REPLACE 
939
940      ( 
941
942       CONVERT 
943
944       ( 
945
946        VARCHAR(MAX), 
947
948        SUBSTRING 
949
950        ( 
951
952         t.text,  
953
954         CHARINDEX('--~', t.text) + 5,  
955
956         CHARINDEX('--~', t.text, CHARINDEX('--~', t.text) + 5) - (CHARINDEX('--~', t.text) + 5) 
957
958        ) 
959
960       ), 
961
962       CHAR(13)+CHAR(10), 
963
964       CHAR(13) 
965
966      ), 
967
968      ' ', 
969
970      '' 
971
972     ), 
973
974    @outputs =  
975
976     CHAR(13) + 
977
978      REPLACE 
979
980      ( 
981
982       REPLACE 
983
984       ( 
985
986        REPLACE 
987
988        ( 
989
990         CONVERT 
991
992         ( 
993
994          VARCHAR(MAX), 
995
996          SUBSTRING 
997
998          ( 
999
1000           t.text,  
1001
1002           CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32, 
1003
1004           CHARINDEX('*/', t.text, CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32) - (CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32) 
1005
1006          ) 
1007
1008         ), 
1009
1010         CHAR(9), 
1011
1012         CHAR(255) 
1013
1014        ), 
1015
1016        CHAR(13)+CHAR(10), 
1017
1018        CHAR(13) 
1019
1020       ), 
1021
1022       ' ', 
1023
1024       '' 
1025
1026      ) + 
1027
1028      CHAR(13) 
1029
1030  FROM sys.dm_exec_requests AS r 
1031
1032  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t 
1033
1034  WHERE 
1035
1036   r.session_id = @@SPID; 
1037
1038  
1039
1040  WITH 
1041
1042  a0 AS 
1043
1044  (SELECT 1 AS n UNION ALL SELECT 1), 
1045
1046  a1 AS 
1047
1048  (SELECT 1 AS n FROM a0 AS a, a0 AS b), 
1049
1050  a2 AS 
1051
1052  (SELECT 1 AS n FROM a1 AS a, a1 AS b), 
1053
1054  a3 AS 
1055
1056  (SELECT 1 AS n FROM a2 AS a, a2 AS b), 
1057
1058  a4 AS 
1059
1060  (SELECT 1 AS n FROM a3 AS a, a3 AS b), 
1061
1062  numbers AS 
1063
1064  ( 
1065
1066   SELECT TOP(LEN(@header) - 1) 
1067
1068    ROW_NUMBER() OVER 
1069
1070    ( 
1071
1072     ORDER BY (SELECT NULL) 
1073
1074    ) AS number 
1075
1076   FROM a4 
1077
1078   ORDER BY  
1079
1080    number 
1081
1082  ) 
1083
1084  SELECT 
1085
1086   RTRIM(LTRIM( 
1087
1088    SUBSTRING 
1089
1090    ( 
1091
1092     @header, 
1093
1094     number + 1, 
1095
1096     CHARINDEX(CHAR(13), @header, number + 1) - number - 1 
1097
1098    ) 
1099
1100   )) AS [------header---------------------------------------------------------------------------------------------------------------] 
1101
1102  FROM numbers 
1103
1104  WHERE 
1105
1106   SUBSTRING(@header, number, 1) = CHAR(13); 
1107
1108  
1109
1110  WITH 
1111
1112  a0 AS 
1113
1114  (SELECT 1 AS n UNION ALL SELECT 1), 
1115
1116  a1 AS 
1117
1118  (SELECT 1 AS n FROM a0 AS a, a0 AS b), 
1119
1120  a2 AS 
1121
1122  (SELECT 1 AS n FROM a1 AS a, a1 AS b), 
1123
1124  a3 AS 
1125
1126  (SELECT 1 AS n FROM a2 AS a, a2 AS b), 
1127
1128  a4 AS 
1129
1130  (SELECT 1 AS n FROM a3 AS a, a3 AS b), 
1131
1132  numbers AS 
1133
1134  ( 
1135
1136   SELECT TOP(LEN(@params) - 1) 
1137
1138    ROW_NUMBER() OVER 
1139
1140    ( 
1141
1142     ORDER BY (SELECT NULL) 
1143
1144    ) AS number 
1145
1146   FROM a4 
1147
1148   ORDER BY 
1149
1150    number 
1151
1152  ), 
1153
1154  tokens AS 
1155
1156  ( 
1157
1158   SELECT  
1159
1160    RTRIM(LTRIM( 
1161
1162     SUBSTRING 
1163
1164     ( 
1165
1166      @params, 
1167
1168      number + 1, 
1169
1170      CHARINDEX(CHAR(13), @params, number + 1) - number - 1 
1171
1172     ) 
1173
1174    )) AS token, 
1175
1176    number, 
1177
1178    CASE 
1179
1180     WHEN SUBSTRING(@params, number + 1, 1) = CHAR(13) THEN number 
1181
1182     ELSE COALESCE(NULLIF(CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number), 0), LEN(@params))  
1183
1184    END AS param_group, 
1185
1186    ROW_NUMBER() OVER 
1187
1188    ( 
1189
1190     PARTITION BY 
1191
1192      CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number), 
1193
1194      SUBSTRING(@params, number+1, 1) 
1195
1196     ORDER BY  
1197
1198      number 
1199
1200    ) AS group_order 
1201
1202   FROM numbers 
1203
1204   WHERE 
1205
1206    SUBSTRING(@params, number, 1) = CHAR(13) 
1207
1208  ), 
1209
1210  parsed_tokens AS 
1211
1212  ( 
1213
1214   SELECT 
1215
1216    MIN 
1217
1218    ( 
1219
1220     CASE 
1221
1222      WHEN token LIKE '@%' THEN token 
1223
1224      ELSE NULL 
1225
1226     END 
1227
1228    ) AS parameter, 
1229
1230    MIN 
1231
1232    ( 
1233
1234     CASE 
1235
1236      WHEN token LIKE '--%' THEN RIGHT(token, LEN(token) - 2) 
1237
1238      ELSE NULL 
1239
1240     END 
1241
1242    ) AS description, 
1243
1244    param_group, 
1245
1246    group_order 
1247
1248   FROM tokens 
1249
1250   WHERE 
1251
1252    NOT  
1253
1254    ( 
1255
1256     token = ''  
1257
1258     AND group_order > 1 
1259
1260    ) 
1261
1262   GROUP BY 
1263
1264    param_group, 
1265
1266    group_order 
1267
1268  ) 
1269
1270  SELECT 
1271
1272   CASE 
1273
1274    WHEN description IS NULL AND parameter IS NULL THEN '-------------------------------------------------------------------------' 
1275
1276    WHEN param_group = MAX(param_group) OVER() THEN parameter 
1277
1278    ELSE COALESCE(LEFT(parameter, LEN(parameter) - 1), '') 
1279
1280   END AS [------parameter----------------------------------------------------------], 
1281
1282   CASE 
1283
1284    WHEN description IS NULL AND parameter IS NULL THEN '----------------------------------------------------------------------------------------------------------------------' 
1285
1286    ELSE COALESCE(description, '') 
1287
1288   END AS [------description-----------------------------------------------------------------------------------------------------] 
1289
1290  FROM parsed_tokens 
1291
1292  ORDER BY 
1293
1294   param_group,  
1295
1296   group_order; 
1297
1298    
1299
1300  WITH 
1301
1302  a0 AS 
1303
1304  (SELECT 1 AS n UNION ALL SELECT 1), 
1305
1306  a1 AS 
1307
1308  (SELECT 1 AS n FROM a0 AS a, a0 AS b), 
1309
1310  a2 AS 
1311
1312  (SELECT 1 AS n FROM a1 AS a, a1 AS b), 
1313
1314  a3 AS 
1315
1316  (SELECT 1 AS n FROM a2 AS a, a2 AS b), 
1317
1318  a4 AS 
1319
1320  (SELECT 1 AS n FROM a3 AS a, a3 AS b), 
1321
1322  numbers AS 
1323
1324  ( 
1325
1326   SELECT TOP(LEN(@outputs) - 1) 
1327
1328    ROW_NUMBER() OVER 
1329
1330    ( 
1331
1332     ORDER BY (SELECT NULL) 
1333
1334    ) AS number 
1335
1336   FROM a4 
1337
1338   ORDER BY 
1339
1340    number 
1341
1342  ), 
1343
1344  tokens AS 
1345
1346  ( 
1347
1348   SELECT  
1349
1350    RTRIM(LTRIM( 
1351
1352     SUBSTRING 
1353
1354     ( 
1355
1356      @outputs, 
1357
1358      number + 1, 
1359
1360      CASE 
1361
1362       WHEN  
1363
1364        COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) <  
1365
1366        COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) 
1367
1368        THEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) - number - 1 
1369
1370       ELSE 
1371
1372        COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) - number - 1 
1373
1374      END 
1375
1376     ) 
1377
1378    )) AS token, 
1379
1380    number, 
1381
1382    COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) AS output_group, 
1383
1384    ROW_NUMBER() OVER 
1385
1386    ( 
1387
1388     PARTITION BY  
1389
1390      COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) 
1391
1392     ORDER BY 
1393
1394      number 
1395
1396    ) AS output_group_order 
1397
1398   FROM numbers 
1399
1400   WHERE 
1401
1402    SUBSTRING(@outputs, number, 10) = CHAR(13) + 'Formatted' 
1403
1404    OR SUBSTRING(@outputs, number, 2) = CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2 
1405
1406  ), 
1407
1408  output_tokens AS 
1409
1410  ( 
1411
1412   SELECT  
1413
1414    *, 
1415
1416    CASE output_group_order 
1417
1418     WHEN 2 THEN MAX(CASE output_group_order WHEN 1 THEN token ELSE NULL END) OVER (PARTITION BY output_group) 
1419
1420     ELSE '' 
1421
1422    END COLLATE Latin1_General_Bin2 AS column_info 
1423
1424   FROM tokens 
1425
1426  ) 
1427
1428  SELECT 
1429
1430   CASE output_group_order 
1431
1432    WHEN 1 THEN '-----------------------------------' 
1433
1434    WHEN 2 THEN  
1435
1436     CASE 
1437
1438      WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN 
1439
1440       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)) 
1441
1442      ELSE 
1443
1444       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)
1445
1446 
1447
1448     END 
1449
1450    ELSE '' 
1451
1452   END AS formatted_column_name, 
1453
1454   CASE output_group_order 
1455
1456    WHEN 1 THEN '-----------------------------------' 
1457
1458    WHEN 2 THEN  
1459
1460     CASE 
1461
1462      WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN 
1463
1464       SUBSTRING(column_info, CHARINDEX(']', column_info)+2, LEN(column_info)) 
1465
1466      ELSE 
1467
1468       SUBSTRING(column_info, CHARINDEX(']', column_info)+2, CHARINDEX('Non-Formatted:', column_info, CHARINDEX(']', column_info)+2) - CHARINDEX(']', column_info)-3) 
1469
1470     END 
1471
1472    ELSE '' 
1473
1474   END AS formatted_column_type, 
1475
1476   CASE output_group_order 
1477
1478    WHEN 1 THEN '---------------------------------------' 
1479
1480    WHEN 2 THEN  
1481
1482     CASE 
1483
1484      WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN '' 
1485
1486      ELSE 
1487
1488       CASE 
1489
1490        WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN 
1491
1492         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:'
1493
1494, column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))) 
1495
1496        ELSE 
1497
1498         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:'
1499
1500, column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))) 
1501
1502       END 
1503
1504     END 
1505
1506    ELSE '' 
1507
1508   END AS unformatted_column_name, 
1509
1510   CASE output_group_order 
1511
1512    WHEN 1 THEN '---------------------------------------' 
1513
1514    WHEN 2 THEN  
1515
1516     CASE 
1517
1518      WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN '' 
1519
1520      ELSE 
1521
1522       CASE 
1523
1524        WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN '' 
1525
1526        ELSE 
1527
1528         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) 
1529
1530       END 
1531
1532     END 
1533
1534    ELSE '' 
1535
1536   END AS unformatted_column_type, 
1537
1538   CASE output_group_order 
1539
1540    WHEN 1 THEN '----------------------------------------------------------------------------------------------------------------------' 
1541
1542    ELSE REPLACE(token, CHAR(255) COLLATE Latin1_General_Bin2, '') 
1543
1544   END AS [------description-----------------------------------------------------------------------------------------------------] 
1545
1546  FROM output_tokens 
1547
1548  WHERE 
1549
1550   NOT  
1551
1552   ( 
1553
1554    output_group_order = 1  
1555
1556    AND output_group = LEN(@outputs) 
1557
1558   ) 
1559
1560  ORDER BY 
1561
1562   output_group, 
1563
1564   CASE output_group_order 
1565
1566    WHEN 1 THEN 99 
1567
1568    ELSE output_group_order 
1569
1570   END; 
1571
1572  
1573
1574  RETURN; 
1575
1576 END; 
1577
1578  
1579
1580 WITH 
1581
1582 a0 AS 
1583
1584 (SELECT 1 AS n UNION ALL SELECT 1), 
1585
1586 a1 AS 
1587
1588 (SELECT 1 AS n FROM a0 AS a, a0 AS b), 
1589
1590 a2 AS 
1591
1592 (SELECT 1 AS n FROM a1 AS a, a1 AS b), 
1593
1594 a3 AS 
1595
1596 (SELECT 1 AS n FROM a2 AS a, a2 AS b), 
1597
1598 a4 AS 
1599
1600 (SELECT 1 AS n FROM a3 AS a, a3 AS b), 
1601
1602 numbers AS 
1603
1604 ( 
1605
1606  SELECT TOP(LEN(@output_column_list)) 
1607
1608   ROW_NUMBER() OVER 
1609
1610   ( 
1611
1612    ORDER BY (SELECT NULL) 
1613
1614   ) AS number 
1615
1616  FROM a4 
1617
1618  ORDER BY 
1619
1620   number 
1621
1622 ), 
1623
1624 tokens AS 
1625
1626 ( 
1627
1628  SELECT  
1629
1630   '|[' + 
1631
1632    SUBSTRING 
1633
1634    ( 
1635
1636     @output_column_list, 
1637
1638     number + 1, 
1639
1640     CHARINDEX(']', @output_column_list, number) - number - 1 
1641
1642    ) + '|]' AS token, 
1643
1644   number 
1645
1646  FROM numbers 
1647
1648  WHERE 
1649
1650   SUBSTRING(@output_column_list, number, 1) = '[' 
1651
1652 ), 
1653
1654 ordered_columns AS 
1655
1656 ( 
1657
1658  SELECT 
1659
1660   x.column_name, 
1661
1662   ROW_NUMBER() OVER 
1663
1664   ( 
1665
1666    PARTITION BY 
1667
1668     x.column_name 
1669
1670    ORDER BY 
1671
1672     tokens.number, 
1673
1674     x.default_order 
1675
1676   ) AS r, 
1677
1678   ROW_NUMBER() OVER 
1679
1680   ( 
1681
1682    ORDER BY 
1683
1684     tokens.number, 
1685
1686     x.default_order 
1687
1688   ) AS s 
1689
1690  FROM tokens 
1691
1692  JOIN 
1693
1694  ( 
1695
1696   SELECT '[session_id]' AS column_name, 1 AS default_order 
1697
1698   UNION ALL 
1699
1700   SELECT '[dd hh:mm:ss.mss]', 2 
1701
1702   WHERE 
1703
1704    @format_output IN (1, 2) 
1705
1706   UNION ALL 
1707
1708   SELECT '[dd hh:mm:ss.mss (avg)]', 3 
1709
1710   WHERE 
1711
1712    @format_output IN (1, 2) 
1713
1714    AND @get_avg_time = 1 
1715
1716   UNION ALL 
1717
1718   SELECT '[avg_elapsed_time]', 4 
1719
1720   WHERE 
1721
1722    @format_output = 0 
1723
1724    AND @get_avg_time = 1 
1725
1726   UNION ALL 
1727
1728   SELECT '[physical_io]', 5 
1729
1730   WHERE 
1731
1732    @get_task_info = 2 
1733
1734   UNION ALL 
1735
1736   SELECT '[reads]', 6 
1737
1738   UNION ALL 
1739
1740   SELECT '[physical_reads]', 7 
1741
1742   UNION ALL 
1743
1744   SELECT '[writes]', 8 
1745
1746   UNION ALL 
1747
1748   SELECT '[tempdb_allocations]', 9 
1749
1750   UNION ALL 
1751
1752   SELECT '[tempdb_current]', 10 
1753
1754   UNION ALL 
1755
1756   SELECT '[CPU]', 11 
1757
1758   UNION ALL 
1759
1760   SELECT '[context_switches]', 12 
1761
1762   WHERE 
1763
1764    @get_task_info = 2 
1765
1766   UNION ALL 
1767
1768   SELECT '[used_memory]', 13 
1769
1770   UNION ALL 
1771
1772   SELECT '[physical_io_delta]', 14 
1773
1774   WHERE 
1775
1776    @delta_interval > 0  
1777
1778    AND @get_task_info = 2 
1779
1780   UNION ALL 
1781
1782   SELECT '[reads_delta]', 15 
1783
1784   WHERE 
1785
1786    @delta_interval > 0 
1787
1788   UNION ALL 
1789
1790   SELECT '[physical_reads_delta]', 16 
1791
1792   WHERE 
1793
1794    @delta_interval > 0 
1795
1796   UNION ALL 
1797
1798   SELECT '[writes_delta]', 17 
1799
1800   WHERE 
1801
1802    @delta_interval > 0 
1803
1804   UNION ALL 
1805
1806   SELECT '[tempdb_allocations_delta]', 18 
1807
1808   WHERE 
1809
1810    @delta_interval > 0 
1811
1812   UNION ALL 
1813
1814   SELECT '[tempdb_current_delta]', 19 
1815
1816   WHERE 
1817
1818    @delta_interval > 0 
1819
1820   UNION ALL 
1821
1822   SELECT '[CPU_delta]', 20 
1823
1824   WHERE 
1825
1826    @delta_interval > 0 
1827
1828   UNION ALL 
1829
1830   SELECT '[context_switches_delta]', 21 
1831
1832   WHERE 
1833
1834    @delta_interval > 0 
1835
1836    AND @get_task_info = 2 
1837
1838   UNION ALL 
1839
1840   SELECT '[used_memory_delta]', 22 
1841
1842   WHERE 
1843
1844    @delta_interval > 0 
1845
1846   UNION ALL 
1847
1848   SELECT '[tasks]', 23 
1849
1850   WHERE 
1851
1852    @get_task_info = 2 
1853
1854   UNION ALL 
1855
1856   SELECT '[status]', 24 
1857
1858   UNION ALL 
1859
1860   SELECT '[wait_info]', 25 
1861
1862   WHERE 
1863
1864    @get_task_info > 0 
1865
1866    OR @find_block_leaders = 1 
1867
1868   UNION ALL 
1869
1870   SELECT '[locks]', 26 
1871
1872   WHERE 
1873
1874    @get_locks = 1 
1875
1876   UNION ALL 
1877
1878   SELECT '[tran_start_time]', 27 
1879
1880   WHERE 
1881
1882    @get_transaction_info = 1 
1883
1884   UNION ALL 
1885
1886   SELECT '[tran_log_writes]', 28 
1887
1888   WHERE 
1889
1890    @get_transaction_info = 1 
1891
1892   UNION ALL 
1893
1894   SELECT '[open_tran_count]', 29 
1895
1896   UNION ALL 
1897
1898   SELECT '[sql_command]', 30 
1899
1900   WHERE 
1901
1902    @get_outer_command = 1 
1903
1904   UNION ALL 
1905
1906   SELECT '[sql_text]', 31 
1907
1908   UNION ALL 
1909
1910   SELECT '[query_plan]', 32 
1911
1912   WHERE 
1913
1914    @get_plans >= 1 
1915
1916   UNION ALL 
1917
1918   SELECT '[blocking_session_id]', 33 
1919
1920   WHERE 
1921
1922    @get_task_info > 0 
1923
1924    OR @find_block_leaders = 1 
1925
1926   UNION ALL 
1927
1928   SELECT '[blocked_session_count]', 34 
1929
1930   WHERE 
1931
1932    @find_block_leaders = 1 
1933
1934   UNION ALL 
1935
1936   SELECT '[percent_complete]', 35 
1937
1938   UNION ALL 
1939
1940   SELECT '[host_name]', 36 
1941
1942   UNION ALL 
1943
1944   SELECT '[login_name]', 37 
1945
1946   UNION ALL 
1947
1948   SELECT '[database_name]', 38 
1949
1950   UNION ALL 
1951
1952   SELECT '[program_name]', 39 
1953
1954   UNION ALL 
1955
1956   SELECT '[additional_info]', 40 
1957
1958   WHERE 
1959
1960    @get_additional_info = 1 
1961
1962   UNION ALL 
1963
1964   SELECT '[start_time]', 41 
1965
1966   UNION ALL 
1967
1968   SELECT '[login_time]', 42 
1969
1970   UNION ALL 
1971
1972   SELECT '[request_id]', 43 
1973
1974   UNION ALL 
1975
1976   SELECT '[collection_time]', 44 
1977
1978  ) AS x ON  
1979
1980   x.column_name LIKE token ESCAPE '|' 
1981
1982 ) 
1983
1984 SELECT 
1985
1986  @output_column_list = 
1987
1988   STUFF 
1989
1990   ( 
1991
1992    ( 
1993
1994     SELECT 
1995
1996      ',' + column_name as [text()] 
1997
1998     FROM ordered_columns 
1999
2000     WHERE 
2001
2002      r = 1 
2003
2004     ORDER BY 
2005
2006      s 
2007
2008     FOR XML 
2009
2010      PATH('') 
2011
2012    ), 
2013
2014    1, 
2015
2016    1, 
2017
2018    '' 
2019
2020   ); 
2021
2022   
2023
2024 IF COALESCE(RTRIM(@output_column_list), '') = '' 
2025
2026 BEGIN; 
2027
2028  RAISERROR('No valid column matches found in @output_column_list or no columns remain due to selected options.', 16, 1); 
2029
2030  RETURN; 
2031
2032 END; 
2033
2034   
2035
2036 IF @destination_table <> '' 
2037
2038 BEGIN; 
2039
2040  SET @destination_table =  
2041
2042   --database 
2043
2044   COALESCE(QUOTENAME(PARSENAME(@destination_table, 3)) + '.', '') + 
2045
2046   --schema 
2047
2048   COALESCE(QUOTENAME(PARSENAME(@destination_table, 2)) + '.', '') + 
2049
2050   --table 
2051
2052   COALESCE(QUOTENAME(PARSENAME(@destination_table, 1)), ''); 
2053
2054     
2055
2056  IF COALESCE(RTRIM(@destination_table), '') = '' 
2057
2058  BEGIN; 
2059
2060   RAISERROR('Destination table not properly formatted.', 16, 1); 
2061
2062   RETURN; 
2063
2064  END; 
2065
2066 END; 
2067
2068  
2069
2070 WITH 
2071
2072 a0 AS 
2073
2074 (SELECT 1 AS n UNION ALL SELECT 1), 
2075
2076 a1 AS 
2077
2078 (SELECT 1 AS n FROM a0 AS a, a0 AS b), 
2079
2080 a2 AS 
2081
2082 (SELECT 1 AS n FROM a1 AS a, a1 AS b), 
2083
2084 a3 AS 
2085
2086 (SELECT 1 AS n FROM a2 AS a, a2 AS b), 
2087
2088 a4 AS 
2089
2090 (SELECT 1 AS n FROM a3 AS a, a3 AS b), 
2091
2092 numbers AS 
2093
2094 ( 
2095
2096  SELECT TOP(LEN(@sort_order)) 
2097
2098   ROW_NUMBER() OVER 
2099
2100   ( 
2101
2102    ORDER BY (SELECT NULL) 
2103
2104   ) AS number 
2105
2106  FROM a4 
2107
2108  ORDER BY 
2109
2110   number 
2111
2112 ), 
2113
2114 tokens AS 
2115
2116 ( 
2117
2118  SELECT  
2119
2120   '|[' + 
2121
2122    SUBSTRING 
2123
2124    ( 
2125
2126     @sort_order, 
2127
2128     number + 1, 
2129
2130     CHARINDEX(']', @sort_order, number) - number - 1 
2131
2132    ) + '|]' AS token, 
2133
2134   SUBSTRING 
2135
2136   ( 
2137
2138    @sort_order, 
2139
2140    CHARINDEX(']', @sort_order, number) + 1, 
2141
2142    COALESCE(NULLIF(CHARINDEX('[', @sort_order, CHARINDEX(']', @sort_order, number)), 0), LEN(@sort_order)) - CHARINDEX(']', @sort_order, number) 
2143
2144   ) AS next_chunk, 
2145
2146   number 
2147
2148  FROM numbers 
2149
2150  WHERE 
2151
2152   SUBSTRING(@sort_order, number, 1) = '[' 
2153
2154 ), 
2155
2156 ordered_columns AS 
2157
2158 ( 
2159
2160  SELECT 
2161
2162   x.column_name + 
2163
2164    CASE 
2165
2166     WHEN tokens.next_chunk LIKE '%asc%' THEN ' ASC' 
2167
2168     WHEN tokens.next_chunk LIKE '%desc%' THEN ' DESC' 
2169
2170     ELSE '' 
2171
2172    END AS column_name, 
2173
2174   ROW_NUMBER() OVER 
2175
2176   ( 
2177
2178    PARTITION BY 
2179
2180     x.column_name 
2181
2182    ORDER BY 
2183
2184     tokens.number 
2185
2186   ) AS r, 
2187
2188   tokens.number 
2189
2190  FROM tokens 
2191
2192  JOIN 
2193
2194  ( 
2195
2196   SELECT '[session_id]' AS column_name 
2197
2198   UNION ALL 
2199
2200   SELECT '[physical_io]' 
2201
2202   UNION ALL 
2203
2204   SELECT '[reads]' 
2205
2206   UNION ALL 
2207
2208   SELECT '[physical_reads]' 
2209
2210   UNION ALL 
2211
2212   SELECT '[writes]' 
2213
2214   UNION ALL 
2215
2216   SELECT '[tempdb_allocations]' 
2217
2218   UNION ALL 
2219
2220   SELECT '[tempdb_current]' 
2221
2222   UNION ALL 
2223
2224   SELECT '[CPU]' 
2225
2226   UNION ALL 
2227
2228   SELECT '[context_switches]' 
2229
2230   UNION ALL 
2231
2232   SELECT '[used_memory]' 
2233
2234   UNION ALL 
2235
2236   SELECT '[physical_io_delta]' 
2237
2238   UNION ALL 
2239
2240   SELECT '[reads_delta]' 
2241
2242   UNION ALL 
2243
2244   SELECT '[physical_reads_delta]' 
2245
2246   UNION ALL 
2247
2248   SELECT '[writes_delta]' 
2249
2250   UNION ALL 
2251
2252   SELECT '[tempdb_allocations_delta]' 
2253
2254   UNION ALL 
2255
2256   SELECT '[tempdb_current_delta]' 
2257
2258   UNION ALL 
2259
2260   SELECT '[CPU_delta]' 
2261
2262   UNION ALL 
2263
2264   SELECT '[context_switches_delta]' 
2265
2266   UNION ALL 
2267
2268   SELECT '[used_memory_delta]' 
2269
2270   UNION ALL 
2271
2272   SELECT '[tasks]' 
2273
2274   UNION ALL 
2275
2276   SELECT '[tran_start_time]' 
2277
2278   UNION ALL 
2279
2280   SELECT '[open_tran_count]' 
2281
2282   UNION ALL 
2283
2284   SELECT '[blocking_session_id]' 
2285
2286   UNION ALL 
2287
2288   SELECT '[blocked_session_count]' 
2289
2290   UNION ALL 
2291
2292   SELECT '[percent_complete]' 
2293
2294   UNION ALL 
2295
2296   SELECT '[host_name]' 
2297
2298   UNION ALL 
2299
2300   SELECT '[login_name]' 
2301
2302   UNION ALL 
2303
2304   SELECT '[database_name]' 
2305
2306   UNION ALL 
2307
2308   SELECT '[start_time]' 
2309
2310   UNION ALL 
2311
2312   SELECT '[login_time]' 
2313
2314   UNION ALL 
2315
2316   SELECT '[program_name]' 
2317
2318  ) AS x ON  
2319
2320   x.column_name LIKE token ESCAPE '|' 
2321
2322 ) 
2323
2324 SELECT 
2325
2326  @sort_order = COALESCE(z.sort_order, '') 
2327
2328 FROM 
2329
2330 ( 
2331
2332  SELECT 
2333
2334   STUFF 
2335
2336   ( 
2337
2338    ( 
2339
2340     SELECT 
2341
2342      ',' + column_name as [text()] 
2343
2344     FROM ordered_columns 
2345
2346     WHERE 
2347
2348      r = 1 
2349
2350     ORDER BY 
2351
2352      number 
2353
2354     FOR XML 
2355
2356      PATH('') 
2357
2358    ), 
2359
2360    1, 
2361
2362    1, 
2363
2364    '' 
2365
2366   ) AS sort_order 
2367
2368 ) AS z; 
2369
2370  
2371
2372 CREATE TABLE #sessions 
2373
2374 ( 
2375
2376  recursion SMALLINT NOT NULL, 
2377
2378  session_id SMALLINT NOT NULL, 
2379
2380  request_id INT NOT NULL, 
2381
2382  session_number INT NOT NULL, 
2383
2384  elapsed_time INT NOT NULL, 
2385
2386  avg_elapsed_time INT NULL, 
2387
2388  physical_io BIGINT NULL, 
2389
2390  reads BIGINT NULL, 
2391
2392  physical_reads BIGINT NULL, 
2393
2394  writes BIGINT NULL, 
2395
2396  tempdb_allocations BIGINT NULL, 
2397
2398  tempdb_current BIGINT NULL, 
2399
2400  CPU INT NULL, 
2401
2402  thread_CPU_snapshot BIGINT NULL, 
2403
2404  context_switches BIGINT NULL, 
2405
2406  used_memory BIGINT NOT NULL,  
2407
2408  tasks SMALLINT NULL, 
2409
2410  status VARCHAR(30) NOT NULL, 
2411
2412  wait_info NVARCHAR(4000) NULL, 
2413
2414  locks XML NULL, 
2415
2416  transaction_id BIGINT NULL, 
2417
2418  tran_start_time DATETIME NULL, 
2419
2420  tran_log_writes NVARCHAR(4000) NULL, 
2421
2422  open_tran_count SMALLINT NULL, 
2423
2424  sql_command XML NULL, 
2425
2426  sql_handle VARBINARY(64) NULL, 
2427
2428  statement_start_offset INT NULL, 
2429
2430  statement_end_offset INT NULL, 
2431
2432  sql_text XML NULL, 
2433
2434  plan_handle VARBINARY(64) NULL, 
2435
2436  query_plan XML NULL, 
2437
2438  blocking_session_id SMALLINT NULL, 
2439
2440  blocked_session_count SMALLINT NULL, 
2441
2442  percent_complete REAL NULL, 
2443
2444  host_name sysname NULL, 
2445
2446  login_name sysname NOT NULL, 
2447
2448  database_name sysname NULL, 
2449
2450  program_name sysname NULL, 
2451
2452  additional_info XML NULL, 
2453
2454  start_time DATETIME NOT NULL, 
2455
2456  login_time DATETIME NULL, 
2457
2458  last_request_start_time DATETIME NULL, 
2459
2460  PRIMARY KEY CLUSTERED (session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON), 
2461
2462  UNIQUE NONCLUSTERED (transaction_id, session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON) 
2463
2464 ); 
2465
2466  
2467
2468 IF @return_schema = 0 
2469
2470 BEGIN; 
2471
2472  --Disable unnecessary autostats on the table 
2473
2474  CREATE STATISTICS s_session_id ON #sessions (session_id) 
2475
2476  WITH SAMPLE 0 ROWS, NORECOMPUTE; 
2477
2478  CREATE STATISTICS s_request_id ON #sessions (request_id) 
2479
2480  WITH SAMPLE 0 ROWS, NORECOMPUTE; 
2481
2482  CREATE STATISTICS s_transaction_id ON #sessions (transaction_id) 
2483
2484  WITH SAMPLE 0 ROWS, NORECOMPUTE; 
2485
2486  CREATE STATISTICS s_session_number ON #sessions (session_number) 
2487
2488  WITH SAMPLE 0 ROWS, NORECOMPUTE; 
2489
2490  CREATE STATISTICS s_status ON #sessions (status) 
2491
2492  WITH SAMPLE 0 ROWS, NORECOMPUTE; 
2493
2494  CREATE STATISTICS s_start_time ON #sessions (start_time) 
2495
2496  WITH SAMPLE 0 ROWS, NORECOMPUTE; 
2497
2498  CREATE STATISTICS s_last_request_start_time ON #sessions (last_request_start_time) 
2499
2500  WITH SAMPLE 0 ROWS, NORECOMPUTE; 
2501
2502  CREATE STATISTICS s_recursion ON #sessions (recursion) 
2503
2504  WITH SAMPLE 0 ROWS, NORECOMPUTE; 
2505
2506  
2507
2508  DECLARE @recursion SMALLINT; 
2509
2510  SET @recursion =  
2511
2512   CASE @delta_interval 
2513
2514    WHEN 0 THEN 1 
2515
2516    ELSE -1 
2517
2518   END; 
2519
2520  
2521
2522  DECLARE @first_collection_ms_ticks BIGINT; 
2523
2524  DECLARE @last_collection_start DATETIME; 
2525
2526  DECLARE @sys_info BIT; 
2527
2528  SET @sys_info = ISNULL(CONVERT(BIT, SIGN(OBJECT_ID('sys.dm_os_sys_info'))), 0); 
2529
2530  
2531
2532  --Used for the delta pull 
2533
2534  REDO:; 
2535
2536    
2537
2538  IF  
2539
2540   @get_locks = 1  
2541
2542   AND @recursion = 1 
2543
2544   AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|' 
2545
2546  BEGIN; 
2547
2548   SELECT 
2549
2550    y.resource_type, 
2551
2552    y.database_name, 
2553
2554    y.object_id, 
2555
2556    y.file_id, 
2557
2558    y.page_type, 
2559
2560    y.hobt_id, 
2561
2562    y.allocation_unit_id, 
2563
2564    y.index_id, 
2565
2566    y.schema_id, 
2567
2568    y.principal_id, 
2569
2570    y.request_mode, 
2571
2572    y.request_status, 
2573
2574    y.session_id, 
2575
2576    y.resource_description, 
2577
2578    y.request_count, 
2579
2580    s.request_id, 
2581
2582    s.start_time, 
2583
2584    CONVERT(sysname, NULL) AS object_name, 
2585
2586    CONVERT(sysname, NULL) AS index_name, 
2587
2588    CONVERT(sysname, NULL) AS schema_name, 
2589
2590    CONVERT(sysname, NULL) AS principal_name, 
2591
2592    CONVERT(NVARCHAR(2048), NULL) AS query_error 
2593
2594   INTO #locks 
2595
2596   FROM 
2597
2598   ( 
2599
2600    SELECT 
2601
2602     sp.spid AS session_id, 
2603
2604     CASE sp.status 
2605
2606      WHEN 'sleeping' THEN CONVERT(INT, 0) 
2607
2608      ELSE sp.request_id 
2609
2610     END AS request_id, 
2611
2612     CASE sp.status 
2613
2614      WHEN 'sleeping' THEN sp.last_batch 
2615
2616      ELSE COALESCE(req.start_time, sp.last_batch) 
2617
2618     END AS start_time, 
2619
2620     sp.dbid 
2621
2622    FROM sys.sysprocesses AS sp 
2623
2624    OUTER APPLY 
2625
2626    ( 
2627
2628     SELECT TOP(1) 
2629
2630      CASE 
2631
2632       WHEN  
2633
2634       ( 
2635
2636        sp.hostprocess > '' 
2637
2638        OR r.total_elapsed_time < 0 
2639
2640       ) THEN 
2641
2642        r.start_time 
2643
2644       ELSE 
2645
2646        DATEADD 
2647
2648        ( 
2649
2650         ms,  
2651
2652         1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),  
2653
2654         DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE()) 
2655
2656        ) 
2657
2658      END AS start_time 
2659
2660     FROM sys.dm_exec_requests AS r 
2661
2662     WHERE 
2663
2664      r.session_id = sp.spid 
2665
2666      AND r.request_id = sp.request_id 
2667
2668    ) AS req 
2669
2670    WHERE 
2671
2672     --Process inclusive filter 
2673
2674     1 = 
2675
2676      CASE 
2677
2678       WHEN @filter <> '' THEN 
2679
2680        CASE @filter_type 
2681
2682         WHEN 'session' THEN 
2683
2684          CASE 
2685
2686           WHEN 
2687
2688            CONVERT(SMALLINT, @filter) = 0 
2689
2690            OR sp.spid = CONVERT(SMALLINT, @filter) 
2691
2692             THEN 1 
2693
2694           ELSE 0 
2695
2696          END 
2697
2698         WHEN 'program' THEN 
2699
2700          CASE 
2701
2702           WHEN sp.program_name LIKE @filter THEN 1 
2703
2704           ELSE 0 
2705
2706          END 
2707
2708         WHEN 'login' THEN 
2709
2710          CASE 
2711
2712           WHEN sp.loginame LIKE @filter THEN 1 
2713
2714           ELSE 0 
2715
2716          END 
2717
2718         WHEN 'host' THEN 
2719
2720          CASE 
2721
2722           WHEN sp.hostname LIKE @filter THEN 1 
2723
2724           ELSE 0 
2725
2726          END 
2727
2728         WHEN 'database' THEN 
2729
2730          CASE 
2731
2732           WHEN DB_NAME(sp.dbid) LIKE @filter THEN 1 
2733
2734           ELSE 0 
2735
2736          END 
2737
2738         ELSE 0 
2739
2740        END 
2741
2742       ELSE 1 
2743
2744      END 
2745
2746     --Process exclusive filter 
2747
2748     AND 0 = 
2749
2750      CASE 
2751
2752       WHEN @not_filter <> '' THEN 
2753
2754        CASE @not_filter_type 
2755
2756         WHEN 'session' THEN 
2757
2758          CASE 
2759
2760           WHEN sp.spid = CONVERT(SMALLINT, @not_filter) THEN 1 
2761
2762           ELSE 0 
2763
2764          END 
2765
2766         WHEN 'program' THEN 
2767
2768          CASE 
2769
2770           WHEN sp.program_name LIKE @not_filter THEN 1 
2771
2772           ELSE 0 
2773
2774          END 
2775
2776         WHEN 'login' THEN 
2777
2778          CASE 
2779
2780           WHEN sp.loginame LIKE @not_filter THEN 1 
2781
2782           ELSE 0 
2783
2784          END 
2785
2786         WHEN 'host' THEN 
2787
2788          CASE 
2789
2790           WHEN sp.hostname LIKE @not_filter THEN 1 
2791
2792           ELSE 0 
2793
2794          END 
2795
2796         WHEN 'database' THEN 
2797
2798          CASE 
2799
2800           WHEN DB_NAME(sp.dbid) LIKE @not_filter THEN 1 
2801
2802           ELSE 0 
2803
2804          END 
2805
2806         ELSE 0 
2807
2808        END 
2809
2810       ELSE 0 
2811
2812      END 
2813
2814     AND  
2815
2816     ( 
2817
2818      @show_own_spid = 1 
2819
2820      OR sp.spid <> @@SPID 
2821
2822     ) 
2823
2824     AND  
2825
2826     ( 
2827
2828      @show_system_spids = 1 
2829
2830      OR sp.hostprocess > '' 
2831
2832     ) 
2833
2834     AND sp.ecid = 0 
2835
2836   ) AS s 
2837
2838   INNER HASH JOIN 
2839
2840   ( 
2841
2842    SELECT 
2843
2844     x.resource_type, 
2845
2846     x.database_name, 
2847
2848     x.object_id, 
2849
2850     x.file_id, 
2851
2852     CASE 
2853
2854      WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS' 
2855
2856      WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM' 
2857
2858      WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM' 
2859
2860      WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM' 
2861
2862      WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM' 
2863
2864      WHEN x.page_no IS NOT NULL THEN '*' 
2865
2866      ELSE NULL 
2867
2868     END AS page_type, 
2869
2870     x.hobt_id, 
2871
2872     x.allocation_unit_id, 
2873
2874     x.index_id, 
2875
2876     x.schema_id, 
2877
2878     x.principal_id, 
2879
2880     x.request_mode, 
2881
2882     x.request_status, 
2883
2884     x.session_id, 
2885
2886     x.request_id, 
2887
2888     CASE 
2889
2890      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, '') 
2891
2892      ELSE NULL 
2893
2894     END AS resource_description, 
2895
2896     COUNT(*) AS request_count 
2897
2898    FROM 
2899
2900    ( 
2901
2902     SELECT 
2903
2904      tl.resource_type + 
2905
2906       CASE 
2907
2908        WHEN tl.resource_subtype = '' THEN '' 
2909
2910        ELSE '.' + tl.resource_subtype 
2911
2912       END AS resource_type, 
2913
2914      COALESCE(DB_NAME(tl.resource_database_id), N'(null)') AS database_name, 
2915
2916      CONVERT 
2917
2918      ( 
2919
2920       INT, 
2921
2922       CASE 
2923
2924        WHEN tl.resource_type = 'OBJECT' THEN tl.resource_associated_entity_id 
2925
2926        WHEN tl.resource_description LIKE '%object_id = %' THEN 
2927
2928         ( 
2929
2930          SUBSTRING 
2931
2932          ( 
2933
2934           tl.resource_description,  
2935
2936           (CHARINDEX('object_id = ', tl.resource_description) + 12),  
2937
2938           COALESCE 
2939
2940           ( 
2941
2942            NULLIF 
2943
2944            ( 
2945
2946             CHARINDEX(',', tl.resource_description, CHARINDEX('object_id = ', tl.resource_description) + 12), 
2947
2948             0 
2949
2950            ),  
2951
2952            DATALENGTH(tl.resource_description)+1 
2953
2954           ) - (CHARINDEX('object_id = ', tl.resource_description) + 12) 
2955
2956          ) 
2957
2958         ) 
2959
2960        ELSE NULL 
2961
2962       END 
2963
2964      ) AS object_id, 
2965
2966      CONVERT 
2967
2968      ( 
2969
2970       INT, 
2971
2972       CASE  
2973
2974        WHEN tl.resource_type = 'FILE' THEN CONVERT(INT, tl.resource_description) 
2975
2976        WHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN LEFT(tl.resource_description, CHARINDEX(':', tl.resource_description)-1) 
2977
2978        ELSE NULL 
2979
2980       END 
2981
2982      ) AS file_id, 
2983
2984      CONVERT 
2985
2986      ( 
2987
2988       INT, 
2989
2990       CASE 
2991
2992        WHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN  
2993
2994         SUBSTRING 
2995
2996         ( 
2997
2998          tl.resource_description,  
2999
3000          CHARINDEX(':', tl.resource_description) + 1,  
3001
3002          COALESCE 
3003
3004          ( 
3005
3006           NULLIF 
3007
3008           ( 
3009
3010            CHARINDEX(':', tl.resource_description, CHARINDEX(':', tl.resource_description) + 1),  
3011
3012            0 
3013
3014           ),  
3015
3016           DATALENGTH(tl.resource_description)+1 
3017
3018          ) - (CHARINDEX(':', tl.resource_description) + 1) 
3019
3020         ) 
3021
3022        ELSE NULL 
3023
3024       END 
3025
3026      ) AS page_no, 
3027
3028      CASE 
3029
3030       WHEN tl.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') THEN tl.resource_associated_entity_id 
3031
3032       ELSE NULL 
3033
3034      END AS hobt_id, 
3035
3036      CASE 
3037
3038       WHEN tl.resource_type = 'ALLOCATION_UNIT' THEN tl.resource_associated_entity_id 
3039
3040       ELSE NULL 
3041
3042      END AS allocation_unit_id, 
3043
3044      CONVERT 
3045
3046      ( 
3047
3048       INT, 
3049
3050       CASE 
3051
3052        WHEN 
3053
3054         /*TODO: Deal with server principals*/  
3055
3056         tl.resource_subtype <> 'SERVER_PRINCIPAL'  
3057
3058         AND tl.resource_description LIKE '%index_id or stats_id = %' THEN 
3059
3060         ( 
3061
3062          SUBSTRING 
3063
3064          ( 
3065
3066           tl.resource_description,  
3067
3068           (CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23),  
3069
3070           COALESCE 
3071
3072           ( 
3073
3074            NULLIF 
3075
3076            ( 
3077
3078             CHARINDEX(',', tl.resource_description, CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23),  
3079
3080             0 
3081
3082            ),  
3083
3084            DATALENGTH(tl.resource_description)+1 
3085
3086           ) - (CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23) 
3087
3088          ) 
3089
3090         ) 
3091
3092        ELSE NULL 
3093
3094       END  
3095
3096      ) AS index_id, 
3097
3098      CONVERT 
3099
3100      ( 
3101
3102       INT, 
3103
3104       CASE 
3105
3106        WHEN tl.resource_description LIKE '%schema_id = %' THEN 
3107
3108         ( 
3109
3110          SUBSTRING 
3111
3112          ( 
3113
3114           tl.resource_description,  
3115
3116           (CHARINDEX('schema_id = ', tl.resource_description) + 12),  
3117
3118           COALESCE 
3119
3120           ( 
3121
3122            NULLIF 
3123
3124            ( 
3125
3126             CHARINDEX(',', tl.resource_description, CHARINDEX('schema_id = ', tl.resource_description) + 12),  
3127
3128             0 
3129
3130            ),  
3131
3132            DATALENGTH(tl.resource_description)+1 
3133
3134           ) - (CHARINDEX('schema_id = ', tl.resource_description) + 12) 
3135
3136          ) 
3137
3138         ) 
3139
3140        ELSE NULL 
3141
3142       END  
3143
3144      ) AS schema_id, 
3145
3146      CONVERT 
3147
3148      ( 
3149
3150       INT, 
3151
3152       CASE 
3153
3154        WHEN tl.resource_description LIKE '%principal_id = %' THEN 
3155
3156         ( 
3157
3158          SUBSTRING 
3159
3160          ( 
3161
3162           tl.resource_description,  
3163
3164           (CHARINDEX('principal_id = ', tl.resource_description) + 15),  
3165
3166           COALESCE 
3167
3168           ( 
3169
3170            NULLIF 
3171
3172            ( 
3173
3174             CHARINDEX(',', tl.resource_description, CHARINDEX('principal_id = ', tl.resource_description) + 15),  
3175
3176             0 
3177
3178            ),  
3179
3180            DATALENGTH(tl.resource_description)+1 
3181
3182           ) - (CHARINDEX('principal_id = ', tl.resource_description) + 15) 
3183
3184          ) 
3185
3186         ) 
3187
3188        ELSE NULL 
3189
3190       END 
3191
3192      ) AS principal_id, 
3193
3194      tl.request_mode, 
3195
3196      tl.request_status, 
3197
3198      tl.request_session_id AS session_id, 
3199
3200      tl.request_request_id AS request_id, 
3201
3202  
3203
3204      /*TODO: Applocks, other resource_descriptions*/ 
3205
3206      RTRIM(tl.resource_description) AS resource_description, 
3207
3208      tl.resource_associated_entity_id 
3209
3210      /*********************************************/ 
3211
3212     FROM  
3213
3214     ( 
3215
3216      SELECT  
3217
3218       request_session_id, 
3219
3220       CONVERT(VARCHAR(120), resource_type) COLLATE Latin1_General_Bin2 AS resource_type, 
3221
3222       CONVERT(VARCHAR(120), resource_subtype) COLLATE Latin1_General_Bin2 AS resource_subtype, 
3223
3224       resource_database_id, 
3225
3226       CONVERT(VARCHAR(512), resource_description) COLLATE Latin1_General_Bin2 AS resource_description, 
3227
3228       resource_associated_entity_id, 
3229
3230       CONVERT(VARCHAR(120), request_mode) COLLATE Latin1_General_Bin2 AS request_mode, 
3231
3232       CONVERT(VARCHAR(120), request_status) COLLATE Latin1_General_Bin2 AS request_status, 
3233
3234       request_request_id 
3235
3236      FROM sys.dm_tran_locks 
3237
3238     ) AS tl 
3239
3240    ) AS x 
3241
3242    GROUP BY 
3243
3244     x.resource_type, 
3245
3246     x.database_name, 
3247
3248     x.object_id, 
3249
3250     x.file_id, 
3251
3252     CASE 
3253
3254      WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS' 
3255
3256      WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM' 
3257
3258      WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM' 
3259
3260      WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM' 
3261
3262      WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM' 
3263
3264      WHEN x.page_no IS NOT NULL THEN '*' 
3265
3266      ELSE NULL 
3267
3268     END, 
3269
3270     x.hobt_id, 
3271
3272     x.allocation_unit_id, 
3273
3274     x.index_id, 
3275
3276     x.schema_id, 
3277
3278     x.principal_id, 
3279
3280     x.request_mode, 
3281
3282     x.request_status, 
3283
3284     x.session_id, 
3285
3286     x.request_id, 
3287
3288     CASE 
3289
3290      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, '') 
3291
3292      ELSE NULL 
3293
3294     END 
3295
3296   ) AS y ON 
3297
3298    y.session_id = s.session_id 
3299
3300    AND y.request_id = s.request_id 
3301
3302   OPTION (HASH GROUP); 
3303
3304  
3305
3306   --Disable unnecessary autostats on the table 
3307
3308   CREATE STATISTICS s_database_name ON #locks (database_name) 
3309
3310   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3311
3312   CREATE STATISTICS s_object_id ON #locks (object_id) 
3313
3314   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3315
3316   CREATE STATISTICS s_hobt_id ON #locks (hobt_id) 
3317
3318   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3319
3320   CREATE STATISTICS s_allocation_unit_id ON #locks (allocation_unit_id) 
3321
3322   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3323
3324   CREATE STATISTICS s_index_id ON #locks (index_id) 
3325
3326   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3327
3328   CREATE STATISTICS s_schema_id ON #locks (schema_id) 
3329
3330   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3331
3332   CREATE STATISTICS s_principal_id ON #locks (principal_id) 
3333
3334   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3335
3336   CREATE STATISTICS s_request_id ON #locks (request_id) 
3337
3338   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3339
3340   CREATE STATISTICS s_start_time ON #locks (start_time) 
3341
3342   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3343
3344   CREATE STATISTICS s_resource_type ON #locks (resource_type) 
3345
3346   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3347
3348   CREATE STATISTICS s_object_name ON #locks (object_name) 
3349
3350   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3351
3352   CREATE STATISTICS s_schema_name ON #locks (schema_name) 
3353
3354   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3355
3356   CREATE STATISTICS s_page_type ON #locks (page_type) 
3357
3358   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3359
3360   CREATE STATISTICS s_request_mode ON #locks (request_mode) 
3361
3362   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3363
3364   CREATE STATISTICS s_request_status ON #locks (request_status) 
3365
3366   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3367
3368   CREATE STATISTICS s_resource_description ON #locks (resource_description) 
3369
3370   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3371
3372   CREATE STATISTICS s_index_name ON #locks (index_name) 
3373
3374   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3375
3376   CREATE STATISTICS s_principal_name ON #locks (principal_name) 
3377
3378   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
3379
3380  END; 
3381
3382    
3383
3384  DECLARE  
3385
3386   @sql VARCHAR(MAX),  
3387
3388   @sql_n NVARCHAR(MAX); 
3389
3390  
3391
3392  SET @sql =  
3393
3394   CONVERT(VARCHAR(MAX), '') + 
3395
3396   'DECLARE @blocker BIT; 
3397
3398   SET @blocker = 0; 
3399
3400   DECLARE @i INT; 
3401
3402   SET @i = 2147483647; 
3403
3404  
3405
3406   DECLARE @sessions TABLE 
3407
3408   ( 
3409
3410    session_id SMALLINT NOT NULL, 
3411
3412    request_id INT NOT NULL, 
3413
3414    login_time DATETIME, 
3415
3416    last_request_end_time DATETIME, 
3417
3418    status VARCHAR(30), 
3419
3420    statement_start_offset INT, 
3421
3422    statement_end_offset INT, 
3423
3424    sql_handle BINARY(20), 
3425
3426    host_name NVARCHAR(128), 
3427
3428    login_name NVARCHAR(128), 
3429
3430    program_name NVARCHAR(128), 
3431
3432    database_id SMALLINT, 
3433
3434    memory_usage INT, 
3435
3436    open_tran_count SMALLINT,  
3437
3438    ' + 
3439
3440    CASE 
3441
3442     WHEN  
3443
3444     ( 
3445
3446      @get_task_info <> 0  
3447
3448      OR @find_block_leaders = 1  
3449
3450     ) THEN 
3451
3452      'wait_type NVARCHAR(32), 
3453
3454      wait_resource NVARCHAR(256), 
3455
3456      wait_time BIGINT,  
3457
3458      ' 
3459
3460     ELSE  
3461
3462      '' 
3463
3464    END + 
3465
3466    'blocked SMALLINT, 
3467
3468    is_user_process BIT, 
3469
3470    cmd VARCHAR(32), 
3471
3472    PRIMARY KEY CLUSTERED (session_id, request_id) WITH (IGNORE_DUP_KEY = ON) 
3473
3474   ); 
3475
3476  
3477
3478   DECLARE @blockers TABLE 
3479
3480   ( 
3481
3482    session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON) 
3483
3484   ); 
3485
3486  
3487
3488   BLOCKERS:; 
3489
3490  
3491
3492   INSERT @sessions 
3493
3494   ( 
3495
3496    session_id, 
3497
3498    request_id, 
3499
3500    login_time, 
3501
3502    last_request_end_time, 
3503
3504    status, 
3505
3506    statement_start_offset, 
3507
3508    statement_end_offset, 
3509
3510    sql_handle, 
3511
3512    host_name, 
3513
3514    login_name, 
3515
3516    program_name, 
3517
3518    database_id, 
3519
3520    memory_usage, 
3521
3522    open_tran_count,  
3523
3524    ' + 
3525
3526    CASE  
3527
3528     WHEN  
3529
3530     ( 
3531
3532      @get_task_info <> 0 
3533
3534      OR @find_block_leaders = 1  
3535
3536     ) THEN 
3537
3538      'wait_type, 
3539
3540      wait_resource, 
3541
3542      wait_time,  
3543
3544      ' 
3545
3546     ELSE 
3547
3548      '' 
3549
3550    END + 
3551
3552    'blocked, 
3553
3554    is_user_process, 
3555
3556    cmd  
3557
3558   ) 
3559
3560   SELECT TOP(@i) 
3561
3562    spy.session_id, 
3563
3564    spy.request_id, 
3565
3566    spy.login_time, 
3567
3568    spy.last_request_end_time, 
3569
3570    spy.status, 
3571
3572    spy.statement_start_offset, 
3573
3574    spy.statement_end_offset, 
3575
3576    spy.sql_handle, 
3577
3578    spy.host_name, 
3579
3580    spy.login_name, 
3581
3582    spy.program_name, 
3583
3584    spy.database_id, 
3585
3586    spy.memory_usage, 
3587
3588    spy.open_tran_count, 
3589
3590    ' + 
3591
3592    CASE 
3593
3594     WHEN  
3595
3596     ( 
3597
3598      @get_task_info <> 0   
3599
3600      OR @find_block_leaders = 1  
3601
3602     ) THEN 
3603
3604      'spy.wait_type, 
3605
3606      CASE 
3607
3608       WHEN 
3609
3610        spy.wait_type LIKE N''PAGE%LATCH_%'' 
3611
3612        OR spy.wait_type = N''CXPACKET'' 
3613
3614        OR spy.wait_type LIKE N''LATCH[_]%'' 
3615
3616        OR spy.wait_type = N''OLEDB'' THEN 
3617
3618         spy.wait_resource 
3619
3620       ELSE 
3621
3622        NULL 
3623
3624      END AS wait_resource, 
3625
3626      spy.wait_time,  
3627
3628      ' 
3629
3630     ELSE 
3631
3632      '' 
3633
3634    END + 
3635
3636    'spy.blocked, 
3637
3638    spy.is_user_process, 
3639
3640    spy.cmd 
3641
3642   FROM 
3643
3644   ( 
3645
3646    SELECT TOP(@i) 
3647
3648     spx.*,  
3649
3650     ' + 
3651
3652     CASE 
3653
3654      WHEN  
3655
3656      ( 
3657
3658       @get_task_info <> 0  
3659
3660       OR @find_block_leaders = 1  
3661
3662      ) THEN 
3663
3664       'ROW_NUMBER() OVER 
3665
3666       ( 
3667
3668        PARTITION BY 
3669
3670         spx.session_id, 
3671
3672         spx.request_id 
3673
3674        ORDER BY 
3675
3676         CASE 
3677
3678          WHEN spx.wait_type LIKE N''LCK[_]%'' THEN  
3679
3680           1 
3681
3682          ELSE 
3683
3684           99 
3685
3686         END, 
3687
3688         spx.wait_time DESC, 
3689
3690         spx.blocked DESC 
3691
3692       ) AS r  
3693
3694       ' 
3695
3696      ELSE  
3697
3698       '1 AS r  
3699
3700       ' 
3701
3702     END + 
3703
3704    'FROM 
3705
3706    ( 
3707
3708     SELECT TOP(@i) 
3709
3710      sp0.session_id, 
3711
3712      sp0.request_id, 
3713
3714      sp0.login_time, 
3715
3716      sp0.last_request_end_time, 
3717
3718      LOWER(sp0.status) AS status, 
3719
3720      CASE 
3721
3722       WHEN sp0.cmd = ''CREATE INDEX'' THEN 
3723
3724        0 
3725
3726       ELSE 
3727
3728        sp0.stmt_start 
3729
3730      END AS statement_start_offset, 
3731
3732      CASE 
3733
3734       WHEN sp0.cmd = N''CREATE INDEX'' THEN 
3735
3736        -1 
3737
3738       ELSE 
3739
3740        COALESCE(NULLIF(sp0.stmt_end, 0), -1) 
3741
3742      END AS statement_end_offset, 
3743
3744      sp0.sql_handle, 
3745
3746      sp0.host_name, 
3747
3748      sp0.login_name, 
3749
3750      sp0.program_name, 
3751
3752      sp0.database_id, 
3753
3754      sp0.memory_usage, 
3755
3756      sp0.open_tran_count,  
3757
3758      ' + 
3759
3760      CASE 
3761
3762       WHEN  
3763
3764       ( 
3765
3766        @get_task_info <> 0  
3767
3768        OR @find_block_leaders = 1  
3769
3770       ) THEN 
3771
3772        'CASE 
3773
3774         WHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN 
3775
3776          sp0.wait_type 
3777
3778         ELSE 
3779
3780          NULL 
3781
3782        END AS wait_type, 
3783
3784        CASE 
3785
3786         WHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN  
3787
3788          sp0.wait_resource 
3789
3790         ELSE 
3791
3792          NULL 
3793
3794        END AS wait_resource, 
3795
3796        CASE 
3797
3798         WHEN sp0.wait_type <> N''CXPACKET'' THEN 
3799
3800          sp0.wait_time 
3801
3802         ELSE 
3803
3804          0 
3805
3806        END AS wait_time,  
3807
3808        ' 
3809
3810       ELSE 
3811
3812        '' 
3813
3814      END + 
3815
3816      'sp0.blocked, 
3817
3818      sp0.is_user_process, 
3819
3820      sp0.cmd 
3821
3822     FROM 
3823
3824     ( 
3825
3826      SELECT TOP(@i) 
3827
3828       sp1.session_id, 
3829
3830       sp1.request_id, 
3831
3832       sp1.login_time, 
3833
3834       sp1.last_request_end_time, 
3835
3836       sp1.status, 
3837
3838       sp1.cmd, 
3839
3840       sp1.stmt_start, 
3841
3842       sp1.stmt_end, 
3843
3844       MAX(NULLIF(sp1.sql_handle, 0x00)) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS sql_handle, 
3845
3846       sp1.host_name, 
3847
3848       MAX(sp1.login_name) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS login_name, 
3849
3850       sp1.program_name, 
3851
3852       sp1.database_id, 
3853
3854       MAX(sp1.memory_usage)  OVER (PARTITION BY sp1.session_id, sp1.request_id) AS memory_usage, 
3855
3856       MAX(sp1.open_tran_count)  OVER (PARTITION BY sp1.session_id, sp1.request_id) AS open_tran_count, 
3857
3858       sp1.wait_type, 
3859
3860       sp1.wait_resource, 
3861
3862       sp1.wait_time, 
3863
3864       sp1.blocked, 
3865
3866       sp1.hostprocess, 
3867
3868       sp1.is_user_process 
3869
3870      FROM 
3871
3872      ( 
3873
3874       SELECT TOP(@i) 
3875
3876        sp2.spid AS session_id, 
3877
3878        CASE sp2.status 
3879
3880         WHEN ''sleeping'' THEN 
3881
3882          CONVERT(INT, 0) 
3883
3884         ELSE 
3885
3886          sp2.request_id 
3887
3888        END AS request_id, 
3889
3890        MAX(sp2.login_time) AS login_time, 
3891
3892        MAX(sp2.last_batch) AS last_request_end_time, 
3893
3894        MAX(CONVERT(VARCHAR(30), RTRIM(sp2.status)) COLLATE Latin1_General_Bin2) AS status, 
3895
3896        MAX(CONVERT(VARCHAR(32), RTRIM(sp2.cmd)) COLLATE Latin1_General_Bin2) AS cmd, 
3897
3898        MAX(sp2.stmt_start) AS stmt_start, 
3899
3900        MAX(sp2.stmt_end) AS stmt_end, 
3901
3902        MAX(sp2.sql_handle) AS sql_handle, 
3903
3904        MAX(CONVERT(sysname, RTRIM(sp2.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS host_name, 
3905
3906        MAX(CONVERT(sysname, RTRIM(sp2.loginame)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS login_name, 
3907
3908        MAX 
3909
3910        ( 
3911
3912         CASE 
3913
3914          WHEN blk.queue_id IS NOT NULL THEN 
3915
3916           N''Service Broker 
3917
3918            database_id: '' + CONVERT(NVARCHAR, blk.database_id) + 
3919
3920            N'' queue_id: '' + CONVERT(NVARCHAR, blk.queue_id) 
3921
3922          ELSE 
3923
3924           CONVERT 
3925
3926           ( 
3927
3928            sysname, 
3929
3930            RTRIM(sp2.program_name) 
3931
3932           ) 
3933
3934         END COLLATE SQL_Latin1_General_CP1_CI_AS 
3935
3936        ) AS program_name, 
3937
3938        MAX(sp2.dbid) AS database_id, 
3939
3940        MAX(sp2.memusage) AS memory_usage, 
3941
3942        MAX(sp2.open_tran) AS open_tran_count, 
3943
3944        RTRIM(sp2.lastwaittype) AS wait_type, 
3945
3946        RTRIM(sp2.waitresource) AS wait_resource, 
3947
3948        MAX(sp2.waittime) AS wait_time, 
3949
3950        COALESCE(NULLIF(sp2.blocked, sp2.spid), 0) AS blocked, 
3951
3952        MAX 
3953
3954        ( 
3955
3956         CASE 
3957
3958          WHEN blk.session_id = sp2.spid THEN 
3959
3960           ''blocker'' 
3961
3962          ELSE 
3963
3964           RTRIM(sp2.hostprocess) 
3965
3966         END 
3967
3968        ) AS hostprocess, 
3969
3970        CONVERT 
3971
3972        ( 
3973
3974         BIT, 
3975
3976         MAX 
3977
3978         ( 
3979
3980          CASE 
3981
3982           WHEN sp2.hostprocess > '''' THEN 
3983
3984            1 
3985
3986           ELSE 
3987
3988            0 
3989
3990          END 
3991
3992         ) 
3993
3994        ) AS is_user_process 
3995
3996       FROM 
3997
3998       ( 
3999
4000        SELECT TOP(@i) 
4001
4002         session_id, 
4003
4004         CONVERT(INT, NULL) AS queue_id, 
4005
4006         CONVERT(INT, NULL) AS database_id 
4007
4008        FROM @blockers 
4009
4010  
4011
4012        UNION ALL 
4013
4014  
4015
4016        SELECT TOP(@i) 
4017
4018         CONVERT(SMALLINT, 0), 
4019
4020         CONVERT(INT, NULL) AS queue_id, 
4021
4022         CONVERT(INT, NULL) AS database_id 
4023
4024        WHERE 
4025
4026         @blocker = 0 
4027
4028  
4029
4030        UNION ALL 
4031
4032  
4033
4034        SELECT TOP(@i) 
4035
4036         CONVERT(SMALLINT, spid), 
4037
4038         queue_id, 
4039
4040         database_id 
4041
4042        FROM sys.dm_broker_activated_tasks 
4043
4044        WHERE 
4045
4046         @blocker = 0 
4047
4048       ) AS blk 
4049
4050       INNER JOIN sys.sysprocesses AS sp2 ON 
4051
4052        sp2.spid = blk.session_id 
4053
4054        OR 
4055
4056        ( 
4057
4058         blk.session_id = 0 
4059
4060         AND @blocker = 0 
4061
4062        ) 
4063
4064       ' + 
4065
4066       CASE  
4067
4068        WHEN  
4069
4070        ( 
4071
4072         @get_task_info = 0  
4073
4074         AND @find_block_leaders = 0 
4075
4076        ) THEN 
4077
4078         'WHERE 
4079
4080          sp2.ecid = 0  
4081
4082         '  
4083
4084        ELSE 
4085
4086         '' 
4087
4088       END + 
4089
4090       'GROUP BY 
4091
4092        sp2.spid, 
4093
4094        CASE sp2.status 
4095
4096         WHEN ''sleeping'' THEN 
4097
4098          CONVERT(INT, 0) 
4099
4100         ELSE 
4101
4102          sp2.request_id 
4103
4104        END, 
4105
4106        RTRIM(sp2.lastwaittype), 
4107
4108        RTRIM(sp2.waitresource), 
4109
4110        COALESCE(NULLIF(sp2.blocked, sp2.spid), 0) 
4111
4112      ) AS sp1 
4113
4114     ) AS sp0 
4115
4116     WHERE 
4117
4118      @blocker = 1 
4119
4120      OR 
4121
4122      (1=1  
4123
4124      ' + 
4125
4126       --inclusive filter 
4127
4128       CASE 
4129
4130        WHEN @filter <> '' THEN 
4131
4132         CASE @filter_type 
4133
4134          WHEN 'session' THEN 
4135
4136           CASE 
4137
4138            WHEN CONVERT(SMALLINT, @filter) <> 0 THEN 
4139
4140             'AND sp0.session_id = CONVERT(SMALLINT, @filter)  
4141
4142             ' 
4143
4144            ELSE 
4145
4146             '' 
4147
4148           END 
4149
4150          WHEN 'program' THEN 
4151
4152           'AND sp0.program_name LIKE @filter  
4153
4154           ' 
4155
4156          WHEN 'login' THEN 
4157
4158           'AND sp0.login_name LIKE @filter  
4159
4160           ' 
4161
4162          WHEN 'host' THEN 
4163
4164           'AND sp0.host_name LIKE @filter  
4165
4166           ' 
4167
4168          WHEN 'database' THEN 
4169
4170           'AND DB_NAME(sp0.database_id) LIKE @filter  
4171
4172           ' 
4173
4174          ELSE 
4175
4176           '' 
4177
4178         END 
4179
4180        ELSE 
4181
4182         '' 
4183
4184       END + 
4185
4186       --exclusive filter 
4187
4188       CASE 
4189
4190        WHEN @not_filter <> '' THEN 
4191
4192         CASE @not_filter_type 
4193
4194          WHEN 'session' THEN 
4195
4196           CASE 
4197
4198            WHEN CONVERT(SMALLINT, @not_filter) <> 0 THEN 
4199
4200             'AND sp0.session_id <> CONVERT(SMALLINT, @not_filter)  
4201
4202             ' 
4203
4204            ELSE 
4205
4206             '' 
4207
4208           END 
4209
4210          WHEN 'program' THEN 
4211
4212           'AND sp0.program_name NOT LIKE @not_filter  
4213
4214           ' 
4215
4216          WHEN 'login' THEN 
4217
4218           'AND sp0.login_name NOT LIKE @not_filter  
4219
4220           ' 
4221
4222          WHEN 'host' THEN 
4223
4224           'AND sp0.host_name NOT LIKE @not_filter  
4225
4226           ' 
4227
4228          WHEN 'database' THEN 
4229
4230           'AND DB_NAME(sp0.database_id) NOT LIKE @not_filter  
4231
4232           ' 
4233
4234          ELSE 
4235
4236           ''  
4237
4238         END 
4239
4240        ELSE 
4241
4242         '' 
4243
4244       END + 
4245
4246       CASE @show_own_spid 
4247
4248        WHEN 1 THEN 
4249
4250         '' 
4251
4252        ELSE 
4253
4254         'AND sp0.session_id <> @@spid  
4255
4256         ' 
4257
4258       END + 
4259
4260       CASE  
4261
4262        WHEN @show_system_spids = 0 THEN 
4263
4264         'AND sp0.hostprocess > ''''  
4265
4266         '  
4267
4268        ELSE 
4269
4270         '' 
4271
4272       END + 
4273
4274       CASE @show_sleeping_spids 
4275
4276        WHEN 0 THEN 
4277
4278         'AND sp0.status <> ''sleeping''  
4279
4280         ' 
4281
4282        WHEN 1 THEN 
4283
4284         'AND 
4285
4286         ( 
4287
4288          sp0.status <> ''sleeping'' 
4289
4290          OR sp0.open_tran_count > 0 
4291
4292         ) 
4293
4294         ' 
4295
4296        ELSE 
4297
4298         '' 
4299
4300       END + 
4301
4302      ') 
4303
4304    ) AS spx 
4305
4306   ) AS spy 
4307
4308   WHERE 
4309
4310    spy.r = 1;  
4311
4312   ' +  
4313
4314   CASE @recursion 
4315
4316    WHEN 1 THEN  
4317
4318     'IF @@ROWCOUNT > 0 
4319
4320     BEGIN; 
4321
4322      INSERT @blockers 
4323
4324      ( 
4325
4326       session_id 
4327
4328      ) 
4329
4330      SELECT TOP(@i) 
4331
4332       blocked 
4333
4334      FROM @sessions 
4335
4336      WHERE 
4337
4338       NULLIF(blocked, 0) IS NOT NULL 
4339
4340  
4341
4342      EXCEPT 
4343
4344  
4345
4346      SELECT TOP(@i) 
4347
4348       session_id 
4349
4350      FROM @sessions;  
4351
4352      ' + 
4353
4354  
4355
4356      CASE 
4357
4358       WHEN 
4359
4360       ( 
4361
4362        @get_task_info > 0 
4363
4364        OR @find_block_leaders = 1 
4365
4366       ) THEN 
4367
4368        'IF @@ROWCOUNT > 0 
4369
4370        BEGIN; 
4371
4372         SET @blocker = 1; 
4373
4374         GOTO BLOCKERS; 
4375
4376        END;  
4377
4378        ' 
4379
4380       ELSE  
4381
4382        '' 
4383
4384      END + 
4385
4386     'END;  
4387
4388     ' 
4389
4390    ELSE   
4391
4392     '' 
4393
4394   END + 
4395
4396   'SELECT TOP(@i) 
4397
4398    @recursion AS recursion, 
4399
4400    x.session_id, 
4401
4402    x.request_id, 
4403
4404    DENSE_RANK() OVER 
4405
4406    ( 
4407
4408     ORDER BY 
4409
4410      x.session_id 
4411
4412    ) AS session_number, 
4413
4414    ' + 
4415
4416    CASE 
4417
4418     WHEN @output_column_list LIKE '%|[dd hh:mm:ss.mss|]%' ESCAPE '|' THEN  
4419
4420      'x.elapsed_time ' 
4421
4422     ELSE  
4423
4424      '0 ' 
4425
4426    END +  
4427
4428     'AS elapsed_time,  
4429
4430     ' + 
4431
4432    CASE 
4433
4434     WHEN 
4435
4436      ( 
4437
4438       @output_column_list LIKE '%|[dd hh:mm:ss.mss (avg)|]%' ESCAPE '|' OR  
4439
4440       @output_column_list LIKE '%|[avg_elapsed_time|]%' ESCAPE '|' 
4441
4442      ) 
4443
4444      AND @recursion = 1 
4445
4446       THEN  
4447
4448        'x.avg_elapsed_time / 1000 ' 
4449
4450     ELSE  
4451
4452      'NULL ' 
4453
4454    END +  
4455
4456     'AS avg_elapsed_time,  
4457
4458     ' + 
4459
4460    CASE 
4461
4462     WHEN  
4463
4464      @output_column_list LIKE '%|[physical_io|]%' ESCAPE '|' 
4465
4466      OR @output_column_list LIKE '%|[physical_io_delta|]%' ESCAPE '|' 
4467
4468       THEN  
4469
4470        'x.physical_io ' 
4471
4472     ELSE  
4473
4474      'NULL ' 
4475
4476    END +  
4477
4478     'AS physical_io,  
4479
4480     ' + 
4481
4482    CASE 
4483
4484     WHEN  
4485
4486      @output_column_list LIKE '%|[reads|]%' ESCAPE '|' 
4487
4488      OR @output_column_list LIKE '%|[reads_delta|]%' ESCAPE '|' 
4489
4490       THEN  
4491
4492        'x.reads ' 
4493
4494     ELSE  
4495
4496      '0 ' 
4497
4498    END +  
4499
4500     'AS reads,  
4501
4502     ' + 
4503
4504    CASE 
4505
4506     WHEN  
4507
4508      @output_column_list LIKE '%|[physical_reads|]%' ESCAPE '|' 
4509
4510      OR @output_column_list LIKE '%|[physical_reads_delta|]%' ESCAPE '|' 
4511
4512       THEN  
4513
4514        'x.physical_reads ' 
4515
4516     ELSE  
4517
4518      '0 ' 
4519
4520    END +  
4521
4522     'AS physical_reads,  
4523
4524     ' + 
4525
4526    CASE 
4527
4528     WHEN  
4529
4530      @output_column_list LIKE '%|[writes|]%' ESCAPE '|' 
4531
4532      OR @output_column_list LIKE '%|[writes_delta|]%' ESCAPE '|' 
4533
4534       THEN  
4535
4536        'x.writes ' 
4537
4538     ELSE  
4539
4540      '0 ' 
4541
4542    END +  
4543
4544     'AS writes,  
4545
4546     ' + 
4547
4548    CASE 
4549
4550     WHEN  
4551
4552      @output_column_list LIKE '%|[tempdb_allocations|]%' ESCAPE '|' 
4553
4554      OR @output_column_list LIKE '%|[tempdb_allocations_delta|]%' ESCAPE '|' 
4555
4556       THEN  
4557
4558        'x.tempdb_allocations ' 
4559
4560     ELSE  
4561
4562      '0 ' 
4563
4564    END +  
4565
4566     'AS tempdb_allocations,  
4567
4568     ' + 
4569
4570    CASE 
4571
4572     WHEN  
4573
4574      @output_column_list LIKE '%|[tempdb_current|]%' ESCAPE '|' 
4575
4576      OR @output_column_list LIKE '%|[tempdb_current_delta|]%' ESCAPE '|' 
4577
4578       THEN  
4579
4580        'x.tempdb_current ' 
4581
4582     ELSE  
4583
4584      '0 ' 
4585
4586    END +  
4587
4588     'AS tempdb_current,  
4589
4590     ' + 
4591
4592    CASE 
4593
4594     WHEN  
4595
4596      @output_column_list LIKE '%|[CPU|]%' ESCAPE '|' 
4597
4598      OR @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|' 
4599
4600       THEN 
4601
4602        'x.CPU ' 
4603
4604     ELSE 
4605
4606      '0 ' 
4607
4608    END +  
4609
4610     'AS CPU,  
4611
4612     ' + 
4613
4614    CASE 
4615
4616     WHEN  
4617
4618      @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|' 
4619
4620      AND @get_task_info = 2 
4621
4622      AND @sys_info = 1 
4623
4624       THEN  
4625
4626        'x.thread_CPU_snapshot ' 
4627
4628     ELSE  
4629
4630      '0 ' 
4631
4632    END +  
4633
4634     'AS thread_CPU_snapshot,  
4635
4636     ' + 
4637
4638    CASE 
4639
4640     WHEN  
4641
4642      @output_column_list LIKE '%|[context_switches|]%' ESCAPE '|' 
4643
4644      OR @output_column_list LIKE '%|[context_switches_delta|]%' ESCAPE '|' 
4645
4646       THEN  
4647
4648        'x.context_switches ' 
4649
4650     ELSE  
4651
4652      'NULL ' 
4653
4654    END +  
4655
4656     'AS context_switches,  
4657
4658     ' + 
4659
4660    CASE 
4661
4662     WHEN  
4663
4664      @output_column_list LIKE '%|[used_memory|]%' ESCAPE '|' 
4665
4666      OR @output_column_list LIKE '%|[used_memory_delta|]%' ESCAPE '|' 
4667
4668       THEN  
4669
4670        'x.used_memory ' 
4671
4672     ELSE  
4673
4674      '0 ' 
4675
4676    END +  
4677
4678     'AS used_memory,  
4679
4680     ' + 
4681
4682    CASE 
4683
4684     WHEN  
4685
4686      @output_column_list LIKE '%|[tasks|]%' ESCAPE '|' 
4687
4688      AND @recursion = 1 
4689
4690       THEN  
4691
4692        'x.tasks ' 
4693
4694     ELSE  
4695
4696      'NULL ' 
4697
4698    END +  
4699
4700     'AS tasks,  
4701
4702     ' + 
4703
4704    CASE 
4705
4706     WHEN  
4707
4708      ( 
4709
4710       @output_column_list LIKE '%|[status|]%' ESCAPE '|'  
4711
4712       OR @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|' 
4713
4714      ) 
4715
4716      AND @recursion = 1 
4717
4718       THEN  
4719
4720        'x.status ' 
4721
4722     ELSE  
4723
4724      ''''' ' 
4725
4726    END +  
4727
4728     'AS status,  
4729
4730     ' + 
4731
4732    CASE 
4733
4734     WHEN  
4735
4736      @output_column_list LIKE '%|[wait_info|]%' ESCAPE '|'  
4737
4738      AND @recursion = 1 
4739
4740       THEN  
4741
4742        CASE @get_task_info 
4743
4744         WHEN 2 THEN 
4745
4746          'COALESCE(x.task_wait_info, x.sys_wait_info) ' 
4747
4748         ELSE 
4749
4750          'x.sys_wait_info ' 
4751
4752        END 
4753
4754     ELSE  
4755
4756      'NULL ' 
4757
4758    END +  
4759
4760     'AS wait_info,  
4761
4762     ' + 
4763
4764    CASE 
4765
4766     WHEN  
4767
4768      ( 
4769
4770       @output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|'  
4771
4772       OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|'  
4773
4774      ) 
4775
4776      AND @recursion = 1 
4777
4778       THEN  
4779
4780        'x.transaction_id ' 
4781
4782     ELSE  
4783
4784      'NULL ' 
4785
4786    END +  
4787
4788     'AS transaction_id,  
4789
4790     ' + 
4791
4792    CASE 
4793
4794     WHEN  
4795
4796      @output_column_list LIKE '%|[open_tran_count|]%' ESCAPE '|'  
4797
4798      AND @recursion = 1 
4799
4800       THEN  
4801
4802        'x.open_tran_count ' 
4803
4804     ELSE  
4805
4806      'NULL ' 
4807
4808    END +  
4809
4810     'AS open_tran_count,  
4811
4812     ' + 
4813
4814    CASE 
4815
4816     WHEN  
4817
4818      @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'  
4819
4820      AND @recursion = 1 
4821
4822       THEN  
4823
4824        'x.sql_handle ' 
4825
4826     ELSE  
4827
4828      'NULL ' 
4829
4830    END +  
4831
4832     'AS sql_handle,  
4833
4834     ' + 
4835
4836    CASE 
4837
4838     WHEN  
4839
4840      ( 
4841
4842       @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'  
4843
4844       OR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'  
4845
4846      ) 
4847
4848      AND @recursion = 1 
4849
4850       THEN  
4851
4852        'x.statement_start_offset ' 
4853
4854     ELSE  
4855
4856      'NULL ' 
4857
4858    END +  
4859
4860     'AS statement_start_offset,  
4861
4862     ' + 
4863
4864    CASE 
4865
4866     WHEN  
4867
4868      ( 
4869
4870       @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'  
4871
4872       OR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'  
4873
4874      ) 
4875
4876      AND @recursion = 1 
4877
4878       THEN  
4879
4880        'x.statement_end_offset ' 
4881
4882     ELSE  
4883
4884      'NULL ' 
4885
4886    END +  
4887
4888     'AS statement_end_offset,  
4889
4890     ' + 
4891
4892    'NULL AS sql_text,  
4893
4894     ' + 
4895
4896    CASE 
4897
4898     WHEN  
4899
4900      @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'  
4901
4902      AND @recursion = 1 
4903
4904       THEN  
4905
4906        'x.plan_handle ' 
4907
4908     ELSE  
4909
4910      'NULL ' 
4911
4912    END +  
4913
4914     'AS plan_handle,  
4915
4916     ' + 
4917
4918    CASE 
4919
4920     WHEN  
4921
4922      @output_column_list LIKE '%|[blocking_session_id|]%' ESCAPE '|'  
4923
4924      AND @recursion = 1 
4925
4926       THEN  
4927
4928        'NULLIF(x.blocking_session_id, 0) ' 
4929
4930     ELSE  
4931
4932      'NULL ' 
4933
4934    END +  
4935
4936     'AS blocking_session_id,  
4937
4938     ' + 
4939
4940    CASE 
4941
4942     WHEN  
4943
4944      @output_column_list LIKE '%|[percent_complete|]%' ESCAPE '|' 
4945
4946      AND @recursion = 1 
4947
4948       THEN  
4949
4950        'x.percent_complete ' 
4951
4952     ELSE  
4953
4954      'NULL ' 
4955
4956    END +  
4957
4958     'AS percent_complete,  
4959
4960     ' + 
4961
4962    CASE 
4963
4964     WHEN  
4965
4966      @output_column_list LIKE '%|[host_name|]%' ESCAPE '|'  
4967
4968      AND @recursion = 1 
4969
4970       THEN  
4971
4972        'x.host_name ' 
4973
4974     ELSE  
4975
4976      ''''' ' 
4977
4978    END +  
4979
4980     'AS host_name,  
4981
4982     ' + 
4983
4984    CASE 
4985
4986     WHEN  
4987
4988      @output_column_list LIKE '%|[login_name|]%' ESCAPE '|'  
4989
4990      AND @recursion = 1 
4991
4992       THEN  
4993
4994        'x.login_name ' 
4995
4996     ELSE  
4997
4998      ''''' ' 
4999
5000    END +  
5001
5002     'AS login_name,  
5003
5004     ' + 
5005
5006    CASE 
5007
5008     WHEN  
5009
5010      @output_column_list LIKE '%|[database_name|]%' ESCAPE '|'  
5011
5012      AND @recursion = 1 
5013
5014       THEN  
5015
5016        'DB_NAME(x.database_id) ' 
5017
5018     ELSE  
5019
5020      'NULL ' 
5021
5022    END +  
5023
5024     'AS database_name,  
5025
5026     ' + 
5027
5028    CASE 
5029
5030     WHEN  
5031
5032      @output_column_list LIKE '%|[program_name|]%' ESCAPE '|'  
5033
5034      AND @recursion = 1 
5035
5036       THEN  
5037
5038        'x.program_name ' 
5039
5040     ELSE  
5041
5042      ''''' ' 
5043
5044    END +  
5045
5046     'AS program_name,  
5047
5048     ' + 
5049
5050    CASE 
5051
5052     WHEN 
5053
5054      @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|' 
5055
5056      AND @recursion = 1 
5057
5058       THEN 
5059
5060        '( 
5061
5062         SELECT TOP(@i) 
5063
5064          x.text_size, 
5065
5066          x.language, 
5067
5068          x.date_format, 
5069
5070          x.date_first, 
5071
5072          CASE x.quoted_identifier 
5073
5074           WHEN 0 THEN ''OFF'' 
5075
5076           WHEN 1 THEN ''ON'' 
5077
5078          END AS quoted_identifier, 
5079
5080          CASE x.arithabort 
5081
5082           WHEN 0 THEN ''OFF'' 
5083
5084           WHEN 1 THEN ''ON'' 
5085
5086          END AS arithabort, 
5087
5088          CASE x.ansi_null_dflt_on 
5089
5090           WHEN 0 THEN ''OFF'' 
5091
5092           WHEN 1 THEN ''ON'' 
5093
5094          END AS ansi_null_dflt_on, 
5095
5096          CASE x.ansi_defaults 
5097
5098           WHEN 0 THEN ''OFF'' 
5099
5100           WHEN 1 THEN ''ON'' 
5101
5102          END AS ansi_defaults, 
5103
5104          CASE x.ansi_warnings 
5105
5106           WHEN 0 THEN ''OFF'' 
5107
5108           WHEN 1 THEN ''ON'' 
5109
5110          END AS ansi_warnings, 
5111
5112          CASE x.ansi_padding 
5113
5114           WHEN 0 THEN ''OFF'' 
5115
5116           WHEN 1 THEN ''ON'' 
5117
5118          END AS ansi_padding, 
5119
5120          CASE ansi_nulls 
5121
5122           WHEN 0 THEN ''OFF'' 
5123
5124           WHEN 1 THEN ''ON'' 
5125
5126          END AS ansi_nulls, 
5127
5128          CASE x.concat_null_yields_null 
5129
5130           WHEN 0 THEN ''OFF'' 
5131
5132           WHEN 1 THEN ''ON'' 
5133
5134          END AS concat_null_yields_null, 
5135
5136          CASE x.transaction_isolation_level 
5137
5138           WHEN 0 THEN ''Unspecified'' 
5139
5140           WHEN 1 THEN ''ReadUncomitted'' 
5141
5142           WHEN 2 THEN ''ReadCommitted'' 
5143
5144           WHEN 3 THEN ''Repeatable'' 
5145
5146           WHEN 4 THEN ''Serializable'' 
5147
5148           WHEN 5 THEN ''Snapshot'' 
5149
5150          END AS transaction_isolation_level, 
5151
5152          x.lock_timeout, 
5153
5154          x.deadlock_priority, 
5155
5156          x.row_count,  
5157
5158          x.command_type,  
5159
5160          ' + 
5161
5162          CASE 
5163
5164           WHEN OBJECT_ID('master.dbo.fn_varbintohexstr') IS NOT NULL THEN 
5165
5166            'master.dbo.fn_varbintohexstr(x.sql_handle) AS sql_handle, 
5167
5168            master.dbo.fn_varbintohexstr(x.plan_handle) AS plan_handle,' 
5169
5170           ELSE 
5171
5172            'CONVERT(VARCHAR(256), x.sql_handle, 1) AS sql_handle, 
5173
5174            CONVERT(VARCHAR(256), x.plan_handle, 1) AS plan_handle,' 
5175
5176          END + 
5177
5178          ' 
5179
5180          x.statement_start_offset, 
5181
5182          x.statement_end_offset, 
5183
5184          ' + 
5185
5186          CASE 
5187
5188           WHEN @output_column_list LIKE '%|[program_name|]%' ESCAPE '|' THEN 
5189
5190            '( 
5191
5192             SELECT TOP(1) 
5193
5194              CONVERT(uniqueidentifier, CONVERT(XML, '''').value(''xs:hexBinary( substring(sql:column("agent_info.job_id_string"), 0) )'', ''binary(16)'')) AS job_id, 
5195
5196              agent_info.step_id, 
5197
5198              ( 
5199
5200               SELECT TOP(1) 
5201
5202                NULL 
5203
5204               FOR XML 
5205
5206                PATH(''job_name''), 
5207
5208                TYPE 
5209
5210              ), 
5211
5212              ( 
5213
5214               SELECT TOP(1) 
5215
5216                NULL 
5217
5218               FOR XML 
5219
5220                PATH(''step_name''), 
5221
5222                TYPE 
5223
5224              ) 
5225
5226             FROM 
5227
5228             ( 
5229
5230              SELECT TOP(1) 
5231
5232               SUBSTRING(x.program_name, CHARINDEX(''0x'', x.program_name) + 2, 32) AS job_id_string, 
5233
5234               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 
5235
5236              WHERE 
5237
5238               x.program_name LIKE N''SQLAgent - TSQL JobStep (Job 0x%'' 
5239
5240             ) AS agent_info 
5241
5242             FOR XML 
5243
5244              PATH(''agent_job_info''), 
5245
5246              TYPE 
5247
5248            ), 
5249
5250            ' 
5251
5252           ELSE '' 
5253
5254          END + 
5255
5256          CASE 
5257
5258           WHEN @get_task_info = 2 THEN 
5259
5260            'CONVERT(XML, x.block_info) AS block_info,  
5261
5262            ' 
5263
5264           ELSE 
5265
5266            '' 
5267
5268          END + ' 
5269
5270          x.host_process_id, 
5271
5272          x.group_id 
5273
5274         FOR XML 
5275
5276          PATH(''additional_info''), 
5277
5278          TYPE 
5279
5280        ) ' 
5281
5282     ELSE 
5283
5284      'NULL ' 
5285
5286    END +  
5287
5288     'AS additional_info,  
5289
5290    x.start_time,  
5291
5292     ' + 
5293
5294    CASE 
5295
5296     WHEN 
5297
5298      @output_column_list LIKE '%|[login_time|]%' ESCAPE '|' 
5299
5300      AND @recursion = 1 
5301
5302       THEN 
5303
5304        'x.login_time ' 
5305
5306     ELSE  
5307
5308      'NULL ' 
5309
5310    END +  
5311
5312     'AS login_time,  
5313
5314    x.last_request_start_time 
5315
5316   FROM 
5317
5318   ( 
5319
5320    SELECT TOP(@i) 
5321
5322     y.*, 
5323
5324     CASE 
5325
5326      WHEN DATEDIFF(hour, y.start_time, GETDATE()) > 576 THEN 
5327
5328       DATEDIFF(second, GETDATE(), y.start_time) 
5329
5330      ELSE DATEDIFF(ms, y.start_time, GETDATE()) 
5331
5332     END AS elapsed_time, 
5333
5334     COALESCE(tempdb_info.tempdb_allocations, 0) AS tempdb_allocations, 
5335
5336     COALESCE 
5337
5338     ( 
5339
5340      CASE 
5341
5342       WHEN tempdb_info.tempdb_current < 0 THEN 0 
5343
5344       ELSE tempdb_info.tempdb_current 
5345
5346      END, 
5347
5348      0 
5349
5350     ) AS tempdb_current,  
5351
5352     ' + 
5353
5354     CASE 
5355
5356      WHEN  
5357
5358       ( 
5359
5360        @get_task_info <> 0 
5361
5362        OR @find_block_leaders = 1 
5363
5364       ) THEN 
5365
5366        'N''('' + CONVERT(NVARCHAR, y.wait_duration_ms) + N''ms)'' + 
5367
5368         y.wait_type + 
5369
5370          CASE 
5371
5372           WHEN y.wait_type LIKE N''PAGE%LATCH_%'' THEN 
5373
5374            N'':'' + 
5375
5376            COALESCE(DB_NAME(CONVERT(INT, LEFT(y.resource_description, CHARINDEX(N'':'', y.resource_description) - 1))), N''(null)'') + 
5377
5378            N'':'' + 
5379
5380            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)) + 
5381
5382            N''('' + 
5383
5384             CASE 
5385
5386              WHEN 
5387
5388               CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 1 OR 
5389
5390               CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 8088 = 0 
5391
5392                THEN  
5393
5394                 N''PFS'' 
5395
5396              WHEN 
5397
5398               CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 2 OR 
5399
5400               CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 511232 = 0 
5401
5402                THEN  
5403
5404                 N''GAM'' 
5405
5406              WHEN 
5407
5408               CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 3 OR 
5409
5410               (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 1) % 511232 = 0 
5411
5412                THEN 
5413
5414                 N''SGAM'' 
5415
5416              WHEN 
5417
5418               CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 6 OR 
5419
5420               (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 6) % 511232 = 0  
5421
5422                THEN  
5423
5424                 N''DCM'' 
5425
5426              WHEN 
5427
5428               CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 7 OR 
5429
5430               (CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 7) % 511232 = 0  
5431
5432                THEN  
5433
5434                 N''BCM'' 
5435
5436              ELSE  
5437
5438               N''*'' 
5439
5440             END + 
5441
5442            N'')'' 
5443
5444           WHEN y.wait_type = N''CXPACKET'' THEN 
5445
5446            N'':'' + SUBSTRING(y.resource_description, CHARINDEX(N''nodeId'', y.resource_description) + 7, 4) 
5447
5448           WHEN y.wait_type LIKE N''LATCH[_]%'' THEN 
5449
5450            N'' ['' + LEFT(y.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', y.resource_description), 0), LEN(y.resource_description) + 1) - 1) + N'']'' 
5451
5452           WHEN 
5453
5454            y.wait_type = N''OLEDB'' 
5455
5456            AND y.resource_description LIKE N''%(SPID=%)'' THEN 
5457
5458             N''['' + LEFT(y.resource_description, CHARINDEX(N''(SPID='', y.resource_description) - 2) + 
5459
5460              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)
5461
5462+ 6)) + '']'' 
5463
5464           ELSE 
5465
5466            N'''' 
5467
5468          END COLLATE Latin1_General_Bin2 AS sys_wait_info,  
5469
5470          ' 
5471
5472       ELSE 
5473
5474        '' 
5475
5476      END + 
5477
5478      CASE 
5479
5480       WHEN @get_task_info = 2 THEN 
5481
5482        'tasks.physical_io, 
5483
5484        tasks.context_switches, 
5485
5486        tasks.tasks, 
5487
5488        tasks.block_info, 
5489
5490        tasks.wait_info AS task_wait_info, 
5491
5492        tasks.thread_CPU_snapshot, 
5493
5494        ' 
5495
5496       ELSE 
5497
5498        ''  
5499
5500     END + 
5501
5502     CASE  
5503
5504      WHEN NOT (@get_avg_time = 1 AND @recursion = 1) THEN 
5505
5506       'CONVERT(INT, NULL) ' 
5507
5508      ELSE  
5509
5510       'qs.total_elapsed_time / qs.execution_count ' 
5511
5512     END +  
5513
5514      'AS avg_elapsed_time  
5515
5516    FROM 
5517
5518    ( 
5519
5520     SELECT TOP(@i) 
5521
5522      sp.session_id, 
5523
5524      sp.request_id, 
5525
5526      COALESCE(r.logical_reads, s.logical_reads) AS reads, 
5527
5528      COALESCE(r.reads, s.reads) AS physical_reads, 
5529
5530      COALESCE(r.writes, s.writes) AS writes, 
5531
5532      COALESCE(r.CPU_time, s.CPU_time) AS CPU, 
5533
5534      sp.memory_usage + COALESCE(r.granted_query_memory, 0) AS used_memory, 
5535
5536      LOWER(sp.status) AS status, 
5537
5538      COALESCE(r.sql_handle, sp.sql_handle) AS sql_handle, 
5539
5540      COALESCE(r.statement_start_offset, sp.statement_start_offset) AS statement_start_offset, 
5541
5542      COALESCE(r.statement_end_offset, sp.statement_end_offset) AS statement_end_offset, 
5543
5544      ' + 
5545
5546      CASE 
5547
5548       WHEN  
5549
5550       ( 
5551
5552        @get_task_info <> 0 
5553
5554        OR @find_block_leaders = 1  
5555
5556       ) THEN 
5557
5558        'sp.wait_type COLLATE Latin1_General_Bin2 AS wait_type, 
5559
5560        sp.wait_resource COLLATE Latin1_General_Bin2 AS resource_description, 
5561
5562        sp.wait_time AS wait_duration_ms,  
5563
5564        ' 
5565
5566       ELSE 
5567
5568        '' 
5569
5570      END + 
5571
5572      'NULLIF(sp.blocked, 0) AS blocking_session_id, 
5573
5574      r.plan_handle, 
5575
5576      NULLIF(r.percent_complete, 0) AS percent_complete, 
5577
5578      sp.host_name, 
5579
5580      sp.login_name, 
5581
5582      sp.program_name, 
5583
5584      s.host_process_id, 
5585
5586      COALESCE(r.text_size, s.text_size) AS text_size, 
5587
5588      COALESCE(r.language, s.language) AS language, 
5589
5590      COALESCE(r.date_format, s.date_format) AS date_format, 
5591
5592      COALESCE(r.date_first, s.date_first) AS date_first, 
5593
5594      COALESCE(r.quoted_identifier, s.quoted_identifier) AS quoted_identifier, 
5595
5596      COALESCE(r.arithabort, s.arithabort) AS arithabort, 
5597
5598      COALESCE(r.ansi_null_dflt_on, s.ansi_null_dflt_on) AS ansi_null_dflt_on, 
5599
5600      COALESCE(r.ansi_defaults, s.ansi_defaults) AS ansi_defaults, 
5601
5602      COALESCE(r.ansi_warnings, s.ansi_warnings) AS ansi_warnings, 
5603
5604      COALESCE(r.ansi_padding, s.ansi_padding) AS ansi_padding, 
5605
5606      COALESCE(r.ansi_nulls, s.ansi_nulls) AS ansi_nulls, 
5607
5608      COALESCE(r.concat_null_yields_null, s.concat_null_yields_null) AS concat_null_yields_null, 
5609
5610      COALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level, 
5611
5612      COALESCE(r.lock_timeout, s.lock_timeout) AS lock_timeout, 
5613
5614      COALESCE(r.deadlock_priority, s.deadlock_priority) AS deadlock_priority, 
5615
5616      COALESCE(r.row_count, s.row_count) AS row_count, 
5617
5618      COALESCE(r.command, sp.cmd) AS command_type, 
5619
5620      COALESCE 
5621
5622      ( 
5623
5624       CASE 
5625
5626        WHEN 
5627
5628        ( 
5629
5630         s.is_user_process = 0 
5631
5632         AND r.total_elapsed_time >= 0 
5633
5634        ) THEN 
5635
5636         DATEADD 
5637
5638         ( 
5639
5640          ms, 
5641
5642          1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())), 
5643
5644          DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE()) 
5645
5646         ) 
5647
5648       END, 
5649
5650       NULLIF(COALESCE(r.start_time, sp.last_request_end_time), CONVERT(DATETIME, ''19000101'', 112)), 
5651
5652       sp.login_time 
5653
5654      ) AS start_time, 
5655
5656      sp.login_time, 
5657
5658      CASE 
5659
5660       WHEN s.is_user_process = 1 THEN 
5661
5662        s.last_request_start_time 
5663
5664       ELSE 
5665
5666        COALESCE 
5667
5668        ( 
5669
5670         DATEADD 
5671
5672         ( 
5673
5674          ms, 
5675
5676          1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())), 
5677
5678          DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE()) 
5679
5680         ), 
5681
5682         s.last_request_start_time 
5683
5684        ) 
5685
5686      END AS last_request_start_time, 
5687
5688      r.transaction_id, 
5689
5690      sp.database_id, 
5691
5692      sp.open_tran_count, 
5693
5694      ' + 
5695
5696       CASE 
5697
5698        WHEN EXISTS 
5699
5700        ( 
5701
5702         SELECT 
5703
5704          * 
5705
5706         FROM sys.all_columns AS ac 
5707
5708         WHERE 
5709
5710          ac.object_id = OBJECT_ID('sys.dm_exec_sessions') 
5711
5712          AND ac.name = 'group_id' 
5713
5714        ) 
5715
5716         THEN 's.group_id' 
5717
5718        ELSE 'CONVERT(INT, NULL) AS group_id' 
5719
5720       END + ' 
5721
5722     FROM @sessions AS sp 
5723
5724     LEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON 
5725
5726      s.session_id = sp.session_id 
5727
5728      AND s.login_time = sp.login_time 
5729
5730     LEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON 
5731
5732      sp.status <> ''sleeping'' 
5733
5734      AND r.session_id = sp.session_id 
5735
5736      AND r.request_id = sp.request_id 
5737
5738      AND 
5739
5740      ( 
5741
5742       ( 
5743
5744        s.is_user_process = 0 
5745
5746        AND sp.is_user_process = 0 
5747
5748       ) 
5749
5750       OR 
5751
5752       ( 
5753
5754        r.start_time = s.last_request_start_time 
5755
5756        AND s.last_request_end_time <= sp.last_request_end_time 
5757
5758       ) 
5759
5760      ) 
5761
5762    ) AS y 
5763
5764    ' +  
5765
5766    CASE  
5767
5768     WHEN @get_task_info = 2 THEN 
5769
5770      CONVERT(VARCHAR(MAX), '') + 
5771
5772      'LEFT OUTER HASH JOIN 
5773
5774      ( 
5775
5776       SELECT TOP(@i) 
5777
5778        task_nodes.task_node.value(''(session_id/text())[1]'', ''SMALLINT'') AS session_id, 
5779
5780        task_nodes.task_node.value(''(request_id/text())[1]'', ''INT'') AS request_id, 
5781
5782        task_nodes.task_node.value(''(physical_io/text())[1]'', ''BIGINT'') AS physical_io, 
5783
5784        task_nodes.task_node.value(''(context_switches/text())[1]'', ''BIGINT'') AS context_switches, 
5785
5786        task_nodes.task_node.value(''(tasks/text())[1]'', ''INT'') AS tasks,  
5787
5788        task_nodes.task_node.value(''(block_info/text())[1]'', ''NVARCHAR(4000)'') AS block_info, 
5789
5790        task_nodes.task_node.value(''(waits/text())[1]'', ''NVARCHAR(4000)'') AS wait_info, 
5791
5792        task_nodes.task_node.value(''(thread_CPU_snapshot/text())[1]'', ''BIGINT'') AS thread_CPU_snapshot 
5793
5794       FROM 
5795
5796       ( 
5797
5798        SELECT TOP(@i) 
5799
5800         CONVERT 
5801
5802         ( 
5803
5804          XML, 
5805
5806          REPLACE 
5807
5808          ( 
5809
5810           CONVERT(NVARCHAR(MAX), tasks_raw.task_xml_raw) COLLATE Latin1_General_Bin2, 
5811
5812           N''</waits></tasks><tasks><waits>'', 
5813
5814           N'', '' 
5815
5816          ) 
5817
5818         ) AS task_xml 
5819
5820        FROM 
5821
5822        ( 
5823
5824         SELECT TOP(@i) 
5825
5826          CASE waits.r 
5827
5828           WHEN 1 THEN 
5829
5830            waits.session_id 
5831
5832           ELSE 
5833
5834            NULL 
5835
5836          END AS [session_id], 
5837
5838          CASE waits.r 
5839
5840           WHEN 1 THEN 
5841
5842            waits.request_id 
5843
5844           ELSE 
5845
5846            NULL 
5847
5848          END AS [request_id],            
5849
5850          CASE waits.r 
5851
5852           WHEN 1 THEN 
5853
5854            waits.physical_io 
5855
5856           ELSE 
5857
5858            NULL 
5859
5860          END AS [physical_io], 
5861
5862          CASE waits.r 
5863
5864           WHEN 1 THEN 
5865
5866            waits.context_switches 
5867
5868           ELSE 
5869
5870            NULL 
5871
5872          END AS [context_switches], 
5873
5874          CASE waits.r 
5875
5876           WHEN 1 THEN 
5877
5878            waits.thread_CPU_snapshot 
5879
5880           ELSE 
5881
5882            NULL 
5883
5884          END AS [thread_CPU_snapshot], 
5885
5886          CASE waits.r 
5887
5888           WHEN 1 THEN 
5889
5890            waits.tasks 
5891
5892           ELSE 
5893
5894            NULL 
5895
5896          END AS [tasks], 
5897
5898          CASE waits.r 
5899
5900           WHEN 1 THEN 
5901
5902            waits.block_info 
5903
5904           ELSE 
5905
5906            NULL 
5907
5908          END AS [block_info], 
5909
5910          REPLACE 
5911
5912          ( 
5913
5914           REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
5915
5916           REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
5917
5918           REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
5919
5920            CONVERT 
5921
5922            ( 
5923
5924             NVARCHAR(MAX), 
5925
5926             N''('' + 
5927
5928              CONVERT(NVARCHAR, num_waits) + N''x: '' + 
5929
5930              CASE num_waits 
5931
5932               WHEN 1 THEN 
5933
5934                CONVERT(NVARCHAR, min_wait_time) + N''ms'' 
5935
5936               WHEN 2 THEN 
5937
5938                CASE 
5939
5940                 WHEN min_wait_time <> max_wait_time THEN 
5941
5942                  CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms'' 
5943
5944                 ELSE 
5945
5946                  CONVERT(NVARCHAR, max_wait_time) + N''ms'' 
5947
5948                END 
5949
5950               ELSE 
5951
5952                CASE 
5953
5954                 WHEN min_wait_time <> max_wait_time THEN 
5955
5956                  CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, avg_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms'' 
5957
5958                 ELSE  
5959
5960                  CONVERT(NVARCHAR, max_wait_time) + N''ms'' 
5961
5962                END 
5963
5964              END + 
5965
5966             N'')'' + wait_type COLLATE Latin1_General_Bin2 
5967
5968            ), 
5969
5970            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''?''), 
5971
5972            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''?''), 
5973
5974            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''?''), 
5975
5976           NCHAR(0), 
5977
5978           N'''' 
5979
5980          ) AS [waits] 
5981
5982         FROM 
5983
5984         ( 
5985
5986          SELECT TOP(@i) 
5987
5988           w1.*, 
5989
5990           ROW_NUMBER() OVER 
5991
5992           ( 
5993
5994            PARTITION BY 
5995
5996             w1.session_id, 
5997
5998             w1.request_id 
5999
6000            ORDER BY 
6001
6002             w1.block_info DESC, 
6003
6004             w1.num_waits DESC, 
6005
6006             w1.wait_type 
6007
6008           ) AS r 
6009
6010          FROM 
6011
6012          ( 
6013
6014           SELECT TOP(@i)  
6015
6016            task_info.session_id, 
6017
6018            task_info.request_id, 
6019
6020            task_info.physical_io, 
6021
6022            task_info.context_switches, 
6023
6024            task_info.thread_CPU_snapshot, 
6025
6026            task_info.num_tasks AS tasks, 
6027
6028            CASE 
6029
6030             WHEN task_info.runnable_time IS NOT NULL THEN 
6031
6032              ''RUNNABLE'' 
6033
6034             ELSE 
6035
6036              wt2.wait_type 
6037
6038            END AS wait_type, 
6039
6040            NULLIF(COUNT(COALESCE(task_info.runnable_time, wt2.waiting_task_address)), 0) AS num_waits, 
6041
6042            MIN(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS min_wait_time, 
6043
6044            AVG(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS avg_wait_time, 
6045
6046            MAX(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS max_wait_time, 
6047
6048            MAX(wt2.block_info) AS block_info 
6049
6050           FROM 
6051
6052           ( 
6053
6054            SELECT TOP(@i) 
6055
6056             t.session_id, 
6057
6058             t.request_id, 
6059
6060             SUM(CONVERT(BIGINT, t.pending_io_count)) OVER (PARTITION BY t.session_id, t.request_id) AS physical_io, 
6061
6062             SUM(CONVERT(BIGINT, t.context_switches_count)) OVER (PARTITION BY t.session_id, t.request_id) AS context_switches,  
6063
6064             ' + 
6065
6066             CASE 
6067
6068              WHEN  
6069
6070               @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|' 
6071
6072               AND @sys_info = 1 
6073
6074               THEN 
6075
6076                'SUM(tr.usermode_time + tr.kernel_time) OVER (PARTITION BY t.session_id, t.request_id) ' 
6077
6078              ELSE 
6079
6080               'CONVERT(BIGINT, NULL) ' 
6081
6082             END +  
6083
6084              ' AS thread_CPU_snapshot,  
6085
6086             COUNT(*) OVER (PARTITION BY t.session_id, t.request_id) AS num_tasks, 
6087
6088             t.task_address, 
6089
6090             t.task_state, 
6091
6092             CASE 
6093
6094              WHEN 
6095
6096               t.task_state = ''RUNNABLE'' 
6097
6098               AND w.runnable_time > 0 THEN 
6099
6100                w.runnable_time 
6101
6102              ELSE 
6103
6104               NULL 
6105
6106             END AS runnable_time 
6107
6108            FROM sys.dm_os_tasks AS t 
6109
6110            CROSS APPLY 
6111
6112            ( 
6113
6114             SELECT TOP(1) 
6115
6116              sp2.session_id 
6117
6118             FROM @sessions AS sp2 
6119
6120             WHERE 
6121
6122              sp2.session_id = t.session_id 
6123
6124              AND sp2.request_id = t.request_id 
6125
6126              AND sp2.status <> ''sleeping'' 
6127
6128            ) AS sp20 
6129
6130            LEFT OUTER HASH JOIN 
6131
6132            (  
6133
6134            ' + 
6135
6136             CASE 
6137
6138              WHEN @sys_info = 1 THEN 
6139
6140               'SELECT TOP(@i) 
6141
6142                ( 
6143
6144                 SELECT TOP(@i) 
6145
6146                  ms_ticks 
6147
6148                 FROM sys.dm_os_sys_info 
6149
6150                ) - 
6151
6152                 w0.wait_resumed_ms_ticks AS runnable_time, 
6153
6154                w0.worker_address, 
6155
6156                w0.thread_address, 
6157
6158                w0.task_bound_ms_ticks 
6159
6160               FROM sys.dm_os_workers AS w0 
6161
6162               WHERE 
6163
6164                w0.state = ''RUNNABLE'' 
6165
6166                OR @first_collection_ms_ticks >= w0.task_bound_ms_ticks' 
6167
6168              ELSE 
6169
6170               'SELECT 
6171
6172                CONVERT(BIGINT, NULL) AS runnable_time, 
6173
6174                CONVERT(VARBINARY(8), NULL) AS worker_address, 
6175
6176                CONVERT(VARBINARY(8), NULL) AS thread_address, 
6177
6178                CONVERT(BIGINT, NULL) AS task_bound_ms_ticks 
6179
6180               WHERE 
6181
6182                1 = 0' 
6183
6184              END + 
6185
6186            ' 
6187
6188            ) AS w ON 
6189
6190             w.worker_address = t.worker_address  
6191
6192            ' + 
6193
6194            CASE 
6195
6196             WHEN 
6197
6198              @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|' 
6199
6200              AND @sys_info = 1 
6201
6202              THEN 
6203
6204               'LEFT OUTER HASH JOIN sys.dm_os_threads AS tr ON 
6205
6206                tr.thread_address = w.thread_address 
6207
6208                AND @first_collection_ms_ticks >= w.task_bound_ms_ticks 
6209
6210               ' 
6211
6212             ELSE 
6213
6214              '' 
6215
6216            END + 
6217
6218           ') AS task_info 
6219
6220           LEFT OUTER HASH JOIN 
6221
6222           ( 
6223
6224            SELECT TOP(@i) 
6225
6226             wt1.wait_type, 
6227
6228             wt1.waiting_task_address, 
6229
6230             MAX(wt1.wait_duration_ms) AS wait_duration_ms, 
6231
6232             MAX(wt1.block_info) AS block_info 
6233
6234            FROM 
6235
6236            ( 
6237
6238             SELECT DISTINCT TOP(@i) 
6239
6240              wt.wait_type + 
6241
6242               CASE 
6243
6244                WHEN wt.wait_type LIKE N''PAGE%LATCH_%'' THEN 
6245
6246                 '':'' + 
6247
6248                 COALESCE(DB_NAME(CONVERT(INT, LEFT(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) - 1))), N''(null)'') + 
6249
6250                 N'':'' + 
6251
6252                 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)) + 
6253
6254                 N''('' + 
6255
6256                  CASE 
6257
6258                   WHEN 
6259
6260                    CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 1 OR 
6261
6262                    CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 8088 = 0 
6263
6264                     THEN  
6265
6266                      N''PFS'' 
6267
6268                   WHEN 
6269
6270                    CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 2 OR 
6271
6272                    CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 511232 = 0  
6273
6274                     THEN  
6275
6276                      N''GAM'' 
6277
6278                   WHEN 
6279
6280                    CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 3 OR 
6281
6282                    (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 1) % 511232 = 0  
6283
6284                     THEN  
6285
6286                      N''SGAM'' 
6287
6288                   WHEN 
6289
6290                    CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 6 OR 
6291
6292                    (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 6) % 511232 = 0  
6293
6294                     THEN  
6295
6296                      N''DCM'' 
6297
6298                   WHEN 
6299
6300                    CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 7 OR 
6301
6302                    (CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 7) % 511232 = 0 
6303
6304                     THEN  
6305
6306                      N''BCM'' 
6307
6308                   ELSE 
6309
6310                    N''*'' 
6311
6312                  END + 
6313
6314                 N'')'' 
6315
6316                WHEN wt.wait_type = N''CXPACKET'' THEN 
6317
6318                 N'':'' + SUBSTRING(wt.resource_description, CHARINDEX(N''nodeId'', wt.resource_description) + 7, 4) 
6319
6320                WHEN wt.wait_type LIKE N''LATCH[_]%'' THEN 
6321
6322                 N'' ['' + LEFT(wt.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description), 0), LEN(wt.resource_description) + 1) - 1) + N'']'' 
6323
6324                ELSE  
6325
6326                 N'''' 
6327
6328               END COLLATE Latin1_General_Bin2 AS wait_type, 
6329
6330              CASE 
6331
6332               WHEN 
6333
6334               ( 
6335
6336                wt.blocking_session_id IS NOT NULL 
6337
6338                AND wt.wait_type LIKE N''LCK[_]%'' 
6339
6340               ) THEN 
6341
6342                ( 
6343
6344                 SELECT TOP(@i) 
6345
6346                  x.lock_type, 
6347
6348                  REPLACE 
6349
6350                  ( 
6351
6352                   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
6353
6354                   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
6355
6356                   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
6357
6358                    DB_NAME 
6359
6360                    ( 
6361
6362                     CONVERT 
6363
6364                     ( 
6365
6366                      INT, 
6367
6368                      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_des
6369
6370cription) + 1) - CHARINDEX(N''dbid='', wt.resource_description) - 5) 
6371
6372                     ) 
6373
6374                    ), 
6375
6376                    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''?''), 
6377
6378                    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''?''), 
6379
6380                    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''?''), 
6381
6382                   NCHAR(0), 
6383
6384                   N'''' 
6385
6386                  ) AS database_name, 
6387
6388                  CASE x.lock_type 
6389
6390                   WHEN N''objectlock'' THEN 
6391
6392                    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_des
6393
6394cription) + 1) - CHARINDEX(N''objid='', wt.resource_description) - 6) 
6395
6396                   ELSE 
6397
6398                    NULL 
6399
6400                  END AS object_id, 
6401
6402                  CASE x.lock_type 
6403
6404                   WHEN N''filelock'' THEN 
6405
6406                    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_d
6407
6408escription) + 1) - CHARINDEX(N''fileid='', wt.resource_description) - 7) 
6409
6410                   ELSE 
6411
6412                    NULL 
6413
6414                  END AS file_id, 
6415
6416                  CASE 
6417
6418                   WHEN x.lock_type in (N''pagelock'', N''extentlock'', N''ridlock'') THEN 
6419
6420                    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) +
6421
642219), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''associatedObjectId='', wt.resource_description) - 19) 
6423
6424                   WHEN x.lock_type in (N''keylock'', N''hobtlock'', N''allocunitlock'') THEN 
6425
6426                    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_d
6427
6428escription) + 1) - CHARINDEX(N''hobtid='', wt.resource_description) - 7) 
6429
6430                   ELSE 
6431
6432                    NULL 
6433
6434                  END AS hobt_id, 
6435
6436                  CASE x.lock_type 
6437
6438                   WHEN N''applicationlock'' THEN 
6439
6440                    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_descr
6441
6442iption) + 1) - CHARINDEX(N''hash='', wt.resource_description) - 5) 
6443
6444                   ELSE 
6445
6446                    NULL 
6447
6448                  END AS applock_hash, 
6449
6450                  CASE x.lock_type 
6451
6452                   WHEN N''metadatalock'' THEN 
6453
6454                    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(w
6455
6456t.resource_description) + 1) - CHARINDEX(N''subresource='', wt.resource_description) - 12) 
6457
6458                   ELSE 
6459
6460                    NULL 
6461
6462                  END AS metadata_resource, 
6463
6464                  CASE x.lock_type 
6465
6466                   WHEN N''metadatalock'' THEN 
6467
6468                    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.resour
6469
6470ce_description) + 1) - 8) 
6471
6472                   ELSE 
6473
6474                    NULL 
6475
6476                  END AS metadata_class_id 
6477
6478                 FROM 
6479
6480                 ( 
6481
6482                  SELECT TOP(1) 
6483
6484                   LEFT(wt.resource_description, CHARINDEX(N'' '', wt.resource_description) - 1) COLLATE Latin1_General_Bin2 AS lock_type 
6485
6486                 ) AS x 
6487
6488                 FOR XML 
6489
6490                  PATH('''') 
6491
6492                ) 
6493
6494               ELSE NULL 
6495
6496              END AS block_info, 
6497
6498              wt.wait_duration_ms, 
6499
6500              wt.waiting_task_address 
6501
6502             FROM 
6503
6504             ( 
6505
6506              SELECT TOP(@i) 
6507
6508               wt0.wait_type COLLATE Latin1_General_Bin2 AS wait_type, 
6509
6510               wt0.resource_description COLLATE Latin1_General_Bin2 AS resource_description, 
6511
6512               wt0.wait_duration_ms, 
6513
6514               wt0.waiting_task_address, 
6515
6516               CASE 
6517
6518                WHEN wt0.blocking_session_id = p.blocked THEN 
6519
6520                 wt0.blocking_session_id 
6521
6522                ELSE 
6523
6524                 NULL 
6525
6526               END AS blocking_session_id 
6527
6528              FROM sys.dm_os_waiting_tasks AS wt0 
6529
6530              CROSS APPLY 
6531
6532              ( 
6533
6534               SELECT TOP(1) 
6535
6536                s0.blocked 
6537
6538               FROM @sessions AS s0 
6539
6540               WHERE 
6541
6542                s0.session_id = wt0.session_id 
6543
6544                AND COALESCE(s0.wait_type, N'''') <> N''OLEDB'' 
6545
6546                AND wt0.wait_type <> N''OLEDB'' 
6547
6548              ) AS p 
6549
6550             ) AS wt 
6551
6552            ) AS wt1 
6553
6554            GROUP BY 
6555
6556             wt1.wait_type, 
6557
6558             wt1.waiting_task_address 
6559
6560           ) AS wt2 ON 
6561
6562            wt2.waiting_task_address = task_info.task_address 
6563
6564            AND wt2.wait_duration_ms > 0 
6565
6566            AND task_info.runnable_time IS NULL 
6567
6568           GROUP BY 
6569
6570            task_info.session_id, 
6571
6572            task_info.request_id, 
6573
6574            task_info.physical_io, 
6575
6576            task_info.context_switches, 
6577
6578            task_info.thread_CPU_snapshot, 
6579
6580            task_info.num_tasks, 
6581
6582            CASE 
6583
6584             WHEN task_info.runnable_time IS NOT NULL THEN 
6585
6586              ''RUNNABLE'' 
6587
6588             ELSE 
6589
6590              wt2.wait_type 
6591
6592            END 
6593
6594          ) AS w1 
6595
6596         ) AS waits 
6597
6598         ORDER BY 
6599
6600          waits.session_id, 
6601
6602          waits.request_id, 
6603
6604          waits.r 
6605
6606         FOR XML 
6607
6608          PATH(N''tasks''), 
6609
6610          TYPE 
6611
6612        ) AS tasks_raw (task_xml_raw) 
6613
6614       ) AS tasks_final 
6615
6616       CROSS APPLY tasks_final.task_xml.nodes(N''/tasks'') AS task_nodes (task_node) 
6617
6618       WHERE 
6619
6620        task_nodes.task_node.exist(N''session_id'') = 1 
6621
6622      ) AS tasks ON 
6623
6624       tasks.session_id = y.session_id 
6625
6626       AND tasks.request_id = y.request_id  
6627
6628      ' 
6629
6630     ELSE 
6631
6632      '' 
6633
6634    END + 
6635
6636    'LEFT OUTER HASH JOIN 
6637
6638    ( 
6639
6640     SELECT TOP(@i) 
6641
6642      t_info.session_id, 
6643
6644      COALESCE(t_info.request_id, -1) AS request_id, 
6645
6646      SUM(t_info.tempdb_allocations) AS tempdb_allocations, 
6647
6648      SUM(t_info.tempdb_current) AS tempdb_current 
6649
6650     FROM 
6651
6652     ( 
6653
6654      SELECT TOP(@i) 
6655
6656       tsu.session_id, 
6657
6658       tsu.request_id, 
6659
6660       tsu.user_objects_alloc_page_count + 
6661
6662        tsu.internal_objects_alloc_page_count AS tempdb_allocations, 
6663
6664       tsu.user_objects_alloc_page_count + 
6665
6666        tsu.internal_objects_alloc_page_count - 
6667
6668        tsu.user_objects_dealloc_page_count - 
6669
6670        tsu.internal_objects_dealloc_page_count AS tempdb_current 
6671
6672      FROM sys.dm_db_task_space_usage AS tsu 
6673
6674      CROSS APPLY 
6675
6676      ( 
6677
6678       SELECT TOP(1) 
6679
6680        s0.session_id 
6681
6682       FROM @sessions AS s0 
6683
6684       WHERE 
6685
6686        s0.session_id = tsu.session_id 
6687
6688      ) AS p 
6689
6690  
6691
6692      UNION ALL 
6693
6694  
6695
6696      SELECT TOP(@i) 
6697
6698       ssu.session_id, 
6699
6700       NULL AS request_id, 
6701
6702       ssu.user_objects_alloc_page_count + 
6703
6704        ssu.internal_objects_alloc_page_count AS tempdb_allocations, 
6705
6706       ssu.user_objects_alloc_page_count + 
6707
6708        ssu.internal_objects_alloc_page_count - 
6709
6710        ssu.user_objects_dealloc_page_count - 
6711
6712        ssu.internal_objects_dealloc_page_count AS tempdb_current 
6713
6714      FROM sys.dm_db_session_space_usage AS ssu 
6715
6716      CROSS APPLY 
6717
6718      ( 
6719
6720       SELECT TOP(1) 
6721
6722        s0.session_id 
6723
6724       FROM @sessions AS s0 
6725
6726       WHERE 
6727
6728        s0.session_id = ssu.session_id 
6729
6730      ) AS p 
6731
6732     ) AS t_info 
6733
6734     GROUP BY 
6735
6736      t_info.session_id, 
6737
6738      COALESCE(t_info.request_id, -1) 
6739
6740    ) AS tempdb_info ON 
6741
6742     tempdb_info.session_id = y.session_id 
6743
6744     AND tempdb_info.request_id = 
6745
6746      CASE 
6747
6748       WHEN y.status = N''sleeping'' THEN 
6749
6750        -1 
6751
6752       ELSE 
6753
6754        y.request_id 
6755
6756      END 
6757
6758    ' + 
6759
6760    CASE  
6761
6762     WHEN  
6763
6764      NOT  
6765
6766      (  
6767
6768       @get_avg_time = 1  
6769
6770       AND @recursion = 1 
6771
6772      ) THEN  
6773
6774       '' 
6775
6776     ELSE 
6777
6778      'LEFT OUTER HASH JOIN 
6779
6780      ( 
6781
6782       SELECT TOP(@i) 
6783
6784        * 
6785
6786       FROM sys.dm_exec_query_stats 
6787
6788      ) AS qs ON 
6789
6790       qs.sql_handle = y.sql_handle 
6791
6792       AND qs.plan_handle = y.plan_handle 
6793
6794       AND qs.statement_start_offset = y.statement_start_offset 
6795
6796       AND qs.statement_end_offset = y.statement_end_offset 
6797
6798      ' 
6799
6800    END +  
6801
6802   ') AS x 
6803
6804   OPTION (KEEPFIXED PLAN, OPTIMIZE FOR (@i = 1)); '; 
6805
6806  
6807
6808  SET @sql_n = CONVERT(NVARCHAR(MAX), @sql); 
6809
6810  
6811
6812  SET @last_collection_start = GETDATE(); 
6813
6814  
6815
6816  IF  
6817
6818   @recursion = -1 
6819
6820   AND @sys_info = 1 
6821
6822  BEGIN; 
6823
6824   SELECT 
6825
6826    @first_collection_ms_ticks = ms_ticks 
6827
6828   FROM sys.dm_os_sys_info; 
6829
6830  END; 
6831
6832  
6833
6834  INSERT #sessions 
6835
6836  ( 
6837
6838   recursion, 
6839
6840   session_id, 
6841
6842   request_id, 
6843
6844   session_number, 
6845
6846   elapsed_time, 
6847
6848   avg_elapsed_time, 
6849
6850   physical_io, 
6851
6852   reads, 
6853
6854   physical_reads, 
6855
6856   writes, 
6857
6858   tempdb_allocations, 
6859
6860   tempdb_current, 
6861
6862   CPU, 
6863
6864   thread_CPU_snapshot, 
6865
6866   context_switches, 
6867
6868   used_memory, 
6869
6870   tasks, 
6871
6872   status, 
6873
6874   wait_info, 
6875
6876   transaction_id, 
6877
6878   open_tran_count, 
6879
6880   sql_handle,  
6881
6882   statement_start_offset, 
6883
6884   statement_end_offset,   
6885
6886   sql_text, 
6887
6888   plan_handle, 
6889
6890   blocking_session_id, 
6891
6892   percent_complete, 
6893
6894   host_name, 
6895
6896   login_name, 
6897
6898   database_name, 
6899
6900   program_name, 
6901
6902   additional_info, 
6903
6904   start_time, 
6905
6906   login_time, 
6907
6908   last_request_start_time 
6909
6910  ) 
6911
6912  EXEC sp_executesql  
6913
6914   @sql_n, 
6915
6916   N'@recursion SMALLINT, @filter sysname, @not_filter sysname, @first_collection_ms_ticks BIGINT', 
6917
6918   @recursion, @filter, @not_filter, @first_collection_ms_ticks; 
6919
6920  
6921
6922  --Collect transaction information? 
6923
6924  IF 
6925
6926   @recursion = 1 
6927
6928   AND 
6929
6930   ( 
6931
6932    @output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|' 
6933
6934    OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|'  
6935
6936   ) 
6937
6938  BEGIN;  
6939
6940   DECLARE @i INT; 
6941
6942   SET @i = 2147483647; 
6943
6944  
6945
6946   UPDATE s 
6947
6948   SET 
6949
6950    tran_start_time = 
6951
6952     CONVERT 
6953
6954     ( 
6955
6956      DATETIME, 
6957
6958      LEFT 
6959
6960      ( 
6961
6962       x.trans_info, 
6963
6964       NULLIF(CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info) - 1, -1) 
6965
6966      ), 
6967
6968      121 
6969
6970     ), 
6971
6972    tran_log_writes = 
6973
6974     RIGHT 
6975
6976     ( 
6977
6978      x.trans_info, 
6979
6980      LEN(x.trans_info) - CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info) 
6981
6982     ) 
6983
6984   FROM 
6985
6986   ( 
6987
6988    SELECT TOP(@i) 
6989
6990     trans_nodes.trans_node.value('(session_id/text())[1]', 'SMALLINT') AS session_id, 
6991
6992     COALESCE(trans_nodes.trans_node.value('(request_id/text())[1]', 'INT'), 0) AS request_id, 
6993
6994     trans_nodes.trans_node.value('(trans_info/text())[1]', 'NVARCHAR(4000)') AS trans_info     
6995
6996    FROM 
6997
6998    ( 
6999
7000     SELECT TOP(@i) 
7001
7002      CONVERT 
7003
7004      ( 
7005
7006       XML, 
7007
7008       REPLACE 
7009
7010       ( 
7011
7012        CONVERT(NVARCHAR(MAX), trans_raw.trans_xml_raw) COLLATE Latin1_General_Bin2,  
7013
7014        N'</trans_info></trans><trans><trans_info>', N'' 
7015
7016       ) 
7017
7018      ) 
7019
7020     FROM 
7021
7022     ( 
7023
7024      SELECT TOP(@i) 
7025
7026       CASE u_trans.r 
7027
7028        WHEN 1 THEN u_trans.session_id 
7029
7030        ELSE NULL 
7031
7032       END AS [session_id], 
7033
7034       CASE u_trans.r 
7035
7036        WHEN 1 THEN u_trans.request_id 
7037
7038        ELSE NULL 
7039
7040       END AS [request_id], 
7041
7042       CONVERT 
7043
7044       ( 
7045
7046        NVARCHAR(MAX), 
7047
7048        CASE 
7049
7050         WHEN u_trans.database_id IS NOT NULL THEN 
7051
7052          CASE u_trans.r 
7053
7054           WHEN 1 THEN COALESCE(CONVERT(NVARCHAR, u_trans.transaction_start_time, 121) + NCHAR(254), N'') 
7055
7056           ELSE N'' 
7057
7058          END +  
7059
7060           REPLACE 
7061
7062           ( 
7063
7064            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
7065
7066            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
7067
7068            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
7069
7070             CONVERT(VARCHAR(128), COALESCE(DB_NAME(u_trans.database_id), N'(null)')), 
7071
7072             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'?'), 
7073
7074             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'?'), 
7075
7076             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'?'), 
7077
7078            NCHAR(0), 
7079
7080            N'?' 
7081
7082           ) + 
7083
7084           N': ' + 
7085
7086          CONVERT(NVARCHAR, u_trans.log_record_count) + N' (' + CONVERT(NVARCHAR, u_trans.log_kb_used) + N' kB)' + 
7087
7088          N',' 
7089
7090         ELSE 
7091
7092          N'N/A,' 
7093
7094        END COLLATE Latin1_General_Bin2 
7095
7096       ) AS [trans_info] 
7097
7098      FROM 
7099
7100      ( 
7101
7102       SELECT TOP(@i) 
7103
7104        trans.*, 
7105
7106        ROW_NUMBER() OVER 
7107
7108        ( 
7109
7110         PARTITION BY 
7111
7112          trans.session_id, 
7113
7114          trans.request_id  
7115
7116         ORDER BY 
7117
7118          trans.transaction_start_time DESC 
7119
7120        ) AS r 
7121
7122       FROM 
7123
7124       ( 
7125
7126        SELECT TOP(@i) 
7127
7128         session_tran_map.session_id, 
7129
7130         session_tran_map.request_id, 
7131
7132         s_tran.database_id, 
7133
7134         COALESCE(SUM(s_tran.database_transaction_log_record_count), 0) AS log_record_count, 
7135
7136         COALESCE(SUM(s_tran.database_transaction_log_bytes_used), 0) / 1024 AS log_kb_used, 
7137
7138         MIN(s_tran.database_transaction_begin_time) AS transaction_start_time 
7139
7140        FROM 
7141
7142        ( 
7143
7144         SELECT TOP(@i) 
7145
7146          * 
7147
7148         FROM sys.dm_tran_active_transactions 
7149
7150         WHERE 
7151
7152          transaction_begin_time <= @last_collection_start 
7153
7154        ) AS a_tran 
7155
7156        INNER HASH JOIN 
7157
7158        ( 
7159
7160         SELECT TOP(@i) 
7161
7162          * 
7163
7164         FROM sys.dm_tran_database_transactions 
7165
7166         WHERE 
7167
7168          database_id < 32767 
7169
7170        ) AS s_tran ON 
7171
7172         s_tran.transaction_id = a_tran.transaction_id 
7173
7174        LEFT OUTER HASH JOIN 
7175
7176        ( 
7177
7178         SELECT TOP(@i) 
7179
7180          * 
7181
7182         FROM sys.dm_tran_session_transactions 
7183
7184        ) AS tst ON 
7185
7186         s_tran.transaction_id = tst.transaction_id 
7187
7188        CROSS APPLY 
7189
7190        ( 
7191
7192         SELECT TOP(1) 
7193
7194          s3.session_id, 
7195
7196          s3.request_id 
7197
7198         FROM 
7199
7200         ( 
7201
7202          SELECT TOP(1) 
7203
7204           s1.session_id, 
7205
7206           s1.request_id 
7207
7208          FROM #sessions AS s1 
7209
7210          WHERE 
7211
7212           s1.transaction_id = s_tran.transaction_id 
7213
7214           AND s1.recursion = 1 
7215
7216             
7217
7218          UNION ALL 
7219
7220           
7221
7222          SELECT TOP(1) 
7223
7224           s2.session_id, 
7225
7226           s2.request_id 
7227
7228          FROM #sessions AS s2 
7229
7230          WHERE 
7231
7232           s2.session_id = tst.session_id 
7233
7234           AND s2.recursion = 1 
7235
7236         ) AS s3 
7237
7238         ORDER BY 
7239
7240          s3.request_id 
7241
7242        ) AS session_tran_map 
7243
7244        GROUP BY 
7245
7246         session_tran_map.session_id, 
7247
7248         session_tran_map.request_id, 
7249
7250         s_tran.database_id 
7251
7252       ) AS trans 
7253
7254      ) AS u_trans 
7255
7256      FOR XML 
7257
7258       PATH('trans'), 
7259
7260       TYPE 
7261
7262     ) AS trans_raw (trans_xml_raw) 
7263
7264    ) AS trans_final (trans_xml) 
7265
7266    CROSS APPLY trans_final.trans_xml.nodes('/trans') AS trans_nodes (trans_node) 
7267
7268   ) AS x 
7269
7270   INNER HASH JOIN #sessions AS s ON 
7271
7272    s.session_id = x.session_id 
7273
7274    AND s.request_id = x.request_id 
7275
7276   OPTION (OPTIMIZE FOR (@i = 1)); 
7277
7278  END; 
7279
7280  
7281
7282  --Variables for text and plan collection 
7283
7284  DECLARE  
7285
7286   @session_id SMALLINT, 
7287
7288   @request_id INT, 
7289
7290   @sql_handle VARBINARY(64), 
7291
7292   @plan_handle VARBINARY(64), 
7293
7294   @statement_start_offset INT, 
7295
7296   @statement_end_offset INT, 
7297
7298   @start_time DATETIME, 
7299
7300   @database_name sysname; 
7301
7302  
7303
7304  IF  
7305
7306   @recursion = 1 
7307
7308   AND @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|' 
7309
7310  BEGIN; 
7311
7312   DECLARE sql_cursor 
7313
7314   CURSOR LOCAL FAST_FORWARD 
7315
7316   FOR  
7317
7318    SELECT  
7319
7320     session_id, 
7321
7322     request_id, 
7323
7324     sql_handle, 
7325
7326     statement_start_offset, 
7327
7328     statement_end_offset 
7329
7330    FROM #sessions 
7331
7332    WHERE 
7333
7334     recursion = 1 
7335
7336     AND sql_handle IS NOT NULL 
7337
7338   OPTION (KEEPFIXED PLAN); 
7339
7340  
7341
7342   OPEN sql_cursor; 
7343
7344  
7345
7346   FETCH NEXT FROM sql_cursor 
7347
7348   INTO  
7349
7350    @session_id, 
7351
7352    @request_id, 
7353
7354    @sql_handle, 
7355
7356    @statement_start_offset, 
7357
7358    @statement_end_offset; 
7359
7360  
7361
7362   --Wait up to 5 ms for the SQL text, then give up 
7363
7364   SET LOCK_TIMEOUT 5; 
7365
7366  
7367
7368   WHILE @@FETCH_STATUS = 0 
7369
7370   BEGIN; 
7371
7372    BEGIN TRY; 
7373
7374     UPDATE s 
7375
7376     SET 
7377
7378      s.sql_text = 
7379
7380      ( 
7381
7382       SELECT 
7383
7384        REPLACE 
7385
7386        ( 
7387
7388         REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
7389
7390         REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
7391
7392         REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
7393
7394          N'--' + NCHAR(13) + NCHAR(10) + 
7395
7396          CASE  
7397
7398           WHEN @get_full_inner_text = 1 THEN est.text 
7399
7400           WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN est.text 
7401
7402           WHEN SUBSTRING(est.text, (@statement_start_offset/2), 2) LIKE N'[a-zA-Z0-9][a-zA-Z0-9]' THEN est.text 
7403
7404           ELSE 
7405
7406            CASE 
7407
7408             WHEN @statement_start_offset > 0 THEN 
7409
7410              SUBSTRING 
7411
7412              ( 
7413
7414               est.text, 
7415
7416               ((@statement_start_offset/2) + 1), 
7417
7418               ( 
7419
7420                CASE 
7421
7422                 WHEN @statement_end_offset = -1 THEN 2147483647 
7423
7424                 ELSE ((@statement_end_offset - @statement_start_offset)/2) + 1 
7425
7426                END 
7427
7428               ) 
7429
7430              ) 
7431
7432             ELSE RTRIM(LTRIM(est.text)) 
7433
7434            END 
7435
7436          END + 
7437
7438          NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2, 
7439
7440          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'?'), 
7441
7442          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'?'), 
7443
7444          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'?'), 
7445
7446         NCHAR(0), 
7447
7448         N'' 
7449
7450        ) AS [processing-instruction(query)] 
7451
7452       FOR XML 
7453
7454        PATH(''), 
7455
7456        TYPE 
7457
7458      ), 
7459
7460      s.statement_start_offset =  
7461
7462       CASE  
7463
7464        WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN 0 
7465
7466        WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN 0 
7467
7468        ELSE @statement_start_offset 
7469
7470       END, 
7471
7472      s.statement_end_offset =  
7473
7474       CASE  
7475
7476        WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN -1 
7477
7478        WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN -1 
7479
7480        ELSE @statement_end_offset 
7481
7482       END 
7483
7484     FROM  
7485
7486      #sessions AS s, 
7487
7488      ( 
7489
7490       SELECT TOP(1) 
7491
7492        text 
7493
7494       FROM 
7495
7496       ( 
7497
7498        SELECT  
7499
7500         text,  
7501
7502         0 AS row_num 
7503
7504        FROM sys.dm_exec_sql_text(@sql_handle) 
7505
7506          
7507
7508        UNION ALL 
7509
7510          
7511
7512        SELECT  
7513
7514         NULL, 
7515
7516         1 AS row_num 
7517
7518       ) AS est0 
7519
7520       ORDER BY 
7521
7522        row_num 
7523
7524      ) AS est 
7525
7526     WHERE  
7527
7528      s.session_id = @session_id 
7529
7530      AND s.request_id = @request_id 
7531
7532      AND s.recursion = 1 
7533
7534     OPTION (KEEPFIXED PLAN); 
7535
7536    END TRY 
7537
7538    BEGIN CATCH; 
7539
7540     UPDATE s 
7541
7542     SET 
7543
7544      s.sql_text =  
7545
7546       CASE ERROR_NUMBER()  
7547
7548        WHEN 1222 THEN '<timeout_exceeded />' 
7549
7550        ELSE '<error message="' + ERROR_MESSAGE() + '" />' 
7551
7552       END 
7553
7554     FROM #sessions AS s 
7555
7556     WHERE  
7557
7558      s.session_id = @session_id 
7559
7560      AND s.request_id = @request_id 
7561
7562      AND s.recursion = 1 
7563
7564     OPTION (KEEPFIXED PLAN); 
7565
7566    END CATCH; 
7567
7568  
7569
7570    FETCH NEXT FROM sql_cursor 
7571
7572    INTO 
7573
7574     @session_id, 
7575
7576     @request_id, 
7577
7578     @sql_handle, 
7579
7580     @statement_start_offset, 
7581
7582     @statement_end_offset; 
7583
7584   END; 
7585
7586  
7587
7588   --Return this to the default 
7589
7590   SET LOCK_TIMEOUT -1; 
7591
7592  
7593
7594   CLOSE sql_cursor; 
7595
7596   DEALLOCATE sql_cursor; 
7597
7598  END; 
7599
7600  
7601
7602  IF  
7603
7604   @get_outer_command = 1  
7605
7606   AND @recursion = 1 
7607
7608   AND @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|' 
7609
7610  BEGIN; 
7611
7612   DECLARE @buffer_results TABLE 
7613
7614   ( 
7615
7616    EventType VARCHAR(30), 
7617
7618    Parameters INT, 
7619
7620    EventInfo NVARCHAR(4000), 
7621
7622    start_time DATETIME, 
7623
7624    session_number INT IDENTITY(1,1) NOT NULL PRIMARY KEY 
7625
7626   ); 
7627
7628  
7629
7630   DECLARE buffer_cursor 
7631
7632   CURSOR LOCAL FAST_FORWARD 
7633
7634   FOR  
7635
7636    SELECT  
7637
7638     session_id, 
7639
7640     MAX(start_time) AS start_time 
7641
7642    FROM #sessions 
7643
7644    WHERE 
7645
7646     recursion = 1 
7647
7648    GROUP BY 
7649
7650     session_id 
7651
7652    ORDER BY 
7653
7654     session_id 
7655
7656    OPTION (KEEPFIXED PLAN); 
7657
7658  
7659
7660   OPEN buffer_cursor; 
7661
7662  
7663
7664   FETCH NEXT FROM buffer_cursor 
7665
7666   INTO  
7667
7668    @session_id, 
7669
7670    @start_time; 
7671
7672  
7673
7674   WHILE @@FETCH_STATUS = 0 
7675
7676   BEGIN; 
7677
7678    BEGIN TRY; 
7679
7680     --In SQL Server 2008, DBCC INPUTBUFFER will throw  
7681
7682     --an exception if the session no longer exists 
7683
7684     INSERT @buffer_results 
7685
7686     ( 
7687
7688      EventType, 
7689
7690      Parameters, 
7691
7692      EventInfo 
7693
7694     ) 
7695
7696     EXEC sp_executesql 
7697
7698      N'DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;', 
7699
7700      N'@session_id SMALLINT', 
7701
7702      @session_id; 
7703
7704  
7705
7706     UPDATE br 
7707
7708     SET 
7709
7710      br.start_time = @start_time 
7711
7712     FROM @buffer_results AS br 
7713
7714     WHERE 
7715
7716      br.session_number =  
7717
7718      ( 
7719
7720       SELECT MAX(br2.session_number) 
7721
7722       FROM @buffer_results br2 
7723
7724      ); 
7725
7726    END TRY 
7727
7728    BEGIN CATCH 
7729
7730    END CATCH; 
7731
7732  
7733
7734    FETCH NEXT FROM buffer_cursor 
7735
7736    INTO  
7737
7738     @session_id, 
7739
7740     @start_time; 
7741
7742   END; 
7743
7744  
7745
7746   UPDATE s 
7747
7748   SET 
7749
7750    sql_command =  
7751
7752    ( 
7753
7754     SELECT  
7755
7756      REPLACE 
7757
7758      ( 
7759
7760       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
7761
7762       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
7763
7764       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
7765
7766        CONVERT 
7767
7768        ( 
7769
7770         NVARCHAR(MAX), 
7771
7772         N'--' + NCHAR(13) + NCHAR(10) + br.EventInfo + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2 
7773
7774        ), 
7775
7776        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'?'), 
7777
7778        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'?'), 
7779
7780        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'?'), 
7781
7782       NCHAR(0), 
7783
7784       N'' 
7785
7786      ) AS [processing-instruction(query)] 
7787
7788     FROM @buffer_results AS br 
7789
7790     WHERE  
7791
7792      br.session_number = s.session_number 
7793
7794      AND br.start_time = s.start_time 
7795
7796      AND  
7797
7798      ( 
7799
7800       ( 
7801
7802        s.start_time = s.last_request_start_time 
7803
7804        AND EXISTS 
7805
7806        ( 
7807
7808         SELECT * 
7809
7810         FROM sys.dm_exec_requests r2 
7811
7812         WHERE 
7813
7814          r2.session_id = s.session_id 
7815
7816          AND r2.request_id = s.request_id 
7817
7818          AND r2.start_time = s.start_time 
7819
7820        ) 
7821
7822       ) 
7823
7824       OR  
7825
7826       ( 
7827
7828        s.request_id = 0 
7829
7830        AND EXISTS 
7831
7832        ( 
7833
7834         SELECT * 
7835
7836         FROM sys.dm_exec_sessions s2 
7837
7838         WHERE 
7839
7840          s2.session_id = s.session_id 
7841
7842          AND s2.last_request_start_time = s.last_request_start_time 
7843
7844        ) 
7845
7846       ) 
7847
7848      ) 
7849
7850     FOR XML 
7851
7852      PATH(''), 
7853
7854      TYPE 
7855
7856    ) 
7857
7858   FROM #sessions AS s 
7859
7860   WHERE 
7861
7862    recursion = 1 
7863
7864   OPTION (KEEPFIXED PLAN); 
7865
7866  
7867
7868   CLOSE buffer_cursor; 
7869
7870   DEALLOCATE buffer_cursor; 
7871
7872  END; 
7873
7874  
7875
7876  IF  
7877
7878   @get_plans >= 1  
7879
7880   AND @recursion = 1 
7881
7882   AND @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|' 
7883
7884  BEGIN; 
7885
7886   DECLARE @live_plan BIT; 
7887
7888   SET @live_plan = ISNULL(CONVERT(BIT, SIGN(OBJECT_ID('sys.dm_exec_query_statistics_xml'))), 0) 
7889
7890  
7891
7892   DECLARE plan_cursor 
7893
7894   CURSOR LOCAL FAST_FORWARD 
7895
7896   FOR  
7897
7898    SELECT 
7899
7900     session_id, 
7901
7902     request_id, 
7903
7904     plan_handle, 
7905
7906     statement_start_offset, 
7907
7908     statement_end_offset 
7909
7910    FROM #sessions 
7911
7912    WHERE 
7913
7914     recursion = 1 
7915
7916     AND plan_handle IS NOT NULL 
7917
7918   OPTION (KEEPFIXED PLAN); 
7919
7920  
7921
7922   OPEN plan_cursor; 
7923
7924  
7925
7926   FETCH NEXT FROM plan_cursor 
7927
7928   INTO  
7929
7930    @session_id, 
7931
7932    @request_id, 
7933
7934    @plan_handle, 
7935
7936    @statement_start_offset, 
7937
7938    @statement_end_offset; 
7939
7940  
7941
7942   --Wait up to 5 ms for a query plan, then give up 
7943
7944   SET LOCK_TIMEOUT 5; 
7945
7946  
7947
7948   WHILE @@FETCH_STATUS = 0 
7949
7950   BEGIN; 
7951
7952    DECLARE @query_plan XML; 
7953
7954    SET @query_plan = NULL; 
7955
7956  
7957
7958    IF @live_plan = 1 
7959
7960    BEGIN; 
7961
7962     BEGIN TRY; 
7963
7964      SELECT 
7965
7966       @query_plan = x.query_plan 
7967
7968      FROM sys.dm_exec_query_statistics_xml(@session_id) AS x; 
7969
7970  
7971
7972      IF  
7973
7974       @query_plan IS NOT NULL 
7975
7976       AND EXISTS 
7977
7978       ( 
7979
7980        SELECT 
7981
7982         * 
7983
7984        FROM sys.dm_exec_requests AS r 
7985
7986        WHERE 
7987
7988         r.session_id = @session_id 
7989
7990         AND r.request_id = @request_id 
7991
7992         AND r.plan_handle = @plan_handle 
7993
7994         AND r.statement_start_offset = @statement_start_offset 
7995
7996         AND r.statement_end_offset = @statement_end_offset 
7997
7998       ) 
7999
8000      BEGIN; 
8001
8002       UPDATE s 
8003
8004       SET 
8005
8006        s.query_plan = @query_plan 
8007
8008       FROM #sessions AS s 
8009
8010       WHERE  
8011
8012        s.session_id = @session_id 
8013
8014        AND s.request_id = @request_id 
8015
8016        AND s.recursion = 1 
8017
8018       OPTION (KEEPFIXED PLAN); 
8019
8020      END; 
8021
8022     END TRY 
8023
8024     BEGIN CATCH; 
8025
8026      SET @query_plan = NULL; 
8027
8028     END CATCH; 
8029
8030    END; 
8031
8032  
8033
8034    IF @query_plan IS NULL 
8035
8036    BEGIN; 
8037
8038     BEGIN TRY; 
8039
8040      UPDATE s 
8041
8042      SET 
8043
8044       s.query_plan = 
8045
8046       ( 
8047
8048        SELECT 
8049
8050         CONVERT(xml, query_plan) 
8051
8052        FROM sys.dm_exec_text_query_plan 
8053
8054        ( 
8055
8056         @plan_handle,  
8057
8058         CASE @get_plans 
8059
8060          WHEN 1 THEN 
8061
8062           @statement_start_offset 
8063
8064          ELSE 
8065
8066           0 
8067
8068         END,  
8069
8070         CASE @get_plans 
8071
8072          WHEN 1 THEN 
8073
8074           @statement_end_offset 
8075
8076          ELSE 
8077
8078           -1 
8079
8080         END 
8081
8082        ) 
8083
8084       ) 
8085
8086      FROM #sessions AS s 
8087
8088      WHERE  
8089
8090       s.session_id = @session_id 
8091
8092       AND s.request_id = @request_id 
8093
8094       AND s.recursion = 1 
8095
8096      OPTION (KEEPFIXED PLAN); 
8097
8098     END TRY 
8099
8100     BEGIN CATCH; 
8101
8102      IF ERROR_NUMBER() = 6335 
8103
8104      BEGIN; 
8105
8106       UPDATE s 
8107
8108       SET 
8109
8110        s.query_plan = 
8111
8112        ( 
8113
8114         SELECT 
8115
8116          N'--' + NCHAR(13) + NCHAR(10) +  
8117
8118          N'-- Could not render showplan due to XML data type limitations. ' + NCHAR(13) + NCHAR(10) +  
8119
8120          N'-- To see the graphical plan save the XML below as a .SQLPLAN file and re-open in SSMS.' + NCHAR(13) + NCHAR(10) + 
8121
8122          N'--' + NCHAR(13) + NCHAR(10) + 
8123
8124           REPLACE(qp.query_plan, N'<RelOp', NCHAR(13)+NCHAR(10)+N'<RelOp') +  
8125
8126           NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)] 
8127
8128         FROM sys.dm_exec_text_query_plan 
8129
8130         ( 
8131
8132          @plan_handle,  
8133
8134          CASE @get_plans 
8135
8136           WHEN 1 THEN 
8137
8138            @statement_start_offset 
8139
8140           ELSE 
8141
8142            0 
8143
8144          END,  
8145
8146          CASE @get_plans 
8147
8148           WHEN 1 THEN 
8149
8150            @statement_end_offset 
8151
8152           ELSE 
8153
8154            -1 
8155
8156          END 
8157
8158         ) AS qp 
8159
8160         FOR XML 
8161
8162          PATH(''), 
8163
8164          TYPE 
8165
8166        ) 
8167
8168       FROM #sessions AS s 
8169
8170       WHERE  
8171
8172        s.session_id = @session_id 
8173
8174        AND s.request_id = @request_id 
8175
8176        AND s.recursion = 1 
8177
8178       OPTION (KEEPFIXED PLAN); 
8179
8180      END; 
8181
8182      ELSE 
8183
8184      BEGIN; 
8185
8186       UPDATE s 
8187
8188       SET 
8189
8190        s.query_plan =  
8191
8192         CASE ERROR_NUMBER()  
8193
8194          WHEN 1222 THEN '<timeout_exceeded />' 
8195
8196          ELSE '<error message="' + ERROR_MESSAGE() + '" />' 
8197
8198         END 
8199
8200       FROM #sessions AS s 
8201
8202       WHERE  
8203
8204        s.session_id = @session_id 
8205
8206        AND s.request_id = @request_id 
8207
8208        AND s.recursion = 1 
8209
8210       OPTION (KEEPFIXED PLAN); 
8211
8212      END; 
8213
8214     END CATCH; 
8215
8216    END; 
8217
8218  
8219
8220    FETCH NEXT FROM plan_cursor 
8221
8222    INTO 
8223
8224     @session_id, 
8225
8226     @request_id, 
8227
8228     @plan_handle, 
8229
8230     @statement_start_offset, 
8231
8232     @statement_end_offset; 
8233
8234   END; 
8235
8236  
8237
8238   --Return this to the default 
8239
8240   SET LOCK_TIMEOUT -1; 
8241
8242  
8243
8244   CLOSE plan_cursor; 
8245
8246   DEALLOCATE plan_cursor; 
8247
8248  END; 
8249
8250  
8251
8252  IF  
8253
8254   @get_locks = 1  
8255
8256   AND @recursion = 1 
8257
8258   AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|' 
8259
8260  BEGIN; 
8261
8262   DECLARE locks_cursor 
8263
8264   CURSOR LOCAL FAST_FORWARD 
8265
8266   FOR  
8267
8268    SELECT DISTINCT 
8269
8270     database_name 
8271
8272    FROM #locks 
8273
8274    WHERE 
8275
8276     EXISTS 
8277
8278     ( 
8279
8280      SELECT * 
8281
8282      FROM #sessions AS s 
8283
8284      WHERE 
8285
8286       s.session_id = #locks.session_id 
8287
8288       AND recursion = 1 
8289
8290     ) 
8291
8292     AND database_name <> '(null)' 
8293
8294    OPTION (KEEPFIXED PLAN); 
8295
8296  
8297
8298   OPEN locks_cursor; 
8299
8300  
8301
8302   FETCH NEXT FROM locks_cursor 
8303
8304   INTO  
8305
8306    @database_name; 
8307
8308  
8309
8310   WHILE @@FETCH_STATUS = 0 
8311
8312   BEGIN; 
8313
8314    BEGIN TRY; 
8315
8316     SET @sql_n = CONVERT(NVARCHAR(MAX), '') + 
8317
8318      'UPDATE l ' + 
8319
8320      'SET ' + 
8321
8322       'object_name = ' + 
8323
8324        'REPLACE ' + 
8325
8326        '( ' + 
8327
8328         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8329
8330         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8331
8332         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8333
8334          'o.name COLLATE Latin1_General_Bin2, ' + 
8335
8336          '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''?''), ' + 
8337
8338          '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''?''), ' + 
8339
8340          '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''?''), ' + 
8341
8342         'NCHAR(0), ' + 
8343
8344         N''''' ' + 
8345
8346        '), ' + 
8347
8348       'index_name = ' + 
8349
8350        'REPLACE ' + 
8351
8352        '( ' + 
8353
8354         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8355
8356         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8357
8358         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8359
8360          'i.name COLLATE Latin1_General_Bin2, ' + 
8361
8362          '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''?''), ' + 
8363
8364          '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''?''), ' + 
8365
8366          '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''?''), ' + 
8367
8368         'NCHAR(0), ' + 
8369
8370         N''''' ' + 
8371
8372        '), ' + 
8373
8374       'schema_name = ' + 
8375
8376        'REPLACE ' + 
8377
8378        '( ' + 
8379
8380         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8381
8382         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8383
8384         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8385
8386          's.name COLLATE Latin1_General_Bin2, ' + 
8387
8388          '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''?''), ' + 
8389
8390          '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''?''), ' + 
8391
8392          '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''?''), ' + 
8393
8394         'NCHAR(0), ' + 
8395
8396         N''''' ' + 
8397
8398        '), ' + 
8399
8400       'principal_name = ' +  
8401
8402        'REPLACE ' + 
8403
8404        '( ' + 
8405
8406         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8407
8408         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8409
8410         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
8411
8412          'dp.name COLLATE Latin1_General_Bin2, ' + 
8413
8414          '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''?''), ' + 
8415
8416          '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''?''), ' + 
8417
8418          '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''?''), ' + 
8419
8420         'NCHAR(0), ' + 
8421
8422         N''''' ' + 
8423
8424        ') ' + 
8425
8426      'FROM #locks AS l ' + 
8427
8428      'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.allocation_units AS au ON ' + 
8429
8430       'au.allocation_unit_id = l.allocation_unit_id ' + 
8431
8432      'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' + 
8433
8434       'p.hobt_id = ' + 
8435
8436        'COALESCE ' + 
8437
8438        '( ' + 
8439
8440         'l.hobt_id, ' + 
8441
8442         'CASE ' + 
8443
8444          'WHEN au.type IN (1, 3) THEN au.container_id ' + 
8445
8446          'ELSE NULL ' + 
8447
8448         'END ' + 
8449
8450        ') ' + 
8451
8452      'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p1 ON ' + 
8453
8454       'l.hobt_id IS NULL ' + 
8455
8456       'AND au.type = 2 ' + 
8457
8458       'AND p1.partition_id = au.container_id ' + 
8459
8460      'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' + 
8461
8462       'o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' + 
8463
8464      'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.indexes AS i ON ' + 
8465
8466       'i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' + 
8467
8468       'AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) ' + 
8469
8470      'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' + 
8471
8472       's.schema_id = COALESCE(l.schema_id, o.schema_id) ' + 
8473
8474      'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.database_principals AS dp ON ' + 
8475
8476       'dp.principal_id = l.principal_id ' + 
8477
8478      'WHERE ' + 
8479
8480       'l.database_name = @database_name ' + 
8481
8482      'OPTION (KEEPFIXED PLAN); '; 
8483
8484       
8485
8486     EXEC sp_executesql 
8487
8488      @sql_n, 
8489
8490      N'@database_name sysname', 
8491
8492      @database_name; 
8493
8494    END TRY 
8495
8496    BEGIN CATCH; 
8497
8498     UPDATE #locks 
8499
8500     SET 
8501
8502      query_error =  
8503
8504       REPLACE 
8505
8506       ( 
8507
8508        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(  
8509
8510        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
8511
8512        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
8513
8514         CONVERT 
8515
8516         ( 
8517
8518          NVARCHAR(MAX),  
8519
8520          ERROR_MESSAGE() COLLATE Latin1_General_Bin2 
8521
8522         ), 
8523
8524         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'?'), 
8525
8526         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'?'), 
8527
8528         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'?'), 
8529
8530        NCHAR(0), 
8531
8532        N'' 
8533
8534       ) 
8535
8536     WHERE  
8537
8538      database_name = @database_name 
8539
8540     OPTION (KEEPFIXED PLAN); 
8541
8542    END CATCH; 
8543
8544  
8545
8546    FETCH NEXT FROM locks_cursor 
8547
8548    INTO 
8549
8550     @database_name; 
8551
8552   END; 
8553
8554  
8555
8556   CLOSE locks_cursor; 
8557
8558   DEALLOCATE locks_cursor; 
8559
8560  
8561
8562   CREATE CLUSTERED INDEX IX_SRD ON #locks (session_id, request_id, database_name); 
8563
8564  
8565
8566   UPDATE s 
8567
8568   SET  
8569
8570    s.locks = 
8571
8572    ( 
8573
8574     SELECT  
8575
8576      REPLACE 
8577
8578      ( 
8579
8580       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
8581
8582       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
8583
8584       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
8585
8586        CONVERT 
8587
8588        ( 
8589
8590         NVARCHAR(MAX),  
8591
8592         l1.database_name COLLATE Latin1_General_Bin2 
8593
8594        ), 
8595
8596        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'?'), 
8597
8598        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'?'), 
8599
8600        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'?'), 
8601
8602       NCHAR(0), 
8603
8604       N'' 
8605
8606      ) AS [Database/@name], 
8607
8608      MIN(l1.query_error) AS [Database/@query_error], 
8609
8610      ( 
8611
8612       SELECT  
8613
8614        l2.request_mode AS [Lock/@request_mode], 
8615
8616        l2.request_status AS [Lock/@request_status], 
8617
8618        COUNT(*) AS [Lock/@request_count] 
8619
8620       FROM #locks AS l2 
8621
8622       WHERE  
8623
8624        l1.session_id = l2.session_id 
8625
8626        AND l1.request_id = l2.request_id 
8627
8628        AND l2.database_name = l1.database_name 
8629
8630        AND l2.resource_type = 'DATABASE' 
8631
8632       GROUP BY 
8633
8634        l2.request_mode, 
8635
8636        l2.request_status 
8637
8638       FOR XML 
8639
8640        PATH(''), 
8641
8642        TYPE 
8643
8644      ) AS [Database/Locks], 
8645
8646      ( 
8647
8648       SELECT 
8649
8650        COALESCE(l3.object_name, '(null)') AS [Object/@name], 
8651
8652        l3.schema_name AS [Object/@schema_name], 
8653
8654        ( 
8655
8656         SELECT 
8657
8658          l4.resource_type AS [Lock/@resource_type], 
8659
8660          l4.page_type AS [Lock/@page_type], 
8661
8662          l4.index_name AS [Lock/@index_name], 
8663
8664          CASE  
8665
8666           WHEN l4.object_name IS NULL THEN l4.schema_name 
8667
8668           ELSE NULL 
8669
8670          END AS [Lock/@schema_name], 
8671
8672          l4.principal_name AS [Lock/@principal_name], 
8673
8674          l4.resource_description AS [Lock/@resource_description], 
8675
8676          l4.request_mode AS [Lock/@request_mode], 
8677
8678          l4.request_status AS [Lock/@request_status], 
8679
8680          SUM(l4.request_count) AS [Lock/@request_count] 
8681
8682         FROM #locks AS l4 
8683
8684         WHERE  
8685
8686          l4.session_id = l3.session_id 
8687
8688          AND l4.request_id = l3.request_id 
8689
8690          AND l3.database_name = l4.database_name 
8691
8692          AND COALESCE(l3.object_name, '(null)') = COALESCE(l4.object_name, '(null)') 
8693
8694          AND COALESCE(l3.schema_name, '') = COALESCE(l4.schema_name, '') 
8695
8696          AND l4.resource_type <> 'DATABASE' 
8697
8698         GROUP BY 
8699
8700          l4.resource_type, 
8701
8702          l4.page_type, 
8703
8704          l4.index_name, 
8705
8706          CASE  
8707
8708           WHEN l4.object_name IS NULL THEN l4.schema_name 
8709
8710           ELSE NULL 
8711
8712          END, 
8713
8714          l4.principal_name, 
8715
8716          l4.resource_description, 
8717
8718          l4.request_mode, 
8719
8720          l4.request_status 
8721
8722         FOR XML 
8723
8724          PATH(''), 
8725
8726          TYPE 
8727
8728        ) AS [Object/Locks] 
8729
8730       FROM #locks AS l3 
8731
8732       WHERE  
8733
8734        l3.session_id = l1.session_id 
8735
8736        AND l3.request_id = l1.request_id 
8737
8738        AND l3.database_name = l1.database_name 
8739
8740        AND l3.resource_type <> 'DATABASE' 
8741
8742       GROUP BY  
8743
8744        l3.session_id, 
8745
8746        l3.request_id, 
8747
8748        l3.database_name, 
8749
8750        COALESCE(l3.object_name, '(null)'), 
8751
8752        l3.schema_name 
8753
8754       FOR XML 
8755
8756        PATH(''), 
8757
8758        TYPE 
8759
8760      ) AS [Database/Objects] 
8761
8762     FROM #locks AS l1 
8763
8764     WHERE 
8765
8766      l1.session_id = s.session_id 
8767
8768      AND l1.request_id = s.request_id 
8769
8770      AND l1.start_time IN (s.start_time, s.last_request_start_time) 
8771
8772      AND s.recursion = 1 
8773
8774     GROUP BY  
8775
8776      l1.session_id, 
8777
8778      l1.request_id, 
8779
8780      l1.database_name 
8781
8782     FOR XML 
8783
8784      PATH(''), 
8785
8786      TYPE 
8787
8788    ) 
8789
8790   FROM #sessions s 
8791
8792   OPTION (KEEPFIXED PLAN); 
8793
8794  END; 
8795
8796  
8797
8798  IF  
8799
8800   @find_block_leaders = 1 
8801
8802   AND @recursion = 1 
8803
8804   AND @output_column_list LIKE '%|[blocked_session_count|]%' ESCAPE '|' 
8805
8806  BEGIN; 
8807
8808   WITH 
8809
8810   blockers AS 
8811
8812   ( 
8813
8814    SELECT 
8815
8816     session_id, 
8817
8818     session_id AS top_level_session_id, 
8819
8820     CONVERT(VARCHAR(8000), '.' + CONVERT(VARCHAR(8000), session_id) + '.') AS the_path 
8821
8822    FROM #sessions 
8823
8824    WHERE 
8825
8826     recursion = 1 
8827
8828  
8829
8830    UNION ALL 
8831
8832  
8833
8834    SELECT 
8835
8836     s.session_id, 
8837
8838     b.top_level_session_id, 
8839
8840     CONVERT(VARCHAR(8000), b.the_path + CONVERT(VARCHAR(8000), s.session_id) + '.') AS the_path 
8841
8842    FROM blockers AS b 
8843
8844    JOIN #sessions AS s ON 
8845
8846     s.blocking_session_id = b.session_id 
8847
8848     AND s.recursion = 1 
8849
8850     AND b.the_path NOT LIKE '%.' + CONVERT(VARCHAR(8000), s.session_id) + '.%' COLLATE Latin1_General_Bin2 
8851
8852   ) 
8853
8854   UPDATE s 
8855
8856   SET 
8857
8858    s.blocked_session_count = x.blocked_session_count 
8859
8860   FROM #sessions AS s 
8861
8862   JOIN 
8863
8864   ( 
8865
8866    SELECT 
8867
8868     b.top_level_session_id AS session_id, 
8869
8870     COUNT(*) - 1 AS blocked_session_count 
8871
8872    FROM blockers AS b 
8873
8874    GROUP BY 
8875
8876     b.top_level_session_id 
8877
8878   ) x ON 
8879
8880    s.session_id = x.session_id 
8881
8882   WHERE 
8883
8884    s.recursion = 1; 
8885
8886  END; 
8887
8888  
8889
8890  IF 
8891
8892   @get_task_info = 2 
8893
8894   AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|' 
8895
8896   AND @recursion = 1 
8897
8898  BEGIN; 
8899
8900   CREATE TABLE #blocked_requests 
8901
8902   ( 
8903
8904    session_id SMALLINT NOT NULL, 
8905
8906    request_id INT NOT NULL, 
8907
8908    database_name sysname NOT NULL, 
8909
8910    object_id INT, 
8911
8912    hobt_id BIGINT, 
8913
8914    schema_id INT, 
8915
8916    schema_name sysname NULL, 
8917
8918    object_name sysname NULL, 
8919
8920    query_error NVARCHAR(2048), 
8921
8922    PRIMARY KEY (database_name, session_id, request_id) 
8923
8924   ); 
8925
8926  
8927
8928   CREATE STATISTICS s_database_name ON #blocked_requests (database_name) 
8929
8930   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
8931
8932   CREATE STATISTICS s_schema_name ON #blocked_requests (schema_name) 
8933
8934   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
8935
8936   CREATE STATISTICS s_object_name ON #blocked_requests (object_name) 
8937
8938   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
8939
8940   CREATE STATISTICS s_query_error ON #blocked_requests (query_error) 
8941
8942   WITH SAMPLE 0 ROWS, NORECOMPUTE; 
8943
8944    
8945
8946   INSERT #blocked_requests 
8947
8948   ( 
8949
8950    session_id, 
8951
8952    request_id, 
8953
8954    database_name, 
8955
8956    object_id, 
8957
8958    hobt_id, 
8959
8960    schema_id 
8961
8962   ) 
8963
8964   SELECT 
8965
8966    session_id, 
8967
8968    request_id, 
8969
8970    database_name, 
8971
8972    object_id, 
8973
8974    hobt_id, 
8975
8976    CONVERT(INT, SUBSTRING(schema_node, CHARINDEX(' = ', schema_node) + 3, LEN(schema_node))) AS schema_id 
8977
8978   FROM 
8979
8980   ( 
8981
8982    SELECT 
8983
8984     session_id, 
8985
8986     request_id, 
8987
8988     agent_nodes.agent_node.value('(database_name/text())[1]', 'sysname') AS database_name, 
8989
8990     agent_nodes.agent_node.value('(object_id/text())[1]', 'int') AS object_id, 
8991
8992     agent_nodes.agent_node.value('(hobt_id/text())[1]', 'bigint') AS hobt_id, 
8993
8994     agent_nodes.agent_node.value('(metadata_resource/text()[.="SCHEMA"]/../../metadata_class_id/text())[1]', 'varchar(100)') AS schema_node 
8995
8996    FROM #sessions AS s 
8997
8998    CROSS APPLY s.additional_info.nodes('//block_info') AS agent_nodes (agent_node) 
8999
9000    WHERE 
9001
9002     s.recursion = 1 
9003
9004   ) AS t 
9005
9006   WHERE 
9007
9008    t.database_name IS NOT NULL 
9009
9010    AND 
9011
9012    ( 
9013
9014     t.object_id IS NOT NULL 
9015
9016     OR t.hobt_id IS NOT NULL 
9017
9018     OR t.schema_node IS NOT NULL 
9019
9020    ); 
9021
9022     
9023
9024   DECLARE blocks_cursor 
9025
9026   CURSOR LOCAL FAST_FORWARD 
9027
9028   FOR 
9029
9030    SELECT DISTINCT 
9031
9032     database_name 
9033
9034    FROM #blocked_requests; 
9035
9036      
9037
9038   OPEN blocks_cursor; 
9039
9040     
9041
9042   FETCH NEXT FROM blocks_cursor 
9043
9044   INTO  
9045
9046    @database_name; 
9047
9048     
9049
9050   WHILE @@FETCH_STATUS = 0 
9051
9052   BEGIN; 
9053
9054    BEGIN TRY; 
9055
9056     SET @sql_n =  
9057
9058      CONVERT(NVARCHAR(MAX), '') + 
9059
9060      'UPDATE b ' + 
9061
9062      'SET ' + 
9063
9064       'b.schema_name = ' + 
9065
9066        'REPLACE ' + 
9067
9068        '( ' + 
9069
9070         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
9071
9072         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
9073
9074         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
9075
9076          's.name COLLATE Latin1_General_Bin2, ' + 
9077
9078          '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''?''), ' + 
9079
9080          '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''?''), ' + 
9081
9082          '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''?''), ' + 
9083
9084         'NCHAR(0), ' + 
9085
9086         N''''' ' + 
9087
9088        '), ' + 
9089
9090       'b.object_name = ' + 
9091
9092        'REPLACE ' + 
9093
9094        '( ' + 
9095
9096         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
9097
9098         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
9099
9100         'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' + 
9101
9102          'o.name COLLATE Latin1_General_Bin2, ' + 
9103
9104          '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''?''), ' + 
9105
9106          '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''?''), ' + 
9107
9108          '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''?''), ' + 
9109
9110         'NCHAR(0), ' + 
9111
9112         N''''' ' + 
9113
9114        ') ' + 
9115
9116      'FROM #blocked_requests AS b ' + 
9117
9118      'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' + 
9119
9120       'p.hobt_id = b.hobt_id ' + 
9121
9122      'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' + 
9123
9124       'o.object_id = COALESCE(p.object_id, b.object_id) ' + 
9125
9126      'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' + 
9127
9128       's.schema_id = COALESCE(o.schema_id, b.schema_id) ' + 
9129
9130      'WHERE ' + 
9131
9132       'b.database_name = @database_name; '; 
9133
9134       
9135
9136     EXEC sp_executesql  
9137
9138      @sql_n, 
9139
9140      N'@database_name sysname', 
9141
9142      @database_name; 
9143
9144    END TRY 
9145
9146    BEGIN CATCH; 
9147
9148     UPDATE #blocked_requests 
9149
9150     SET 
9151
9152      query_error =  
9153
9154       REPLACE 
9155
9156       ( 
9157
9158        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
9159
9160        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
9161
9162        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
9163
9164         CONVERT 
9165
9166         ( 
9167
9168          NVARCHAR(MAX),  
9169
9170          ERROR_MESSAGE() COLLATE Latin1_General_Bin2 
9171
9172         ), 
9173
9174         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'?'), 
9175
9176         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'?'), 
9177
9178         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'?'), 
9179
9180        NCHAR(0), 
9181
9182        N'' 
9183
9184       ) 
9185
9186     WHERE 
9187
9188      database_name = @database_name; 
9189
9190    END CATCH; 
9191
9192  
9193
9194    FETCH NEXT FROM blocks_cursor 
9195
9196    INTO 
9197
9198     @database_name; 
9199
9200   END; 
9201
9202     
9203
9204   CLOSE blocks_cursor; 
9205
9206   DEALLOCATE blocks_cursor; 
9207
9208     
9209
9210   UPDATE s 
9211
9212   SET 
9213
9214    additional_info.modify 
9215
9216    (' 
9217
9218     insert <schema_name>{sql:column("b.schema_name")}</schema_name> 
9219
9220     as last 
9221
9222     into (/additional_info/block_info)[1] 
9223
9224    ') 
9225
9226   FROM #sessions AS s 
9227
9228   INNER JOIN #blocked_requests AS b ON 
9229
9230    b.session_id = s.session_id 
9231
9232    AND b.request_id = s.request_id 
9233
9234    AND s.recursion = 1 
9235
9236   WHERE 
9237
9238    b.schema_name IS NOT NULL; 
9239
9240  
9241
9242   UPDATE s 
9243
9244   SET 
9245
9246    additional_info.modify 
9247
9248    (' 
9249
9250     insert <object_name>{sql:column("b.object_name")}</object_name> 
9251
9252     as last 
9253
9254     into (/additional_info/block_info)[1] 
9255
9256    ') 
9257
9258   FROM #sessions AS s 
9259
9260   INNER JOIN #blocked_requests AS b ON 
9261
9262    b.session_id = s.session_id 
9263
9264    AND b.request_id = s.request_id 
9265
9266    AND s.recursion = 1 
9267
9268   WHERE 
9269
9270    b.object_name IS NOT NULL; 
9271
9272  
9273
9274   UPDATE s 
9275
9276   SET 
9277
9278    additional_info.modify 
9279
9280    (' 
9281
9282     insert <query_error>{sql:column("b.query_error")}</query_error> 
9283
9284     as last 
9285
9286     into (/additional_info/block_info)[1] 
9287
9288    ') 
9289
9290   FROM #sessions AS s 
9291
9292   INNER JOIN #blocked_requests AS b ON 
9293
9294    b.session_id = s.session_id 
9295
9296    AND b.request_id = s.request_id 
9297
9298    AND s.recursion = 1 
9299
9300   WHERE 
9301
9302    b.query_error IS NOT NULL; 
9303
9304  END; 
9305
9306  
9307
9308  IF 
9309
9310   @output_column_list LIKE '%|[program_name|]%' ESCAPE '|' 
9311
9312   AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|' 
9313
9314   AND @recursion = 1 
9315
9316   AND DB_ID('msdb') IS NOT NULL 
9317
9318  BEGIN; 
9319
9320   SET @sql_n = 
9321
9322    N'BEGIN TRY; 
9323
9324     DECLARE @job_name sysname; 
9325
9326     SET @job_name = NULL; 
9327
9328     DECLARE @step_name sysname; 
9329
9330     SET @step_name = NULL; 
9331
9332  
9333
9334     SELECT 
9335
9336      @job_name =  
9337
9338       REPLACE 
9339
9340       ( 
9341
9342        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
9343
9344        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
9345
9346        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
9347
9348         j.name, 
9349
9350         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''?''), 
9351
9352         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''?''), 
9353
9354         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''?''), 
9355
9356        NCHAR(0), 
9357
9358        N''?'' 
9359
9360       ), 
9361
9362      @step_name =  
9363
9364       REPLACE 
9365
9366       ( 
9367
9368        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
9369
9370        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
9371
9372        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 
9373
9374         s.step_name, 
9375
9376         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''?''), 
9377
9378         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''?''), 
9379
9380         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''?''), 
9381
9382        NCHAR(0), 
9383
9384        N''?'' 
9385
9386       ) 
9387
9388     FROM msdb.dbo.sysjobs AS j 
9389
9390     INNER JOIN msdb.dbo.sysjobsteps AS s ON 
9391
9392      j.job_id = s.job_id 
9393
9394     WHERE 
9395
9396      j.job_id = @job_id 
9397
9398      AND s.step_id = @step_id; 
9399
9400  
9401
9402     IF @job_name IS NOT NULL 
9403
9404     BEGIN; 
9405
9406      UPDATE s 
9407
9408      SET 
9409
9410       additional_info.modify 
9411
9412       ('' 
9413
9414        insert text{sql:variable("@job_name")} 
9415
9416        into (/additional_info/agent_job_info/job_name)[1] 
9417
9418       '') 
9419
9420      FROM #sessions AS s 
9421
9422      WHERE  
9423
9424       s.session_id = @session_id 
9425
9426       AND s.recursion = 1 
9427
9428      OPTION (KEEPFIXED PLAN); 
9429
9430        
9431
9432      UPDATE s 
9433
9434      SET 
9435
9436       additional_info.modify 
9437
9438       ('' 
9439
9440        insert text{sql:variable("@step_name")} 
9441
9442        into (/additional_info/agent_job_info/step_name)[1] 
9443
9444       '') 
9445
9446      FROM #sessions AS s 
9447
9448      WHERE  
9449
9450       s.session_id = @session_id 
9451
9452       AND s.recursion = 1 
9453
9454      OPTION (KEEPFIXED PLAN); 
9455
9456     END; 
9457
9458    END TRY 
9459
9460    BEGIN CATCH; 
9461
9462     DECLARE @msdb_error_message NVARCHAR(256); 
9463
9464     SET @msdb_error_message = ERROR_MESSAGE(); 
9465
9466      
9467
9468     UPDATE s 
9469
9470     SET 
9471
9472      additional_info.modify 
9473
9474      ('' 
9475
9476       insert <msdb_query_error>{sql:variable("@msdb_error_message")}</msdb_query_error> 
9477
9478       as last 
9479
9480       into (/additional_info/agent_job_info)[1] 
9481
9482      '') 
9483
9484     FROM #sessions AS s 
9485
9486     WHERE  
9487
9488      s.session_id = @session_id 
9489
9490      AND s.recursion = 1 
9491
9492     OPTION (KEEPFIXED PLAN); 
9493
9494    END CATCH;' 
9495
9496  
9497
9498   DECLARE @job_id UNIQUEIDENTIFIER; 
9499
9500   DECLARE @step_id INT; 
9501
9502  
9503
9504   DECLARE agent_cursor 
9505
9506   CURSOR LOCAL FAST_FORWARD 
9507
9508   FOR  
9509
9510    SELECT 
9511
9512     s.session_id, 
9513
9514     agent_nodes.agent_node.value('(job_id/text())[1]', 'uniqueidentifier') AS job_id, 
9515
9516     agent_nodes.agent_node.value('(step_id/text())[1]', 'int') AS step_id 
9517
9518    FROM #sessions AS s 
9519
9520    CROSS APPLY s.additional_info.nodes('//agent_job_info') AS agent_nodes (agent_node) 
9521
9522    WHERE 
9523
9524     s.recursion = 1 
9525
9526   OPTION (KEEPFIXED PLAN); 
9527
9528     
9529
9530   OPEN agent_cursor; 
9531
9532  
9533
9534   FETCH NEXT FROM agent_cursor 
9535
9536   INTO  
9537
9538    @session_id, 
9539
9540    @job_id, 
9541
9542    @step_id; 
9543
9544  
9545
9546   WHILE @@FETCH_STATUS = 0 
9547
9548   BEGIN; 
9549
9550    EXEC sp_executesql 
9551
9552     @sql_n, 
9553
9554     N'@job_id UNIQUEIDENTIFIER, @step_id INT, @session_id SMALLINT', 
9555
9556     @job_id, @step_id, @session_id 
9557
9558  
9559
9560    FETCH NEXT FROM agent_cursor 
9561
9562    INTO  
9563
9564     @session_id, 
9565
9566     @job_id, 
9567
9568     @step_id; 
9569
9570   END; 
9571
9572  
9573
9574   CLOSE agent_cursor; 
9575
9576   DEALLOCATE agent_cursor; 
9577
9578  END;  
9579
9580    
9581
9582  IF  
9583
9584   @delta_interval > 0  
9585
9586   AND @recursion <> 1 
9587
9588  BEGIN; 
9589
9590   SET @recursion = 1; 
9591
9592  
9593
9594   DECLARE @delay_time CHAR(12); 
9595
9596   SET @delay_time = CONVERT(VARCHAR, DATEADD(second, @delta_interval, 0), 114); 
9597
9598   WAITFOR DELAY @delay_time; 
9599
9600  
9601
9602   GOTO REDO; 
9603
9604  END; 
9605
9606 END; 
9607
9608  
9609
9610 SET @sql =  
9611
9612  --Outer column list 
9613
9614  CONVERT 
9615
9616  ( 
9617
9618   VARCHAR(MAX), 
9619
9620   CASE 
9621
9622    WHEN  
9623
9624     @destination_table <> ''  
9625
9626     AND @return_schema = 0  
9627
9628      THEN 'INSERT ' + @destination_table + ' ' 
9629
9630    ELSE '' 
9631
9632   END + 
9633
9634   'SELECT ' + 
9635
9636    @output_column_list + ' ' + 
9637
9638   CASE @return_schema 
9639
9640    WHEN 1 THEN 'INTO #session_schema ' 
9641
9642    ELSE '' 
9643
9644   END 
9645
9646  --End outer column list 
9647
9648  ) +  
9649
9650  --Inner column list 
9651
9652  CONVERT 
9653
9654  ( 
9655
9656   VARCHAR(MAX), 
9657
9658   'FROM ' + 
9659
9660   '( ' + 
9661
9662    'SELECT ' + 
9663
9664     'session_id, ' + 
9665
9666     --[dd hh:mm:ss.mss] 
9667
9668     CASE 
9669
9670      WHEN @format_output IN (1, 2) THEN 
9671
9672       'CASE ' + 
9673
9674        'WHEN elapsed_time < 0 THEN ' + 
9675
9676         'RIGHT ' + 
9677
9678         '( ' + 
9679
9680          'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, (-1 * elapsed_time) / 86400), ' + 
9681
9682          'max_elapsed_length ' + 
9683
9684         ') + ' + 
9685
9686          'RIGHT ' + 
9687
9688          '( ' + 
9689
9690           'CONVERT(VARCHAR, DATEADD(second, (-1 * elapsed_time), 0), 120), ' + 
9691
9692           '9 ' + 
9693
9694          ') + ' + 
9695
9696          '''.000'' ' + 
9697
9698        'ELSE ' + 
9699
9700         'RIGHT ' + 
9701
9702         '( ' + 
9703
9704          'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, elapsed_time / 86400000), ' + 
9705
9706          'max_elapsed_length ' + 
9707
9708         ') + ' + 
9709
9710          'RIGHT ' + 
9711
9712          '( ' + 
9713
9714           'CONVERT(VARCHAR, DATEADD(second, elapsed_time / 1000, 0), 120), ' + 
9715
9716           '9 ' + 
9717
9718          ') + ' + 
9719
9720          '''.'' + ' +  
9721
9722          'RIGHT(''000'' + CONVERT(VARCHAR, elapsed_time % 1000), 3) ' + 
9723
9724       'END AS [dd hh:mm:ss.mss], ' 
9725
9726      ELSE 
9727
9728       '' 
9729
9730     END + 
9731
9732     --[dd hh:mm:ss.mss (avg)] / avg_elapsed_time 
9733
9734     CASE  
9735
9736      WHEN  @format_output IN (1, 2) THEN  
9737
9738       'RIGHT ' + 
9739
9740       '( ' + 
9741
9742        '''00'' + CONVERT(VARCHAR, avg_elapsed_time / 86400000), ' + 
9743
9744        '2 ' + 
9745
9746       ') + ' + 
9747
9748        'RIGHT ' +  
9749
9750        '( ' + 
9751
9752         'CONVERT(VARCHAR, DATEADD(second, avg_elapsed_time / 1000, 0), 120), ' + 
9753
9754         '9 ' + 
9755
9756        ') + ' + 
9757
9758        '''.'' + ' + 
9759
9760        'RIGHT(''000'' + CONVERT(VARCHAR, avg_elapsed_time % 1000), 3) AS [dd hh:mm:ss.mss (avg)], ' 
9761
9762      ELSE 
9763
9764       'avg_elapsed_time, ' 
9765
9766     END + 
9767
9768     --physical_io 
9769
9770     CASE @format_output 
9771
9772      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 ' 
9773
9774      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS ' 
9775
9776      ELSE '' 
9777
9778     END + 'physical_io, ' + 
9779
9780     --reads 
9781
9782     CASE @format_output 
9783
9784      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 ' 
9785
9786      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS ' 
9787
9788      ELSE '' 
9789
9790     END + 'reads, ' + 
9791
9792     --physical_reads 
9793
9794     CASE @format_output 
9795
9796      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 ' 
9797
9798      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS ' 
9799
9800      ELSE '' 
9801
9802     END + 'physical_reads, ' + 
9803
9804     --writes 
9805
9806     CASE @format_output 
9807
9808      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 ' 
9809
9810      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS ' 
9811
9812      ELSE '' 
9813
9814     END + 'writes, ' + 
9815
9816     --tempdb_allocations 
9817
9818     CASE @format_output 
9819
9820      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 ' 
9821
9822      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS ' 
9823
9824      ELSE '' 
9825
9826     END + 'tempdb_allocations, ' + 
9827
9828     --tempdb_current 
9829
9830     CASE @format_output 
9831
9832      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 ' 
9833
9834      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS ' 
9835
9836      ELSE '' 
9837
9838     END + 'tempdb_current, ' + 
9839
9840     --CPU 
9841
9842     CASE @format_output 
9843
9844      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 ' 
9845
9846      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS ' 
9847
9848      ELSE '' 
9849
9850     END + 'CPU, ' + 
9851
9852     --context_switches 
9853
9854     CASE @format_output 
9855
9856      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 ' 
9857
9858      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS ' 
9859
9860      ELSE '' 
9861
9862     END + 'context_switches, ' + 
9863
9864     --used_memory 
9865
9866     CASE @format_output 
9867
9868      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 ' 
9869
9870      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS ' 
9871
9872      ELSE '' 
9873
9874     END + 'used_memory, ' + 
9875
9876     CASE 
9877
9878      WHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN 
9879
9880       --physical_io_delta    
9881
9882       'CASE ' + 
9883
9884        'WHEN ' + 
9885
9886         'first_request_start_time = last_request_start_time ' +  
9887
9888         'AND num_events = 2 ' + 
9889
9890         'AND physical_io_delta >= 0 ' + 
9891
9892          'THEN ' + 
9893
9894          CASE @format_output 
9895
9896           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)) '  
9897
9898           WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) ' 
9899
9900           ELSE 'physical_io_delta ' 
9901
9902          END + 
9903
9904        'ELSE NULL ' + 
9905
9906       'END AS physical_io_delta, ' + 
9907
9908       --reads_delta 
9909
9910       'CASE ' + 
9911
9912        'WHEN ' + 
9913
9914         'first_request_start_time = last_request_start_time ' +  
9915
9916         'AND num_events = 2 ' + 
9917
9918         'AND reads_delta >= 0 ' + 
9919
9920          'THEN ' + 
9921
9922          CASE @format_output 
9923
9924           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)) ' 
9925
9926           WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) ' 
9927
9928           ELSE 'reads_delta ' 
9929
9930          END + 
9931
9932        'ELSE NULL ' + 
9933
9934       'END AS reads_delta, ' + 
9935
9936       --physical_reads_delta 
9937
9938       'CASE ' + 
9939
9940        'WHEN ' + 
9941
9942         'first_request_start_time = last_request_start_time ' +  
9943
9944         'AND num_events = 2 ' + 
9945
9946         'AND physical_reads_delta >= 0 ' + 
9947
9948          'THEN ' + 
9949
9950          CASE @format_output 
9951
9952           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)) ' 
9953
9954           WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) ' 
9955
9956           ELSE 'physical_reads_delta ' 
9957
9958          END +  
9959
9960        'ELSE NULL ' + 
9961
9962       'END AS physical_reads_delta, ' + 
9963
9964       --writes_delta 
9965
9966       'CASE ' + 
9967
9968        'WHEN ' + 
9969
9970         'first_request_start_time = last_request_start_time ' +  
9971
9972         'AND num_events = 2 ' + 
9973
9974         'AND writes_delta >= 0 ' + 
9975
9976          'THEN ' + 
9977
9978          CASE @format_output 
9979
9980           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)) ' 
9981
9982           WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) ' 
9983
9984           ELSE 'writes_delta ' 
9985
9986          END +  
9987
9988        'ELSE NULL ' + 
9989
9990       'END AS writes_delta, ' + 
9991
9992       --tempdb_allocations_delta 
9993
9994       'CASE ' + 
9995
9996        'WHEN ' + 
9997
9998         'first_request_start_time = last_request_start_time ' +  
9999
10000         'AND num_events = 2 ' + 
10001
10002         'AND tempdb_allocations_delta >= 0 ' + 
10003
10004          'THEN ' + 
10005
10006          CASE @format_output 
10007
10008           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)) ' 
10009
10010           WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) ' 
10011
10012           ELSE 'tempdb_allocations_delta ' 
10013
10014          END +  
10015
10016        'ELSE NULL ' + 
10017
10018       'END AS tempdb_allocations_delta, ' + 
10019
10020       --tempdb_current_delta 
10021
10022       --this is the only one that can (legitimately) go negative  
10023
10024       'CASE ' + 
10025
10026        'WHEN ' + 
10027
10028         'first_request_start_time = last_request_start_time ' +  
10029
10030         'AND num_events = 2 ' + 
10031
10032          'THEN ' + 
10033
10034          CASE @format_output 
10035
10036           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)) ' 
10037
10038           WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) ' 
10039
10040           ELSE 'tempdb_current_delta ' 
10041
10042          END +  
10043
10044        'ELSE NULL ' + 
10045
10046       'END AS tempdb_current_delta, ' + 
10047
10048       --CPU_delta 
10049
10050       'CASE ' + 
10051
10052        'WHEN ' + 
10053
10054         'first_request_start_time = last_request_start_time ' +  
10055
10056         'AND num_events = 2 ' + 
10057
10058          'THEN ' + 
10059
10060           'CASE ' + 
10061
10062            'WHEN ' + 
10063
10064             'thread_CPU_delta > CPU_delta ' + 
10065
10066             'AND thread_CPU_delta > 0 ' + 
10067
10068              'THEN ' + 
10069
10070               CASE @format_output 
10071
10072                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)) ' 
10073
10074                WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) ' 
10075
10076                ELSE 'thread_CPU_delta ' 
10077
10078               END +  
10079
10080            'WHEN CPU_delta >= 0 THEN ' + 
10081
10082             CASE @format_output 
10083
10084              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)) ' 
10085
10086              WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) ' 
10087
10088              ELSE 'CPU_delta ' 
10089
10090             END +  
10091
10092            'ELSE NULL ' + 
10093
10094           'END ' + 
10095
10096        'ELSE ' + 
10097
10098         'NULL ' + 
10099
10100       'END AS CPU_delta, ' + 
10101
10102       --context_switches_delta 
10103
10104       'CASE ' + 
10105
10106        'WHEN ' + 
10107
10108         'first_request_start_time = last_request_start_time ' +  
10109
10110         'AND num_events = 2 ' + 
10111
10112         'AND context_switches_delta >= 0 ' + 
10113
10114          'THEN ' + 
10115
10116          CASE @format_output 
10117
10118           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)) ' 
10119
10120           WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) ' 
10121
10122           ELSE 'context_switches_delta ' 
10123
10124          END +  
10125
10126        'ELSE NULL ' + 
10127
10128       'END AS context_switches_delta, ' + 
10129
10130       --used_memory_delta 
10131
10132       'CASE ' + 
10133
10134        'WHEN ' + 
10135
10136         'first_request_start_time = last_request_start_time ' +  
10137
10138         'AND num_events = 2 ' + 
10139
10140         'AND used_memory_delta >= 0 ' + 
10141
10142          'THEN ' + 
10143
10144          CASE @format_output 
10145
10146           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)) ' 
10147
10148           WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) ' 
10149
10150           ELSE 'used_memory_delta ' 
10151
10152          END +  
10153
10154        'ELSE NULL ' + 
10155
10156       'END AS used_memory_delta, ' 
10157
10158      ELSE '' 
10159
10160     END + 
10161
10162     --tasks 
10163
10164     CASE @format_output 
10165
10166      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 ' 
10167
10168      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) ' 
10169
10170      ELSE '' 
10171
10172     END + 'tasks, ' + 
10173
10174     'status, ' + 
10175
10176     'wait_info, ' + 
10177
10178     'locks, ' + 
10179
10180     'tran_start_time, ' + 
10181
10182     'LEFT(tran_log_writes, LEN(tran_log_writes) - 1) AS tran_log_writes, ' + 
10183
10184     --open_tran_count 
10185
10186     CASE @format_output 
10187
10188      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 ' 
10189
10190      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS ' 
10191
10192      ELSE '' 
10193
10194     END + 'open_tran_count, ' + 
10195
10196     --sql_command 
10197
10198     CASE @format_output  
10199
10200      WHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_command), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS ' 
10201
10202      ELSE '' 
10203
10204     END + 'sql_command, ' + 
10205
10206     --sql_text 
10207
10208     CASE @format_output  
10209
10210      WHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_text), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS ' 
10211
10212      ELSE '' 
10213
10214     END + 'sql_text, ' + 
10215
10216     'query_plan, ' + 
10217
10218     'blocking_session_id, ' + 
10219
10220     --blocked_session_count 
10221
10222     CASE @format_output 
10223
10224      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 ' 
10225
10226      WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS ' 
10227
10228      ELSE '' 
10229
10230     END + 'blocked_session_count, ' + 
10231
10232     --percent_complete 
10233
10234     CASE @format_output 
10235
10236      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 ' 
10237
10238      WHEN 2 THEN 'CONVERT(VARCHAR, CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1)) AS ' 
10239
10240      ELSE '' 
10241
10242     END + 'percent_complete, ' + 
10243
10244     'host_name, ' + 
10245
10246     'login_name, ' + 
10247
10248     'database_name, ' + 
10249
10250     'program_name, ' + 
10251
10252     'additional_info, ' + 
10253
10254     'start_time, ' + 
10255
10256     'login_time, ' + 
10257
10258     'CASE ' + 
10259
10260      'WHEN status = N''sleeping'' THEN NULL ' + 
10261
10262      'ELSE request_id ' + 
10263
10264     'END AS request_id, ' + 
10265
10266     'GETDATE() AS collection_time ' 
10267
10268  --End inner column list 
10269
10270  ) + 
10271
10272  --Derived table and INSERT specification 
10273
10274  CONVERT 
10275
10276  ( 
10277
10278   VARCHAR(MAX), 
10279
10280    'FROM ' + 
10281
10282    '( ' + 
10283
10284     'SELECT TOP(2147483647) ' + 
10285
10286      '*, ' + 
10287
10288      'CASE ' + 
10289
10290       'MAX ' + 
10291
10292       '( ' + 
10293
10294        'LEN ' + 
10295
10296        '( ' + 
10297
10298         'CONVERT ' + 
10299
10300         '( ' + 
10301
10302          'VARCHAR, ' + 
10303
10304          'CASE ' + 
10305
10306           'WHEN elapsed_time < 0 THEN ' + 
10307
10308            '(-1 * elapsed_time) / 86400 ' + 
10309
10310           'ELSE ' + 
10311
10312            'elapsed_time / 86400000 ' + 
10313
10314          'END ' + 
10315
10316         ') ' + 
10317
10318        ') ' + 
10319
10320       ') OVER () ' + 
10321
10322        'WHEN 1 THEN 2 ' + 
10323
10324        'ELSE ' + 
10325
10326         'MAX ' + 
10327
10328         '( ' + 
10329
10330          'LEN ' + 
10331
10332          '( ' + 
10333
10334           'CONVERT ' + 
10335
10336           '( ' + 
10337
10338            'VARCHAR, ' + 
10339
10340            'CASE ' + 
10341
10342             'WHEN elapsed_time < 0 THEN ' + 
10343
10344              '(-1 * elapsed_time) / 86400 ' + 
10345
10346             'ELSE ' + 
10347
10348              'elapsed_time / 86400000 ' + 
10349
10350            'END ' + 
10351
10352           ') ' + 
10353
10354          ') ' + 
10355
10356         ') OVER () ' + 
10357
10358      'END AS max_elapsed_length, ' + 
10359
10360      CASE 
10361
10362       WHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN 
10363
10364        'MAX(physical_io * recursion) OVER (PARTITION BY session_id, request_id) + ' + 
10365
10366         'MIN(physical_io * recursion) OVER (PARTITION BY session_id, request_id) AS physical_io_delta, ' + 
10367
10368        'MAX(reads * recursion) OVER (PARTITION BY session_id, request_id) + ' + 
10369
10370         'MIN(reads * recursion) OVER (PARTITION BY session_id, request_id) AS reads_delta, ' + 
10371
10372        'MAX(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) + ' + 
10373
10374         'MIN(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) AS physical_reads_delta, ' + 
10375
10376        'MAX(writes * recursion) OVER (PARTITION BY session_id, request_id) + ' + 
10377
10378         'MIN(writes * recursion) OVER (PARTITION BY session_id, request_id) AS writes_delta, ' + 
10379
10380        'MAX(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) + ' + 
10381
10382         'MIN(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_allocations_delta, ' + 
10383
10384        'MAX(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) + ' + 
10385
10386         'MIN(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_current_delta, ' + 
10387
10388        'MAX(CPU * recursion) OVER (PARTITION BY session_id, request_id) + ' + 
10389
10390         'MIN(CPU * recursion) OVER (PARTITION BY session_id, request_id) AS CPU_delta, ' + 
10391
10392        'MAX(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) + ' + 
10393
10394         'MIN(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) AS thread_CPU_delta, ' + 
10395
10396        'MAX(context_switches * recursion) OVER (PARTITION BY session_id, request_id) + ' + 
10397
10398         'MIN(context_switches * recursion) OVER (PARTITION BY session_id, request_id) AS context_switches_delta, ' + 
10399
10400        'MAX(used_memory * recursion) OVER (PARTITION BY session_id, request_id) + ' + 
10401
10402         'MIN(used_memory * recursion) OVER (PARTITION BY session_id, request_id) AS used_memory_delta, ' + 
10403
10404        'MIN(last_request_start_time) OVER (PARTITION BY session_id, request_id) AS first_request_start_time, ' 
10405
10406       ELSE '' 
10407
10408      END + 
10409
10410      'COUNT(*) OVER (PARTITION BY session_id, request_id) AS num_events ' + 
10411
10412     'FROM #sessions AS s1 ' + 
10413
10414     CASE  
10415
10416      WHEN @sort_order = '' THEN '' 
10417
10418      ELSE 
10419
10420       'ORDER BY ' + 
10421
10422        @sort_order 
10423
10424     END + 
10425
10426    ') AS s ' + 
10427
10428    'WHERE ' + 
10429
10430     's.recursion = 1 ' + 
10431
10432   ') x ' + 
10433
10434   'OPTION (KEEPFIXED PLAN); ' + 
10435
10436   '' + 
10437
10438   CASE @return_schema 
10439
10440    WHEN 1 THEN 
10441
10442     'SET @schema = ' + 
10443
10444      '''CREATE TABLE <table_name> ( '' + ' + 
10445
10446       'STUFF ' + 
10447
10448       '( ' + 
10449
10450        '( ' + 
10451
10452         'SELECT ' + 
10453
10454          ''','' + ' + 
10455
10456          'QUOTENAME(COLUMN_NAME) + '' '' + ' + 
10457
10458          'DATA_TYPE + ' +  
10459
10460          'CASE ' + 
10461
10462           'WHEN DATA_TYPE LIKE ''%char'' THEN ''('' + COALESCE(NULLIF(CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH), ''-1''), ''max'') + '') '' ' + 
10463
10464           'ELSE '' '' ' + 
10465
10466          'END + ' + 
10467
10468          'CASE IS_NULLABLE ' + 
10469
10470           'WHEN ''NO'' THEN ''NOT '' ' + 
10471
10472           'ELSE '''' ' + 
10473
10474          'END + ''NULL'' AS [text()] ' + 
10475
10476         'FROM tempdb.INFORMATION_SCHEMA.COLUMNS ' + 
10477
10478         'WHERE ' + 
10479
10480          'TABLE_NAME = (SELECT name FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(''tempdb..#session_schema'')) ' + 
10481
10482          'ORDER BY ' + 
10483
10484           'ORDINAL_POSITION ' + 
10485
10486         'FOR XML ' + 
10487
10488          'PATH('''') ' + 
10489
10490        '), + ' + 
10491
10492        '1, ' + 
10493
10494        '1, ' + 
10495
10496        ''''' ' + 
10497
10498       ') + ' + 
10499
10500      ''')''; '  
10501
10502    ELSE '' 
10503
10504   END 
10505
10506  --End derived table and INSERT specification 
10507
10508  ); 
10509
10510  
10511
10512 SET @sql_n = CONVERT(NVARCHAR(MAX), @sql); 
10513
10514  
10515
10516 EXEC sp_executesql 
10517
10518  @sql_n, 
10519
10520  N'@schema VARCHAR(MAX) OUTPUT', 
10521
10522  @schema OUTPUT; 
10523
10524END;