120 private links
Official 9anime website to watch all anime online in HD quality with DUB and SUB 100% FREE
How to use NetGuard + personalDNSFilter+ Any VPN, without root, without external self-hosted servers.
Record your keystrokes and mouse movements and clicks relative to window or screen. Reproduce your macros with automatic window activation. Pause and play them step-by-step. Get long repetitive tasks done in instants. All using customizable Hotkeys that will work at any time.
CDisplay Ex is a light, efficient and Free CBR Reader, and it is also the most popular comic book reader. It is able to read all comic book formats (.cbr file, .cbz, .pdf, etc..) and Manga. Everything is designed to give you the best comic reading experience, it load comic books immediately, reading is fluid and comfortable even on slow computer. It uses advanced resize techniques to provide a smooth rendering.
Open Source SQL Server Performance Monitor
Asgardia aims to unite people in a transnational, equal and progressive society to build a new home for humanity in space and protect our cradle — planet Earth.
Opserver is a monitoring system by the team at Stack Exchange, home of Stack Overflow. It independently monitors several systems as well as supports pulling data for an “all servers” view with respect to CPU, Memory, Network, and hardware stats. Currently, Opserver can monitor:
Create online Certificate
weather prediction and meteorological data visualisation
Highest to Lowest - Prison Population Rate
collection of maps and other data visualization
Your name in Japanase
When it comes to making a long-distance call, it's hard to top NASA's Deep Space Network. It’s the largest and most sensitive scientific telecommunications system in the world.
RetroArch is a frontend for emulators, game engines and media players.
It enables you to run classic games on a wide range of computers and consoles through its slick graphical interface. Settings are also unified so configuration is done once and for all.
In addition to this, you are able to run original game discs (CDs) from RetroArch.
RetroArch has advanced features like shaders, netplay, rewinding, next-frame response times, runahead, machine translation, blind accessibility features, and more!
Show open camera over all world
O www.Correcao.pt encontra e destaca os erros ortográficos, assim como muitos erros básicos gramaticais e de estilo, em textos portugueses, e oferece sugestões para melhorias. Quando há duas ou mais opções corretas, é notificado para escolher uma delas.
A free and open source alternative Twitter front-end focused on privacy.
Inspired by the Invidious project.
Open Live Writer is like Word for your blog. Open Live Writer is a powerful, lightweight blog editor that allows you to create blog posts, add photos and videos then publish to your website. You can also compose blogs posts offline and then publish on your return. Open Live Writer works with many popular blog service providers such as WordPress, Blogger, TypePad, Moveable Type, DasBlog and many more.
The Invisible Internet Project (I2P) is a fully encrypted private network layer that has been developed with privacy and security by design in order to provide protection for your activity, location and your identity. The software ships with a router that connects you to the network and applications for sharing, communicating and building.
Free and open source software for video recording and live streaming.
BOOKBANK, des PDF à télécharger
Changing the location on your iPhone or Android device involves tricking your phone into telling apps that you’re located somewhere you're not. In most cases, when you spoof your GPS location, every location-based app on your phone will be fooled.
https://avepdf.com
https://www.ilovepdf.com/
Every tool you need to work with PDFs in one place
A tool for easy online testing and sharing of database problems and their solutions.
Digital Attack Map is a live data visualization of DDoS attacks around the globe, built through a collaboration between Google Ideas and Arbor Networks. The tool surfaces anonymous attack traffic data to let users explore historic trends and find reports of outages happening on a given day.
Il ne faut jamais remettre au lendemain ce que l’on peut faire le jour même » : signification et origine du proverbe
How small are we really?
Popcorn.js is a JavaScript library that allows video, audio and other media to control and be controlled by arbitrary elements of a webpage. Combining open source web technologies, authors can let their media be the “conductor” of interactive and immersive experiences.
Through years of trial and error - skipping around internet radio stations, playing our entire music collections on shuffle, or just hammering single albums on repeat, we have found that the most effective music to aid prolonged periods of intense concentration tends to have a mixture of the following qualities:
Search engine Tor on Darknet
#1 DuckDuckGo
https://3g2upl4pq6kufc4m.onion/
#2 Ahmia
http://msydqstlz2kzerdg.onion/
#3 Not Evil
http://hss3uro2hsxfogfq.onion/
#4 Candle
http://gjobqjj7wyczbqie.onion/
#5 Torch - best
http://xmh57jrzrnw6insl.onion/
#6 Tordex
http://tordex7iie7z2wcg.onion/
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
Flashpoint : pour préserver le patrimoine des jeux Flash
par Faskil · 11 février 2020
Avec la mort prochaine de Flash (qui devrait officiellement rendre son dernier souffle le 31 décembre de cette année), c’est aussi une bonne partie du patrimoine ludique qui risque de disparaître. Au placard ces bons vieux jeux utilisant le framework d’Abode et auxquels on s’adonnait dans nos navigateurs au lieu de bosser !
/*
sp_whoisactive is a comprehensive activity monitoring stored procedure that works for all versions of SQL Server from 2005 through 2017.
You can find the most recent versions on the Downloads page.
Documentation is available on the Documentation page.
Subscribe to the super-low-volume (once a quarter, max)
*/
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS ON;
SET NUMERIC_ROUNDABORT OFF;
SET ARITHABORT ON;
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_WhoIsActive')
EXEC ('CREATE PROC dbo.sp_WhoIsActive AS SELECT ''stub version, to be replaced''')
GO
/*****
Who Is Active? v11.33 (2019-07-28)
(C) 2007-2019, Adam Machanic
Feedback: mailto:adam@dataeducation.com
Updates: http://whoisactive.com
Blog: http://dataeducation.com
License:
https://github.com/amachanic/sp_whoisactive/blob/master/LICENSE
*****/
ALTER PROC dbo.spWhoIsActive
(
--~
--Filters--Both inclusive and exclusive
--Set either filter to '' to disable
--Valid filter types are: session, program, database, login, and host
--Session is a session ID, and either 0 or '' can be used to indicate "all" sessions
--All other filter types support % or as wildcards
@filter sysname = '',
@filter_type VARCHAR(10) = 'session',
@not_filter sysname = '',
@not_filter_type VARCHAR(10) = 'session',
--Retrieve data about the calling session?
@show_own_spid BIT = 0,
--Retrieve data about system sessions?
@show_system_spids BIT = 0,
--Controls how sleeping SPIDs are handled, based on the idea of levels of interest
--0 does not pull any sleeping SPIDs
--1 pulls only those sleeping SPIDs that also have an open transaction
--2 pulls all sleeping SPIDs
@show_sleeping_spids TINYINT = 1,
--If 1, gets the full stored procedure or running batch, when available
--If 0, gets only the actual statement that is currently running in the batch or procedure
@get_full_inner_text BIT = 0,
--Get associated query plans for running tasks, if available
--If @get_plans = 1, gets the plan based on the request's statement offset
--If @get_plans = 2, gets the entire plan based on the request's plan_handle
@get_plans TINYINT = 0,
--Get the associated outer ad hoc query or stored procedure call, if available
@get_outer_command BIT = 0,
--Enables pulling transaction log write info and transaction duration
@get_transaction_info BIT = 0,
--Get information on active tasks, based on three interest levels
--Level 0 does not pull any task-related information
--Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers
--Level 2 pulls all available task-based metrics, including:
--number of active tasks, current wait stats, physical I/O, context switches, and blocker information
@get_task_info TINYINT = 1,
--Gets associated locks for each request, aggregated in an XML format
@get_locks BIT = 0,
--Get average time for past runs of an active query
--(based on the combination of plan handle, sql handle, and offset)
@get_avg_time BIT = 0,
--Get additional non-performance-related information about the session or request
--text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on,
--ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null,
--transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type
--
--If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of
--the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error)
--
--If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be
--populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id,
--applock_hash, metadata_resource, metadata_class_id, object_name, schema_name
@get_additional_info BIT = 0,
--Walk the blocking chain and count the number of
--total SPIDs blocked all the way down by a given session
--Also enables task_info Level 1, if @get_task_info is set to 0
@find_block_leaders BIT = 0,
--Pull deltas on various metrics
--Interval in seconds to wait before doing the second data pull
@delta_interval TINYINT = 0,
--List of desired output columns, in desired order
--Note that the final output will be the intersection of all enabled features and all
--columns in the list. Therefore, only columns associated with enabled features will
--actually appear in the output. Likewise, removing columns from this list may effectively
--disable features, even if they are turned on
--
--Each element in this list must be one of the valid output column names. Names must be
--delimited by square brackets. White space, formatting, and additional characters are
--allowed, as long as the list contains exact matches of delimited valid column names.
@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][%]',
--Column(s) by which to sort output, optionally with sort directions.
--Valid column choices:
--session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,
--tempdb_current, CPU, context_switches, used_memory, physical_io_delta, reads_delta,
--physical_reads_delta, writes_delta, tempdb_allocations_delta, tempdb_current_delta,
--CPU_delta, context_switches_delta, used_memory_delta, tasks, tran_start_time,
--open_tran_count, blocking_session_id, blocked_session_count, percent_complete,
--host_name, login_name, database_name, start_time, login_time, program_name
--
--Note that column names in the list must be bracket-delimited. Commas and/or white
--space are not required.
@sort_order VARCHAR(500) = '[start_time] ASC',
--Formats some of the output columns in a more "human readable" form
--0 disables outfput format
--1 formats the output for variable-width fonts
--2 formats the output for fixed-width fonts
@format_output TINYINT = 1,
--If set to a non-blank value, the script will attempt to insert into the specified
--destination table. Please note that the script will not verify that the table exists,
--or that it has the correct schema, before doing the insert.
--Table can be specified in one, two, or three-part format
@destination_table VARCHAR(4000) = '',
--If set to 1, no data collection will happen and no result set will be returned; instead,
--a CREATE TABLE statement will be returned via the @schema parameter, which will match
--the schema of the result set that would be returned by using the same collection of the
--rest of the parameters. The CREATE TABLE statement will have a placeholder token of
--<table_name> in place of an actual table name.
@return_schema BIT = 0,
@schema VARCHAR(MAX) = NULL OUTPUT,
--Help! What do I do?
@help BIT = 0
--~
)
/*
OUTPUT COLUMNS
Formatted/Non: [session_id] [smallint] NOT NULL
Session ID (a.k.a. SPID)
Formatted: [dd hh:mm:ss.mss] varchar NULL
Non-Formatted: <not returned>
For an active request, time the query has been running
For a sleeping session, time since the last batch completed
Formatted: [dd hh:mm:ss.mss (avg)] varchar NULL
Non-Formatted: [avg_elapsed_time] [int] NULL
(Requires @get_avg_time option)
How much time has the active portion of the query taken in the past, on average?
Formatted: [physical_io] varchar NULL
Non-Formatted: [physical_io] [bigint] NULL
Shows the number of physical I/Os, for active requests
Formatted: [reads] varchar NULL
Non-Formatted: [reads] [bigint] NULL
For an active request, number of reads done for the current query
For a sleeping session, total number of reads done over the lifetime of the session
Formatted: [physical_reads] varchar NULL
Non-Formatted: [physical_reads] [bigint] NULL
For an active request, number of physical reads done for the current query
For a sleeping session, total number of physical reads done over the lifetime of the session
Formatted: [writes] varchar NULL
Non-Formatted: [writes] [bigint] NULL
For an active request, number of writes done for the current query
For a sleeping session, total number of writes done over the lifetime of the session
Formatted: [tempdb_allocations] varchar NULL
Non-Formatted: [tempdb_allocations] [bigint] NULL
For an active request, number of TempDB writes done for the current query
For a sleeping session, total number of TempDB writes done over the lifetime of the session
Formatted: [tempdb_current] varchar NULL
Non-Formatted: [tempdb_current] [bigint] NULL
For an active request, number of TempDB pages currently allocated for the query
For a sleeping session, number of TempDB pages currently allocated for the session
Formatted: [CPU] varchar NULL
Non-Formatted: [CPU] [int] NULL
For an active request, total CPU time consumed by the current query
For a sleeping session, total CPU time consumed over the lifetime of the session
Formatted: [context_switches] varchar NULL
Non-Formatted: [context_switches] [bigint] NULL
Shows the number of context switches, for active requests
Formatted: [used_memory] varchar NOT NULL
Non-Formatted: [used_memory] [bigint] NOT NULL
For an active request, total memory consumption for the current query
For a sleeping session, total current memory consumption
Formatted: [physical_io_delta] varchar NULL
Non-Formatted: [physical_io_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of physical I/Os reported on the first and second collections.
If the request started after the first collection, the value will be NULL
Formatted: [reads_delta] varchar NULL
Non-Formatted: [reads_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of reads reported on the first and second collections.
If the request started after the first collection, the value will be NULL
Formatted: [physical_reads_delta] varchar NULL
Non-Formatted: [physical_reads_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of physical reads reported on the first and second collections.
If the request started after the first collection, the value will be NULL
Formatted: [writes_delta] varchar NULL
Non-Formatted: [writes_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of writes reported on the first and second collections.
If the request started after the first collection, the value will be NULL
Formatted: [tempdb_allocations_delta] varchar NULL
Non-Formatted: [tempdb_allocations_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of TempDB writes reported on the first and second collections.
If the request started after the first collection, the value will be NULL
Formatted: [tempdb_current_delta] varchar NULL
Non-Formatted: [tempdb_current_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the number of allocated TempDB pages reported on the first and second
collections. If the request started after the first collection, the value will be NULL
Formatted: [CPU_delta] varchar NULL
Non-Formatted: [CPU_delta] [int] NULL
(Requires @delta_interval option)
Difference between the CPU time reported on the first and second collections.
If the request started after the first collection, the value will be NULL
Formatted: [context_switches_delta] varchar NULL
Non-Formatted: [context_switches_delta] [bigint] NULL
(Requires @delta_interval option)
Difference between the context switches count reported on the first and second collections
If the request started after the first collection, the value will be NULL
Formatted: [used_memory_delta] varchar NULL
Non-Formatted: [used_memory_delta] [bigint] NULL
Difference between the memory usage reported on the first and second collections
If the request started after the first collection, the value will be NULL
Formatted: [tasks] varchar NULL
Non-Formatted: [tasks] [smallint] NULL
Number of worker tasks currently allocated, for active requests
Formatted/Non: [status] varchar NOT NULL
Activity status for the session (running, sleeping, etc)
Formatted/Non: [wait_info] nvarchar NULL
Aggregates wait information, in the following format:
(Ax: Bms/Cms/Dms)E
A is the number of waiting tasks currently waiting on resource type E. B/C/D are wait
times, in milliseconds. If only one thread is waiting, its wait time will be shown as B.
If two tasks are waiting, each of their wait times will be shown (B/C). If three or more
tasks are waiting, the minimum, average, and maximum wait times will be shown (B/C/D).
If wait type E is a page latch wait and the page is of a "special" type (e.g. PFS, GAM, SGAM),
the page type will be identified.
If wait type E is CXPACKET, the nodeId from the query plan will be identified
Formatted/Non: [locks] [xml] NULL
(Requires @get_locks option)
Aggregates lock information, in XML format.
The lock XML includes the lock mode, locked object, and aggregates the number of requests.
Attempts are made to identify locked objects by name
Formatted/Non: [tran_start_time] [datetime] NULL
(Requires @get_transaction_info option)
Date and time that the first transaction opened by a session caused a transaction log
write to occur.
Formatted/Non: [tran_log_writes] nvarchar NULL
(Requires @get_transaction_info option)
Aggregates transaction log write information, in the following format:
A:wB (C kB)
A is a database that has been touched by an active transaction
B is the number of log writes that have been made in the database as a result of the transaction
C is the number of log kilobytes consumed by the log records
Formatted: [open_tran_count] varchar NULL
Non-Formatted: [open_tran_count] [smallint] NULL
Shows the number of open transactions the session has open
Formatted: [sql_command] [xml] NULL
Non-Formatted: [sql_command] nvarchar NULL
(Requires @get_outer_command option)
Shows the "outer" SQL command, i.e. the text of the batch or RPC sent to the server,
if available
Formatted: [sql_text] [xml] NULL
Non-Formatted: [sql_text] nvarchar NULL
Shows the SQL text for active requests or the last statement executed
for sleeping sessions, if available in either case.
If @get_full_inner_text option is set, shows the full text of the batch.
Otherwise, shows only the active statement within the batch.
If the query text is locked, a special timeout message will be sent, in the following format:
<timeout_exceeded />
If an error occurs, an error message will be sent, in the following format:
<error message="message" />
Formatted/Non: [query_plan] [xml] NULL
(Requires @get_plans option)
Shows the query plan for the request, if available.
If the plan is locked, a special timeout message will be sent, in the following format:
<timeout_exceeded />
If an error occurs, an error message will be sent, in the following format:
<error message="message" />
Formatted/Non: [blocking_session_id] [smallint] NULL
When applicable, shows the blocking SPID
Formatted: [blocked_session_count] varchar NULL
Non-Formatted: [blocked_session_count] [smallint] NULL
(Requires @find_block_leaders option)
The total number of SPIDs blocked by this session,
all the way down the blocking chain.
Formatted: [percent_complete] varchar NULL
Non-Formatted: [percent_complete] [real] NULL
When applicable, shows the percent complete (e.g. for backups, restores, and some rollbacks)
Formatted/Non: [host_name] [sysname] NOT NULL
Shows the host name for the connection
Formatted/Non: [login_name] [sysname] NOT NULL
Shows the login name for the connection
Formatted/Non: [database_name] [sysname] NULL
Shows the connected database
Formatted/Non: [program_name] [sysname] NULL
Shows the reported program/application name
Formatted/Non: [additional_info] [xml] NULL
(Requires @get_additional_info option)
Returns additional non-performance-related session/request information
If the script finds a SQL Agent job running, the name of the job and job step will be reported
If @get_task_info = 2 and the script finds a lock wait, the locked object will be reported
Formatted/Non: [start_time] [datetime] NOT NULL
For active requests, shows the time the request started
For sleeping sessions, shows the time the last batch completed
Formatted/Non: [login_time] [datetime] NOT NULL
Shows the time that the session connected
Formatted/Non: [request_id] [int] NULL
For active requests, shows the request_id
Should be 0 unless MARS is being used
Formatted/Non: [collection_time] [datetime] NOT NULL
Time that this script's final SELECT ran
*/
AS
BEGIN;
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS ON;
SET NUMERIC_ROUNDABORT OFF;
SET ARITHABORT ON;
IF
@filter IS NULL
OR @filter_type IS NULL
OR @not_filter IS NULL
OR @not_filter_type IS NULL
OR @show_own_spid IS NULL
OR @show_system_spids IS NULL
OR @show_sleeping_spids IS NULL
OR @get_full_inner_text IS NULL
OR @get_plans IS NULL
OR @get_outer_command IS NULL
OR @get_transaction_info IS NULL
OR @get_task_info IS NULL
OR @get_locks IS NULL
OR @get_avg_time IS NULL
OR @get_additional_info IS NULL
OR @find_block_leaders IS NULL
OR @delta_interval IS NULL
OR @format_output IS NULL
OR @output_column_list IS NULL
OR @sort_order IS NULL
OR @return_schema IS NULL
OR @destination_table IS NULL
OR @help IS NULL
BEGIN;
RAISERROR('Input parameters cannot be NULL', 16, 1);
RETURN;
END;
IF @filter_type NOT IN ('session', 'program', 'database', 'login', 'host')
BEGIN;
RAISERROR('Valid filter types are: session, program, database, login, host', 16, 1);
RETURN;
END;
IF @filter_type = 'session' AND @filter LIKE '%[^0123456789]%'
BEGIN;
RAISERROR('Session filters must be valid integers', 16, 1);
RETURN;
END;
IF @not_filter_type NOT IN ('session', 'program', 'database', 'login', 'host')
BEGIN;
RAISERROR('Valid filter types are: session, program, database, login, host', 16, 1);
RETURN;
END;
IF @not_filter_type = 'session' AND @not_filter LIKE '%[^0123456789]%'
BEGIN;
RAISERROR('Session filters must be valid integers', 16, 1);
RETURN;
END;
IF @show_sleeping_spids NOT IN (0, 1, 2)
BEGIN;
RAISERROR('Valid values for @show_sleeping_spids are: 0, 1, or 2', 16, 1);
RETURN;
END;
IF @get_plans NOT IN (0, 1, 2)
BEGIN;
RAISERROR('Valid values for @get_plans are: 0, 1, or 2', 16, 1);
RETURN;
END;
IF @get_task_info NOT IN (0, 1, 2)
BEGIN;
RAISERROR('Valid values for @get_task_info are: 0, 1, or 2', 16, 1);
RETURN;
END;
IF @format_output NOT IN (0, 1, 2)
BEGIN;
RAISERROR('Valid values for @format_output are: 0, 1, or 2', 16, 1);
RETURN;
END;
IF @help = 1
BEGIN;
DECLARE
@header VARCHAR(MAX),
@params VARCHAR(MAX),
@outputs VARCHAR(MAX);
SELECT
@header =
REPLACE
(
REPLACE
(
CONVERT
(
VARCHAR(MAX),
SUBSTRING
(
t.text,
CHARINDEX('/' + REPLICATE('*', 93), t.text) + 94,
CHARINDEX(REPLICATE('*', 93) + '/', t.text) - (CHARINDEX('/' + REPLICATE('*', 93), t.text) + 94)
)
),
CHAR(13)+CHAR(10),
CHAR(13)
),
' ',
''
),
@params =
CHAR(13) +
REPLACE
(
REPLACE
(
CONVERT
(
VARCHAR(MAX),
SUBSTRING
(
t.text,
CHARINDEX('--~', t.text) + 5,
CHARINDEX('--~', t.text, CHARINDEX('--~', t.text) + 5) - (CHARINDEX('--~', t.text) + 5)
)
),
CHAR(13)+CHAR(10),
CHAR(13)
),
' ',
''
),
@outputs =
CHAR(13) +
REPLACE
(
REPLACE
(
REPLACE
(
CONVERT
(
VARCHAR(MAX),
SUBSTRING
(
t.text,
CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32,
CHARINDEX('*/', t.text, CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32) - (CHARINDEX('OUTPUT COLUMNS'+CHAR(13)+CHAR(10)+'--------------', t.text) + 32)
)
),
CHAR(9),
CHAR(255)
),
CHAR(13)+CHAR(10),
CHAR(13)
),
' ',
''
) +
CHAR(13)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE
r.session_id = @@SPID;
WITH
a0 AS
(SELECT 1 AS n UNION ALL SELECT 1),
a1 AS
(SELECT 1 AS n FROM a0 AS a, a0 AS b),
a2 AS
(SELECT 1 AS n FROM a1 AS a, a1 AS b),
a3 AS
(SELECT 1 AS n FROM a2 AS a, a2 AS b),
a4 AS
(SELECT 1 AS n FROM a3 AS a, a3 AS b),
numbers AS
(
SELECT TOP(LEN(@header) - 1)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS number
FROM a4
ORDER BY
number
)
SELECT
RTRIM(LTRIM(
SUBSTRING
(
@header,
number + 1,
CHARINDEX(CHAR(13), @header, number + 1) - number - 1
)
)) AS [------header---------------------------------------------------------------------------------------------------------------]
FROM numbers
WHERE
SUBSTRING(@header, number, 1) = CHAR(13);
WITH
a0 AS
(SELECT 1 AS n UNION ALL SELECT 1),
a1 AS
(SELECT 1 AS n FROM a0 AS a, a0 AS b),
a2 AS
(SELECT 1 AS n FROM a1 AS a, a1 AS b),
a3 AS
(SELECT 1 AS n FROM a2 AS a, a2 AS b),
a4 AS
(SELECT 1 AS n FROM a3 AS a, a3 AS b),
numbers AS
(
SELECT TOP(LEN(@params) - 1)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS number
FROM a4
ORDER BY
number
),
tokens AS
(
SELECT
RTRIM(LTRIM(
SUBSTRING
(
@params,
number + 1,
CHARINDEX(CHAR(13), @params, number + 1) - number - 1
)
)) AS token,
number,
CASE
WHEN SUBSTRING(@params, number + 1, 1) = CHAR(13) THEN number
ELSE COALESCE(NULLIF(CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number), 0), LEN(@params))
END AS param_group,
ROW_NUMBER() OVER
(
PARTITION BY
CHARINDEX(',' + CHAR(13) + CHAR(13), @params, number),
SUBSTRING(@params, number+1, 1)
ORDER BY
number
) AS group_order
FROM numbers
WHERE
SUBSTRING(@params, number, 1) = CHAR(13)
),
parsed_tokens AS
(
SELECT
MIN
(
CASE
WHEN token LIKE '@%' THEN token
ELSE NULL
END
) AS parameter,
MIN
(
CASE
WHEN token LIKE '--%' THEN RIGHT(token, LEN(token) - 2)
ELSE NULL
END
) AS description,
param_group,
group_order
FROM tokens
WHERE
NOT
(
token = ''
AND group_order > 1
)
GROUP BY
param_group,
group_order
)
SELECT
CASE
WHEN description IS NULL AND parameter IS NULL THEN '-------------------------------------------------------------------------'
WHEN param_group = MAX(param_group) OVER() THEN parameter
ELSE COALESCE(LEFT(parameter, LEN(parameter) - 1), '')
END AS [------parameter----------------------------------------------------------],
CASE
WHEN description IS NULL AND parameter IS NULL THEN '----------------------------------------------------------------------------------------------------------------------'
ELSE COALESCE(description, '')
END AS [------description-----------------------------------------------------------------------------------------------------]
FROM parsed_tokens
ORDER BY
param_group,
group_order;
WITH
a0 AS
(SELECT 1 AS n UNION ALL SELECT 1),
a1 AS
(SELECT 1 AS n FROM a0 AS a, a0 AS b),
a2 AS
(SELECT 1 AS n FROM a1 AS a, a1 AS b),
a3 AS
(SELECT 1 AS n FROM a2 AS a, a2 AS b),
a4 AS
(SELECT 1 AS n FROM a3 AS a, a3 AS b),
numbers AS
(
SELECT TOP(LEN(@outputs) - 1)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS number
FROM a4
ORDER BY
number
),
tokens AS
(
SELECT
RTRIM(LTRIM(
SUBSTRING
(
@outputs,
number + 1,
CASE
WHEN
COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) <
COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs))
THEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) - number - 1
ELSE
COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) - number - 1
END
)
)) AS token,
number,
COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs)) AS output_group,
ROW_NUMBER() OVER
(
PARTITION BY
COALESCE(NULLIF(CHARINDEX(CHAR(13) + 'Formatted', @outputs, number + 1), 0), LEN(@outputs))
ORDER BY
number
) AS output_group_order
FROM numbers
WHERE
SUBSTRING(@outputs, number, 10) = CHAR(13) + 'Formatted'
OR SUBSTRING(@outputs, number, 2) = CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2
),
output_tokens AS
(
SELECT
*,
CASE output_group_order
WHEN 2 THEN MAX(CASE output_group_order WHEN 1 THEN token ELSE NULL END) OVER (PARTITION BY output_group)
ELSE ''
END COLLATE Latin1_General_Bin2 AS column_info
FROM tokens
)
SELECT
CASE output_group_order
WHEN 1 THEN '-----------------------------------'
WHEN 2 THEN
CASE
WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN
SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+1, CHARINDEX(']', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info))
ELSE
SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2, CHARINDEX(']', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)-1)
END
ELSE ''
END AS formatted_column_name,
CASE output_group_order
WHEN 1 THEN '-----------------------------------'
WHEN 2 THEN
CASE
WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN
SUBSTRING(column_info, CHARINDEX(']', column_info)+2, LEN(column_info))
ELSE
SUBSTRING(column_info, CHARINDEX(']', column_info)+2, CHARINDEX('Non-Formatted:', column_info, CHARINDEX(']', column_info)+2) - CHARINDEX(']', column_info)-3)
END
ELSE ''
END AS formatted_column_type,
CASE output_group_order
WHEN 1 THEN '---------------------------------------'
WHEN 2 THEN
CASE
WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN ''
ELSE
CASE
WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN
SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, CHARINDEX('>', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info)))
ELSE
SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, CHARINDEX(']', column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info)))
END
END
ELSE ''
END AS unformatted_column_name,
CASE output_group_order
WHEN 1 THEN '---------------------------------------'
WHEN 2 THEN
CASE
WHEN CHARINDEX('Formatted/Non:', column_info) = 1 THEN ''
ELSE
CASE
WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX('Non-Formatted:', column_info))+1, 1) = '<' THEN ''
ELSE
SUBSTRING(column_info, CHARINDEX(']', column_info, CHARINDEX('Non-Formatted:', column_info))+2, CHARINDEX('Non-Formatted:', column_info, CHARINDEX(']', column_info)+2) - CHARINDEX(']', column_info)-3)
END
END
ELSE ''
END AS unformatted_column_type,
CASE output_group_order
WHEN 1 THEN '----------------------------------------------------------------------------------------------------------------------'
ELSE REPLACE(token, CHAR(255) COLLATE Latin1_General_Bin2, '')
END AS [------description-----------------------------------------------------------------------------------------------------]
FROM output_tokens
WHERE
NOT
(
output_group_order = 1
AND output_group = LEN(@outputs)
)
ORDER BY
output_group,
CASE output_group_order
WHEN 1 THEN 99
ELSE output_group_order
END;
RETURN;
END;
WITH
a0 AS
(SELECT 1 AS n UNION ALL SELECT 1),
a1 AS
(SELECT 1 AS n FROM a0 AS a, a0 AS b),
a2 AS
(SELECT 1 AS n FROM a1 AS a, a1 AS b),
a3 AS
(SELECT 1 AS n FROM a2 AS a, a2 AS b),
a4 AS
(SELECT 1 AS n FROM a3 AS a, a3 AS b),
numbers AS
(
SELECT TOP(LEN(@output_column_list))
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS number
FROM a4
ORDER BY
number
),
tokens AS
(
SELECT
'|[' +
SUBSTRING
(
@output_column_list,
number + 1,
CHARINDEX(']', @output_column_list, number) - number - 1
) + '|]' AS token,
number
FROM numbers
WHERE
SUBSTRING(@output_column_list, number, 1) = '['
),
ordered_columns AS
(
SELECT
x.column_name,
ROW_NUMBER() OVER
(
PARTITION BY
x.column_name
ORDER BY
tokens.number,
x.default_order
) AS r,
ROW_NUMBER() OVER
(
ORDER BY
tokens.number,
x.default_order
) AS s
FROM tokens
JOIN
(
SELECT '[session_id]' AS column_name, 1 AS default_order
UNION ALL
SELECT '[dd hh:mm:ss.mss]', 2
WHERE
@format_output IN (1, 2)
UNION ALL
SELECT '[dd hh:mm:ss.mss (avg)]', 3
WHERE
@format_output IN (1, 2)
AND @get_avg_time = 1
UNION ALL
SELECT '[avg_elapsed_time]', 4
WHERE
@format_output = 0
AND @get_avg_time = 1
UNION ALL
SELECT '[physical_io]', 5
WHERE
@get_task_info = 2
UNION ALL
SELECT '[reads]', 6
UNION ALL
SELECT '[physical_reads]', 7
UNION ALL
SELECT '[writes]', 8
UNION ALL
SELECT '[tempdb_allocations]', 9
UNION ALL
SELECT '[tempdb_current]', 10
UNION ALL
SELECT '[CPU]', 11
UNION ALL
SELECT '[context_switches]', 12
WHERE
@get_task_info = 2
UNION ALL
SELECT '[used_memory]', 13
UNION ALL
SELECT '[physical_io_delta]', 14
WHERE
@delta_interval > 0
AND @get_task_info = 2
UNION ALL
SELECT '[reads_delta]', 15
WHERE
@delta_interval > 0
UNION ALL
SELECT '[physical_reads_delta]', 16
WHERE
@delta_interval > 0
UNION ALL
SELECT '[writes_delta]', 17
WHERE
@delta_interval > 0
UNION ALL
SELECT '[tempdb_allocations_delta]', 18
WHERE
@delta_interval > 0
UNION ALL
SELECT '[tempdb_current_delta]', 19
WHERE
@delta_interval > 0
UNION ALL
SELECT '[CPU_delta]', 20
WHERE
@delta_interval > 0
UNION ALL
SELECT '[context_switches_delta]', 21
WHERE
@delta_interval > 0
AND @get_task_info = 2
UNION ALL
SELECT '[used_memory_delta]', 22
WHERE
@delta_interval > 0
UNION ALL
SELECT '[tasks]', 23
WHERE
@get_task_info = 2
UNION ALL
SELECT '[status]', 24
UNION ALL
SELECT '[wait_info]', 25
WHERE
@get_task_info > 0
OR @find_block_leaders = 1
UNION ALL
SELECT '[locks]', 26
WHERE
@get_locks = 1
UNION ALL
SELECT '[tran_start_time]', 27
WHERE
@get_transaction_info = 1
UNION ALL
SELECT '[tran_log_writes]', 28
WHERE
@get_transaction_info = 1
UNION ALL
SELECT '[open_tran_count]', 29
UNION ALL
SELECT '[sql_command]', 30
WHERE
@get_outer_command = 1
UNION ALL
SELECT '[sql_text]', 31
UNION ALL
SELECT '[query_plan]', 32
WHERE
@get_plans >= 1
UNION ALL
SELECT '[blocking_session_id]', 33
WHERE
@get_task_info > 0
OR @find_block_leaders = 1
UNION ALL
SELECT '[blocked_session_count]', 34
WHERE
@find_block_leaders = 1
UNION ALL
SELECT '[percent_complete]', 35
UNION ALL
SELECT '[host_name]', 36
UNION ALL
SELECT '[login_name]', 37
UNION ALL
SELECT '[database_name]', 38
UNION ALL
SELECT '[program_name]', 39
UNION ALL
SELECT '[additional_info]', 40
WHERE
@get_additional_info = 1
UNION ALL
SELECT '[start_time]', 41
UNION ALL
SELECT '[login_time]', 42
UNION ALL
SELECT '[request_id]', 43
UNION ALL
SELECT '[collection_time]', 44
) AS x ON
x.column_name LIKE token ESCAPE '|'
)
SELECT
@output_column_list =
STUFF
(
(
SELECT
',' + column_name as [text()]
FROM ordered_columns
WHERE
r = 1
ORDER BY
s
FOR XML
PATH('')
),
1,
1,
''
);
IF COALESCE(RTRIM(@output_column_list), '') = ''
BEGIN;
RAISERROR('No valid column matches found in @output_column_list or no columns remain due to selected options.', 16, 1);
RETURN;
END;
IF @destination_table <> ''
BEGIN;
SET @destination_table =
--database
COALESCE(QUOTENAME(PARSENAME(@destination_table, 3)) + '.', '') +
--schema
COALESCE(QUOTENAME(PARSENAME(@destination_table, 2)) + '.', '') +
--table
COALESCE(QUOTENAME(PARSENAME(@destination_table, 1)), '');
IF COALESCE(RTRIM(@destination_table), '') = ''
BEGIN;
RAISERROR('Destination table not properly formatted.', 16, 1);
RETURN;
END;
END;
WITH
a0 AS
(SELECT 1 AS n UNION ALL SELECT 1),
a1 AS
(SELECT 1 AS n FROM a0 AS a, a0 AS b),
a2 AS
(SELECT 1 AS n FROM a1 AS a, a1 AS b),
a3 AS
(SELECT 1 AS n FROM a2 AS a, a2 AS b),
a4 AS
(SELECT 1 AS n FROM a3 AS a, a3 AS b),
numbers AS
(
SELECT TOP(LEN(@sort_order))
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS number
FROM a4
ORDER BY
number
),
tokens AS
(
SELECT
'|[' +
SUBSTRING
(
@sort_order,
number + 1,
CHARINDEX(']', @sort_order, number) - number - 1
) + '|]' AS token,
SUBSTRING
(
@sort_order,
CHARINDEX(']', @sort_order, number) + 1,
COALESCE(NULLIF(CHARINDEX('[', @sort_order, CHARINDEX(']', @sort_order, number)), 0), LEN(@sort_order)) - CHARINDEX(']', @sort_order, number)
) AS next_chunk,
number
FROM numbers
WHERE
SUBSTRING(@sort_order, number, 1) = '['
),
ordered_columns AS
(
SELECT
x.column_name +
CASE
WHEN tokens.next_chunk LIKE '%asc%' THEN ' ASC'
WHEN tokens.next_chunk LIKE '%desc%' THEN ' DESC'
ELSE ''
END AS column_name,
ROW_NUMBER() OVER
(
PARTITION BY
x.column_name
ORDER BY
tokens.number
) AS r,
tokens.number
FROM tokens
JOIN
(
SELECT '[session_id]' AS column_name
UNION ALL
SELECT '[physical_io]'
UNION ALL
SELECT '[reads]'
UNION ALL
SELECT '[physical_reads]'
UNION ALL
SELECT '[writes]'
UNION ALL
SELECT '[tempdb_allocations]'
UNION ALL
SELECT '[tempdb_current]'
UNION ALL
SELECT '[CPU]'
UNION ALL
SELECT '[context_switches]'
UNION ALL
SELECT '[used_memory]'
UNION ALL
SELECT '[physical_io_delta]'
UNION ALL
SELECT '[reads_delta]'
UNION ALL
SELECT '[physical_reads_delta]'
UNION ALL
SELECT '[writes_delta]'
UNION ALL
SELECT '[tempdb_allocations_delta]'
UNION ALL
SELECT '[tempdb_current_delta]'
UNION ALL
SELECT '[CPU_delta]'
UNION ALL
SELECT '[context_switches_delta]'
UNION ALL
SELECT '[used_memory_delta]'
UNION ALL
SELECT '[tasks]'
UNION ALL
SELECT '[tran_start_time]'
UNION ALL
SELECT '[open_tran_count]'
UNION ALL
SELECT '[blocking_session_id]'
UNION ALL
SELECT '[blocked_session_count]'
UNION ALL
SELECT '[percent_complete]'
UNION ALL
SELECT '[host_name]'
UNION ALL
SELECT '[login_name]'
UNION ALL
SELECT '[database_name]'
UNION ALL
SELECT '[start_time]'
UNION ALL
SELECT '[login_time]'
UNION ALL
SELECT '[program_name]'
) AS x ON
x.column_name LIKE token ESCAPE '|'
)
SELECT
@sort_order = COALESCE(z.sort_order, '')
FROM
(
SELECT
STUFF
(
(
SELECT
',' + column_name as [text()]
FROM ordered_columns
WHERE
r = 1
ORDER BY
number
FOR XML
PATH('')
),
1,
1,
''
) AS sort_order
) AS z;
CREATE TABLE [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions)
(
recursion SMALLINT NOT NULL,
session_id SMALLINT NOT NULL,
request_id INT NOT NULL,
session_number INT NOT NULL,
elapsed_time INT NOT NULL,
avg_elapsed_time INT NULL,
physical_io BIGINT NULL,
reads BIGINT NULL,
physical_reads BIGINT NULL,
writes BIGINT NULL,
tempdb_allocations BIGINT NULL,
tempdb_current BIGINT NULL,
CPU INT NULL,
thread_CPU_snapshot BIGINT NULL,
context_switches BIGINT NULL,
used_memory BIGINT NOT NULL,
tasks SMALLINT NULL,
status VARCHAR(30) NOT NULL,
wait_info NVARCHAR(4000) NULL,
locks XML NULL,
transaction_id BIGINT NULL,
tran_start_time DATETIME NULL,
tran_log_writes NVARCHAR(4000) NULL,
open_tran_count SMALLINT NULL,
sql_command XML NULL,
sql_handle VARBINARY(64) NULL,
statement_start_offset INT NULL,
statement_end_offset INT NULL,
sql_text XML NULL,
plan_handle VARBINARY(64) NULL,
query_plan XML NULL,
blocking_session_id SMALLINT NULL,
blocked_session_count SMALLINT NULL,
percent_complete REAL NULL,
host_name sysname NULL,
login_name sysname NOT NULL,
database_name sysname NULL,
program_name sysname NULL,
additional_info XML NULL,
start_time DATETIME NOT NULL,
login_time DATETIME NULL,
last_request_start_time DATETIME NULL,
PRIMARY KEY CLUSTERED (session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON),
UNIQUE NONCLUSTERED (transaction_id, session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON)
);
IF @return_schema = 0
BEGIN;
--Disable unnecessary autostats on the table
CREATE STATISTICS s_session_id ON [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) (session_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_request_id ON [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) (request_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_transaction_id ON [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) (transaction_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_session_number ON [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) (session_number)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_status ON [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) (status)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_start_time ON [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) (start_time)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_last_request_start_time ON [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) (last_request_start_time)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_recursion ON [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) (recursion)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
DECLARE @recursion SMALLINT;
SET @recursion =
CASE @delta_interval
WHEN 0 THEN 1
ELSE -1
END;
DECLARE @first_collection_ms_ticks BIGINT;
DECLARE @last_collection_start DATETIME;
DECLARE @sys_info BIT;
SET @sys_info = ISNULL(CONVERT(BIT, SIGN(OBJECT_ID('sys.dm_os_sys_info'))), 0);
--Used for the delta pull
REDO:;
IF
@get_locks = 1
AND @recursion = 1
AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'
BEGIN;
SELECT
y.resource_type,
y.database_name,
y.object_id,
y.file_id,
y.page_type,
y.hobt_id,
y.allocation_unit_id,
y.index_id,
y.schema_id,
y.principal_id,
y.request_mode,
y.request_status,
y.session_id,
y.resource_description,
y.request_count,
s.request_id,
s.start_time,
CONVERT(sysname, NULL) AS object_name,
CONVERT(sysname, NULL) AS index_name,
CONVERT(sysname, NULL) AS schema_name,
CONVERT(sysname, NULL) AS principal_name,
CONVERT(NVARCHAR(2048), NULL) AS query_error
INTO [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks)
FROM
(
SELECT
sp.spid AS session_id,
CASE sp.status
WHEN 'sleeping' THEN CONVERT(INT, 0)
ELSE sp.request_id
END AS request_id,
CASE sp.status
WHEN 'sleeping' THEN sp.last_batch
ELSE COALESCE(req.start_time, sp.last_batch)
END AS start_time,
sp.dbid
FROM sys.sysprocesses AS sp
OUTER APPLY
(
SELECT TOP(1)
CASE
WHEN
(
sp.hostprocess > ''
OR r.total_elapsed_time < 0
) THEN
r.start_time
ELSE
DATEADD
(
ms,
1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
)
END AS start_time
FROM sys.dm_exec_requests AS r
WHERE
r.session_id = sp.spid
AND r.request_id = sp.request_id
) AS req
WHERE
--Process inclusive filter
1 =
CASE
WHEN @filter <> '' THEN
CASE @filter_type
WHEN 'session' THEN
CASE
WHEN
CONVERT(SMALLINT, @filter) = 0
OR sp.spid = CONVERT(SMALLINT, @filter)
THEN 1
ELSE 0
END
WHEN 'program' THEN
CASE
WHEN sp.program_name LIKE @filter THEN 1
ELSE 0
END
WHEN 'login' THEN
CASE
WHEN sp.loginame LIKE @filter THEN 1
ELSE 0
END
WHEN 'host' THEN
CASE
WHEN sp.hostname LIKE @filter THEN 1
ELSE 0
END
WHEN 'database' THEN
CASE
WHEN DB_NAME(sp.dbid) LIKE @filter THEN 1
ELSE 0
END
ELSE 0
END
ELSE 1
END
--Process exclusive filter
AND 0 =
CASE
WHEN @not_filter <> '' THEN
CASE @not_filter_type
WHEN 'session' THEN
CASE
WHEN sp.spid = CONVERT(SMALLINT, @not_filter) THEN 1
ELSE 0
END
WHEN 'program' THEN
CASE
WHEN sp.program_name LIKE @not_filter THEN 1
ELSE 0
END
WHEN 'login' THEN
CASE
WHEN sp.loginame LIKE @not_filter THEN 1
ELSE 0
END
WHEN 'host' THEN
CASE
WHEN sp.hostname LIKE @not_filter THEN 1
ELSE 0
END
WHEN 'database' THEN
CASE
WHEN DB_NAME(sp.dbid) LIKE @not_filter THEN 1
ELSE 0
END
ELSE 0
END
ELSE 0
END
AND
(
@show_own_spid = 1
OR sp.spid <> @@SPID
)
AND
(
@show_system_spids = 1
OR sp.hostprocess > ''
)
AND sp.ecid = 0
) AS s
INNER HASH JOIN
(
SELECT
x.resource_type,
x.database_name,
x.object_id,
x.file_id,
CASE
WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS'
WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM'
WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM'
WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM'
WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM'
WHEN x.page_no IS NOT NULL THEN '*'
ELSE NULL
END AS page_type,
x.hobt_id,
x.allocation_unit_id,
x.index_id,
x.schema_id,
x.principal_id,
x.request_mode,
x.request_status,
x.session_id,
x.request_id,
CASE
WHEN COALESCE(x.object_id, x.file_id, x.hobt_id, x.allocation_unit_id, x.index_id, x.schema_id, x.principal_id) IS NULL THEN NULLIF(resource_description, '')
ELSE NULL
END AS resource_description,
COUNT(*) AS request_count
FROM
(
SELECT
tl.resource_type +
CASE
WHEN tl.resource_subtype = '' THEN ''
ELSE '.' + tl.resource_subtype
END AS resource_type,
COALESCE(DB_NAME(tl.resource_database_id), N'(null)') AS database_name,
CONVERT
(
INT,
CASE
WHEN tl.resource_type = 'OBJECT' THEN tl.resource_associated_entity_id
WHEN tl.resource_description LIKE '%object_id = %' THEN
(
SUBSTRING
(
tl.resource_description,
(CHARINDEX('object_id = ', tl.resource_description) + 12),
COALESCE
(
NULLIF
(
CHARINDEX(',', tl.resource_description, CHARINDEX('object_id = ', tl.resource_description) + 12),
0
),
DATALENGTH(tl.resource_description)+1
) - (CHARINDEX('object_id = ', tl.resource_description) + 12)
)
)
ELSE NULL
END
) AS object_id,
CONVERT
(
INT,
CASE
WHEN tl.resource_type = 'FILE' THEN CONVERT(INT, tl.resource_description)
WHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN LEFT(tl.resource_description, CHARINDEX(':', tl.resource_description)-1)
ELSE NULL
END
) AS file_id,
CONVERT
(
INT,
CASE
WHEN tl.resource_type IN ('PAGE', 'EXTENT', 'RID') THEN
SUBSTRING
(
tl.resource_description,
CHARINDEX(':', tl.resource_description) + 1,
COALESCE
(
NULLIF
(
CHARINDEX(':', tl.resource_description, CHARINDEX(':', tl.resource_description) + 1),
0
),
DATALENGTH(tl.resource_description)+1
) - (CHARINDEX(':', tl.resource_description) + 1)
)
ELSE NULL
END
) AS page_no,
CASE
WHEN tl.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') THEN tl.resource_associated_entity_id
ELSE NULL
END AS hobt_id,
CASE
WHEN tl.resource_type = 'ALLOCATION_UNIT' THEN tl.resource_associated_entity_id
ELSE NULL
END AS allocation_unit_id,
CONVERT
(
INT,
CASE
WHEN
/*TODO: Deal with server principals*/
tl.resource_subtype <> 'SERVER_PRINCIPAL'
AND tl.resource_description LIKE '%index_id or stats_id = %' THEN
(
SUBSTRING
(
tl.resource_description,
(CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23),
COALESCE
(
NULLIF
(
CHARINDEX(',', tl.resource_description, CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23),
0
),
DATALENGTH(tl.resource_description)+1
) - (CHARINDEX('index_id or stats_id = ', tl.resource_description) + 23)
)
)
ELSE NULL
END
) AS index_id,
CONVERT
(
INT,
CASE
WHEN tl.resource_description LIKE '%schema_id = %' THEN
(
SUBSTRING
(
tl.resource_description,
(CHARINDEX('schema_id = ', tl.resource_description) + 12),
COALESCE
(
NULLIF
(
CHARINDEX(',', tl.resource_description, CHARINDEX('schema_id = ', tl.resource_description) + 12),
0
),
DATALENGTH(tl.resource_description)+1
) - (CHARINDEX('schema_id = ', tl.resource_description) + 12)
)
)
ELSE NULL
END
) AS schema_id,
CONVERT
(
INT,
CASE
WHEN tl.resource_description LIKE '%principal_id = %' THEN
(
SUBSTRING
(
tl.resource_description,
(CHARINDEX('principal_id = ', tl.resource_description) + 15),
COALESCE
(
NULLIF
(
CHARINDEX(',', tl.resource_description, CHARINDEX('principal_id = ', tl.resource_description) + 15),
0
),
DATALENGTH(tl.resource_description)+1
) - (CHARINDEX('principal_id = ', tl.resource_description) + 15)
)
)
ELSE NULL
END
) AS principal_id,
tl.request_mode,
tl.request_status,
tl.request_session_id AS session_id,
tl.request_request_id AS request_id,
/*TODO: Applocks, other resource_descriptions*/
RTRIM(tl.resource_description) AS resource_description,
tl.resource_associated_entity_id
/*********************************************/
FROM
(
SELECT
request_session_id,
CONVERT(VARCHAR(120), resource_type) COLLATE Latin1_General_Bin2 AS resource_type,
CONVERT(VARCHAR(120), resource_subtype) COLLATE Latin1_General_Bin2 AS resource_subtype,
resource_database_id,
CONVERT(VARCHAR(512), resource_description) COLLATE Latin1_General_Bin2 AS resource_description,
resource_associated_entity_id,
CONVERT(VARCHAR(120), request_mode) COLLATE Latin1_General_Bin2 AS request_mode,
CONVERT(VARCHAR(120), request_status) COLLATE Latin1_General_Bin2 AS request_status,
request_request_id
FROM sys.dm_tran_locks
) AS tl
) AS x
GROUP BY
x.resource_type,
x.database_name,
x.object_id,
x.file_id,
CASE
WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN 'PFS'
WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN 'GAM'
WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN 'SGAM'
WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN 'DCM'
WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN 'BCM'
WHEN x.page_no IS NOT NULL THEN '*'
ELSE NULL
END,
x.hobt_id,
x.allocation_unit_id,
x.index_id,
x.schema_id,
x.principal_id,
x.request_mode,
x.request_status,
x.session_id,
x.request_id,
CASE
WHEN COALESCE(x.object_id, x.file_id, x.hobt_id, x.allocation_unit_id, x.index_id, x.schema_id, x.principal_id) IS NULL THEN NULLIF(resource_description, '')
ELSE NULL
END
) AS y ON
y.session_id = s.session_id
AND y.request_id = s.request_id
OPTION (HASH GROUP);
--Disable unnecessary autostats on the table
CREATE STATISTICS s_database_name ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (database_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_object_id ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (object_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_hobt_id ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (hobt_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_allocation_unit_id ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (allocation_unit_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_index_id ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (index_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_schema_id ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (schema_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_principal_id ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (principal_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_request_id ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (request_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_start_time ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (start_time)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_resource_type ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (resource_type)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_object_name ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (object_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_schema_name ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (schema_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_page_type ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (page_type)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_request_mode ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (request_mode)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_request_status ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (request_status)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_resource_description ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (resource_description)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_index_name ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (index_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_principal_name ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (principal_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
END;
DECLARE
@sql VARCHAR(MAX),
@sql_n NVARCHAR(MAX);
SET @sql =
CONVERT(VARCHAR(MAX), '') +
'DECLARE @blocker BIT;
SET @blocker = 0;
DECLARE @i INT;
SET @i = 2147483647;
DECLARE @sessions TABLE
(
session_id SMALLINT NOT NULL,
request_id INT NOT NULL,
login_time DATETIME,
last_request_end_time DATETIME,
status VARCHAR(30),
statement_start_offset INT,
statement_end_offset INT,
sql_handle BINARY(20),
host_name NVARCHAR(128),
login_name NVARCHAR(128),
program_name NVARCHAR(128),
database_id SMALLINT,
memory_usage INT,
open_tran_count SMALLINT,
' +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
'wait_type NVARCHAR(32),
wait_resource NVARCHAR(256),
wait_time BIGINT,
'
ELSE
''
END +
'blocked SMALLINT,
is_user_process BIT,
cmd VARCHAR(32),
PRIMARY KEY CLUSTERED (session_id, request_id) WITH (IGNORE_DUP_KEY = ON)
);
DECLARE @blockers TABLE
(
session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON)
);
BLOCKERS:;
INSERT @sessions
(
session_id,
request_id,
login_time,
last_request_end_time,
status,
statement_start_offset,
statement_end_offset,
sql_handle,
host_name,
login_name,
program_name,
database_id,
memory_usage,
open_tran_count,
' +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
'wait_type,
wait_resource,
wait_time,
'
ELSE
''
END +
'blocked,
is_user_process,
cmd
)
SELECT TOP(@i)
spy.session_id,
spy.request_id,
spy.login_time,
spy.last_request_end_time,
spy.status,
spy.statement_start_offset,
spy.statement_end_offset,
spy.sql_handle,
spy.host_name,
spy.login_name,
spy.program_name,
spy.database_id,
spy.memory_usage,
spy.open_tran_count,
' +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
'spy.wait_type,
CASE
WHEN
spy.wait_type LIKE N''PAGE%LATCH_%''
OR spy.wait_type = N''CXPACKET''
OR spy.wait_type LIKE N''LATCH[_]%''
OR spy.wait_type = N''OLEDB'' THEN
spy.wait_resource
ELSE
NULL
END AS wait_resource,
spy.wait_time,
'
ELSE
''
END +
'spy.blocked,
spy.is_user_process,
spy.cmd
FROM
(
SELECT TOP(@i)
spx.*,
' +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
'ROW_NUMBER() OVER
(
PARTITION BY
spx.session_id,
spx.request_id
ORDER BY
CASE
WHEN spx.wait_type LIKE N''LCK[_]%'' THEN
1
ELSE
99
END,
spx.wait_time DESC,
spx.blocked DESC
) AS r
'
ELSE
'1 AS r
'
END +
'FROM
(
SELECT TOP(@i)
sp0.session_id,
sp0.request_id,
sp0.login_time,
sp0.last_request_end_time,
LOWER(sp0.status) AS status,
CASE
WHEN sp0.cmd = ''CREATE INDEX'' THEN
0
ELSE
sp0.stmt_start
END AS statement_start_offset,
CASE
WHEN sp0.cmd = N''CREATE INDEX'' THEN
-1
ELSE
COALESCE(NULLIF(sp0.stmt_end, 0), -1)
END AS statement_end_offset,
sp0.sql_handle,
sp0.host_name,
sp0.login_name,
sp0.program_name,
sp0.database_id,
sp0.memory_usage,
sp0.open_tran_count,
' +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
'CASE
WHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN
sp0.wait_type
ELSE
NULL
END AS wait_type,
CASE
WHEN sp0.wait_time > 0 AND sp0.wait_type <> N''CXPACKET'' THEN
sp0.wait_resource
ELSE
NULL
END AS wait_resource,
CASE
WHEN sp0.wait_type <> N''CXPACKET'' THEN
sp0.wait_time
ELSE
0
END AS wait_time,
'
ELSE
''
END +
'sp0.blocked,
sp0.is_user_process,
sp0.cmd
FROM
(
SELECT TOP(@i)
sp1.session_id,
sp1.request_id,
sp1.login_time,
sp1.last_request_end_time,
sp1.status,
sp1.cmd,
sp1.stmt_start,
sp1.stmt_end,
MAX(NULLIF(sp1.sql_handle, 0x00)) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS sql_handle,
sp1.host_name,
MAX(sp1.login_name) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS login_name,
sp1.program_name,
sp1.database_id,
MAX(sp1.memory_usage) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS memory_usage,
MAX(sp1.open_tran_count) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS open_tran_count,
sp1.wait_type,
sp1.wait_resource,
sp1.wait_time,
sp1.blocked,
sp1.hostprocess,
sp1.is_user_process
FROM
(
SELECT TOP(@i)
sp2.spid AS session_id,
CASE sp2.status
WHEN ''sleeping'' THEN
CONVERT(INT, 0)
ELSE
sp2.request_id
END AS request_id,
MAX(sp2.login_time) AS login_time,
MAX(sp2.last_batch) AS last_request_end_time,
MAX(CONVERT(VARCHAR(30), RTRIM(sp2.status)) COLLATE Latin1_General_Bin2) AS status,
MAX(CONVERT(VARCHAR(32), RTRIM(sp2.cmd)) COLLATE Latin1_General_Bin2) AS cmd,
MAX(sp2.stmt_start) AS stmt_start,
MAX(sp2.stmt_end) AS stmt_end,
MAX(sp2.sql_handle) AS sql_handle,
MAX(CONVERT(sysname, RTRIM(sp2.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS host_name,
MAX(CONVERT(sysname, RTRIM(sp2.loginame)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS login_name,
MAX
(
CASE
WHEN blk.queue_id IS NOT NULL THEN
N''Service Broker
database_id: '' + CONVERT(NVARCHAR, blk.database_id) +
N'' queue_id: '' + CONVERT(NVARCHAR, blk.queue_id)
ELSE
CONVERT
(
sysname,
RTRIM(sp2.program_name)
)
END COLLATE SQL_Latin1_General_CP1_CI_AS
) AS program_name,
MAX(sp2.dbid) AS database_id,
MAX(sp2.memusage) AS memory_usage,
MAX(sp2.open_tran) AS open_tran_count,
RTRIM(sp2.lastwaittype) AS wait_type,
RTRIM(sp2.waitresource) AS wait_resource,
MAX(sp2.waittime) AS wait_time,
COALESCE(NULLIF(sp2.blocked, sp2.spid), 0) AS blocked,
MAX
(
CASE
WHEN blk.session_id = sp2.spid THEN
''blocker''
ELSE
RTRIM(sp2.hostprocess)
END
) AS hostprocess,
CONVERT
(
BIT,
MAX
(
CASE
WHEN sp2.hostprocess > '''' THEN
1
ELSE
0
END
)
) AS is_user_process
FROM
(
SELECT TOP(@i)
session_id,
CONVERT(INT, NULL) AS queue_id,
CONVERT(INT, NULL) AS database_id
FROM @blockers
UNION ALL
SELECT TOP(@i)
CONVERT(SMALLINT, 0),
CONVERT(INT, NULL) AS queue_id,
CONVERT(INT, NULL) AS database_id
WHERE
@blocker = 0
UNION ALL
SELECT TOP(@i)
CONVERT(SMALLINT, spid),
queue_id,
database_id
FROM sys.dm_broker_activated_tasks
WHERE
@blocker = 0
) AS blk
INNER JOIN sys.sysprocesses AS sp2 ON
sp2.spid = blk.session_id
OR
(
blk.session_id = 0
AND @blocker = 0
)
' +
CASE
WHEN
(
@get_task_info = 0
AND @find_block_leaders = 0
) THEN
'WHERE
sp2.ecid = 0
'
ELSE
''
END +
'GROUP BY
sp2.spid,
CASE sp2.status
WHEN ''sleeping'' THEN
CONVERT(INT, 0)
ELSE
sp2.request_id
END,
RTRIM(sp2.lastwaittype),
RTRIM(sp2.waitresource),
COALESCE(NULLIF(sp2.blocked, sp2.spid), 0)
) AS sp1
) AS sp0
WHERE
@blocker = 1
OR
(1=1
' +
--inclusive filter
CASE
WHEN @filter <> '' THEN
CASE @filter_type
WHEN 'session' THEN
CASE
WHEN CONVERT(SMALLINT, @filter) <> 0 THEN
'AND sp0.session_id = CONVERT(SMALLINT, @filter)
'
ELSE
''
END
WHEN 'program' THEN
'AND sp0.program_name LIKE @filter
'
WHEN 'login' THEN
'AND sp0.login_name LIKE @filter
'
WHEN 'host' THEN
'AND sp0.host_name LIKE @filter
'
WHEN 'database' THEN
'AND DB_NAME(sp0.database_id) LIKE @filter
'
ELSE
''
END
ELSE
''
END +
--exclusive filter
CASE
WHEN @not_filter <> '' THEN
CASE @not_filter_type
WHEN 'session' THEN
CASE
WHEN CONVERT(SMALLINT, @not_filter) <> 0 THEN
'AND sp0.session_id <> CONVERT(SMALLINT, @not_filter)
'
ELSE
''
END
WHEN 'program' THEN
'AND sp0.program_name NOT LIKE @not_filter
'
WHEN 'login' THEN
'AND sp0.login_name NOT LIKE @not_filter
'
WHEN 'host' THEN
'AND sp0.host_name NOT LIKE @not_filter
'
WHEN 'database' THEN
'AND DB_NAME(sp0.database_id) NOT LIKE @not_filter
'
ELSE
''
END
ELSE
''
END +
CASE @show_own_spid
WHEN 1 THEN
''
ELSE
'AND sp0.session_id <> @@spid
'
END +
CASE
WHEN @show_system_spids = 0 THEN
'AND sp0.hostprocess > ''''
'
ELSE
''
END +
CASE @show_sleeping_spids
WHEN 0 THEN
'AND sp0.status <> ''sleeping''
'
WHEN 1 THEN
'AND
(
sp0.status <> ''sleeping''
OR sp0.open_tran_count > 0
)
'
ELSE
''
END +
')
) AS spx
) AS spy
WHERE
spy.r = 1;
' +
CASE @recursion
WHEN 1 THEN
'IF @@ROWCOUNT > 0
BEGIN;
INSERT @blockers
(
session_id
)
SELECT TOP(@i)
blocked
FROM @sessions
WHERE
NULLIF(blocked, 0) IS NOT NULL
EXCEPT
SELECT TOP(@i)
session_id
FROM @sessions;
' +
CASE
WHEN
(
@get_task_info > 0
OR @find_block_leaders = 1
) THEN
'IF @@ROWCOUNT > 0
BEGIN;
SET @blocker = 1;
GOTO BLOCKERS;
END;
'
ELSE
''
END +
'END;
'
ELSE
''
END +
'SELECT TOP(@i)
@recursion AS recursion,
x.session_id,
x.request_id,
DENSE_RANK() OVER
(
ORDER BY
x.session_id
) AS session_number,
' +
CASE
WHEN @output_column_list LIKE '%|[dd hh:mm:ss.mss|]%' ESCAPE '|' THEN
'x.elapsed_time '
ELSE
'0 '
END +
'AS elapsed_time,
' +
CASE
WHEN
(
@output_column_list LIKE '%|[dd hh:mm:ss.mss (avg)|]%' ESCAPE '|' OR
@output_column_list LIKE '%|[avg_elapsed_time|]%' ESCAPE '|'
)
AND @recursion = 1
THEN
'x.avg_elapsed_time / 1000 '
ELSE
'NULL '
END +
'AS avg_elapsed_time,
' +
CASE
WHEN
@output_column_list LIKE '%|[physical_io|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[physical_io_delta|]%' ESCAPE '|'
THEN
'x.physical_io '
ELSE
'NULL '
END +
'AS physical_io,
' +
CASE
WHEN
@output_column_list LIKE '%|[reads|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[reads_delta|]%' ESCAPE '|'
THEN
'x.reads '
ELSE
'0 '
END +
'AS reads,
' +
CASE
WHEN
@output_column_list LIKE '%|[physical_reads|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[physical_reads_delta|]%' ESCAPE '|'
THEN
'x.physical_reads '
ELSE
'0 '
END +
'AS physical_reads,
' +
CASE
WHEN
@output_column_list LIKE '%|[writes|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[writes_delta|]%' ESCAPE '|'
THEN
'x.writes '
ELSE
'0 '
END +
'AS writes,
' +
CASE
WHEN
@output_column_list LIKE '%|[tempdb_allocations|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[tempdb_allocations_delta|]%' ESCAPE '|'
THEN
'x.tempdb_allocations '
ELSE
'0 '
END +
'AS tempdb_allocations,
' +
CASE
WHEN
@output_column_list LIKE '%|[tempdb_current|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[tempdb_current_delta|]%' ESCAPE '|'
THEN
'x.tempdb_current '
ELSE
'0 '
END +
'AS tempdb_current,
' +
CASE
WHEN
@output_column_list LIKE '%|[CPU|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
THEN
'x.CPU '
ELSE
'0 '
END +
'AS CPU,
' +
CASE
WHEN
@output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
AND @get_task_info = 2
AND @sys_info = 1
THEN
'x.thread_CPU_snapshot '
ELSE
'0 '
END +
'AS thread_CPU_snapshot,
' +
CASE
WHEN
@output_column_list LIKE '%|[context_switches|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[context_switches_delta|]%' ESCAPE '|'
THEN
'x.context_switches '
ELSE
'NULL '
END +
'AS context_switches,
' +
CASE
WHEN
@output_column_list LIKE '%|[used_memory|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[used_memory_delta|]%' ESCAPE '|'
THEN
'x.used_memory '
ELSE
'0 '
END +
'AS used_memory,
' +
CASE
WHEN
@output_column_list LIKE '%|[tasks|]%' ESCAPE '|'
AND @recursion = 1
THEN
'x.tasks '
ELSE
'NULL '
END +
'AS tasks,
' +
CASE
WHEN
(
@output_column_list LIKE '%|[status|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'
)
AND @recursion = 1
THEN
'x.status '
ELSE
''''' '
END +
'AS status,
' +
CASE
WHEN
@output_column_list LIKE '%|[wait_info|]%' ESCAPE '|'
AND @recursion = 1
THEN
CASE @get_task_info
WHEN 2 THEN
'COALESCE(x.task_wait_info, x.sys_wait_info) '
ELSE
'x.sys_wait_info '
END
ELSE
'NULL '
END +
'AS wait_info,
' +
CASE
WHEN
(
@output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|'
)
AND @recursion = 1
THEN
'x.transaction_id '
ELSE
'NULL '
END +
'AS transaction_id,
' +
CASE
WHEN
@output_column_list LIKE '%|[open_tran_count|]%' ESCAPE '|'
AND @recursion = 1
THEN
'x.open_tran_count '
ELSE
'NULL '
END +
'AS open_tran_count,
' +
CASE
WHEN
@output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
AND @recursion = 1
THEN
'x.sql_handle '
ELSE
'NULL '
END +
'AS sql_handle,
' +
CASE
WHEN
(
@output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
)
AND @recursion = 1
THEN
'x.statement_start_offset '
ELSE
'NULL '
END +
'AS statement_start_offset,
' +
CASE
WHEN
(
@output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
)
AND @recursion = 1
THEN
'x.statement_end_offset '
ELSE
'NULL '
END +
'AS statement_end_offset,
' +
'NULL AS sql_text,
' +
CASE
WHEN
@output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
AND @recursion = 1
THEN
'x.plan_handle '
ELSE
'NULL '
END +
'AS plan_handle,
' +
CASE
WHEN
@output_column_list LIKE '%|[blocking_session_id|]%' ESCAPE '|'
AND @recursion = 1
THEN
'NULLIF(x.blocking_session_id, 0) '
ELSE
'NULL '
END +
'AS blocking_session_id,
' +
CASE
WHEN
@output_column_list LIKE '%|[percent_complete|]%' ESCAPE '|'
AND @recursion = 1
THEN
'x.percent_complete '
ELSE
'NULL '
END +
'AS percent_complete,
' +
CASE
WHEN
@output_column_list LIKE '%|[host_name|]%' ESCAPE '|'
AND @recursion = 1
THEN
'x.host_name '
ELSE
''''' '
END +
'AS host_name,
' +
CASE
WHEN
@output_column_list LIKE '%|[login_name|]%' ESCAPE '|'
AND @recursion = 1
THEN
'x.login_name '
ELSE
''''' '
END +
'AS login_name,
' +
CASE
WHEN
@output_column_list LIKE '%|[database_name|]%' ESCAPE '|'
AND @recursion = 1
THEN
'DB_NAME(x.database_id) '
ELSE
'NULL '
END +
'AS database_name,
' +
CASE
WHEN
@output_column_list LIKE '%|[program_name|]%' ESCAPE '|'
AND @recursion = 1
THEN
'x.program_name '
ELSE
''''' '
END +
'AS program_name,
' +
CASE
WHEN
@output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
AND @recursion = 1
THEN
'(
SELECT TOP(@i)
x.text_size,
x.language,
x.date_format,
x.date_first,
CASE x.quoted_identifier
WHEN 0 THEN ''OFF''
WHEN 1 THEN ''ON''
END AS quoted_identifier,
CASE x.arithabort
WHEN 0 THEN ''OFF''
WHEN 1 THEN ''ON''
END AS arithabort,
CASE x.ansi_null_dflt_on
WHEN 0 THEN ''OFF''
WHEN 1 THEN ''ON''
END AS ansi_null_dflt_on,
CASE x.ansi_defaults
WHEN 0 THEN ''OFF''
WHEN 1 THEN ''ON''
END AS ansi_defaults,
CASE x.ansi_warnings
WHEN 0 THEN ''OFF''
WHEN 1 THEN ''ON''
END AS ansi_warnings,
CASE x.ansi_padding
WHEN 0 THEN ''OFF''
WHEN 1 THEN ''ON''
END AS ansi_padding,
CASE ansi_nulls
WHEN 0 THEN ''OFF''
WHEN 1 THEN ''ON''
END AS ansi_nulls,
CASE x.concat_null_yields_null
WHEN 0 THEN ''OFF''
WHEN 1 THEN ''ON''
END AS concat_null_yields_null,
CASE x.transaction_isolation_level
WHEN 0 THEN ''Unspecified''
WHEN 1 THEN ''ReadUncomitted''
WHEN 2 THEN ''ReadCommitted''
WHEN 3 THEN ''Repeatable''
WHEN 4 THEN ''Serializable''
WHEN 5 THEN ''Snapshot''
END AS transaction_isolation_level,
x.lock_timeout,
x.deadlock_priority,
x.row_count,
x.command_type,
' +
CASE
WHEN OBJECT_ID('master.dbo.fn_varbintohexstr') IS NOT NULL THEN
'master.dbo.fn_varbintohexstr(x.sql_handle) AS sql_handle,
master.dbo.fn_varbintohexstr(x.plan_handle) AS plan_handle,'
ELSE
'CONVERT(VARCHAR(256), x.sql_handle, 1) AS sql_handle,
CONVERT(VARCHAR(256), x.plan_handle, 1) AS plan_handle,'
END +
'
x.statement_start_offset,
x.statement_end_offset,
' +
CASE
WHEN @output_column_list LIKE '%|[program_name|]%' ESCAPE '|' THEN
'(
SELECT TOP(1)
CONVERT(uniqueidentifier, CONVERT(XML, '''').value(''xs:hexBinary( substring(sql:column("agent_info.job_id_string"), 0) )'', ''binary(16)'')) AS job_id,
agent_info.step_id,
(
SELECT TOP(1)
NULL
FOR XML
PATH(''job_name''),
TYPE
),
(
SELECT TOP(1)
NULL
FOR XML
PATH(''step_name''),
TYPE
)
FROM
(
SELECT TOP(1)
SUBSTRING(x.program_name, CHARINDEX(''0x'', x.program_name) + 2, 32) AS job_id_string,
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
WHERE
x.program_name LIKE N''SQLAgent - TSQL JobStep (Job 0x%''
) AS agent_info
FOR XML
PATH(''agent_job_info''),
TYPE
),
'
ELSE ''
END +
CASE
WHEN @get_task_info = 2 THEN
'CONVERT(XML, x.block_info) AS block_info,
'
ELSE
''
END + '
x.host_process_id,
x.group_id
FOR XML
PATH(''additional_info''),
TYPE
) '
ELSE
'NULL '
END +
'AS additional_info,
x.start_time,
' +
CASE
WHEN
@output_column_list LIKE '%|[login_time|]%' ESCAPE '|'
AND @recursion = 1
THEN
'x.login_time '
ELSE
'NULL '
END +
'AS login_time,
x.last_request_start_time
FROM
(
SELECT TOP(@i)
y.*,
CASE
WHEN DATEDIFF(hour, y.start_time, GETDATE()) > 576 THEN
DATEDIFF(second, GETDATE(), y.start_time)
ELSE DATEDIFF(ms, y.start_time, GETDATE())
END AS elapsed_time,
COALESCE(tempdb_info.tempdb_allocations, 0) AS tempdb_allocations,
COALESCE
(
CASE
WHEN tempdb_info.tempdb_current < 0 THEN 0
ELSE tempdb_info.tempdb_current
END,
0
) AS tempdb_current,
' +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
'N''('' + CONVERT(NVARCHAR, y.wait_duration_ms) + N''ms)'' +
y.wait_type +
CASE
WHEN y.wait_type LIKE N''PAGE%LATCH_%'' THEN
N'':'' +
COALESCE(DB_NAME(CONVERT(INT, LEFT(y.resource_description, CHARINDEX(N'':'', y.resource_description) - 1))), N''(null)'') +
N'':'' +
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)) +
N''('' +
CASE
WHEN
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 1 OR
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 8088 = 0
THEN
N''PFS''
WHEN
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 2 OR
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) % 511232 = 0
THEN
N''GAM''
WHEN
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 3 OR
(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 1) % 511232 = 0
THEN
N''SGAM''
WHEN
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 6 OR
(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 6) % 511232 = 0
THEN
N''DCM''
WHEN
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) = 7 OR
(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N'':'', REVERSE(y.resource_description)) - 1)) - 7) % 511232 = 0
THEN
N''BCM''
ELSE
N''*''
END +
N'')''
WHEN y.wait_type = N''CXPACKET'' THEN
N'':'' + SUBSTRING(y.resource_description, CHARINDEX(N''nodeId'', y.resource_description) + 7, 4)
WHEN y.wait_type LIKE N''LATCH[_]%'' THEN
N'' ['' + LEFT(y.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', y.resource_description), 0), LEN(y.resource_description) + 1) - 1) + N'']''
WHEN
y.wait_type = N''OLEDB''
AND y.resource_description LIKE N''%(SPID=%)'' THEN
N''['' + LEFT(y.resource_description, CHARINDEX(N''(SPID='', y.resource_description) - 2) +
N'':'' + SUBSTRING(y.resource_description, CHARINDEX(N''(SPID='', y.resource_description) + 6, CHARINDEX(N'')'', y.resource_description, (CHARINDEX(N''(SPID='', y.resource_description) + 6)) - (CHARINDEX(N''(SPID='', y.resource_description) + 6)) + '']''
ELSE
N''''
END COLLATE Latin1_General_Bin2 AS sys_wait_info,
'
ELSE
''
END +
CASE
WHEN @get_task_info = 2 THEN
'tasks.physical_io,
tasks.context_switches,
tasks.tasks,
tasks.block_info,
tasks.wait_info AS task_wait_info,
tasks.thread_CPU_snapshot,
'
ELSE
''
END +
CASE
WHEN NOT (@get_avg_time = 1 AND @recursion = 1) THEN
'CONVERT(INT, NULL) '
ELSE
'qs.total_elapsed_time / qs.execution_count '
END +
'AS avg_elapsed_time
FROM
(
SELECT TOP(@i)
sp.session_id,
sp.request_id,
COALESCE(r.logical_reads, s.logical_reads) AS reads,
COALESCE(r.reads, s.reads) AS physical_reads,
COALESCE(r.writes, s.writes) AS writes,
COALESCE(r.CPU_time, s.CPU_time) AS CPU,
sp.memory_usage + COALESCE(r.granted_query_memory, 0) AS used_memory,
LOWER(sp.status) AS status,
COALESCE(r.sql_handle, sp.sql_handle) AS sql_handle,
COALESCE(r.statement_start_offset, sp.statement_start_offset) AS statement_start_offset,
COALESCE(r.statement_end_offset, sp.statement_end_offset) AS statement_end_offset,
' +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
'sp.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
sp.wait_resource COLLATE Latin1_General_Bin2 AS resource_description,
sp.wait_time AS wait_duration_ms,
'
ELSE
''
END +
'NULLIF(sp.blocked, 0) AS blocking_session_id,
r.plan_handle,
NULLIF(r.percent_complete, 0) AS percent_complete,
sp.host_name,
sp.login_name,
sp.program_name,
s.host_process_id,
COALESCE(r.text_size, s.text_size) AS text_size,
COALESCE(r.language, s.language) AS language,
COALESCE(r.date_format, s.date_format) AS date_format,
COALESCE(r.date_first, s.date_first) AS date_first,
COALESCE(r.quoted_identifier, s.quoted_identifier) AS quoted_identifier,
COALESCE(r.arithabort, s.arithabort) AS arithabort,
COALESCE(r.ansi_null_dflt_on, s.ansi_null_dflt_on) AS ansi_null_dflt_on,
COALESCE(r.ansi_defaults, s.ansi_defaults) AS ansi_defaults,
COALESCE(r.ansi_warnings, s.ansi_warnings) AS ansi_warnings,
COALESCE(r.ansi_padding, s.ansi_padding) AS ansi_padding,
COALESCE(r.ansi_nulls, s.ansi_nulls) AS ansi_nulls,
COALESCE(r.concat_null_yields_null, s.concat_null_yields_null) AS concat_null_yields_null,
COALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level,
COALESCE(r.lock_timeout, s.lock_timeout) AS lock_timeout,
COALESCE(r.deadlock_priority, s.deadlock_priority) AS deadlock_priority,
COALESCE(r.row_count, s.row_count) AS row_count,
COALESCE(r.command, sp.cmd) AS command_type,
COALESCE
(
CASE
WHEN
(
s.is_user_process = 0
AND r.total_elapsed_time >= 0
) THEN
DATEADD
(
ms,
1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
)
END,
NULLIF(COALESCE(r.start_time, sp.last_request_end_time), CONVERT(DATETIME, ''19000101'', 112)),
sp.login_time
) AS start_time,
sp.login_time,
CASE
WHEN s.is_user_process = 1 THEN
s.last_request_start_time
ELSE
COALESCE
(
DATEADD
(
ms,
1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
),
s.last_request_start_time
)
END AS last_request_start_time,
r.transaction_id,
sp.database_id,
sp.open_tran_count,
' +
CASE
WHEN EXISTS
(
SELECT
*
FROM sys.all_columns AS ac
WHERE
ac.object_id = OBJECT_ID('sys.dm_exec_sessions')
AND ac.name = 'group_id'
)
THEN 's.group_id'
ELSE 'CONVERT(INT, NULL) AS group_id'
END + '
FROM @sessions AS sp
LEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON
s.session_id = sp.session_id
AND s.login_time = sp.login_time
LEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON
sp.status <> ''sleeping''
AND r.session_id = sp.session_id
AND r.request_id = sp.request_id
AND
(
(
s.is_user_process = 0
AND sp.is_user_process = 0
)
OR
(
r.start_time = s.last_request_start_time
AND s.last_request_end_time <= sp.last_request_end_time
)
)
) AS y
' +
CASE
WHEN @get_task_info = 2 THEN
CONVERT(VARCHAR(MAX), '') +
'LEFT OUTER HASH JOIN
(
SELECT TOP(@i)
task_nodes.task_node.value(''(session_id/text())[1]'', ''SMALLINT'') AS session_id,
task_nodes.task_node.value(''(request_id/text())[1]'', ''INT'') AS request_id,
task_nodes.task_node.value(''(physical_io/text())[1]'', ''BIGINT'') AS physical_io,
task_nodes.task_node.value(''(context_switches/text())[1]'', ''BIGINT'') AS context_switches,
task_nodes.task_node.value(''(tasks/text())[1]'', ''INT'') AS tasks,
task_nodes.task_node.value(''(block_info/text())[1]'', ''NVARCHAR(4000)'') AS block_info,
task_nodes.task_node.value(''(waits/text())[1]'', ''NVARCHAR(4000)'') AS wait_info,
task_nodes.task_node.value(''(thread_CPU_snapshot/text())[1]'', ''BIGINT'') AS thread_CPU_snapshot
FROM
(
SELECT TOP(@i)
CONVERT
(
XML,
REPLACE
(
CONVERT(NVARCHAR(MAX), tasks_raw.task_xml_raw) COLLATE Latin1_General_Bin2,
N''</waits></tasks><tasks><waits>'',
N'', ''
)
) AS task_xml
FROM
(
SELECT TOP(@i)
CASE waits.r
WHEN 1 THEN
waits.session_id
ELSE
NULL
END AS [session_id],
CASE waits.r
WHEN 1 THEN
waits.request_id
ELSE
NULL
END AS [request_id],
CASE waits.r
WHEN 1 THEN
waits.physical_io
ELSE
NULL
END AS [physical_io],
CASE waits.r
WHEN 1 THEN
waits.context_switches
ELSE
NULL
END AS [context_switches],
CASE waits.r
WHEN 1 THEN
waits.thread_CPU_snapshot
ELSE
NULL
END AS [thread_CPU_snapshot],
CASE waits.r
WHEN 1 THEN
waits.tasks
ELSE
NULL
END AS [tasks],
CASE waits.r
WHEN 1 THEN
waits.block_info
ELSE
NULL
END AS [block_info],
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
N''('' +
CONVERT(NVARCHAR, num_waits) + N''x: '' +
CASE num_waits
WHEN 1 THEN
CONVERT(NVARCHAR, min_wait_time) + N''ms''
WHEN 2 THEN
CASE
WHEN min_wait_time <> max_wait_time THEN
CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''
ELSE
CONVERT(NVARCHAR, max_wait_time) + N''ms''
END
ELSE
CASE
WHEN min_wait_time <> max_wait_time THEN
CONVERT(NVARCHAR, min_wait_time) + N''/'' + CONVERT(NVARCHAR, avg_wait_time) + N''/'' + CONVERT(NVARCHAR, max_wait_time) + N''ms''
ELSE
CONVERT(NVARCHAR, max_wait_time) + N''ms''
END
END +
N'')'' + wait_type COLLATE Latin1_General_Bin2
),
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''?''),
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''?''),
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''?''),
NCHAR(0),
N''''
) AS [waits]
FROM
(
SELECT TOP(@i)
w1.*,
ROW_NUMBER() OVER
(
PARTITION BY
w1.session_id,
w1.request_id
ORDER BY
w1.block_info DESC,
w1.num_waits DESC,
w1.wait_type
) AS r
FROM
(
SELECT TOP(@i)
task_info.session_id,
task_info.request_id,
task_info.physical_io,
task_info.context_switches,
task_info.thread_CPU_snapshot,
task_info.num_tasks AS tasks,
CASE
WHEN task_info.runnable_time IS NOT NULL THEN
''RUNNABLE''
ELSE
wt2.wait_type
END AS wait_type,
NULLIF(COUNT(COALESCE(task_info.runnable_time, wt2.waiting_task_address)), 0) AS num_waits,
MIN(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS min_wait_time,
AVG(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS avg_wait_time,
MAX(COALESCE(task_info.runnable_time, wt2.wait_duration_ms)) AS max_wait_time,
MAX(wt2.block_info) AS block_info
FROM
(
SELECT TOP(@i)
t.session_id,
t.request_id,
SUM(CONVERT(BIGINT, t.pending_io_count)) OVER (PARTITION BY t.session_id, t.request_id) AS physical_io,
SUM(CONVERT(BIGINT, t.context_switches_count)) OVER (PARTITION BY t.session_id, t.request_id) AS context_switches,
' +
CASE
WHEN
@output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
AND @sys_info = 1
THEN
'SUM(tr.usermode_time + tr.kernel_time) OVER (PARTITION BY t.session_id, t.request_id) '
ELSE
'CONVERT(BIGINT, NULL) '
END +
' AS thread_CPU_snapshot,
COUNT(*) OVER (PARTITION BY t.session_id, t.request_id) AS num_tasks,
t.task_address,
t.task_state,
CASE
WHEN
t.task_state = ''RUNNABLE''
AND w.runnable_time > 0 THEN
w.runnable_time
ELSE
NULL
END AS runnable_time
FROM sys.dm_os_tasks AS t
CROSS APPLY
(
SELECT TOP(1)
sp2.session_id
FROM @sessions AS sp2
WHERE
sp2.session_id = t.session_id
AND sp2.request_id = t.request_id
AND sp2.status <> ''sleeping''
) AS sp20
LEFT OUTER HASH JOIN
(
' +
CASE
WHEN @sys_info = 1 THEN
'SELECT TOP(@i)
(
SELECT TOP(@i)
ms_ticks
FROM sys.dm_os_sys_info
) -
w0.wait_resumed_ms_ticks AS runnable_time,
w0.worker_address,
w0.thread_address,
w0.task_bound_ms_ticks
FROM sys.dm_os_workers AS w0
WHERE
w0.state = ''RUNNABLE''
OR @first_collection_ms_ticks >= w0.task_bound_ms_ticks'
ELSE
'SELECT
CONVERT(BIGINT, NULL) AS runnable_time,
CONVERT(VARBINARY(8), NULL) AS worker_address,
CONVERT(VARBINARY(8), NULL) AS thread_address,
CONVERT(BIGINT, NULL) AS task_bound_ms_ticks
WHERE
1 = 0'
END +
'
) AS w ON
w.worker_address = t.worker_address
' +
CASE
WHEN
@output_column_list LIKE '%|[CPU_delta|]%' ESCAPE '|'
AND @sys_info = 1
THEN
'LEFT OUTER HASH JOIN sys.dm_os_threads AS tr ON
tr.thread_address = w.thread_address
AND @first_collection_ms_ticks >= w.task_bound_ms_ticks
'
ELSE
''
END +
') AS task_info
LEFT OUTER HASH JOIN
(
SELECT TOP(@i)
wt1.wait_type,
wt1.waiting_task_address,
MAX(wt1.wait_duration_ms) AS wait_duration_ms,
MAX(wt1.block_info) AS block_info
FROM
(
SELECT DISTINCT TOP(@i)
wt.wait_type +
CASE
WHEN wt.wait_type LIKE N''PAGE%LATCH_%'' THEN
'':'' +
COALESCE(DB_NAME(CONVERT(INT, LEFT(wt.resource_description, CHARINDEX(N'':'', wt.resource_description) - 1))), N''(null)'') +
N'':'' +
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)) +
N''('' +
CASE
WHEN
CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 1 OR
CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 8088 = 0
THEN
N''PFS''
WHEN
CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 2 OR
CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) % 511232 = 0
THEN
N''GAM''
WHEN
CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 3 OR
(CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 1) % 511232 = 0
THEN
N''SGAM''
WHEN
CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 6 OR
(CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 6) % 511232 = 0
THEN
N''DCM''
WHEN
CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) = 7 OR
(CONVERT(INT, RIGHT(wt.resource_description, CHARINDEX(N'':'', REVERSE(wt.resource_description)) - 1)) - 7) % 511232 = 0
THEN
N''BCM''
ELSE
N''*''
END +
N'')''
WHEN wt.wait_type = N''CXPACKET'' THEN
N'':'' + SUBSTRING(wt.resource_description, CHARINDEX(N''nodeId'', wt.resource_description) + 7, 4)
WHEN wt.wait_type LIKE N''LATCH[_]%'' THEN
N'' ['' + LEFT(wt.resource_description, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description), 0), LEN(wt.resource_description) + 1) - 1) + N'']''
ELSE
N''''
END COLLATE Latin1_General_Bin2 AS wait_type,
CASE
WHEN
(
wt.blocking_session_id IS NOT NULL
AND wt.wait_type LIKE N''LCK[_]%''
) THEN
(
SELECT TOP(@i)
x.lock_type,
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
DB_NAME
(
CONVERT
(
INT,
SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''dbid='', wt.resource_description), 0) + 5, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''dbid='', wt.resource_description) + 5), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''dbid='', wt.resource_description) - 5)
)
),
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''?''),
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''?''),
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''?''),
NCHAR(0),
N''''
) AS database_name,
CASE x.lock_type
WHEN N''objectlock'' THEN
SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''objid='', wt.resource_description), 0) + 6, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''objid='', wt.resource_description) + 6), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''objid='', wt.resource_description) - 6)
ELSE
NULL
END AS object_id,
CASE x.lock_type
WHEN N''filelock'' THEN
SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''fileid='', wt.resource_description), 0) + 7, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''fileid='', wt.resource_description) + 7), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''fileid='', wt.resource_description) - 7)
ELSE
NULL
END AS file_id,
CASE
WHEN x.lock_type in (N''pagelock'', N''extentlock'', N''ridlock'') THEN
SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''associatedObjectId='', wt.resource_description), 0) + 19, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''associatedObjectId='', wt.resource_description) + 19), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''associatedObjectId='', wt.resource_description) - 19)
WHEN x.lock_type in (N''keylock'', N''hobtlock'', N''allocunitlock'') THEN
SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''hobtid='', wt.resource_description), 0) + 7, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''hobtid='', wt.resource_description) + 7), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''hobtid='', wt.resource_description) - 7)
ELSE
NULL
END AS hobt_id,
CASE x.lock_type
WHEN N''applicationlock'' THEN
SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''hash='', wt.resource_description), 0) + 5, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''hash='', wt.resource_description) + 5), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''hash='', wt.resource_description) - 5)
ELSE
NULL
END AS applock_hash,
CASE x.lock_type
WHEN N''metadatalock'' THEN
SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''subresource='', wt.resource_description), 0) + 12, COALESCE(NULLIF(CHARINDEX(N'' '', wt.resource_description, CHARINDEX(N''subresource='', wt.resource_description) + 12), 0), LEN(wt.resource_description) + 1) - CHARINDEX(N''subresource='', wt.resource_description) - 12)
ELSE
NULL
END AS metadata_resource,
CASE x.lock_type
WHEN N''metadatalock'' THEN
SUBSTRING(wt.resource_description, NULLIF(CHARINDEX(N''classid='', wt.resource_description), 0) + 8, COALESCE(NULLIF(CHARINDEX(N'' dbid='', wt.resource_description) - CHARINDEX(N''classid='', wt.resource_description), 0), LEN(wt.resource_description) + 1) - 8)
ELSE
NULL
END AS metadata_class_id
FROM
(
SELECT TOP(1)
LEFT(wt.resource_description, CHARINDEX(N'' '', wt.resource_description) - 1) COLLATE Latin1_General_Bin2 AS lock_type
) AS x
FOR XML
PATH('''')
)
ELSE NULL
END AS block_info,
wt.wait_duration_ms,
wt.waiting_task_address
FROM
(
SELECT TOP(@i)
wt0.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
wt0.resource_description COLLATE Latin1_General_Bin2 AS resource_description,
wt0.wait_duration_ms,
wt0.waiting_task_address,
CASE
WHEN wt0.blocking_session_id = p.blocked THEN
wt0.blocking_session_id
ELSE
NULL
END AS blocking_session_id
FROM sys.dm_os_waiting_tasks AS wt0
CROSS APPLY
(
SELECT TOP(1)
s0.blocked
FROM @sessions AS s0
WHERE
s0.session_id = wt0.session_id
AND COALESCE(s0.wait_type, N'''') <> N''OLEDB''
AND wt0.wait_type <> N''OLEDB''
) AS p
) AS wt
) AS wt1
GROUP BY
wt1.wait_type,
wt1.waiting_task_address
) AS wt2 ON
wt2.waiting_task_address = task_info.task_address
AND wt2.wait_duration_ms > 0
AND task_info.runnable_time IS NULL
GROUP BY
task_info.session_id,
task_info.request_id,
task_info.physical_io,
task_info.context_switches,
task_info.thread_CPU_snapshot,
task_info.num_tasks,
CASE
WHEN task_info.runnable_time IS NOT NULL THEN
''RUNNABLE''
ELSE
wt2.wait_type
END
) AS w1
) AS waits
ORDER BY
waits.session_id,
waits.request_id,
waits.r
FOR XML
PATH(N''tasks''),
TYPE
) AS tasks_raw (task_xml_raw)
) AS tasks_final
CROSS APPLY tasks_final.task_xml.nodes(N''/tasks'') AS task_nodes (task_node)
WHERE
task_nodes.task_node.exist(N''session_id'') = 1
) AS tasks ON
tasks.session_id = y.session_id
AND tasks.request_id = y.request_id
'
ELSE
''
END +
'LEFT OUTER HASH JOIN
(
SELECT TOP(@i)
t_info.session_id,
COALESCE(t_info.request_id, -1) AS request_id,
SUM(t_info.tempdb_allocations) AS tempdb_allocations,
SUM(t_info.tempdb_current) AS tempdb_current
FROM
(
SELECT TOP(@i)
tsu.session_id,
tsu.request_id,
tsu.user_objects_alloc_page_count +
tsu.internal_objects_alloc_page_count AS tempdb_allocations,
tsu.user_objects_alloc_page_count +
tsu.internal_objects_alloc_page_count -
tsu.user_objects_dealloc_page_count -
tsu.internal_objects_dealloc_page_count AS tempdb_current
FROM sys.dm_db_task_space_usage AS tsu
CROSS APPLY
(
SELECT TOP(1)
s0.session_id
FROM @sessions AS s0
WHERE
s0.session_id = tsu.session_id
) AS p
UNION ALL
SELECT TOP(@i)
ssu.session_id,
NULL AS request_id,
ssu.user_objects_alloc_page_count +
ssu.internal_objects_alloc_page_count AS tempdb_allocations,
ssu.user_objects_alloc_page_count +
ssu.internal_objects_alloc_page_count -
ssu.user_objects_dealloc_page_count -
ssu.internal_objects_dealloc_page_count AS tempdb_current
FROM sys.dm_db_session_space_usage AS ssu
CROSS APPLY
(
SELECT TOP(1)
s0.session_id
FROM @sessions AS s0
WHERE
s0.session_id = ssu.session_id
) AS p
) AS t_info
GROUP BY
t_info.session_id,
COALESCE(t_info.request_id, -1)
) AS tempdb_info ON
tempdb_info.session_id = y.session_id
AND tempdb_info.request_id =
CASE
WHEN y.status = N''sleeping'' THEN
-1
ELSE
y.request_id
END
' +
CASE
WHEN
NOT
(
@get_avg_time = 1
AND @recursion = 1
) THEN
''
ELSE
'LEFT OUTER HASH JOIN
(
SELECT TOP(@i)
*
FROM sys.dm_exec_query_stats
) AS qs ON
qs.sql_handle = y.sql_handle
AND qs.plan_handle = y.plan_handle
AND qs.statement_start_offset = y.statement_start_offset
AND qs.statement_end_offset = y.statement_end_offset
'
END +
') AS x
OPTION (KEEPFIXED PLAN, OPTIMIZE FOR (@i = 1)); ';
SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);
SET @last_collection_start = GETDATE();
IF
@recursion = -1
AND @sys_info = 1
BEGIN;
SELECT
@first_collection_ms_ticks = ms_ticks
FROM sys.dm_os_sys_info;
END;
INSERT [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions)
(
recursion,
session_id,
request_id,
session_number,
elapsed_time,
avg_elapsed_time,
physical_io,
reads,
physical_reads,
writes,
tempdb_allocations,
tempdb_current,
CPU,
thread_CPU_snapshot,
context_switches,
used_memory,
tasks,
status,
wait_info,
transaction_id,
open_tran_count,
sql_handle,
statement_start_offset,
statement_end_offset,
sql_text,
plan_handle,
blocking_session_id,
percent_complete,
host_name,
login_name,
database_name,
program_name,
additional_info,
start_time,
login_time,
last_request_start_time
)
EXEC sp_executesql
@sql_n,
N'@recursion SMALLINT, @filter sysname, @not_filter sysname, @first_collection_ms_ticks BIGINT',
@recursion, @filter, @not_filter, @first_collection_ms_ticks;
--Collect transaction information?
IF
@recursion = 1
AND
(
@output_column_list LIKE '%|[tran_start_time|]%' ESCAPE '|'
OR @output_column_list LIKE '%|[tran_log_writes|]%' ESCAPE '|'
)
BEGIN;
DECLARE @i INT;
SET @i = 2147483647;
UPDATE s
SET
tran_start_time =
CONVERT
(
DATETIME,
LEFT
(
x.trans_info,
NULLIF(CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info) - 1, -1)
),
121
),
tran_log_writes =
RIGHT
(
x.trans_info,
LEN(x.trans_info) - CHARINDEX(NCHAR(254) COLLATE Latin1_General_Bin2, x.trans_info)
)
FROM
(
SELECT TOP(@i)
trans_nodes.trans_node.value('(session_id/text())[1]', 'SMALLINT') AS session_id,
COALESCE(trans_nodes.trans_node.value('(request_id/text())[1]', 'INT'), 0) AS request_id,
trans_nodes.trans_node.value('(trans_info/text())[1]', 'NVARCHAR(4000)') AS trans_info
FROM
(
SELECT TOP(@i)
CONVERT
(
XML,
REPLACE
(
CONVERT(NVARCHAR(MAX), trans_raw.trans_xml_raw) COLLATE Latin1_General_Bin2,
N'</trans_info></trans><trans><trans_info>', N''
)
)
FROM
(
SELECT TOP(@i)
CASE u_trans.r
WHEN 1 THEN u_trans.session_id
ELSE NULL
END AS [session_id],
CASE u_trans.r
WHEN 1 THEN u_trans.request_id
ELSE NULL
END AS [request_id],
CONVERT
(
NVARCHAR(MAX),
CASE
WHEN u_trans.database_id IS NOT NULL THEN
CASE u_trans.r
WHEN 1 THEN COALESCE(CONVERT(NVARCHAR, u_trans.transaction_start_time, 121) + NCHAR(254), N'')
ELSE N''
END +
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT(VARCHAR(128), COALESCE(DB_NAME(u_trans.database_id), N'(null)')),
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'?'),
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'?'),
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'?'),
NCHAR(0),
N'?'
) +
N': ' +
CONVERT(NVARCHAR, u_trans.log_record_count) + N' (' + CONVERT(NVARCHAR, u_trans.log_kb_used) + N' kB)' +
N','
ELSE
N'N/A,'
END COLLATE Latin1_General_Bin2
) AS [trans_info]
FROM
(
SELECT TOP(@i)
trans.*,
ROW_NUMBER() OVER
(
PARTITION BY
trans.session_id,
trans.request_id
ORDER BY
trans.transaction_start_time DESC
) AS r
FROM
(
SELECT TOP(@i)
session_tran_map.session_id,
session_tran_map.request_id,
s_tran.database_id,
COALESCE(SUM(s_tran.database_transaction_log_record_count), 0) AS log_record_count,
COALESCE(SUM(s_tran.database_transaction_log_bytes_used), 0) / 1024 AS log_kb_used,
MIN(s_tran.database_transaction_begin_time) AS transaction_start_time
FROM
(
SELECT TOP(@i)
*
FROM sys.dm_tran_active_transactions
WHERE
transaction_begin_time <= @last_collection_start
) AS a_tran
INNER HASH JOIN
(
SELECT TOP(@i)
*
FROM sys.dm_tran_database_transactions
WHERE
database_id < 32767
) AS s_tran ON
s_tran.transaction_id = a_tran.transaction_id
LEFT OUTER HASH JOIN
(
SELECT TOP(@i)
*
FROM sys.dm_tran_session_transactions
) AS tst ON
s_tran.transaction_id = tst.transaction_id
CROSS APPLY
(
SELECT TOP(1)
s3.session_id,
s3.request_id
FROM
(
SELECT TOP(1)
s1.session_id,
s1.request_id
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s1
WHERE
s1.transaction_id = s_tran.transaction_id
AND s1.recursion = 1
UNION ALL
SELECT TOP(1)
s2.session_id,
s2.request_id
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s2
WHERE
s2.session_id = tst.session_id
AND s2.recursion = 1
) AS s3
ORDER BY
s3.request_id
) AS session_tran_map
GROUP BY
session_tran_map.session_id,
session_tran_map.request_id,
s_tran.database_id
) AS trans
) AS u_trans
FOR XML
PATH('trans'),
TYPE
) AS trans_raw (trans_xml_raw)
) AS trans_final (trans_xml)
CROSS APPLY trans_final.trans_xml.nodes('/trans') AS trans_nodes (trans_node)
) AS x
INNER HASH JOIN [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s ON
s.session_id = x.session_id
AND s.request_id = x.request_id
OPTION (OPTIMIZE FOR (@i = 1));
END;
--Variables for text and plan collection
DECLARE
@session_id SMALLINT,
@request_id INT,
@sql_handle VARBINARY(64),
@plan_handle VARBINARY(64),
@statement_start_offset INT,
@statement_end_offset INT,
@start_time DATETIME,
@database_name sysname;
IF
@recursion = 1
AND @output_column_list LIKE '%|[sql_text|]%' ESCAPE '|'
BEGIN;
DECLARE sql_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT
session_id,
request_id,
sql_handle,
statement_start_offset,
statement_end_offset
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions)
WHERE
recursion = 1
AND sql_handle IS NOT NULL
OPTION (KEEPFIXED PLAN);
OPEN sql_cursor;
FETCH NEXT FROM sql_cursor
INTO
@session_id,
@request_id,
@sql_handle,
@statement_start_offset,
@statement_end_offset;
--Wait up to 5 ms for the SQL text, then give up
SET LOCK_TIMEOUT 5;
WHILE @@FETCH_STATUS = 0
BEGIN;
BEGIN TRY;
UPDATE s
SET
s.sql_text =
(
SELECT
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
N'--' + NCHAR(13) + NCHAR(10) +
CASE
WHEN @get_full_inner_text = 1 THEN est.text
WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN est.text
WHEN SUBSTRING(est.text, (@statement_start_offset/2), 2) LIKE N'[a-zA-Z0-9][a-zA-Z0-9]' THEN est.text
ELSE
CASE
WHEN @statement_start_offset > 0 THEN
SUBSTRING
(
est.text,
((@statement_start_offset/2) + 1),
(
CASE
WHEN @statement_end_offset = -1 THEN 2147483647
ELSE ((@statement_end_offset - @statement_start_offset)/2) + 1
END
)
)
ELSE RTRIM(LTRIM(est.text))
END
END +
NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2,
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'?'),
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'?'),
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'?'),
NCHAR(0),
N''
) AS [processing-instruction(query)]
FOR XML
PATH(''),
TYPE
),
s.statement_start_offset =
CASE
WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN 0
WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN 0
ELSE @statement_start_offset
END,
s.statement_end_offset =
CASE
WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN -1
WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN -1
ELSE @statement_end_offset
END
FROM
[#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s,
(
SELECT TOP(1)
text
FROM
(
SELECT
text,
0 AS row_num
FROM sys.dm_exec_sql_text(@sql_handle)
UNION ALL
SELECT
NULL,
1 AS row_num
) AS est0
ORDER BY
row_num
) AS est
WHERE
s.session_id = @session_id
AND s.request_id = @request_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END TRY
BEGIN CATCH;
UPDATE s
SET
s.sql_text =
CASE ERROR_NUMBER()
WHEN 1222 THEN '<timeout_exceeded />'
ELSE '<error message="' + ERROR_MESSAGE() + '" />'
END
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
WHERE
s.session_id = @session_id
AND s.request_id = @request_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END CATCH;
FETCH NEXT FROM sql_cursor
INTO
@session_id,
@request_id,
@sql_handle,
@statement_start_offset,
@statement_end_offset;
END;
--Return this to the default
SET LOCK_TIMEOUT -1;
CLOSE sql_cursor;
DEALLOCATE sql_cursor;
END;
IF
@get_outer_command = 1
AND @recursion = 1
AND @output_column_list LIKE '%|[sql_command|]%' ESCAPE '|'
BEGIN;
DECLARE @buffer_results TABLE
(
EventType VARCHAR(30),
Parameters INT,
EventInfo NVARCHAR(4000),
start_time DATETIME,
session_number INT IDENTITY(1,1) NOT NULL PRIMARY KEY
);
DECLARE buffer_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT
session_id,
MAX(start_time) AS start_time
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions)
WHERE
recursion = 1
GROUP BY
session_id
ORDER BY
session_id
OPTION (KEEPFIXED PLAN);
OPEN buffer_cursor;
FETCH NEXT FROM buffer_cursor
INTO
@session_id,
@start_time;
WHILE @@FETCH_STATUS = 0
BEGIN;
BEGIN TRY;
--In SQL Server 2008, DBCC INPUTBUFFER will throw
--an exception if the session no longer exists
INSERT @buffer_results
(
EventType,
Parameters,
EventInfo
)
EXEC sp_executesql
N'DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;',
N'@session_id SMALLINT',
@session_id;
UPDATE br
SET
br.start_time = @start_time
FROM @buffer_results AS br
WHERE
br.session_number =
(
SELECT MAX(br2.session_number)
FROM @buffer_results br2
);
END TRY
BEGIN CATCH
END CATCH;
FETCH NEXT FROM buffer_cursor
INTO
@session_id,
@start_time;
END;
UPDATE s
SET
sql_command =
(
SELECT
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
N'--' + NCHAR(13) + NCHAR(10) + br.EventInfo + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2
),
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'?'),
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'?'),
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'?'),
NCHAR(0),
N''
) AS [processing-instruction(query)]
FROM @buffer_results AS br
WHERE
br.session_number = s.session_number
AND br.start_time = s.start_time
AND
(
(
s.start_time = s.last_request_start_time
AND EXISTS
(
SELECT *
FROM sys.dm_exec_requests r2
WHERE
r2.session_id = s.session_id
AND r2.request_id = s.request_id
AND r2.start_time = s.start_time
)
)
OR
(
s.request_id = 0
AND EXISTS
(
SELECT *
FROM sys.dm_exec_sessions s2
WHERE
s2.session_id = s.session_id
AND s2.last_request_start_time = s.last_request_start_time
)
)
)
FOR XML
PATH(''),
TYPE
)
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
WHERE
recursion = 1
OPTION (KEEPFIXED PLAN);
CLOSE buffer_cursor;
DEALLOCATE buffer_cursor;
END;
IF
@get_plans >= 1
AND @recursion = 1
AND @output_column_list LIKE '%|[query_plan|]%' ESCAPE '|'
BEGIN;
DECLARE @live_plan BIT;
SET @live_plan = ISNULL(CONVERT(BIT, SIGN(OBJECT_ID('sys.dm_exec_query_statistics_xml'))), 0)
DECLARE plan_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT
session_id,
request_id,
plan_handle,
statement_start_offset,
statement_end_offset
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions)
WHERE
recursion = 1
AND plan_handle IS NOT NULL
OPTION (KEEPFIXED PLAN);
OPEN plan_cursor;
FETCH NEXT FROM plan_cursor
INTO
@session_id,
@request_id,
@plan_handle,
@statement_start_offset,
@statement_end_offset;
--Wait up to 5 ms for a query plan, then give up
SET LOCK_TIMEOUT 5;
WHILE @@FETCH_STATUS = 0
BEGIN;
DECLARE @query_plan XML;
SET @query_plan = NULL;
IF @live_plan = 1
BEGIN;
BEGIN TRY;
SELECT
@query_plan = x.query_plan
FROM sys.dm_exec_query_statistics_xml(@session_id) AS x;
IF
@query_plan IS NOT NULL
AND EXISTS
(
SELECT
*
FROM sys.dm_exec_requests AS r
WHERE
r.session_id = @session_id
AND r.request_id = @request_id
AND r.plan_handle = @plan_handle
AND r.statement_start_offset = @statement_start_offset
AND r.statement_end_offset = @statement_end_offset
)
BEGIN;
UPDATE s
SET
s.query_plan = @query_plan
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
WHERE
s.session_id = @session_id
AND s.request_id = @request_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END;
END TRY
BEGIN CATCH;
SET @query_plan = NULL;
END CATCH;
END;
IF @query_plan IS NULL
BEGIN;
BEGIN TRY;
UPDATE s
SET
s.query_plan =
(
SELECT
CONVERT(xml, query_plan)
FROM sys.dm_exec_text_query_plan
(
@plan_handle,
CASE @get_plans
WHEN 1 THEN
@statement_start_offset
ELSE
0
END,
CASE @get_plans
WHEN 1 THEN
@statement_end_offset
ELSE
-1
END
)
)
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
WHERE
s.session_id = @session_id
AND s.request_id = @request_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END TRY
BEGIN CATCH;
IF ERROR_NUMBER() = 6335
BEGIN;
UPDATE s
SET
s.query_plan =
(
SELECT
N'--' + NCHAR(13) + NCHAR(10) +
N'-- Could not render showplan due to XML data type limitations. ' + NCHAR(13) + NCHAR(10) +
N'-- To see the graphical plan save the XML below as a .SQLPLAN file and re-open in SSMS.' + NCHAR(13) + NCHAR(10) +
N'--' + NCHAR(13) + NCHAR(10) +
REPLACE(qp.query_plan, N'<RelOp', NCHAR(13)+NCHAR(10)+N'<RelOp') +
NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]
FROM sys.dm_exec_text_query_plan
(
@plan_handle,
CASE @get_plans
WHEN 1 THEN
@statement_start_offset
ELSE
0
END,
CASE @get_plans
WHEN 1 THEN
@statement_end_offset
ELSE
-1
END
) AS qp
FOR XML
PATH(''),
TYPE
)
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
WHERE
s.session_id = @session_id
AND s.request_id = @request_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END;
ELSE
BEGIN;
UPDATE s
SET
s.query_plan =
CASE ERROR_NUMBER()
WHEN 1222 THEN '<timeout_exceeded />'
ELSE '<error message="' + ERROR_MESSAGE() + '" />'
END
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
WHERE
s.session_id = @session_id
AND s.request_id = @request_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END;
END CATCH;
END;
FETCH NEXT FROM plan_cursor
INTO
@session_id,
@request_id,
@plan_handle,
@statement_start_offset,
@statement_end_offset;
END;
--Return this to the default
SET LOCK_TIMEOUT -1;
CLOSE plan_cursor;
DEALLOCATE plan_cursor;
END;
IF
@get_locks = 1
AND @recursion = 1
AND @output_column_list LIKE '%|[locks|]%' ESCAPE '|'
BEGIN;
DECLARE locks_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT DISTINCT
database_name
FROM [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks)
WHERE
EXISTS
(
SELECT *
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
WHERE
s.session_id = [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks).session_id
AND recursion = 1
)
AND database_name <> '(null)'
OPTION (KEEPFIXED PLAN);
OPEN locks_cursor;
FETCH NEXT FROM locks_cursor
INTO
@database_name;
WHILE @@FETCH_STATUS = 0
BEGIN;
BEGIN TRY;
SET @sql_n = CONVERT(NVARCHAR(MAX), '') +
'UPDATE l ' +
'SET ' +
'object_name = ' +
'REPLACE ' +
'( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'o.name COLLATE Latin1_General_Bin2, ' +
'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''?''), ' +
'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''?''), ' +
'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''?''), ' +
'NCHAR(0), ' +
N''''' ' +
'), ' +
'index_name = ' +
'REPLACE ' +
'( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'i.name COLLATE Latin1_General_Bin2, ' +
'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''?''), ' +
'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''?''), ' +
'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''?''), ' +
'NCHAR(0), ' +
N''''' ' +
'), ' +
'schema_name = ' +
'REPLACE ' +
'( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
's.name COLLATE Latin1_General_Bin2, ' +
'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''?''), ' +
'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''?''), ' +
'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''?''), ' +
'NCHAR(0), ' +
N''''' ' +
'), ' +
'principal_name = ' +
'REPLACE ' +
'( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'dp.name COLLATE Latin1_General_Bin2, ' +
'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''?''), ' +
'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''?''), ' +
'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''?''), ' +
'NCHAR(0), ' +
N''''' ' +
') ' +
'FROM [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) AS l ' +
'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.allocation_units AS au ON ' +
'au.allocation_unit_id = l.allocation_unit_id ' +
'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +
'p.hobt_id = ' +
'COALESCE ' +
'( ' +
'l.hobt_id, ' +
'CASE ' +
'WHEN au.type IN (1, 3) THEN au.container_id ' +
'ELSE NULL ' +
'END ' +
') ' +
'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p1 ON ' +
'l.hobt_id IS NULL ' +
'AND au.type = 2 ' +
'AND p1.partition_id = au.container_id ' +
'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +
'o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +
'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.indexes AS i ON ' +
'i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) ' +
'AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) ' +
'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +
's.schema_id = COALESCE(l.schema_id, o.schema_id) ' +
'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.database_principals AS dp ON ' +
'dp.principal_id = l.principal_id ' +
'WHERE ' +
'l.database_name = @database_name ' +
'OPTION (KEEPFIXED PLAN); ';
EXEC sp_executesql
@sql_n,
N'@database_name sysname',
@database_name;
END TRY
BEGIN CATCH;
UPDATE [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks)
SET
query_error =
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
ERROR_MESSAGE() COLLATE Latin1_General_Bin2
),
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'?'),
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'?'),
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'?'),
NCHAR(0),
N''
)
WHERE
database_name = @database_name
OPTION (KEEPFIXED PLAN);
END CATCH;
FETCH NEXT FROM locks_cursor
INTO
@database_name;
END;
CLOSE locks_cursor;
DEALLOCATE locks_cursor;
CREATE CLUSTERED INDEX IX_SRD ON [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) (session_id, request_id, database_name);
UPDATE s
SET
s.locks =
(
SELECT
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
l1.database_name COLLATE Latin1_General_Bin2
),
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'?'),
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'?'),
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'?'),
NCHAR(0),
N''
) AS [Database/@name],
MIN(l1.query_error) AS [Database/@query_error],
(
SELECT
l2.request_mode AS [Lock/@request_mode],
l2.request_status AS [Lock/@request_status],
COUNT(*) AS [Lock/@request_count]
FROM [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) AS l2
WHERE
l1.session_id = l2.session_id
AND l1.request_id = l2.request_id
AND l2.database_name = l1.database_name
AND l2.resource_type = 'DATABASE'
GROUP BY
l2.request_mode,
l2.request_status
FOR XML
PATH(''),
TYPE
) AS [Database/Locks],
(
SELECT
COALESCE(l3.object_name, '(null)') AS [Object/@name],
l3.schema_name AS [Object/@schema_name],
(
SELECT
l4.resource_type AS [Lock/@resource_type],
l4.page_type AS [Lock/@page_type],
l4.index_name AS [Lock/@index_name],
CASE
WHEN l4.object_name IS NULL THEN l4.schema_name
ELSE NULL
END AS [Lock/@schema_name],
l4.principal_name AS [Lock/@principal_name],
l4.resource_description AS [Lock/@resource_description],
l4.request_mode AS [Lock/@request_mode],
l4.request_status AS [Lock/@request_status],
SUM(l4.request_count) AS [Lock/@request_count]
FROM [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) AS l4
WHERE
l4.session_id = l3.session_id
AND l4.request_id = l3.request_id
AND l3.database_name = l4.database_name
AND COALESCE(l3.object_name, '(null)') = COALESCE(l4.object_name, '(null)')
AND COALESCE(l3.schema_name, '') = COALESCE(l4.schema_name, '')
AND l4.resource_type <> 'DATABASE'
GROUP BY
l4.resource_type,
l4.page_type,
l4.index_name,
CASE
WHEN l4.object_name IS NULL THEN l4.schema_name
ELSE NULL
END,
l4.principal_name,
l4.resource_description,
l4.request_mode,
l4.request_status
FOR XML
PATH(''),
TYPE
) AS [Object/Locks]
FROM [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) AS l3
WHERE
l3.session_id = l1.session_id
AND l3.request_id = l1.request_id
AND l3.database_name = l1.database_name
AND l3.resource_type <> 'DATABASE'
GROUP BY
l3.session_id,
l3.request_id,
l3.database_name,
COALESCE(l3.object_name, '(null)'),
l3.schema_name
FOR XML
PATH(''),
TYPE
) AS [Database/Objects]
FROM [#locks](https://www.sozezzo.com/bookmark/./add-tag/locks) AS l1
WHERE
l1.session_id = s.session_id
AND l1.request_id = s.request_id
AND l1.start_time IN (s.start_time, s.last_request_start_time)
AND s.recursion = 1
GROUP BY
l1.session_id,
l1.request_id,
l1.database_name
FOR XML
PATH(''),
TYPE
)
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) s
OPTION (KEEPFIXED PLAN);
END;
IF
@find_block_leaders = 1
AND @recursion = 1
AND @output_column_list LIKE '%|[blocked_session_count|]%' ESCAPE '|'
BEGIN;
WITH
blockers AS
(
SELECT
session_id,
session_id AS top_level_session_id,
CONVERT(VARCHAR(8000), '.' + CONVERT(VARCHAR(8000), session_id) + '.') AS the_path
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions)
WHERE
recursion = 1
UNION ALL
SELECT
s.session_id,
b.top_level_session_id,
CONVERT(VARCHAR(8000), b.the_path + CONVERT(VARCHAR(8000), s.session_id) + '.') AS the_path
FROM blockers AS b
JOIN [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s ON
s.blocking_session_id = b.session_id
AND s.recursion = 1
AND b.the_path NOT LIKE '%.' + CONVERT(VARCHAR(8000), s.session_id) + '.%' COLLATE Latin1_General_Bin2
)
UPDATE s
SET
s.blocked_session_count = x.blocked_session_count
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
JOIN
(
SELECT
b.top_level_session_id AS session_id,
COUNT(*) - 1 AS blocked_session_count
FROM blockers AS b
GROUP BY
b.top_level_session_id
) x ON
s.session_id = x.session_id
WHERE
s.recursion = 1;
END;
IF
@get_task_info = 2
AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
AND @recursion = 1
BEGIN;
CREATE TABLE [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests)
(
session_id SMALLINT NOT NULL,
request_id INT NOT NULL,
database_name sysname NOT NULL,
object_id INT,
hobt_id BIGINT,
schema_id INT,
schema_name sysname NULL,
object_name sysname NULL,
query_error NVARCHAR(2048),
PRIMARY KEY (database_name, session_id, request_id)
);
CREATE STATISTICS s_database_name ON [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests) (database_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_schema_name ON [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests) (schema_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_object_name ON [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests) (object_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_query_error ON [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests) (query_error)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
INSERT [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests)
(
session_id,
request_id,
database_name,
object_id,
hobt_id,
schema_id
)
SELECT
session_id,
request_id,
database_name,
object_id,
hobt_id,
CONVERT(INT, SUBSTRING(schema_node, CHARINDEX(' = ', schema_node) + 3, LEN(schema_node))) AS schema_id
FROM
(
SELECT
session_id,
request_id,
agent_nodes.agent_node.value('(database_name/text())[1]', 'sysname') AS database_name,
agent_nodes.agent_node.value('(object_id/text())[1]', 'int') AS object_id,
agent_nodes.agent_node.value('(hobt_id/text())[1]', 'bigint') AS hobt_id,
agent_nodes.agent_node.value('(metadata_resource/text()[.="SCHEMA"]/../../metadata_class_id/text())[1]', 'varchar(100)') AS schema_node
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
CROSS APPLY s.additional_info.nodes('//block_info') AS agent_nodes (agent_node)
WHERE
s.recursion = 1
) AS t
WHERE
t.database_name IS NOT NULL
AND
(
t.object_id IS NOT NULL
OR t.hobt_id IS NOT NULL
OR t.schema_node IS NOT NULL
);
DECLARE blocks_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT DISTINCT
database_name
FROM [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests);
OPEN blocks_cursor;
FETCH NEXT FROM blocks_cursor
INTO
@database_name;
WHILE @@FETCH_STATUS = 0
BEGIN;
BEGIN TRY;
SET @sql_n =
CONVERT(NVARCHAR(MAX), '') +
'UPDATE b ' +
'SET ' +
'b.schema_name = ' +
'REPLACE ' +
'( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
's.name COLLATE Latin1_General_Bin2, ' +
'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''?''), ' +
'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''?''), ' +
'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''?''), ' +
'NCHAR(0), ' +
N''''' ' +
'), ' +
'b.object_name = ' +
'REPLACE ' +
'( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( ' +
'o.name COLLATE Latin1_General_Bin2, ' +
'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''?''), ' +
'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''?''), ' +
'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''?''), ' +
'NCHAR(0), ' +
N''''' ' +
') ' +
'FROM [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests) AS b ' +
'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.partitions AS p ON ' +
'p.hobt_id = b.hobt_id ' +
'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.objects AS o ON ' +
'o.object_id = COALESCE(p.object_id, b.object_id) ' +
'LEFT OUTER JOIN ' + QUOTENAME(@database_name) + '.sys.schemas AS s ON ' +
's.schema_id = COALESCE(o.schema_id, b.schema_id) ' +
'WHERE ' +
'b.database_name = @database_name; ';
EXEC sp_executesql
@sql_n,
N'@database_name sysname',
@database_name;
END TRY
BEGIN CATCH;
UPDATE [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests)
SET
query_error =
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
ERROR_MESSAGE() COLLATE Latin1_General_Bin2
),
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'?'),
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'?'),
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'?'),
NCHAR(0),
N''
)
WHERE
database_name = @database_name;
END CATCH;
FETCH NEXT FROM blocks_cursor
INTO
@database_name;
END;
CLOSE blocks_cursor;
DEALLOCATE blocks_cursor;
UPDATE s
SET
additional_info.modify
('
insert <schema_name>{sql:column("b.schema_name")}</schema_name>
as last
into (/additional_info/block_info)[1]
')
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
INNER JOIN [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests) AS b ON
b.session_id = s.session_id
AND b.request_id = s.request_id
AND s.recursion = 1
WHERE
b.schema_name IS NOT NULL;
UPDATE s
SET
additional_info.modify
('
insert <object_name>{sql:column("b.object_name")}</object_name>
as last
into (/additional_info/block_info)[1]
')
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
INNER JOIN [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests) AS b ON
b.session_id = s.session_id
AND b.request_id = s.request_id
AND s.recursion = 1
WHERE
b.object_name IS NOT NULL;
UPDATE s
SET
additional_info.modify
('
insert <query_error>{sql:column("b.query_error")}</query_error>
as last
into (/additional_info/block_info)[1]
')
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
INNER JOIN [#blocked_requests](https://www.sozezzo.com/bookmark/./add-tag/blocked_requests) AS b ON
b.session_id = s.session_id
AND b.request_id = s.request_id
AND s.recursion = 1
WHERE
b.query_error IS NOT NULL;
END;
IF
@output_column_list LIKE '%|[program_name|]%' ESCAPE '|'
AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
AND @recursion = 1
AND DB_ID('msdb') IS NOT NULL
BEGIN;
SET @sql_n =
N'BEGIN TRY;
DECLARE @job_name sysname;
SET @job_name = NULL;
DECLARE @step_name sysname;
SET @step_name = NULL;
SELECT
@job_name =
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
j.name,
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''?''),
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''?''),
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''?''),
NCHAR(0),
N''?''
),
@step_name =
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
s.step_name,
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''?''),
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''?''),
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''?''),
NCHAR(0),
N''?''
)
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS s ON
j.job_id = s.job_id
WHERE
j.job_id = @job_id
AND s.step_id = @step_id;
IF @job_name IS NOT NULL
BEGIN;
UPDATE s
SET
additional_info.modify
(''
insert text{sql:variable("@job_name")}
into (/additional_info/agent_job_info/job_name)[1]
'')
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
WHERE
s.session_id = @session_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
UPDATE s
SET
additional_info.modify
(''
insert text{sql:variable("@step_name")}
into (/additional_info/agent_job_info/step_name)[1]
'')
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
WHERE
s.session_id = @session_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END;
END TRY
BEGIN CATCH;
DECLARE @msdb_error_message NVARCHAR(256);
SET @msdb_error_message = ERROR_MESSAGE();
UPDATE s
SET
additional_info.modify
(''
insert <msdb_query_error>{sql:variable("@msdb_error_message")}</msdb_query_error>
as last
into (/additional_info/agent_job_info)[1]
'')
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
WHERE
s.session_id = @session_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END CATCH;'
DECLARE @job_id UNIQUEIDENTIFIER;
DECLARE @step_id INT;
DECLARE agent_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT
s.session_id,
agent_nodes.agent_node.value('(job_id/text())[1]', 'uniqueidentifier') AS job_id,
agent_nodes.agent_node.value('(step_id/text())[1]', 'int') AS step_id
FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s
CROSS APPLY s.additional_info.nodes('//agent_job_info') AS agent_nodes (agent_node)
WHERE
s.recursion = 1
OPTION (KEEPFIXED PLAN);
OPEN agent_cursor;
FETCH NEXT FROM agent_cursor
INTO
@session_id,
@job_id,
@step_id;
WHILE @@FETCH_STATUS = 0
BEGIN;
EXEC sp_executesql
@sql_n,
N'@job_id UNIQUEIDENTIFIER, @step_id INT, @session_id SMALLINT',
@job_id, @step_id, @session_id
FETCH NEXT FROM agent_cursor
INTO
@session_id,
@job_id,
@step_id;
END;
CLOSE agent_cursor;
DEALLOCATE agent_cursor;
END;
IF
@delta_interval > 0
AND @recursion <> 1
BEGIN;
SET @recursion = 1;
DECLARE @delay_time CHAR(12);
SET @delay_time = CONVERT(VARCHAR, DATEADD(second, @delta_interval, 0), 114);
WAITFOR DELAY @delay_time;
GOTO REDO;
END;
END;
SET @sql =
--Outer column list
CONVERT
(
VARCHAR(MAX),
CASE
WHEN
@destination_table <> ''
AND @return_schema = 0
THEN 'INSERT ' + @destination_table + ' '
ELSE ''
END +
'SELECT ' +
@output_column_list + ' ' +
CASE @return_schema
WHEN 1 THEN 'INTO [#session_schema](https://www.sozezzo.com/bookmark/./add-tag/session_schema) '
ELSE ''
END
--End outer column list
) +
--Inner column list
CONVERT
(
VARCHAR(MAX),
'FROM ' +
'( ' +
'SELECT ' +
'session_id, ' +
--[dd hh:mm:ss.mss]
CASE
WHEN @format_output IN (1, 2) THEN
'CASE ' +
'WHEN elapsed_time < 0 THEN ' +
'RIGHT ' +
'( ' +
'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, (-1 * elapsed_time) / 86400), ' +
'max_elapsed_length ' +
') + ' +
'RIGHT ' +
'( ' +
'CONVERT(VARCHAR, DATEADD(second, (-1 * elapsed_time), 0), 120), ' +
'9 ' +
') + ' +
'''.000'' ' +
'ELSE ' +
'RIGHT ' +
'( ' +
'REPLICATE(''0'', max_elapsed_length) + CONVERT(VARCHAR, elapsed_time / 86400000), ' +
'max_elapsed_length ' +
') + ' +
'RIGHT ' +
'( ' +
'CONVERT(VARCHAR, DATEADD(second, elapsed_time / 1000, 0), 120), ' +
'9 ' +
') + ' +
'''.'' + ' +
'RIGHT(''000'' + CONVERT(VARCHAR, elapsed_time % 1000), 3) ' +
'END AS [dd hh:mm:ss.mss], '
ELSE
''
END +
--[dd hh:mm:ss.mss (avg)] / avg_elapsed_time
CASE
WHEN @format_output IN (1, 2) THEN
'RIGHT ' +
'( ' +
'''00'' + CONVERT(VARCHAR, avg_elapsed_time / 86400000), ' +
'2 ' +
') + ' +
'RIGHT ' +
'( ' +
'CONVERT(VARCHAR, DATEADD(second, avg_elapsed_time / 1000, 0), 120), ' +
'9 ' +
') + ' +
'''.'' + ' +
'RIGHT(''000'' + CONVERT(VARCHAR, avg_elapsed_time % 1000), 3) AS [dd hh:mm:ss.mss (avg)], '
ELSE
'avg_elapsed_time, '
END +
--physical_io
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io))) OVER() - LEN(CONVERT(VARCHAR, physical_io))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS '
ELSE ''
END + 'physical_io, ' +
--reads
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads))) OVER() - LEN(CONVERT(VARCHAR, reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS '
ELSE ''
END + 'reads, ' +
--physical_reads
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads))) OVER() - LEN(CONVERT(VARCHAR, physical_reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS '
ELSE ''
END + 'physical_reads, ' +
--writes
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes))) OVER() - LEN(CONVERT(VARCHAR, writes))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS '
ELSE ''
END + 'writes, ' +
--tempdb_allocations
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS '
ELSE ''
END + 'tempdb_allocations, ' +
--tempdb_current
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS '
ELSE ''
END + 'tempdb_current, ' +
--CPU
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CPU))) OVER() - LEN(CONVERT(VARCHAR, CPU))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS '
ELSE ''
END + 'CPU, ' +
--context_switches
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches))) OVER() - LEN(CONVERT(VARCHAR, context_switches))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS '
ELSE ''
END + 'context_switches, ' +
--used_memory
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory))) OVER() - LEN(CONVERT(VARCHAR, used_memory))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS '
ELSE ''
END + 'used_memory, ' +
CASE
WHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN
--physical_io_delta
'CASE ' +
'WHEN ' +
'first_request_start_time = last_request_start_time ' +
'AND num_events = 2 ' +
'AND physical_io_delta >= 0 ' +
'THEN ' +
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io_delta))) OVER() - LEN(CONVERT(VARCHAR, physical_io_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) '
ELSE 'physical_io_delta '
END +
'ELSE NULL ' +
'END AS physical_io_delta, ' +
--reads_delta
'CASE ' +
'WHEN ' +
'first_request_start_time = last_request_start_time ' +
'AND num_events = 2 ' +
'AND reads_delta >= 0 ' +
'THEN ' +
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads_delta))) OVER() - LEN(CONVERT(VARCHAR, reads_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) '
ELSE 'reads_delta '
END +
'ELSE NULL ' +
'END AS reads_delta, ' +
--physical_reads_delta
'CASE ' +
'WHEN ' +
'first_request_start_time = last_request_start_time ' +
'AND num_events = 2 ' +
'AND physical_reads_delta >= 0 ' +
'THEN ' +
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads_delta))) OVER() - LEN(CONVERT(VARCHAR, physical_reads_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) '
ELSE 'physical_reads_delta '
END +
'ELSE NULL ' +
'END AS physical_reads_delta, ' +
--writes_delta
'CASE ' +
'WHEN ' +
'first_request_start_time = last_request_start_time ' +
'AND num_events = 2 ' +
'AND writes_delta >= 0 ' +
'THEN ' +
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes_delta))) OVER() - LEN(CONVERT(VARCHAR, writes_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) '
ELSE 'writes_delta '
END +
'ELSE NULL ' +
'END AS writes_delta, ' +
--tempdb_allocations_delta
'CASE ' +
'WHEN ' +
'first_request_start_time = last_request_start_time ' +
'AND num_events = 2 ' +
'AND tempdb_allocations_delta >= 0 ' +
'THEN ' +
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations_delta))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) '
ELSE 'tempdb_allocations_delta '
END +
'ELSE NULL ' +
'END AS tempdb_allocations_delta, ' +
--tempdb_current_delta
--this is the only one that can (legitimately) go negative
'CASE ' +
'WHEN ' +
'first_request_start_time = last_request_start_time ' +
'AND num_events = 2 ' +
'THEN ' +
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current_delta))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) '
ELSE 'tempdb_current_delta '
END +
'ELSE NULL ' +
'END AS tempdb_current_delta, ' +
--CPU_delta
'CASE ' +
'WHEN ' +
'first_request_start_time = last_request_start_time ' +
'AND num_events = 2 ' +
'THEN ' +
'CASE ' +
'WHEN ' +
'thread_CPU_delta > CPU_delta ' +
'AND thread_CPU_delta > 0 ' +
'THEN ' +
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, thread_CPU_delta + CPU_delta))) OVER() - LEN(CONVERT(VARCHAR, thread_CPU_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) '
ELSE 'thread_CPU_delta '
END +
'WHEN CPU_delta >= 0 THEN ' +
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, thread_CPU_delta + CPU_delta))) OVER() - LEN(CONVERT(VARCHAR, CPU_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) '
ELSE 'CPU_delta '
END +
'ELSE NULL ' +
'END ' +
'ELSE ' +
'NULL ' +
'END AS CPU_delta, ' +
--context_switches_delta
'CASE ' +
'WHEN ' +
'first_request_start_time = last_request_start_time ' +
'AND num_events = 2 ' +
'AND context_switches_delta >= 0 ' +
'THEN ' +
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches_delta))) OVER() - LEN(CONVERT(VARCHAR, context_switches_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) '
ELSE 'context_switches_delta '
END +
'ELSE NULL ' +
'END AS context_switches_delta, ' +
--used_memory_delta
'CASE ' +
'WHEN ' +
'first_request_start_time = last_request_start_time ' +
'AND num_events = 2 ' +
'AND used_memory_delta >= 0 ' +
'THEN ' +
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory_delta))) OVER() - LEN(CONVERT(VARCHAR, used_memory_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) '
ELSE 'used_memory_delta '
END +
'ELSE NULL ' +
'END AS used_memory_delta, '
ELSE ''
END +
--tasks
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tasks))) OVER() - LEN(CONVERT(VARCHAR, tasks))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) '
ELSE ''
END + 'tasks, ' +
'status, ' +
'wait_info, ' +
'locks, ' +
'tran_start_time, ' +
'LEFT(tran_log_writes, LEN(tran_log_writes) - 1) AS tran_log_writes, ' +
--open_tran_count
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, open_tran_count))) OVER() - LEN(CONVERT(VARCHAR, open_tran_count))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS '
ELSE ''
END + 'open_tran_count, ' +
--sql_command
CASE @format_output
WHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_command), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS '
ELSE ''
END + 'sql_command, ' +
--sql_text
CASE @format_output
WHEN 0 THEN 'REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_text), ''<?query --''+CHAR(13)+CHAR(10), ''''), CHAR(13)+CHAR(10)+''--?>'', '''') AS '
ELSE ''
END + 'sql_text, ' +
'query_plan, ' +
'blocking_session_id, ' +
--blocked_session_count
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, blocked_session_count))) OVER() - LEN(CONVERT(VARCHAR, blocked_session_count))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS '
ELSE ''
END + 'blocked_session_count, ' +
--percent_complete
CASE @format_output
WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CONVERT(MONEY, percent_complete), 2))) OVER() - LEN(CONVERT(VARCHAR, CONVERT(MONEY, percent_complete), 2))) + CONVERT(CHAR(22), CONVERT(MONEY, percent_complete), 2)) AS '
WHEN 2 THEN 'CONVERT(VARCHAR, CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1)) AS '
ELSE ''
END + 'percent_complete, ' +
'host_name, ' +
'login_name, ' +
'database_name, ' +
'program_name, ' +
'additional_info, ' +
'start_time, ' +
'login_time, ' +
'CASE ' +
'WHEN status = N''sleeping'' THEN NULL ' +
'ELSE request_id ' +
'END AS request_id, ' +
'GETDATE() AS collection_time '
--End inner column list
) +
--Derived table and INSERT specification
CONVERT
(
VARCHAR(MAX),
'FROM ' +
'( ' +
'SELECT TOP(2147483647) ' +
'*, ' +
'CASE ' +
'MAX ' +
'( ' +
'LEN ' +
'( ' +
'CONVERT ' +
'( ' +
'VARCHAR, ' +
'CASE ' +
'WHEN elapsed_time < 0 THEN ' +
'(-1 * elapsed_time) / 86400 ' +
'ELSE ' +
'elapsed_time / 86400000 ' +
'END ' +
') ' +
') ' +
') OVER () ' +
'WHEN 1 THEN 2 ' +
'ELSE ' +
'MAX ' +
'( ' +
'LEN ' +
'( ' +
'CONVERT ' +
'( ' +
'VARCHAR, ' +
'CASE ' +
'WHEN elapsed_time < 0 THEN ' +
'(-1 * elapsed_time) / 86400 ' +
'ELSE ' +
'elapsed_time / 86400000 ' +
'END ' +
') ' +
') ' +
') OVER () ' +
'END AS max_elapsed_length, ' +
CASE
WHEN @output_column_list LIKE '%|_delta|]%' ESCAPE '|' THEN
'MAX(physical_io * recursion) OVER (PARTITION BY session_id, request_id) + ' +
'MIN(physical_io * recursion) OVER (PARTITION BY session_id, request_id) AS physical_io_delta, ' +
'MAX(reads * recursion) OVER (PARTITION BY session_id, request_id) + ' +
'MIN(reads * recursion) OVER (PARTITION BY session_id, request_id) AS reads_delta, ' +
'MAX(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) + ' +
'MIN(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) AS physical_reads_delta, ' +
'MAX(writes * recursion) OVER (PARTITION BY session_id, request_id) + ' +
'MIN(writes * recursion) OVER (PARTITION BY session_id, request_id) AS writes_delta, ' +
'MAX(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) + ' +
'MIN(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_allocations_delta, ' +
'MAX(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) + ' +
'MIN(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_current_delta, ' +
'MAX(CPU * recursion) OVER (PARTITION BY session_id, request_id) + ' +
'MIN(CPU * recursion) OVER (PARTITION BY session_id, request_id) AS CPU_delta, ' +
'MAX(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) + ' +
'MIN(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) AS thread_CPU_delta, ' +
'MAX(context_switches * recursion) OVER (PARTITION BY session_id, request_id) + ' +
'MIN(context_switches * recursion) OVER (PARTITION BY session_id, request_id) AS context_switches_delta, ' +
'MAX(used_memory * recursion) OVER (PARTITION BY session_id, request_id) + ' +
'MIN(used_memory * recursion) OVER (PARTITION BY session_id, request_id) AS used_memory_delta, ' +
'MIN(last_request_start_time) OVER (PARTITION BY session_id, request_id) AS first_request_start_time, '
ELSE ''
END +
'COUNT(*) OVER (PARTITION BY session_id, request_id) AS num_events ' +
'FROM [#sessions](https://www.sozezzo.com/bookmark/./add-tag/sessions) AS s1 ' +
CASE
WHEN @sort_order = '' THEN ''
ELSE
'ORDER BY ' +
@sort_order
END +
') AS s ' +
'WHERE ' +
's.recursion = 1 ' +
') x ' +
'OPTION (KEEPFIXED PLAN); ' +
'' +
CASE @return_schema
WHEN 1 THEN
'SET @schema = ' +
'''CREATE TABLE <table_name> ( '' + ' +
'STUFF ' +
'( ' +
'( ' +
'SELECT ' +
''','' + ' +
'QUOTENAME(COLUMN_NAME) + '' '' + ' +
'DATA_TYPE + ' +
'CASE ' +
'WHEN DATA_TYPE LIKE ''%char'' THEN ''('' + COALESCE(NULLIF(CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH), ''-1''), ''max'') + '') '' ' +
'ELSE '' '' ' +
'END + ' +
'CASE IS_NULLABLE ' +
'WHEN ''NO'' THEN ''NOT '' ' +
'ELSE '''' ' +
'END + ''NULL'' AS [text()] ' +
'FROM tempdb.INFORMATION_SCHEMA.COLUMNS ' +
'WHERE ' +
'TABLE_NAME = (SELECT name FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(''tempdb..#session_schema'')) ' +
'ORDER BY ' +
'ORDINAL_POSITION ' +
'FOR XML ' +
'PATH('''') ' +
'), + ' +
'1, ' +
'1, ' +
''''' ' +
') + ' +
''')''; '
ELSE ''
END
--End derived table and INSERT specification
);
SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);
EXEC sp_executesql
@sql_n,
N'@schema VARCHAR(MAX) OUTPUT',
@schema OUTPUT;
END;
GO
https://www.youtube.com/watch?v=p3SXxclj_vg
https://www.youtube.com/watch?v=fD1CZVc6oUk
https://dba.stackexchange.com/questions/87417/why-not-rebuild-indexes-with-page-count-1000
https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
27 Jokes and Puns Only Those Who Know a Little French Can Understand.
Ce moment when you start penser en 2 langues at the same temps.
Free/Libre Open Source Software Binaries of VSCode
Microsoft’s vscode source code is open source (MIT-licensed), but the product available for download (Visual Studio Code) is licensed under this not-FLOSS license and contains telemetry/tracking. According to this comment from a Visual Studio Code maintainer
First open a terminal and enter sudo apt-get install xrdp. When that is installed enter sudo nano /etc/xrdp/startwm.sh in the terminal. Make sure the last line looks like this:
. /etc/X11/Xsession
Then go to your home folder, rightclick and select Show hidden. If there is no file named .xsession, create it. If there is a filed named like that, open it and make sure that it looks like this when your done: lxsession -e LXDE -s Lubuntu
Now type sudo service xrdp restart in the terminal to restart xrdp. Now it should work :)
youtube-dl is a command-line program to download videos from YouTube.com and a few more sites. It requires the Python interpreter (2.6, 2.7, or 3.2+), and it is not platform specific. We also provide a Windows executable that includes Python. youtube-dl should work in your Unix box, in Windows or in Mac OS X. It is released to the public domain, which means you can modify it, redistribute it or use it however you like.
01 The Pirate Bay (TPB) – http://thepiratebay.org/ Le roi du torrent incontesté
02 Kickass Torrents – https://kickasstorrents.cr/ Une excellente sélection de séries et films (vieux et récents)
03 RARBG – http://rarbg.to/index70.php Des informations utiles pour évaluer rapidement un torrent
04 YIFY Movies – https://yts.am/ La star des films de haute qualité
05 EZTV – https://eztv.io/ Les téléchargements de séries TV les plus rapides du marché
06 1337x – https://1337x.to/ Une utilisation très conviviale
07 ISOHunt – https://isohunt.tv/ Une excellente sélection de torrents
08 Lime Torrents – https://www.limetorrents.info/ Une interface épurée et des annonces limitées
09 Torrentz2 – https://torrentz2.eu/ Un moteur de recherche torrent ultra-rapide
10 TorLock – https://torlock.com/ Un moteur de recherche et un catalogue vérifiés
11 Zooqle – https://zooqle.com/ Des films et séries TV dans toutes les résolutions
12 Torrentseeker – https://torrentseeker.com/ Un autre moteur de recherche solide
Clearview’s Facial Recognition App Has Been Used By The Justice Department, ICE, Macy’s, Walmart, And The NBA
The United States’ main immigration enforcement agency, the Department of Justice, retailers including Best Buy and Macy’s, and a sovereign wealth fund in the United Arab Emirates are among the thousands of government entities and private businesses around the world listed as clients of the controversial facial recognition startup with a database of billions of photos scraped from social media and the web.
The startup, Clearview AI, is facing legal threats from Facebook, Google, and Twitter, as well as calls for regulation and scrutiny in the US. But new documents reviewed by BuzzFeed News reveal that it has already shared or sold its technology to thousands of organizations around the world.
In its quest to create a global biometric identification system to span both public and private sectors, Clearview has signed paid contracts with US Immigration and Customs Enforcement (ICE), the US Attorney's Office for the Southern District of New York, and Macy’s, according to the document obtained by BuzzFeed News. The company has credentialed users at the FBI, Customs and Border Protection (CBP), Interpol, and hundreds of local police departments. In doing so, Clearview has taken a flood-the-zone approach to seeking out new clients, providing access not just to organizations, but to individuals within those organizations — sometimes with little or no oversight or awareness from their own management.
52768 (1998 OR2)
Calc impact
Using the Impact Calculator, students can create a virtual impact on Earth by changing the size, speed and composition of an approaching asteroid or comet.
MultiBootUSB is a cross platform software written in python which allows you to install multiple live linux on a USB disk non destructively and option to uninstall distros. Try out the world's first true cross platform multi boot live usb creator for free.
Mot clé pertinent dans le titre
Sous-titres contenant des mots clés
Mot clé pertinent dans la description
Textes d'ancrage contenant des mots-clés (maillage interne)
Mot-clé dans l'URL (URL descriptive)
Mots-clés et synonymes dans le texte.
330 Tools for Remote Work
As of 7.0, we have over 36,000 games (and 2,300 animations)
The full package, ready for offline use. 241GB download, 288GB extracted. Extract with 7-Zip, not WinRAR. You will probably want a download manager for the mirrors, or a torrent program for the torrent.
Manuskript is a perfect tool for those writer who like to organize and plan everything before writing.
The snowflake method can help you grow your idea into a book, by leading you step by step and asking you questions to go deeper.
While writing, keep track of notes about every characters, plot, event, place in your story.
Manuskript is still in development, and in need of extensive testing.
Convert video, images, audio, ebook, pdf, jpg and documents for free
This free online file converter lets you convert media easy and fast from one format to another. We support a lot of different source formats, just try. If you can't find the conversion you need, please let us know and write us an e-mail. We probably can help you...
Osons le dire, Internet engendre une pollution astronomique d’autant plus inquiétante qu’elle est invisible. L’utilisation d’internet nous est devenue indispensable, elle s’est totalement fondue dans notre mode de vie moderne. Nombre de gens y travaillent aujourd’hui et imaginent mal pouvoir vivre sans. Avouons-le, peu d’entre nous seraient capables de s’en passer au quotidien car même de simples démarches administratives ne se font plus qu’en ligne. D’où la problématique à laquelle nous sommes tous confrontés, comment faire en sorte que la toile qui s’étend de jour en jour puisse limiter son impact écologique ?
Les lois de l'informatique
Loi de Murphy & Moore
The Earth's weather functions as an interdependent system. For example, a hurricane in the Atlantic has the ability to influence the distribution of pressure formations in Europe. Occasionally, it may even make its way to Europe itself as a post-tropical storm. The Ventusky application allows for the illustration of the interdependence of the entire system, displaying the development of pressure, wind, cloud cover, precipitation, and temperature on the map.
Please note, changing your handset’s IMEI number may void warranty or break the law. As far as I know, it’s strictly prohibited to change any phone’s IMEI number in the UK. So always refer to your user manual and local law, you are responsible for any action you take.
Node.js is a very powerful JavaScript-based platform built on Google Chrome's JavaScript V8 Engine. It is used to develop I/O intensive web applications like video streaming sites, single-page applications, and other web applications. Node.js is open source, completely free, and used by thousands of developers around the world.
Tips to improve your English pronunciation
Break down into sounds
Record yourself
Look up tutorials on Youtube
Focus on one accent
work on word/sentence reduction
work on your intonation
Comment être sûr de rater ton projet informatique
J’ai vu énormément de projets informatiques prendre feu et sombrer dans le chaos devant mes yeux. Aujourd’hui, je vais te donner la recette parfaite pour réduire en cendre tous tes efforts et ceux de ton équipe. Et oui tout brûle.
hoa.ro
Jekyll • Simple, blog-aware, static sites | Transform your plain text into static websites and blogs
Transform your plain text into static websites and blogs.
Simple
No more databases, comment moderation, or pesky updates to install—just your content.
Static
Markdown, Liquid, HTML & CSS go in. Static sites come out ready for deployment.
Blog-aware
Permalinks, categories, pages, posts, and custom layouts are all first-class citizens here.
DECLARE
@object_name SYSNAME
, @object_id INT
, @SQL NVARCHAR(MAX)
SELECT
@object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
, @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID('dbo.WorkOut', 'U')) o
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT CHAR(13) + ' , [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
ELSE
CASE WHEN c.system_type_id != c.user_type_id
THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'
ELSE '[' + UPPER(tp.name) + ']'
END +
CASE
WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('nvarchar', 'nchar')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length / 2 AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name = 'decimal'
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id
THEN ' COLLATE ' + c.collation_name
ELSE ''
END +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END +
CASE WHEN c.default_object_id != 0
THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
ELSE ''
END +
CASE WHEN cc.[object_id] IS NOT NULL
THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
ELSE ''
END +
CASE WHEN c.is_identity = 1
THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +
CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'
ELSE ''
END
END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.check_constraints cc WITH(NOLOCK)
ON c.[object_id] = cc.parent_object_id
AND cc.parent_column_id = c.column_id
WHERE c.[object_id] = @object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') +
ISNULL((SELECT '
, CONSTRAINT [' + i.name + '] PRIMARY KEY ' +
CASE WHEN i.index_id = 1
THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END +' (' + (
SELECT STUFF(CAST((
SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +
CASE WHEN ic.is_descending_key = 1
THEN ' DESC'
ELSE ''
END
FROM sys.index_columns ic WITH(NOLOCK)
WHERE i.[object_id] = ic.[object_id]
AND i.index_id = ic.index_id
FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
FROM sys.indexes i WITH(NOLOCK)
WHERE i.[object_id] = @object_id
AND i.is_primary_key = 1), '') + CHAR(13) + ');'
PRINT @SQL
PSR
Steps Recorder (called Problems Steps Recorder in Windows 7), is a program that helps you troubleshoot a problem on your device by recording the exact steps you took when the problem occurred. You can then send this record to a support professional to help them diagnose the problem.
A quoi sert Ma Dada ?#
Ma Dada est une plateforme qui vous aide à demander l’accès à des informations détenues par les autorités publiques. Qu’est-ce qu’une autorité publique ? Quelques exemples : Communes, CPAS, Sociétés de logement social, Cabinets ministériels, Ministères publics, Intercommunales,...).
Loyers à Paris et en Ile-de-France : où pouvez-vous habiter selon votre budget ?
Un concept simple : tous les matins 10h une nouvelle photo, une nouvelle fracture de l’oeil.
Le site qui vous fera dire Bonjour Madame !
This essay draws in part on Pierre Desrochers’ new policy paper The Myths of Local Food Policy: Lessons From the Economic and Social History of the Food System (Fraser Institute, October 2019). Readers looking for additional references and supporting evidence on the subjects discussed in this essay are invited to consult the policy paper._
1. Don’t do it ‘just because’
We already buy enough stuff that we don’t end up using—don’t add a server to that list. Terminals can be daunting, especially for beginners, and don’t necessarily invite being played around with unless you have a certain baseline of skill. Set a goal, even if it’s just learning Linux/terminal fundamentals, and then get to work.
2. Keep your eye on the prize
No matter which host you chose, you bought a server for a reason. Maybe you wanted to set up a personal development blog to talk about what you’re building, or perhaps you have a business that needs a website and didn’t want to rely on shared hosting or automated WHMCS panels. Whatever the reason, it’s going to be what grounds when you’re stuck in one of the frustrating ruts of managing a server. Don’t forget what you’re building, and let that momentum guide you the process—both good and bad.
3. Do only one thing at a time
Sometimes I think of managing servers like carpentry or woodworking. You wouldn’t dive right into building an entire house, but you may start mastering the art of straight cuts on 2x4s, followed by some basic framing. You learn how to create a foundation, and you do that a bunch of times, and then you get into the tough stuff.
Try deploying a static site on a LAMP/LEMP stack. Get good at that, get it working exactly the way you want, and then move on to more complex installations, like self-hosting with Docker or segregated Linux containers.
4. Figure out how you learn best
Everyone has a different learning style, especially when it comes to complex topics like servers. Some might do best watching videos of someone coding “live,” whereas others might prefer videos. I tend to do best when I can read solid documentation and try various solutions out myself, but that might not be right for you.
5. Hoover up all the free resources you can
The Linux and FOSS communities are remarkably generous and love to share knowledge—use that to your advantage. You shouldn’t have to fork over cash to learn about deploying services and apps on servers. Learn how to search on Google/DuckDuckGo and get to it.
6. Find a good community, too
You’ll come across problems you can’t figure out on your own. By finding a community, you’ll have a handful of people who have your back and can help you work through a tough problem. Great communities are almost impossible to find these days, but here are a few:
7. Break your problems down as much as possible
Tutorials can be confusing to follow no matter your skill level. It’s easy to get overwhelmed with all the commands to run on the terminal and all the configuration files to edit. Focus on what you can get done in a given development/deployment “session” and don’t worry about the rest.
8. Focus on solving problems, not checking boxes
You’re trying to build knowledge and solve problems, not just check boxes. Let’s say you have a development consultancy and are looking for better ways to connect with potential customers. The problem is that you don’t have a blog. The solution isn’t LAMP+Wordpress, or Ghost, or Hugo, or any of the individual content management systems (CMS), but rather a working, functioning CMS on which you can rely. This small tweak in perspective—from deploying a tool to instead solving your problem—can be useful in maintaining your focus if and when things don’t always go your way.
If you’re building a business, it’s also a great way to talk about your product with potential customers.
9. Don’t compare your ‘stack’ to others
Beginner server users often get stuck on which tech “stack” to use—stick with the tried-and-tested WordPress installation, or go Ghost, or maybe a flat-file CMS? It’s a recipe for indecision. Remember that when someone recommends a given solution, they’re not doing so objectively, and their pros might end up being your cons.
10. Avoid the flavor of the month, too
Avoid hype and don’t always buy into the newest and coolest framework, toolkit, or deployment strategy. Chances are they’ll fade out of fame as quickly as they arrived, and you’ll be stuck with an abandoned stack and a shrinking community around it to help you out when things go wrong.
11. Don’t let jargon seep into the way you talk
It’s tempting to use all your new server vocabulary when talking to others, whether in person or online in one of the communities I mentioned above, but remember that our profession or our skillset do not define us—we’re people, first and foremost. If you’re having issues with and are looking for help, don’t just copy a logfile and expect someone to debug for you. Instead, state your issue and your goals in language we can all relate to, and then get into the details.
12. Learn to love ‘less’ and ‘tail’
Embrace your logs—
/var/log/
should be one of the most-visited folders on your server. These warning/error messages will be difficult to understand at first, but you’ll get the hang of them with enough searching. Developers work hard to create useful log messages that you’ll be able to suss out yourself. If not, you can always copy some text from the terminal and head over to Stack Overflow.13. Look for wisdom in public repositories
If you’re still running into troubleshooting issues, remember that almost all of the services and applications you install on a server are open source. Not only is their code freely published online via GitHub or another version control software, but so are all their “warts,” or issues, that others have stumbled on. Be sure to search both open and closed issues for others who have already found solutions to your current problem.
14. You don’t need to follow a roadmap
Not long ago, a post on HackerNoon called “The 2018 DevOps RoadMap” went viral, at least on the DevOps/sysadmin scale. The roadmap image is wildly daunting, especially for beginners. Some boxes are so broad you might need months or years to master them.
Unless your goal in running a server is explicitly to become a DevOps engineer, don’t get too caught up in all these educational to-dos. Learn only what you need to get your project online, master those skills, and then learn whatever will push you closer to your end goal, not whatever is “next” on the list.
15. Commit to a lifelong education
Server, development, and deployment technology moves fast. If your job depends on these technologies, you’ll, of course, need to stay in touch with the latest and greatest, but remain committed to solving problems, not spending time on something new just because it’s new. You shouldn’t learn Ansible just because you feel you should. You should learn it if automatically provisioning servers will streamline your server work. Same goes for any of the technologies in the roadmap featured just above.
You’ll also need to continuously learn more than technology—to succeed in any field you need excellent people skills, empathy, communication know-how, and a whole lot more. We’ll soon be publishing a comprehensive piece about “evergreen” skills for developers and server geeks. Stay tuned.
16. Build your own cheat sheets
It’s okay to search for solutions, but remember that no one can teach you better than yourself. If you find yourself looking up the proper syntax and settings for a Nginx server block, for example, write up a quick document with a working solution and document it. Then you’ll have your own answer, written in your own style, precisely the way you’ll understand.
Take this a step further by building a folder full of purpose-built shell scripts that help you accomplish different repetitive tasks.
17. Embrace failure
There might come times when you make a critical mistake that breaks your server or its applications. You might be tempted to spend hours researching logs and troubleshooting tips in the effort to rescue the work you’ve already put in, but often the best option is to start over. You’ll either get faster at setting up your stack, or you’ll give into configuration management. Either way, you gain in the end.
You’re backing things up, right?
18. Don’t be afraid of the nuclear option
There’s no better place to experiment than in software. You can always wipe the slate clean and try again. No matter how skilled you are in development and deployment, you’ll make mistakes, perhaps bad enough to take down everything you’ve built. That’s exactly why the
Reinstall
button exists. In about 30 seconds, you’ll have a fresh Linux installation and can take everything you’ve learned since to make your deployment better, or at least a little more resilient to your… meddling.19. Take note of your successes
Not long ago I was reminded of the fact that the most successful people tend to write down their goals. A written target is actionable and reviewable. I think the same is true of goals we’ve accomplished. By writing them down, we give ourselves essential perspective on how much we’ve learned, created, or shared.
This is why I always advocate for developers to deploy and write content for their own blogs—the journey from idea to deployed project might not sound like thrilling content, but it’s a useful way to mark your progress and prove (maybe to potential employers!) that you know how to problem solve and can write a decent sentence or two.
20. Try crazy things and get more from your investment
You spent your hard-earned money on a server with a certain amount of RAM, disk space, and transfer, and it runs Linux, the most customizable and flexible operating system out there. You might have gotten that portfolio site up, but why not try self-hosting on top of that? Perhaps one of the many strange uses for Docker would be down your alley? Or, if you’re financially motivated, figure out a way to get a positive ROI from your VPS.
That blinking terminal is a gateway—make sure you take full advantage.
21. There is no such thing as a perfect deployment
We want to believe big tech companies have these precise and efficient setups that have been finely tuned by dozens of experts over the course of years. The truth is that each of these experts likely has a list, longer than we’d like to know, of issues they plan to fix, or bugs they’d love to squash, or ground-up improvements they know the CTO will never sign off. Same goes for a startup’s SaaS app, or a corporate blog, or a small personal development portfolio.
The goal isn’t perfection, but rather creation. If you have a server up and running, no matter which hosting provider you’re with, you’re well on your way. Best of luck with everything to come.
This database is comprised of a country directory with summaries of national drone laws. The objective is to provide humanitarian and non-humanitarian actors with a database of relevant national regulations, additional resources, and links to original regulatory documents to ensure that drones are deployed safely and in compliance with national regulations.
Archive.org The Internet Archive has just opened its torrents section, which offers over 1 million items from its collection for free download through torrents. This collection includes (as of August, 2012) 1,225,491 ebooks, 127,383 items from the music and audio section of the Archive and 9,674 videos from the movies archive. This collection will grow rapidly as all new entries to the archive will be offered as torrents and older entries are being added continually. In my test of a few of the torrents, download speeds were excellent. The Archive is seeding all content.
BitLove creates torrents for RSS feeds of Podcasts and helps to seed them. Many of the current podcasts are in German, but there are a few English podcasts available. All are free downloads through torrents.
BitTorrent Bundle mostly music with some video and audiobooks. All available through torrents. Though put out by BitTorrent, any bittorrent client may be used.
C. Dominik Bodi has each of the 24 promotional CDs of Baen's free science fiction novels available in ISO.
ClearBits about 1300 torrents of Creative Commons licensed digital media.
eTree is a community for sharing the live concerts of Trade Friendly artists. It is a very large collection of many genres and has a nice mix of old and new.
Fanatics4Classics a good size collection of classic novels from Project Gutenberg available for free download in genre bundles. There did not appear to be a web seed supporting these and the speeds for each torrent were variable. The bundles are available in EPUB and Kindle compatible MOBI.
FrostClick an impressive collection of free music, games, software, movies and shows offered under creative commons license.
GameUpdates.org carries legal patches, demos and mods for the latest games. There are dedicated high speed seeders, so files are always available. Looking at their forums, they weed out any illegal material uploaded quickly.
ibiblio torrents provides Bittorrent access to ibiblio's open source content. There is a great deal of free content available here in software, audio and video.
JamToThis is a private site featuring legal audio and video in a similar vein to eTree above.
Legit Torrents is a collection of close to 1,700 torrents, which appears to be mostly software with some of the other content. It bills itself as hosting 100% Legally Free Media.
Librivox offers all of its public domain audiobooks for free download through torrents. From what I saw, all the torrents are seeded by the Archive.org servers.
Linux Tracker bills itself as the Premiere Linux Bittorrent Website and hosts around 500 torrents of free and open source software, preferably Linux.
Media History Digital Library a collection of works regarding the history of cinema, broadcasting and sound. If you click on the link to the Internet Archive page (IA), a torrent is available for each of the library offerings.
Panda.cd is a private site featuring creative commons and artist permission releases of music. Their collection has grown rather large. Registration is open.
PlaneShift a 3d fantasy MMORPG that is free and open source. Cross platform downloads are available free through torrents. Registration, with valid email, is required to play the game. There did appear to be at least one web seed available, so speeds were good.
Public Domain Flicks offers a collection of free public domain movies for free download through torrents. All appear to be through Archive.org and its web seeds.
Public Domain Torrents is a collection of classic movies and B-movies that are in the public domain. The collection includes approximately 1,000 movies that are available in DivX, iPod, PDA and PSP. Speeds are generally a bit slow.
StanfordSchoolOfEngineering offers free classes and lectures. The print material is available through direct download. Video lectures are available through torrents.
SXSW Torrents not the official site of SXSW, but this site has 9 years of the music releases from SXSW. There are over 9,000 songs included in these collections.
TAS Videos tool assisted game movies. These run through classic and modern games at high speed, allowing new users to quickly become experts. All are available as free downloads through torrents. The torrents all appear to be supported by Archive.org, web seeds.
The Mercury Theater On The Air an excellent collection of shows from the classic radio drama, famous for the War of The Worlds broadcast, but also responsible for some of the best radio dramas ever. A torrent of all the material is at the bottom of this page.
The New Voyages a fan series based set in the Star Trek universe. Now referred to as Star Trek: Phase II, the series began as Star Trek: New Voyages and continues where the original series left off. All episodes available for free download through torrents. Spanish and Portuguese hard coded subtitled versions also available.
ThePaulWayMixes Paul Way released 52 of his mixes for free and legal download through torrents.
Vuze. As of June, 2009, Vuze now carries only videos. This is a small collection of some TV shows, movie trailers and news clips. All are available for free download with any bittorrent client.
Vuze Blog posts links to an interesting collection of legal torrents around the web. All are free.
This is not a complete listing of free and legal sites. If anyone knows of other sites they feel should be on this list, then please post here.
Is your browser safe against tracking?
How well are you protected against non-consensual Web tracking? After analyzing your browser and add-ons, the answer is ...
Yes! You have strong protection against Web tracking, though your software isn’t checking for Do Not Track policies.
Help us defend the Web against tracking:
We let you use a free and simple online file converter that works with a wide range of file types. It will convert any document, archive file, spreadsheet, audio and video file from one format to another. Our service is offered at absolutely no charge and without the need to download additional software. Just click the Browse button, select your file and upload it. We will take care of the rest
Hear & Learn with HowToPronounce.com
Free Online Audio Pronunciation Multilingual Dictionaries
Free peer-to-peer file transfers in your browser.
We never store anything. Files only served fresh.
The internet’s source of freely useable images.
Powered by creators everywhere.
Create beautiful, professional resumes in minutes, free.
Create a cv now
Jotti's malware scan is a free service that lets you scan suspicious files with several anti-virus programs. You can submit up to 5 files at the same time. There is a 250MB limit per file. Please be aware that no security solution offers 100% protection, not even when it uses several anti-virus engines. All files are shared with anti-virus companies so detection accuracy of their anti-virus products can be improved.
Stunning free images & royalty free stock
Over 1 million+ high quality stock images and videos shared by our talented community.
Website value calculator
More then 20 Metrics to Measure & Track Success of your website.
No need to check Web stats on different websites and tools.
Just one website One tool.
Internet speed
Remove Image Background
Systems with microprocessors utilizing speculative execution and indirect branch prediction may allow unauthorized disclosure of information to an attacker with local user access via a side-channel analysis.
References to Advisories, Solutions, and Tools
By selecting these links, you will be leaving NIST webspace. We have provided these links to other web sites because they may have information that would be of interest to you. No inferences should be drawn on account of other sites being referenced, or not, from this page. There may be other web sites that are more appropriate for your purpose. NIST does not necessarily endorse the views expressed, or concur with the facts presented on these sites. Further, NIST does not endorse any commercial products that may be mentioned on these sites. Please address comments about this page to nvd@nist.gov.
Le DNS-over-HTTPS finira probablement par être déployé sur tous les navigateurs, malgré l'opposition des FAI
Au moins six éditeurs de navigateurs Web prévoient ou prennent déjà en charge le protocole DNS-over-HTTPS (DoH) - qui permet de chiffrer le trafic DNS et contribue à améliorer la confidentialité de l’utilisateur sur Internet - sur leurs produits. Le protocole DOH semblait initialement bénéficier du soutien exclusif de Google et de Mozilla parmi les éditeurs de navigateurs Web. Mais des sources proches du dossier suggèrent qu’en réalité, les principaux fournisseurs de navigateurs Web (notamment Microsoft, Apple, Google, et Mozilla) prévoient d’implémenter cette technologie au sein de leur navigateur Web, sous une forme ou sous une autre. Chrome, Firefox, Opera, Vivaldi, Safari, Edge et Brave sont concernés par cette annonce.
La dépêche « Protéger sa vie privée sur le Web, exemple avec Firefox » (février 2018) a ouvert des questions sur la protection par HTTPS, Tor et VPN. Ces techniques protègent, mais contre quoi et dans quelles limites ? Cet article essaie de l’expliquer plus en détails. N’ayez pas l’illusion d’être totalement protégés en utilisant l’une d’elles : soit, elle ne permet pas vraiment ce que vous croyez, soit il faut l’utiliser d’une certaine façon ou la compléter d’autres précautions pour avoir le résultat attendu.
You’ve seen CAPTCHAs all over the internet for over a decade now. Those squiggly lines, words, or numbers that block your way when you try to log in, sign up, or post a comment anywhere.
CAPTCHAs (or Completely Automated Public Turing tests to tell Computers and Humans Apart) are designed to be a gate that lets humans through and robots (programs) out. The squiggly lines and wiggly words are less common nowadays and they have been replaced by version 2 of Google’s reCAPTCHA. This is the CAPTCHA that gives you the green checkmark as long as your humanity quotient is deemed high enough.
https://github.com/GoogleChrome/puppeteer/blob/master/docs/api.md
Brave is a free and open-source web browser developed by Brave Software, Inc. based on the Chromium web browser. The browser blocks ads and website trackers. The company has proposed adopting a Get paid to surf business model in a future version of the browser.
The “private” web browsing mode that others offer is not truly private. Brave is the new privacy web browser from the inventor of JavaScript and co-founder of Mozilla, with a built-in adblocker to give you the fastest internet browsing experience, while providing a deep level of security and privacy protection. Brave also lets you reward favorite publishers with blockchain-based tokens. Faster, Safer, Ad-Free Browsing.
The numbers we write are made up of algorithms, (1, 2, 3, 4, etc) called arabic algorithms, to distinguish them from roman algorithms (I, II, III, IV, etc)
The Arabs popularise these algorithms, but their origin goes back to the phenecian merchant that used them to count and do their commercial contability.
Have you ever asked why 1 is “one” and 2 is “two”? What is the logic that exist in the arabic algorithms?
Best Hiking Gear
Don’t waste your money on gear that’s no good; I’ve already done that for you! Here’s my trail-tested best hiking gear list, updated regularly. I only recommend hiking gear that I’ve used over hundreds of miles. I don’t post any paid reviews and don’t waste your time with gear that I try but isn’t up to snuff.
No Slang.com is a translator similar to those offered by Google or AltaVista with one major difference: We don't translate language, we translate slang and acronyms.
You know, those weird acronyms your kids use; things like lol, idk, rotflmfao, bbiab, and those stupid alternate spellings like kewl, dood, ur, and thanx. We hate those, and have made it our life's goal to educate people about proper grammar.