Current_Running_Tasks_and_Server_Info.sql

/*

This script shows current status and activity for currently running tasks on
the SQL Server. It returns 6 recordsets, in the following order:

1: General server-wide information
2: Database files, usage, disk space
3: Connections by SPID including CPU and I/O totals
4: Running queries, memory use/grant, query plan
5: Open transactions, type, isolation level
6: Locks by object, partition, including size, wait type, blocking info.

Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
http://creativecommons.org/licenses/by/4.0/

Source:  http://sqlsunday.com/downloads/
Version: 2016-10-20

DISCLAIMER: This script does not make any modifications to the server, except
            for creating three temporary functions in tempdb, used to format
        values in a user-friendly manner. However, the script may not be
        suitable to run in a production environment. I cannot assume any
        responsibility regarding the accuracy of the output information,
        performance impacts on your server, or any other consequence. If
        your juristiction does not allow for this kind of waiver/disclaimer,
        or if you do not accept these terms, you are NOT allowed to store,
        distribute or use this code in any way.

*/

USE tempdb;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SET DEADLOCK_PRIORITY LOW;
SET STATISTICS XML OFF;
SET LOCK_TIMEOUT 500;
SET NOCOUNT ON;

DECLARE @hasPermissions bit=HAS_PERMS_BY_NAME(NULL, NULL, 'VIEW SERVER STATE');
IF (@hasPermissions=0) BEGIN;
    RAISERROR('This script requires VIEW SERVER STATE permissions.', 16, 1);
    RETURN;
END;

SET @hasPermissions=HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'CREATE FUNCTION')
IF (@hasPermissions=0) BEGIN;
    RAISERROR('This script requires CREATE FUNCTION permissions in tempdb.', 16, 1);
    RETURN;
END;

-------------------------------------------------------------------------------








BEGIN TRANSACTION;
BEGIN TRY;

-------------------------------------------------------------------------------
--- Temporary functions, used to beautify numbers, bits&bytes and dates.
-------------------------------------------------------------------------------

IF (NOT EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_friendly_age'))
    EXEC('
CREATE FUNCTION dbo.fn_friendly_age (
    @from	datetime,
    @to	datetime)
RETURNS varchar(20)
WITH SCHEMABINDING
AS

BEGIN
    DECLARE @age varchar(20)

    IF (@from IS NOT NULL AND @to IS NOT NULL)
        SET @age=(CASE
            WHEN DATEDIFF(ss, @from, @to)<1
            THEN ''       ''+STR(DATEDIFF(ms, @from, @to), 10, 0)+'' ms.''

            WHEN DATEDIFF(ss, @from, @to)<180
            THEN ''        ''+STR(0.001*DATEDIFF(ms, @from, @to), 10, 1)+'' s.''

            WHEN DATEDIFF(ss, @from, @to)<24*3600
            THEN ''     ''+SUBSTRING(CONVERT(varchar(20), DATEADD(ss, DATEDIFF(ss, @from, @to), 0), 120), 12, 8)

            WHEN DATEDIFF(dd, @from, @to)<3
            THEN STR(FLOOR(1.0*DATEDIFF(ss, @from, @to)/(3600*24)), 3, 0)+''d+''+
                SUBSTRING(CONVERT(varchar(20), DATEADD(ss, DATEDIFF(ss, @from, @to), 0), 120), 12, 5)+''  ''

            ELSE STR(FLOOR(1.0*DATEDIFF(ss, @from, @to)/(3600*24)), 3, 0)+''d ''+
                REPLICATE('' '', 5)+''  ''
            END)

    RETURN @age
END');

IF (NOT EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_format_number'))
    EXEC('
CREATE FUNCTION dbo.fn_format_number(
    @number		numeric(28, 8),
    @prec		tinyint)
RETURNS varchar(100)
WITH SCHEMABINDING
AS

BEGIN
    DECLARE @out varchar(100), @offset int=(CASE WHEN @prec=0 THEN 0 ELSE @prec+1 END)

    SET @out=REVERSE(LTRIM(ISNULL(STR(@number, 20, @prec), '''')))
    WHILE (@offset<LEN(@out)) BEGIN
        IF (SUBSTRING(@out, @offset, 1)!=''.'') BEGIN
            SET @out=LEFT(@out, @offset)+'' ''+SUBSTRING(@out, @offset+1, LEN(@out))
            SET @offset=@offset+1
        END
        SET @offset=@offset+3
    END
    SET @out=LTRIM(REVERSE(@out))
    IF (LEN(@out)<15)
        SET @out=NULLIF(RIGHT(REPLICATE('' '', 15+@prec)+@out, 15+@prec), '''')

    RETURN @out
END');

IF (NOT EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_friendly_size'))
    EXEC('
CREATE FUNCTION dbo.fn_friendly_size (
    @bytes	bigint)
RETURNS varchar(20)
WITH SCHEMABINDING
AS

BEGIN;
    DECLARE @size varchar(20), @k bigint=1024;

    IF (@bytes IS NOT NULL)
        SET @size=(CASE
            --WHEN @bytes<4*@k
            --THEN dbo.fn_format_number(1.0*@bytes, 0)+'' B''

            WHEN @bytes<4*@k*@k
            THEN dbo.fn_format_number(1.0*@bytes/@k, 1)+'' kB''

            WHEN @bytes<4*@k*@k*@k
            THEN dbo.fn_format_number(1.0*@bytes/@k/@k, 2)+'' MB''

            WHEN @bytes<4*@k*@k*@k*@k
            THEN dbo.fn_format_number(1.0*@bytes/@k/@k/@k, 2)+'' GB''

            ELSE dbo.fn_format_number(1.0*@bytes/@k/@k/@k/@k, 2)+'' TB''
            END);

    RETURN @size;
END;');



-------------------------------------------------------------------------------
--- Gather information on database files and objects/indexes
-------------------------------------------------------------------------------

DECLARE @sql varchar(max), @sql2 varchar(max), @sql3 varchar(max),
    @sql4 varchar(max), @name sysname, @virtual_machine_type int,
    @version varchar(4), @physical_memory bigint, @virtual_memory bigint,
    @gb bigint=1024*1024*1024, @mb bigint=1024*1024,
    @max_server_memory bigint, @min_server_memory bigint, @max_degree_of_parallelism tinyint;

SET @version=LEFT(CAST(SERVERPROPERTY('ProductVersion') AS varchar(10)), 4);
IF (@version LIKE '9%') SET @version=LEFT('0'+@version, 4);

SELECT @max_server_memory=CAST([value] AS bigint)*1024*1024 FROM sys.configurations WHERE [name]='max server memory (MB)';
SELECT @min_server_memory=CAST([value] AS bigint)*1024*1024 FROM sys.configurations WHERE [name]='min server memory (MB)';
SELECT @max_degree_of_parallelism=CAST([value] AS tinyint)  FROM sys.configurations WHERE [name]='max degree of parallelism';

IF (@version<'11.0')
    EXECUTE sp_executesql N'
        SELECT @physical_memory=physical_memory_in_bytes,
            @virtual_memory=virtual_memory_in_bytes
        FROM sys.dm_os_sys_info',
        N'@physical_memory bigint OUTPUT, @virtual_memory bigint OUTPUT',
        @physical_memory=@physical_memory OUTPUT,
        @virtual_memory=@virtual_memory OUTPUT;

IF (@version>='11.0')
    EXECUTE sp_executesql N'
        SELECT @physical_memory=physical_memory_kb*1024,
            @virtual_memory=virtual_memory_kb*1024
        FROM sys.dm_os_sys_info',
        N'@physical_memory bigint OUTPUT, @virtual_memory bigint OUTPUT',
        @physical_memory=@physical_memory OUTPUT,
        @virtual_memory=@virtual_memory OUTPUT;

BEGIN TRY;
    EXECUTE sp_executesql N'
        SELECT @virtual_machine_type=virtual_machine_type
        FROM sys.dm_os_sys_info',
        N'@virtual_machine_type int OUTPUT',
        @virtual_machine_type=@virtual_machine_type OUTPUT;
END TRY
BEGIN CATCH;
    PRINT 'This version of SQL Server does not provide virtual_machine_type.';
END CATCH;

-------------------------------------------------------------------------------

DECLARE @block_chain TABLE (
    spid				int NOT NULL,
    blocked_by			varchar(max) NOT NULL,
    PRIMARY KEY CLUSTERED (spid)
);

DECLARE @blocking TABLE (
    spid				int NOT NULL,
    blocking			varchar(max) NOT NULL,
    PRIMARY KEY CLUSTERED (spid)
);

DECLARE @locks TABLE (
    spid				int NOT NULL,
    tran_id				bigint NOT NULL,
    lock_ord			int NOT NULL,
    mode				varchar(255) NOT NULL,
    [database]			varchar(255) NOT NULL,
    [object_id]			int NOT NULL,
    [object_name]		varchar(255) NULL,
    index_id			int NOT NULL,
    index_type			tinyint NULL,
    index_name			varchar(255) NULL,
    index_filter		varchar(max) NULL,
    object_notes		varchar(255) NULL,
    obj_rows			bigint NULL,
    partition_number	 int NOT NULL,
    partition_count		int NULL,
    partition_boundary	varchar(max) NULL,
    request_owner_type	varchar(50) NOT NULL,
    wait_type			varchar(120) NULL,
    wait_time			varchar(100) NULL,
    blkd_by_spid		int NULL,
    [count]				int NULL,
    isWaiting			bit NULL,
    isBlocking			bit NULL,
    ident				int IDENTITY(1, 1) NOT NULL,
    PRIMARY KEY CLUSTERED (spid, tran_id, [object_id], index_id, partition_number, lock_ord, ident)
);

DECLARE @tran_locks TABLE (
    resource_database_id int NOT NULL,
    request_session_id	int NOT NULL,
    request_owner_id	bigint NULL,
    request_mode		nvarchar(120) NOT NULL,
    request_status		nvarchar(120) NOT NULL,
    resource_type		nvarchar(120) NOT NULL,
    request_owner_type	nvarchar(120) NULL,
    lock_owner_address	varbinary(8) NOT NULL,
    resource_associated_entity_id bigint NULL,
    ord					int NOT NULL,
    PRIMARY KEY CLUSTERED (request_session_id, ord)
);

INSERT INTO @tran_locks
SELECT resource_database_id, request_session_id, request_owner_id, request_mode, request_status,
    resource_type, request_owner_type, lock_owner_address, resource_associated_entity_id,
    ROW_NUMBER() OVER (PARTITION BY request_session_id ORDER BY lock_owner_address) AS ord
FROM sys.dm_tran_locks WITH (NOLOCK);

DECLARE @tran_session_transactions TABLE (
    transaction_id		bigint NOT NULL,
    session_id			int NOT NULL,
    is_local			bit NOT NULL,
    PRIMARY KEY CLUSTERED (transaction_id)
);

INSERT INTO @tran_session_transactions
SELECT transaction_id, session_id, is_local
FROM sys.dm_tran_session_transactions WITH (NOLOCK);

DECLARE @exec_sessions TABLE (
    session_id			int NOT NULL,
    [program_name]		nvarchar(256) NULL,
    login_name			nvarchar(256) NOT NULL,
    [status]			nvarchar(60) NOT NULL,
    last_request_start_time datetime NOT NULL,
    cpu_time			int NOT NULL,
    memory_usage		int NOT NULL,
    reads				bigint NOT NULL,
    writes				bigint NOT NULL,
    logical_reads		bigint NOT NULL,
    flags				varchar(200) NOT NULL,
    client_interface_name nvarchar(64) NULL,
    PRIMARY KEY CLUSTERED (session_id)
);

INSERT INTO @exec_sessions
SELECT session_id, [program_name], login_name, [status], last_request_start_time, cpu_time,
    memory_usage, reads, writes, logical_reads,
      'arithabort '+(CASE [arithabort] WHEN 1 THEN 'ON' ELSE 'OFF' END)+
    ', quot.id '+(CASE [quoted_identifier] WHEN 1 THEN 'ON' ELSE 'OFF' END)+
    (CASE [ansi_warnings] WHEN 0 THEN ', ansi warn OFF' ELSE '' END)+
    (CASE [ansi_padding] WHEN 0 THEN ', ansi pad OFF' ELSE '' END)+
    (CASE [ansi_nulls] WHEN 0 THEN ', nulls OFF' ELSE '' END)+
    (CASE [concat_null_yields_null] WHEN 0 THEN ', conc.null OFF' ELSE '' END) AS flags,
    LEFT(client_interface_name, CHARINDEX(' ', client_interface_name+' ')-1)
FROM sys.dm_exec_sessions WITH (NOLOCK);

DECLARE @jobs TABLE (
    job_id				uniqueidentifier NOT NULL,
    job_name			sysname NOT NULL,
    step_id				int NOT NULL,
    step_name			sysname NOT NULL,
    search_name			varchar(max) NOT NULL,
    PRIMARY KEY CLUSTERED (job_id, step_id)
);

INSERT INTO @jobs
SELECT j.job_id, j.name AS job_name, js.step_id, js.step_name, ' 0x'+
    SUBSTRING(CAST(j.job_id AS varchar(max)),  7,  2)+
    SUBSTRING(CAST(j.job_id AS varchar(max)),  5,  2)+
    SUBSTRING(CAST(j.job_id AS varchar(max)),  3,  2)+
    SUBSTRING(CAST(j.job_id AS varchar(max)),  1,  2)+
    SUBSTRING(CAST(j.job_id AS varchar(max)), 12,  2)+
    SUBSTRING(CAST(j.job_id AS varchar(max)), 10,  2)+
    SUBSTRING(CAST(j.job_id AS varchar(max)), 17,  2)+
    SUBSTRING(CAST(j.job_id AS varchar(max)), 15,  2)+
    SUBSTRING(CAST(j.job_id AS varchar(max)), 20,  4)+
    SUBSTRING(CAST(j.job_id AS varchar(max)), 25, 12)+'%:% '+
    CAST(js.step_id AS varchar(max))+')' AS search_name
FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobsteps AS js WITH (NOLOCK) ON j.job_id=js.job_id;

DECLARE @exec_requests TABLE (
    session_id			int NOT NULL,
    request_id			int NOT NULL,
    database_id			smallint NOT NULL,
    transaction_isolation_level smallint NOT NULL,
    percent_complete	real NOT NULL,
    total_elapsed_time	int NOT NULL,
    command				varchar(32) NOT NULL,
    statement_start_offset int NULL,
    statement_end_offset int NULL,
    plan_handle			varbinary(64) NULL,
    _id                 int IDENTITY(1, 1) NOT NULL,
    PRIMARY KEY CLUSTERED (session_id, request_id, _id)
);

INSERT INTO @exec_requests (session_id, request_id, database_id, transaction_isolation_level, percent_complete,
    total_elapsed_time, command, statement_start_offset, statement_end_offset, plan_handle)
SELECT session_id, request_id, database_id, transaction_isolation_level, percent_complete,
    total_elapsed_time, command, statement_start_offset, statement_end_offset, plan_handle
FROM sys.dm_exec_requests WITH (NOLOCK);

DECLARE @exec_connections TABLE (
    session_id			int NOT NULL,
    client_net_address	varchar(48) NULL,
    client_tcp_port		int NULL,
    local_net_address	varchar(48) NULL,
    auth_scheme			nvarchar(80) NOT NULL,
    connect_time		datetime NOT NULL,
    num_reads			int NULL,
    num_writes			int NULL,
    most_recent_sql_handle varbinary(64),
    PRIMARY KEY CLUSTERED (session_id)
);

INSERT INTO @exec_connections
SELECT session_id, client_net_address, client_tcp_port, local_net_address, auth_scheme,
    connect_time, num_reads, num_writes, most_recent_sql_handle
FROM sys.dm_exec_connections WITH (NOLOCK)
WHERE endpoint_id!=0 AND session_id IS NOT NULL;

DECLARE @os_tasks TABLE (
    session_id          int NOT NULL,
    scheduler_id        int NOT NULL,
    PRIMARY KEY CLUSTERED (session_id, scheduler_id)
);

INSERT INTO @os_tasks
SELECT DISTINCT session_id, scheduler_id
FROM sys.dm_os_tasks
WHERE session_id IS NOT NULL AND
      scheduler_id IS NOT NULL;

DECLARE @tran_active_transactions TABLE (
    transaction_id		bigint NOT NULL,
    transaction_begin_time	datetime NOT NULL,
    transaction_type	int NOT NULL,
    transaction_state	int NOT NULL,
    PRIMARY KEY CLUSTERED (transaction_id)
);

INSERT INTO @tran_active_transactions
SELECT transaction_id, transaction_begin_time, transaction_type, transaction_state
FROM sys.dm_tran_active_transactions WITH (NOLOCK);

DECLARE @os_waiting_tasks TABLE (
    session_id			int NULL,
    blocking_session_id	int NULL,
    waiting_task_address varbinary(8) NOT NULL,
    wait_type			nvarchar(120) NULL,
    wait_duration_ms	bigint NULL,
    resource_address	varbinary(8) NULL,
    exec_context_id		int NULL,
    dupl				int NOT NULL,
    PRIMARY KEY CLUSTERED (waiting_task_address, dupl)
);

INSERT INTO @os_waiting_tasks
SELECT session_id, blocking_session_id, waiting_task_address,
    wait_type, wait_duration_ms, resource_address, exec_context_id,
    ROW_NUMBER() OVER (
        PARTITION BY waiting_task_address
        ORDER BY wait_duration_ms, resource_address) AS dupl
FROM sys.dm_os_waiting_tasks WITH (NOLOCK);

DECLARE @exec_query_memory_grants TABLE (
    session_id			int NOT NULL,
    request_id			int NOT NULL,
    requested_mb		numeric(16, 3) NOT NULL,
    granted_mb			numeric(16, 3) NULL,
    minimum_required_mb	numeric(16, 3) NOT NULL,
    used_mb				numeric(16, 3) NOT NULL,
    max_used_mb			numeric(16, 3) NOT NULL,
    ideal_mb			numeric(16, 3) NOT NULL,
    est_query_cost		numeric(16, 1) NOT NULL,
    PRIMARY KEY CLUSTERED (session_id, request_id)
);

INSERT INTO @exec_query_memory_grants
SELECT session_id, request_id,
    1.0*requested_memory_kb/1024 AS requested_mb,
    1.0*granted_memory_kb/1024 AS granted_mb,
    1.0*required_memory_kb/1024 AS minimum_required_mb,
    ISNULL(1.0*used_memory_kb/1024, 0.0) AS used_mb,
    ISNULL(1.0*max_used_memory_kb/1024, 0.0) AS max_used_mb,
    1.0*ideal_memory_kb/1024 AS ideal_mb,
    query_cost AS est_query_cost
FROM sys.dm_exec_query_memory_grants WITH (NOLOCK);

DECLARE @dm_server_services TABLE (
    _id                 tinyint IDENTITY(1, 1) NOT NULL,
    servicename         nvarchar(512) NOT NULL,
    startup_type_desc   nvarchar(512) NOT NULL,
    status_desc         nvarchar(512) NOT NULL,
    service_account     nvarchar(512) NOT NULL,
    cluster_nodename    nvarchar(512) NULL,
    PRIMARY KEY CLUSTERED (_id)
);

BEGIN TRY;
    INSERT INTO @dm_server_services
    SELECT servicename, startup_type_desc, status_desc, service_account, cluster_nodename
    FROM sys.dm_server_services;
END TRY
BEGIN CATCH;
    PRINT 'sys.dm_server_services is not supported on this version of SQL Server.';
END CATCH;

DECLARE @database_procedures TABLE (
    database_id			int NOT NULL,
    [object_id]			int NOT NULL,
    [name]				varchar(255) NOT NULL,
    PRIMARY KEY CLUSTERED (database_id, [object_id])
);

DECLARE @database_objects TABLE (
    database_id			int NOT NULL,
    [object_id]			int NOT NULL,
    index_id			int NOT NULL,
    hobt_id				bigint NOT NULL,
    [name]				varchar(255) NOT NULL,
    index_type			tinyint NULL,
    index_name			varchar(255) NULL,
    partition_number	int NOT NULL,
    [rows]				bigint NULL,
    [type_desc]			varchar(255) NOT NULL,
    is_clustered		bit NULL,
    index_filter		varchar(max) NULL,
    PRIMARY KEY CLUSTERED (database_id, [object_id], hobt_id)
);

DECLARE @files TABLE (
    database_id			int NOT NULL,
    [file_id]			int NOT NULL,
    filetype			varchar(20) NOT NULL,
    [name]				varchar(255) NOT NULL,
    drive				char(3) NOT NULL,
    [filegroup]			varchar(255) NULL,
    size_mb				numeric(18, 2) NOT NULL,
    autogrow			numeric(18, 2) NULL,
    is_percent_growth	bit NOT NULL,
    max_size_mb			numeric(18, 2) NULL,
    used_size_mb		numeric(18, 2) NULL,
    total_bytes			bigint NULL,
    available_bytes		bigint NULL,
    file_system_type	varchar(100) NULL,
    primary_replica     sysname NULL,
    [availability_mode] tinyint NULL,
    suspend_state       varchar(255) NULL,
    queue_kb            bigint NULL,
    rpo                 bigint NULL,
    PRIMARY KEY CLUSTERED (database_id, [name])
);

DECLARE @parts TABLE (
    database_id			int NOT NULL,
    [object_id]			int NOT NULL,
    index_id			int NOT NULL,
    hobt_id				bigint NULL,
    boundary_id			int NOT NULL,
    boundary			varchar(max) NULL,
    [rows]				int NULL,
    PRIMARY KEY CLUSTERED (database_id, [object_id], index_id, boundary_id)
);

DECLARE #cur CURSOR FOR
    SELECT name,
        --- Databases, schemas, tables, number of rows
        'SELECT '+CAST(database_id AS varchar(max))+', o.[object_id], ISNULL(ix.index_id, 0), p.hobt_id,
            s.name+''.''+o.name, ix.[type] AS index_type, ix.name AS index_name, p.partition_number, p.[rows], o.type_desc,
            (CASE WHEN cix.[object_id] IS NULL THEN 0 ELSE 1 END) AS is_clustered,
            ix.filter_definition
         FROM ['+[name]+'].sys.objects AS o WITH (NOLOCK)
         INNER JOIN ['+[name]+'].sys.partitions AS p WITH (NOLOCK) ON o.[object_id]=p.[object_id]
         INNER JOIN ['+[name]+'].sys.schemas AS s WITH (NOLOCK) ON o.[schema_id]=s.[schema_id]
         LEFT JOIN ['+[name]+'].sys.indexes AS ix WITH (NOLOCK) ON p.[object_id]=ix.[object_id] AND p.index_id=ix.index_id
         LEFT JOIN ['+[name]+'].sys.indexes AS cix WITH (NOLOCK) ON p.[object_id]=cix.[object_id] AND cix.[type]=1
        ' AS [sql],

        --- Database files
        'USE ['+[name]+'];
         SELECT '+CAST(database_id AS varchar(max))+', f.[file_id], f.type_desc AS filetype,
            f.name, UPPER(LEFT(f.physical_name, 3)) AS drive,
            ds.name AS [filegroup], 1.0*f.size*8/1024 AS size_mb,
            (CASE WHEN f.is_percent_growth=1 THEN 1.0*f.growth
                ELSE 1.0*f.growth*8/1024 END) AS autogrow,
            f.is_percent_growth, ROUND(1.0*NULLIF(f.max_size, -1)*8/1024, 1) AS max_size_mb,
            1.0*FILEPROPERTY(f.name, ''SpaceUsed'')*8/1024 AS used_size_mb, '+
            (CASE WHEN @version>'10.5' OR
                @version='10.5' AND CAST(SERVERPROPERTY('ProductLevel') AS varchar(10))>='SP1' THEN '
            v.total_bytes, v.available_bytes, v.file_system_type' ELSE '
            NULL AS total_bytes, NULL AS available_bytes, NULL AS file_system_type' END)+', NULL, NULL, NULL, NULL, NULL
         FROM ['+name+'].sys.database_files AS f WITH (NOLOCK)
         LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK) ON f.data_space_id=ds.data_space_id'+
            (CASE WHEN @version>'10.5' OR
                   @version='10.5' AND CAST(SERVERPROPERTY('ProductLevel') AS varchar(10))>='SP1' THEN '
         CROSS APPLY sys.dm_os_volume_stats('+CAST(database_id AS varchar(max))+', f.[file_id]) AS v' ELSE '' END)+
                --- In-memory OLTP tables:
                (CASE WHEN @version>='12.0' THEN '
                 UNION ALL
         SELECT '+CAST(database_id AS varchar(max))+', -1 AS [file_id], ''IN-MEMORY'' AS filetype,
            '''' AS [name], ''RAM'' AS drive,
            '''' AS [filegroup], 1.0*SUM(xtp.allocated_bytes)/(1024*1024) AS size_mb,
            NULL AS autogrow,
            0 AS is_percent_growth, NULL AS max_size_mb,
            1.0*SUM(xtp.used_bytes)/(1024*1024) AS used_size_mb,
            NULL AS total_bytes, NULL AS available_bytes, NULL AS file_system_type, NULL, NULL, NULL, NULL, NULL
         FROM sys.dm_db_xtp_memory_consumers AS xtp
         HAVING COUNT(*)>0' ELSE '' END) AS sql2,

        --- Partitioning schemes, functions, boundaries
        'WITH spc (data_space_id, function_id, pf_name, boundary_id, boundary_value_on_right, [value])
         AS (	SELECT ps.data_space_id, pf.function_id, pf.name AS pf_name, val.boundary_id, pf.boundary_value_on_right,
                (CASE SQL_VARIANT_PROPERTY(val.[value], ''BaseType'')
                    WHEN ''date'' THEN LEFT(CONVERT(varchar(max), val.[value], 120), 10)
                    WHEN ''datetime'' THEN CONVERT(varchar(max), val.[value], 120)
                    ELSE CAST(val.[value] AS varchar(max))
                END) AS [value]
            FROM ['+[name]+'].sys.partition_range_values AS val WITH (NOLOCK)
            INNER JOIN ['+[name]+'].sys.partition_functions AS pf WITH (NOLOCK) ON val.function_id = pf.function_id
            INNER JOIN ['+[name]+'].sys.partition_schemes AS ps WITH (NOLOCK) ON pf.function_Id=ps.function_id),

             rng (pf_name, data_space_id, boundary_id, lower_rng, upper_rng)
         AS (	SELECT	ISNULL(a.pf_name, b.pf_name),
                ISNULL(a.data_space_id, b.data_space_id),
                ISNULL(a.boundary_id+1, b.boundary_id),
                a.[value]+(CASE
                    WHEN a.boundary_value_on_right=1 THEN ''<=''
                    WHEN a.boundary_value_on_right=0 THEN ''<'' END),
                (CASE	WHEN b.boundary_value_on_right=1 THEN ''<''+b.[value]
                    WHEN b.boundary_value_on_right=0 THEN ''<=''+b.[value] END)
            FROM spc AS a
            FULL JOIN spc AS b ON
                a.function_id=b.function_id AND
                a.boundary_id+1=b.boundary_id)
        SELECT '+CAST(database_id AS varchar(max))+', ix.[object_id], ix.index_id, p.hobt_id, ISNULL(rng.boundary_id, 0),
            ISNULL(rng.lower_rng, '''')+c.name+ISNULL(rng.upper_rng, '''') AS boundary, p.[rows]
        FROM rng
        INNER JOIN ['+[name]+'].sys.indexes AS ix WITH (NOLOCK) ON ix.data_space_id=rng.data_space_id
        INNER JOIN ['+[name]+'].sys.index_columns AS ic WITH (NOLOCK) ON ix.[object_id]=ic.[object_id] AND ix.index_id=ic.index_id AND ic.partition_ordinal>0
        INNER JOIN ['+[name]+'].sys.columns AS c WITH (NOLOCK) ON ic.[object_id]=c.[object_id] AND ic.column_id=c.column_id
        LEFT  JOIN ['+[name]+'].sys.partitions AS p WITH (NOLOCK) ON ix.[object_id]=p.[object_id] AND ix.index_id=p.index_id AND rng.boundary_id=p.partition_number
        ' AS sql3,

        --- Databases, schemas, tables
        'SELECT '+CAST(database_id AS varchar(max))+', o.[object_id], s.name+''.''+o.name
         FROM ['+[name]+'].sys.objects AS o WITH (NOLOCK)
         INNER JOIN ['+[name]+'].sys.schemas AS s WITH (NOLOCK) ON o.[schema_id]=s.[schema_id]
         WHERE o.[type] NOT IN (''U'', ''S'')
        ' AS sql4

    FROM sys.databases WITH (NOLOCK)
    WHERE state_desc='ONLINE' AND database_id IN (
        SELECT resource_database_id
        FROM @tran_locks);
OPEN #cur;

FETCH NEXT FROM #cur INTO @name, @sql, @sql2, @sql3, @sql4;
WHILE (@@FETCH_STATUS=0) BEGIN;
    BEGIN TRY;
        INSERT INTO @database_objects EXEC(@sql);
    END TRY
    BEGIN CATCH;
        PRINT @name+'/schema: '+ERROR_MESSAGE();
    END CATCH;

    BEGIN TRY;
        INSERT INTO @files EXEC(@sql2);
    END TRY
    BEGIN CATCH;
        PRINT @name+'/database files: '+ERROR_MESSAGE();
    END CATCH;

    BEGIN TRY;
        INSERT INTO @parts EXEC(@sql3);
    END TRY
    BEGIN CATCH;
        PRINT @name+'/partitions: '+ERROR_MESSAGE();
    END CATCH;

    BEGIN TRY;
        INSERT INTO @database_procedures EXEC(@sql4);
    END TRY
    BEGIN CATCH;
        PRINT @name+'/tables: '+ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM #cur INTO @name, @sql, @sql2, @sql3, @sql4;
END;

CLOSE #cur;
DEALLOCATE #cur;


-------------------------------------------------------------------------------
--- Availability groups stuff:

UPDATE f SET f.primary_replica=ags.primary_replica, f.[availability_mode]=ar.[availability_mode], f.suspend_state=x.suspend_state, f.queue_kb=x.queue_kb, f.rpo=x.rpo
FROM @files AS f
INNER JOIN sys.databases AS db ON f.database_id=db.database_id
INNER JOIN sys.availability_replicas AS ar ON db.replica_id=ar.replica_id
--INNER JOIN sys.availability_groups AS ag ON ar.group_id=ag.group_id
INNER JOIN sys.dm_hadr_availability_group_states AS ags ON ar.group_id=ags.group_id
LEFT JOIN (
    SELECT database_id, group_id, (CASE MAX(suspend_reason_desc)
                WHEN 'SUSPEND_FROM_USER' THEN 'A user manually suspended data movement'
                WHEN 'SUSPEND_FROM_PARTNER' THEN 'The database replica is suspended after a forced failover'
                WHEN 'SUSPEND_FROM_REDO' THEN 'An error occurred during the redo phase'
                WHEN 'SUSPEND_FROM_APPLY' THEN 'An error occurred when writing the log to file (see error log)'
                WHEN 'SUSPEND_FROM_CAPTURE' THEN 'An error occurred while capturing log on the primary replica'
                WHEN 'SUSPEND_FROM_RESTART' THEN 'The database replica was suspended before the database was restarted (see error log)'
                WHEN 'SUSPEND_FROM_UNDO' THEN 'An error occurred during the undo phase (see error log)'
                WHEN 'SUSPEND_FROM_REVALIDATION' THEN 'Log change mismatch is detected on reconnection (see error log)'
                WHEN 'SUSPEND_FROM_XRF_UPDATE' THEN 'Unable to find the common log point (see error log)'
                ELSE MAX(suspend_reason_desc)
                END) AS suspend_state, MAX(log_send_queue_size) AS queue_kb, DATEDIFF(second, MIN(last_commit_time), MAX(last_commit_time)) AS rpo
    FROM sys.dm_hadr_database_replica_states
    GROUP BY database_id, group_id) AS x ON f.database_id=x.database_id AND ar.group_id=x.group_id;

-------------------------------------------------------------------------------
--- Beautify names of temp tables:
UPDATE @database_objects
SET [name]=SUBSTRING(LEFT([name], CHARINDEX(REPLICATE('_', 8), [name]+REPLICATE('_', 8))-1), 5, LEN([name]))
WHERE database_id=(SELECT database_id FROM sys.databases WITH (NOLOCK) WHERE [name]='tempdb') AND
    REPLACE([name], '_', '.') LIKE 'dbo.#%'+REPLICATE('.', 8)+'[0-9A-F][0-9A-F]%[0-9A-F][0-9A-F]';

--- Beautify names of temp table indexes:
UPDATE @database_objects
SET index_name='Primary key'
WHERE database_id=(SELECT database_id FROM sys.databases WITH (NOLOCK) WHERE [name]='tempdb') AND
    LEFT(index_name, 12) LIKE 'PK__'+LEFT([name], 8);

UPDATE @database_objects
SET index_name=REPLACE(RTRIM(REPLACE(LEFT(index_name, LEN(index_name)-16), '_', ' ')), ' ', '_')
WHERE database_id=(SELECT database_id FROM sys.databases WITH (NOLOCK) WHERE [name]='tempdb') AND
    REPLACE(index_name, '_', '.') LIKE '%#%'+REPLICATE('[0-9A-F]', 16);



-------------------------------------------------------------------------------
--- Chains of spids that are being blocked, per spid:
WITH blocks (spid, blocked_by, ord)
AS (
    SELECT DISTINCT session_id AS spid, blocking_session_id AS blocked_by,
        DENSE_RANK() OVER (PARTITION BY blocking_session_id ORDER BY session_id) AS ord
    FROM @os_waiting_tasks
    WHERE blocking_session_id IS NOT NULL AND session_id!=blocking_session_id),

     list (spid, blocking, list, ord)
AS (
    SELECT blocked_by AS spid, spid AS blocking, CAST(spid AS varchar(max)) AS list, ord
    FROM blocks
    WHERE ord=1
    UNION ALL
    SELECT c.spid, c.blocking, CAST(c.list+', '+CAST(b.spid AS varchar(max)) AS varchar(max)) AS list, b.ord
    FROM list AS c
    INNER JOIN blocks AS b ON
        c.spid=b.blocked_by AND
        c.ord+1=b.ord)

INSERT INTO @blocking (spid, blocking)
SELECT spid, list AS blocking
FROM list AS l
WHERE ord=(SELECT MAX(ord) FROM list WHERE spid=l.spid);



--- Spids that are blocking other spids (reverse-lookup)
WITH blocks (spid, blocked_by)
AS (
    SELECT DISTINCT session_id AS spid, blocking_session_id AS blocked_by
    FROM @os_waiting_tasks
    WHERE blocking_session_id IS NOT NULL AND session_id!=blocking_session_id),

     chain (spid, blocked_by, chain, lvl)
AS (
    SELECT spid, blocked_by, CAST(blocked_by AS varchar(max)) AS chain, 1 AS lvl
    FROM blocks
    UNION ALL
    SELECT c.spid, b.blocked_by, CAST(c.chain+' <- '+CAST(b.blocked_by AS varchar(max)) AS varchar(max)) AS chain, lvl+1
    FROM chain AS c
    INNER JOIN blocks AS b ON b.spid=c.blocked_by)

INSERT INTO @block_chain (spid, blocked_by)
SELECT spid, MAX('<- '+chain)
FROM chain AS c
WHERE lvl=(SELECT MAX(lvl) FROM chain WHERE spid=c.spid)
GROUP BY spid;



--- Compile locks
INSERT INTO @locks
SELECT
    tl.request_session_id AS spid,
    NULLIF(tl.request_owner_id, 0) AS tran_id,
    DENSE_RANK() OVER (
        PARTITION BY tl.request_session_id, tl.request_owner_id, obj.[object_id], obj.index_id, obj.partition_number
        ORDER BY (CASE WHEN tl.request_mode='GRANT' THEN 1 ELSE 2 END), tl.request_mode
        ) AS lock_ord,
    ISNULL(NULLIF(tl.request_status, 'GRANT')+' ', '')+tl.request_mode+' '+(CASE tl.resource_type
        WHEN 'DATABASE' THEN 'db'
        WHEN 'FILE' THEN 'file'
        WHEN 'OBJECT' THEN ISNULL(LOWER(REPLACE(REPLACE(REPLACE(obj.type_desc, '_', ' '), 'SQL ', ''), 'USER ', '')), 'obj')
        WHEN 'PAGE' THEN 'page'
        WHEN 'KEY' THEN 'key'
        WHEN 'EXTENT' THEN 'extent'
        WHEN 'RID' THEN 'RID'
        WHEN 'APPLICATION' THEN 'app'
        WHEN 'METADATA' THEN 'metadata'
        WHEN 'HOBT' THEN 'HoBT'
        WHEN 'ALLOCATION_UNIT' THEN 'alloc unit'
        END) AS mode,
    db.name AS [database], ISNULL(obj.[object_id], 0), obj.name AS [object_name],
    ISNULL(obj.index_id, 0), obj.index_type, obj.index_name, obj.index_filter,
    (CASE	WHEN obj.type_desc='USER_TABLE' AND obj.is_clustered=1 THEN 'Clustered'
        WHEN obj.type_desc='USER_TABLE' THEN 'Heap'
        WHEN obj.type_desc='VIEW' AND obj.is_clustered=1 THEN 'Indexed view'
        ELSE '' END) AS object_notes,
    obj.[rows] AS obj_rows,
    ISNULL(obj.partition_number, 0), (SELECT MAX(partition_number)
            FROM @database_objects AS sub
            WHERE sub.database_id=obj.database_id AND
                sub.[object_id]=obj.[object_id] AND
                index_id=obj.index_id
            ) AS partition_count,
    parts.boundary AS partition_boundary,
    (CASE tl.request_owner_type
        WHEN 'TRANSACTION' THEN 'Trans'			    --- The request is owned by a transaction.
        WHEN 'CURSOR' THEN 'Cursor'			        --- The request is owned by a cursor.
        WHEN 'SESSION' THEN 'Session'			    --- The request is owned by a user session.
        WHEN 'SHARED_TRANSACTION_WORKSPACE' THEN 'Tran ws (shared)' --- The request is owned by the shared part of the transaction workspace.
        WHEN 'EXCLUSIVE_TRANSACTION_WORKSPACE' THEN 'Tran ws (excl)' --- The request is owned by the exclusive part of the transaction workspace.
        WHEN 'NOTIFICATION_OBJECT' THEN 'Internal'	--- The request is owned by an internal SQL Server component. This component has requested the lock manager to notify it when another component is waiting to take the lock. The FileTable feature is a component that uses this value.
        END) AS request_owner_type,
    wt.wait_type,
    dbo.fn_friendly_age(DATEADD(ms, 0-wt.wait_duration_ms, GETDATE()), GETDATE()) AS wait_time,
    wt.blocking_session_id AS blkd_by_spid,
    COUNT(*) AS [count],
    (CASE WHEN tl.request_status='WAIT' THEN 1 ELSE 0 END) AS isWaiting,
    0 AS isBlocking
FROM @tran_locks AS tl
INNER HASH JOIN sys.databases AS db WITH (NOLOCK) ON tl.resource_database_id=db.database_id
INNER HASH JOIN @tran_session_transactions AS st ON tl.request_owner_id=st.transaction_id
LEFT HASH JOIN @os_waiting_tasks AS wt ON tl.lock_owner_address=wt.resource_address
LEFT HASH JOIN @database_objects AS obj ON tl.resource_database_id=obj.database_id AND tl.resource_associated_entity_id IN (obj.[object_id], obj.hobt_id)
LEFT HASH JOIN @parts AS parts ON obj.database_id=parts.database_id AND parts.hobt_id=obj.hobt_id
WHERE tl.request_session_id!=@@SPID
GROUP BY tl.request_session_id, tl.request_owner_id, tl.request_status, tl.request_mode, tl.resource_type, db.name, obj.[object_id], obj.index_id, obj.index_type,
    obj.index_name, obj.index_filter, obj.type_desc, obj.[object_id], obj.database_id, obj.is_clustered, obj.name, obj.[rows],
    obj.partition_number, parts.boundary, tl.request_owner_type, wt.wait_type, wt.wait_duration_ms, wt.blocking_session_id;


--- Update @locks.isBlocking column.
UPDATE blocking
SET blocking.isBlocking=1
FROM @locks AS blocked
INNER JOIN @locks AS blocking ON
    blocked.[database]=blocking.[database] AND
    ISNULL(blocked.[object_id], -1)=ISNULL(blocking.[object_id], -1) AND
    ISNULL(blocked.index_id, -1)=ISNULL(blocking.index_id, -1) AND
    ISNULL(blocked.partition_number, -1)=ISNULL(blocking.partition_number, -1) AND
    blocked.tran_id!=blocking.tran_id AND
    blocked.spid!=blocking.spid AND
    blocked.isWaiting=1;



SET LOCK_TIMEOUT -1;

-------------------------------------------------------------------------------
--- 1. Server properties:
-------------------------------------------------------------------------------

SELECT
    CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS varchar(128)) AS [Physical name],
    CAST(SERVERPROPERTY('ServerName') AS varchar(128)) AS [Instance name],
    (CASE CAST(SERVERPROPERTY('IsClustered') AS bit) WHEN 1 THEN ISNULL('Cluster node '+sqlsrv.cluster_nodename, 'Clustered') ELSE 'Stand-alone' END)+
    (CASE WHEN SERVERPROPERTY('IsHadrEnabled')=1 THEN ' with Availability Groups' ELSE '' END)+
    (CASE @virtual_machine_type
        WHEN 1 THEN ' on Hypervisor'
        WHEN 2 THEN ' on virtual machine'
        ELSE '' END) AS [Configuration],
    'SQL Server '+(CASE @version
        WHEN '09.0' THEN '2005'
        WHEN '10.0' THEN '2008'
        WHEN '10.5' THEN '2008 R2'
        WHEN '11.0' THEN '2012'
        WHEN '12.0' THEN '2014'
        WHEN '13.0' THEN '2016' ELSE @version END)+' '+
        REPLACE(REPLACE(REPLACE(
            CAST(SERVERPROPERTY('Edition') AS varchar(128)), ' Edition', ''),
            'Standard', 'Std'), 'Enterprise', 'Ent') AS [Product, edition],
    CAST(SERVERPROPERTY('ProductLevel') AS varchar(128))+ISNULL(', '+
        CAST(SERVERPROPERTY('ProductUpdateLevel') AS varchar(128)), '')+' ('+
        CAST(SERVERPROPERTY('ProductVersion') AS varchar(128))+')' AS [Level, CU],
    (CASE WHEN @max_server_memory/(1024*1024)>2000000 THEN ''
          ELSE
            (CASE WHEN @min_server_memory<@max_server_memory AND @min_server_memory>0
                THEN LTRIM(dbo.fn_friendly_size(@min_server_memory))+' - '
                ELSE '' END)+
            LTRIM(dbo.fn_friendly_size(@max_server_memory))+
            (CASE WHEN @max_server_memory!=@physical_memory THEN ' / ' ELSE '' END) END)+
        LTRIM(dbo.fn_friendly_size(@physical_memory)) AS [Min - max / physical mem],
--	dbo.fn_friendly_size(@virtual_memory) AS [Virtual mem],
    CAST(sysinfo.cpu_count/sysinfo.hyperthread_ratio AS varchar(10))+
        ISNULL('x'+CAST(NULLIF(sysinfo.hyperthread_ratio, 1) AS varchar(10)), '')+
        ISNULL(' ('+CAST((SELECT NULLIF(MAX(memory_node_id), 0)+1
                          FROM sys.dm_os_memory_clerks
                          WHERE memory_node_id<64) AS varchar(10))+' NUMA)', '') AS [Core count],
    ISNULL(CAST(NULLIF(@max_degree_of_parallelism, 0) AS varchar(10)), '-') AS [MaxDOP],
    dbo.fn_friendly_age(sysinfo.sqlserver_start_time, GETDATE()) AS Uptime,
    CAST(SERVERPROPERTY('Collation') AS varchar(128)) AS [Server collation],
    sqlsrv.service_account AS [Service acct],
--  sqlagt.service_account AS [Agent svc acct],
    sqlagt.status_desc+' ('+sqlagt.startup_type_desc+')' AS [SQL Server Agent]
FROM sys.dm_os_sys_info AS sysinfo WITH (NOLOCK)
LEFT JOIN @dm_server_services AS sqlsrv ON sqlsrv.servicename LIKE 'SQL Server (%'
LEFT JOIN @dm_server_services AS sqlagt ON sqlagt.servicename LIKE 'SQL Server Agent (%';



-------------------------------------------------------------------------------
--- 2. Display database file usages:
-------------------------------------------------------------------------------

SELECT
    db.name AS [Database],
    UPPER(LEFT(f.filetype, 1))+LOWER(SUBSTRING(f.filetype, 2, 100)) AS [Type],
    COALESCE(
        (CASE WHEN f.primary_replica=@@SERVERNAME THEN 'Primary AG replica' WHEN f.primary_replica IS NOT NULL THEN (CASE f.[availability_mode] WHEN 1 THEN 'Synchronous AG replica' WHEN 0 THEN 'Async AG replica' ELSE '?' END) END),
        LEFT(m.mirroring_role_desc, 1)+LOWER(SUBSTRING(m.mirroring_role_desc, 2, 100)+
        ', '+REPLACE(m.mirroring_state_desc, '_', ' '))+
        (CASE m.mirroring_safety_level WHEN 1 THEN ' (async)' WHEN 2 THEN '(sync)' ELSE '(unknown)' END), '') AS [Mirror/AG],
    SUBSTRING(
        ISNULL(', '+f.suspend_state, '')+
        ISNULL(', '+LTRIM(dbo.fn_friendly_size(NULLIF(f.queue_kb, 0)*1024))+' queue', '')+
        ISNULL(', '+LTRIM(dbo.fn_friendly_age('00:00:00', DATEADD(second, NULLIF(f.rpo, 0), '00:00:00')))+' RPO', ''),
        3, 1000) [AG state],
    (CASE WHEN f.filetype='LOG' AND db.log_reuse_wait_desc!='NOTHING' THEN LEFT(db.log_reuse_wait_desc, 1)+LOWER(SUBSTRING(REPLACE(db.log_reuse_wait_desc, '_', ' '), 2, 1000)) ELSE '' END) AS [Log wait],
    UPPER(LEFT(db.recovery_model_desc, 1))+LOWER(SUBSTRING(db.recovery_model_desc, 2, 100)) AS [Recovery model],
    f.name AS [File name],
    dbo.fn_friendly_size(f.size_mb*@mb) AS [Allocated size], ISNULL((CASE
                WHEN f.filetype='IN-MEMORY' THEN 'n/a'
                WHEN f.is_percent_growth=1 THEN dbo.fn_format_number(NULLIF(f.autogrow, 0), 1)+'%'
        ELSE dbo.fn_friendly_size(NULLIF(f.autogrow, 0)*1.0*@mb) END), 'None') AS [Autogrow],
    (CASE WHEN f.max_size_mb=0 THEN ''
        WHEN f.max_size_mb IS NULL AND f.filetype='Rows' AND CAST(SERVERPROPERTY('Edition') AS varchar(128)) LIKE '%Express edition%' AND @version>='10.50' THEN dbo.fn_friendly_size(10*@gb)+' (Express ed.)'
        WHEN f.max_size_mb IS NULL AND f.filetype='Rows' AND CAST(SERVERPROPERTY('Edition') AS varchar(128)) LIKE '%Express edition%' THEN dbo.fn_friendly_size(4*@gb)+' (Express ed.)'
        WHEN f.max_size_mb IS NULL AND f.filetype='IN-MEMORY' THEN 'n/a'
                WHEN f.max_size_mb IS NULL THEN '('+ISNULL(f.file_system_type, 'OS')+' max)'
        WHEN f.max_size_mb=268435456/128 THEN '(Log max, 2 TB)'
        ELSE ISNULL(dbo.fn_friendly_size(f.max_size_mb*@mb), '') END) AS [Max file size],
    ISNULL(dbo.fn_friendly_size(f.used_size_mb*@mb), '') AS [File usage],
    ISNULL(dbo.fn_format_number(100.0*f.used_size_mb/f.size_mb, 2)+'%', '') AS [File usage %],
    f.drive AS [Drive],
    ISNULL(LTRIM(dbo.fn_friendly_size(1.0*f.available_bytes))+' of '+
           LTRIM(dbo.fn_friendly_size(1.0*f.total_bytes)), '') AS [Free disk space]
FROM @files AS f
INNER JOIN master.sys.databases AS db WITH (NOLOCK) ON db.database_id=f.database_id
INNER JOIN master.sys.database_mirroring AS m WITH (NOLOCK) ON db.database_id=m.database_id
WHERE f.database_id IN (
    SELECT database_id
    FROM @exec_requests
    UNION
    SELECT DISTINCT resource_database_id
    FROM @tran_locks
    WHERE request_session_id IN (
        SELECT session_id
        FROM @exec_sessions
        WHERE login_name=SUSER_SNAME()))
ORDER BY db.name, (CASE f.filetype
    WHEN 'ROWS' THEN 0
    WHEN 'LOG' THEN 1
    WHEN 'FILESTREAM' THEN 2
    WHEN 'IN-MEMORY' THEN 3 ELSE 0 END), f.name;



-------------------------------------------------------------------------------
--- 3. Display all sessions:
-------------------------------------------------------------------------------

SELECT
    xc.session_id AS SPID,
    ISNULL('"'+job.job_name+'", step '+CAST(job.step_id AS varchar(max))+' "'+job.step_name+'"',
        (CASE xs.[program_name]
            WHEN 'Microsoft SQL Server Management Studio - Query' THEN 'SSMS Query'
            WHEN 'Microsoft SQL Server Management Studio' THEN 'SSMS'
            ELSE xs.[program_name]
            END)) AS [Application],
--	xs.client_interface_name AS [Interface],
--	ISNULL(NULLIF(xc.client_net_address, xc.local_net_address), 'local')+ISNULL(':'+CAST(xc.client_tcp_port AS varchar(max)), '') AS [Client addr],
    xs.login_name+ISNULL(' ('+xc.auth_scheme+')', '') AS [Login name (auth)],
    dbo.fn_friendly_age(xc.connect_time, GETDATE()) AS [Conn. age],
    ISNULL(dbo.fn_friendly_size(NULLIF(xs.logical_reads*8192, 0)), '') AS [Logical reads],
    ISNULL(dbo.fn_friendly_size(NULLIF(xs.reads*8192, 0)), '') AS [Reads],
    ISNULL(dbo.fn_friendly_size(NULLIF(xs.writes*8192, 0)), '') AS [Writes],
    ISNULL(dbo.fn_friendly_age(0, DATEADD(ms, NULLIF(xs.cpu_time, 0), 0)), '') AS [CPU time],
    ISNULL(NULLIF(xs.[status], 'sleeping'), '') AS [State],
    ISNULL(CAST(NULLIF(trn.[count], 0) AS varchar(10)), '') AS [Tran count],
    ISNULL(blocking.blocking, '') AS [Blocking],
    ISNULL(chain.blocked_by, '') AS [Blocked by],
    ISNULL(SUBSTRING(cur.x.value('.', 'varchar(1000)'), 3, 1000), '') AS [Cursors],
    ISNULL(CAST(NULLIF((SELECT COUNT(*)
        FROM @os_tasks AS t
        WHERE t.session_id=xc.session_id), 0) AS varchar(10)), '') AS [Schedulers (DOP)],
    (CASE WHEN ISNULL(trn.[count], 0)>0 AND xs.[status]='sleeping' THEN dbo.fn_friendly_age(xs.last_request_start_time, GETDATE()) ELSE '' END) AS [Abandoned?],
    xs.flags AS [Connection/ANSI flags]
FROM @exec_connections AS xc
LEFT JOIN @exec_sessions AS xs ON xc.session_id=xs.session_id
LEFT JOIN @jobs AS job ON xs.[program_name] LIKE '%'+job.search_name+'%'
LEFT JOIN @block_chain AS chain ON chain.spid=xc.session_id
LEFT JOIN @blocking AS blocking ON blocking.spid=xc.session_id
LEFT JOIN (
    SELECT session_id, COUNT(*) AS [count]
    FROM @tran_session_transactions
    GROUP BY session_id) AS trn ON xc.session_id=trn.session_id
CROSS APPLY (
    SELECT ', '+[name]
    FROM sys.dm_exec_cursors(xc.session_id)
    FOR XML PATH(''), TYPE) AS cur(x)
WHERE xc.session_id!=@@SPID
ORDER BY (CASE
    WHEN xs.[status] NOT IN ('sleeping', 'dormant') OR ISNULL(trn.[count], 0)>0 THEN 1
    ELSE 2 END), xc.session_id;



-------------------------------------------------------------------------------
--- 4. Running queries:
-------------------------------------------------------------------------------

SELECT
    xc.session_id AS SPID,
    ISNULL('"'+job.job_name+'", step '+CAST(job.step_id AS varchar(max))+' "'+job.step_name+'"',
        (CASE xs.[program_name]
            WHEN 'Microsoft SQL Server Management Studio - Query' THEN 'SSMS Query'
            WHEN 'Microsoft SQL Server Management Studio' THEN 'SSMS'
            ELSE xs.[program_name]
            END)) AS [Application],
    ISNULL(dbobj.name, '') AS [Proc],
    (CASE WHEN xs.[status] NOT IN ('sleeping', 'dormant') THEN (CASE WHEN cmd.[encrypted]=1
        THEN xr.command+' (encrypted)'
        ELSE COALESCE(
            SUBSTRING(cmd.[text], xr.statement_start_offset/2, NULLIF(xr.statement_end_offset, -1)/2-xr.statement_start_offset/2),
            cmd.[text],
            xr.command COLLATE database_default,
            '') END) ELSE '('+xs.[status]+')' END) AS [Command/T-SQL],
    (CASE WHEN xs.[status]!='sleeping' THEN dbo.fn_friendly_age(xs.last_request_start_time, GETDATE()) ELSE '' END) AS [Stmt age],
    ISNULL(dbo.fn_friendly_size(mem.max_used_mb)+(CASE WHEN mem.max_used_mb<mem.granted_mb
        THEN ' of '+LTRIM(dbo.fn_friendly_size(mem.granted_mb*@mb))
        ELSE '' END)+(CASE WHEN mem.granted_mb<mem.requested_mb
        THEN ' ('+LTRIM(dbo.fn_friendly_size(mem.requested_mb*@mb))+' requested)'
        ELSE '' END), '') AS [Max used/granted memory],
    ISNULL(dbo.fn_friendly_size(mem.used_mb), '') AS [Used mem],
    ISNULL(dbo.fn_friendly_size(NULLIF(mem.ideal_mb, mem.granted_mb)), '') AS [Ideal mem],
    ISNULL(dbo.fn_format_number(mem.est_query_cost, 2), '') AS [Est. cost],
    ISNULL(dbo.fn_format_number(NULLIF(xr.percent_complete, 0.0), 1)+'%', '') AS [Progress],
    ISNULL(dbo.fn_friendly_age(GETDATE(), DATEADD(ms, 100.0*xr.total_elapsed_time/NULLIF(xr.percent_complete, 0.0), xs.last_request_start_time)), '') AS [Est remaining],
    [plan].query_plan AS [Query plan]
FROM @exec_connections AS xc
CROSS APPLY sys.dm_exec_sql_text(xc.most_recent_sql_handle) AS cmd
LEFT JOIN @exec_sessions AS xs ON xc.session_id=xs.session_id
LEFT JOIN @jobs AS job ON xs.[program_name] LIKE '%'+job.search_name+'%'
LEFT JOIN @exec_requests AS xr ON xc.session_id=xr.session_id
OUTER APPLY sys.dm_exec_query_plan(xr.plan_handle) AS [plan]
LEFT JOIN @block_chain AS chain ON chain.spid=xc.session_id
LEFT JOIN @blocking AS blocking ON blocking.spid=xc.session_id
LEFT JOIN @exec_query_memory_grants AS mem ON xc.session_id=mem.session_id
LEFT JOIN @database_procedures AS dbobj ON dbobj.database_id=cmd.dbid AND dbobj.[object_id]=cmd.objectid
WHERE xc.session_id!=@@SPID AND (
    xs.[status]!='sleeping' OR
    xc.session_id IN (SELECT session_id FROM @tran_session_transactions))
ORDER BY xc.session_id



-------------------------------------------------------------------------------
--- 5. Display open transactions:
-------------------------------------------------------------------------------

SELECT
    st.session_id AS SPID,
    act.transaction_id AS [Transaction],
    dbo.fn_friendly_age(act.transaction_begin_time, GETDATE()) AS [Transaction age],
    REPLACE((CASE st.is_local
            WHEN 0 THEN 'Distr '
            WHEN 1 THEN 'Local ' END)+
        (CASE act.transaction_type
            WHEN 1 THEN 'r/w'
            WHEN 2 THEN 'read'
            WHEN 3 THEN 'sys'
            WHEN 4 THEN 'distr'
            END), 'Distr distr', 'Distr') AS [Transaction type],
    (CASE act.transaction_state
        WHEN 0 THEN 'None'	        --- The transaction has not been completely initialized yet.
        WHEN 1 THEN 'Init'	        --- The transaction has been initialized but has not started.
        WHEN 2 THEN 'Active'	    --- The transaction is active.
        WHEN 3 THEN 'Ended'	        --- The transaction has ended. This is used for read-only transactions.
        WHEN 4 THEN 'Distr commit'  --- The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.
        WHEN 5 THEN 'Prepared'	    --- The transaction is in a prepared state and waiting resolution.
        WHEN 6 THEN 'Committed'	    --- The transaction has been committed.
        WHEN 7 THEN 'Rolling back'  --- The transaction is being rolled back.
        WHEN 8 THEN 'Rolled back'   --- The transaction has been rolled back.
        END) AS [State],
    (CASE xr.transaction_isolation_level
        WHEN 0 THEN '?'
        WHEN 1 THEN 'Read uncommitted'
        WHEN 2 THEN 'Read committed'
        WHEN 3 THEN 'Repeatable read'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
        ELSE ISNULL(CAST(xr.transaction_isolation_level AS varchar(10)), '')
        END) AS [Isolation level],
    ISNULL(CAST(dbo.fn_friendly_age(0, DATEADD(ms, (
        SELECT AVG(wt.wait_duration_ms)
        FROM @os_waiting_tasks AS wt
        WHERE wt.session_id=wt.blocking_session_id AND
            wt.session_id=st.session_id AND
            wt.wait_type='CXPACKET'), 0)) AS varchar(20)), 'None') AS [Avg cxPkt wait]
FROM @tran_session_transactions AS st
INNER JOIN @tran_active_transactions AS act ON st.transaction_id=act.transaction_id
LEFT JOIN @exec_requests AS xr ON st.session_id=xr.session_id
WHERE st.session_id!=@@SPID
ORDER BY st.session_id, act.transaction_id;



-------------------------------------------------------------------------------
--- 6. Display all locks in all sessions and databases:
-------------------------------------------------------------------------------

WITH rcte (spid, tran_id, lock_ord, mode, [count], [database], [object_id], [object_name], index_id, index_type, index_name, index_filter,
       partition_number, partition_count, partition_boundary, obj_rows, request_owner_type, wait_type, wait_time, isBlocking, isWaiting, lock_descr)
AS (
    SELECT spid, tran_id, lock_ord, mode, [count], [database], [object_id], [object_name], index_id, index_type, index_name, index_filter,
        partition_number, partition_count, partition_boundary, obj_rows, request_owner_type, wait_type, wait_time, isBlocking, isWaiting,
        CAST((CASE WHEN mode LIKE '%table' THEN '' ELSE LTRIM(ISNULL(dbo.fn_format_number(NULLIF([count], 1), 0)+' ', '')) END)+mode AS varchar(max))
    FROM @locks
    WHERE lock_ord=1
    UNION ALL
    SELECT rcte.spid, rcte.tran_id, lck.lock_ord, lck.mode, lck.[count], rcte.[database], rcte.[object_id], rcte.[object_name], rcte.index_id, rcte.index_type, rcte.index_name, rcte.index_filter,
        rcte.partition_number, rcte.partition_count, rcte.partition_boundary, rcte.obj_rows, rcte.request_owner_type, rcte.wait_type, rcte.wait_time,
        CAST((CASE WHEN 1 IN (rcte.isBlocking, lck.isBlocking) THEN 1 ELSE 0 END) AS bit),
        CAST((CASE WHEN 1 IN (rcte.isWaiting, lck.isWaiting) THEN 1 ELSE 0 END) AS bit),
        CAST(rcte.lock_descr+', '+(CASE WHEN lck.mode LIKE '%table' THEN '' ELSE LTRIM(ISNULL(dbo.fn_format_number(NULLIF(lck.[count], 1), 0)+' ', '')) END)+lck.mode AS varchar(max))
    FROM @locks AS lck
    INNER JOIN rcte ON
        rcte.lock_ord+1=lck.lock_ord AND
        rcte.spid=lck.spid AND
        rcte.tran_id=lck.tran_id AND
        rcte.[database]=lck.[database] AND
        rcte.[object_id]=lck.[object_id] AND
        ISNULL(rcte.index_id, -1)=ISNULL(lck.index_id, -1) AND
        rcte.partition_number=lck.partition_number)

SELECT spid AS SPID, tran_id AS [Transaction], lock_descr AS [Lock types],
    [database] AS [Database],
    ISNULL([object_name], '') AS [Object],
    ISNULL((CASE
        WHEN index_name='Primary key' AND index_id=1 THEN 'Primary key, clustered'
        WHEN index_type=5 THEN index_name+' (clustered colstore)'
        WHEN index_type=6 THEN index_name+' (colstore)'
        WHEN index_type=7 THEN index_name+' (hash)'
        WHEN index_id=1 THEN index_name+' (clustered)'
        WHEN index_id=0 OR index_id IS NULL THEN index_name+' (heap)' ELSE index_name END)+ISNULL(' '+index_filter, ''), '') AS [Index],
    ISNULL(STR(NULLIF(partition_number, 0), 4, 0)+'/'+CAST(NULLIF(partition_count, 1) AS varchar(10)), '') AS [Part'n],
    ISNULL(partition_boundary, '') AS [..bounds],
    ISNULL(dbo.fn_format_number(obj_rows, 0), '') AS [Est. object rows],
    request_owner_type AS [Req/trans owned by],
    ISNULL((CASE wait_type
        WHEN 'LCK_M_SCH_S' THEN 'Schema stability'
        WHEN 'LCK_M_SCH_M' THEN 'Schema modification'
        WHEN 'LCK_M_S' THEN	    'Share'
        WHEN 'LCK_M_U' THEN	    'Update'
        WHEN 'LCK_M_X' THEN	    'Exclusive'
        WHEN 'LCK_M_IS' THEN    'Intent-Share'
        WHEN 'LCK_M_IU' THEN    'Intent-Update'
        WHEN 'LCK_M_IX' THEN    'Intent-Exclusive'
        WHEN 'LCK_M_SIU' THEN   'Shared intent to update'
        WHEN 'LCK_M_SIX' THEN   'Share-Intent-Exclusive'
        WHEN 'LCK_M_UIX' THEN   'Update-Intent-Exclusive'
        WHEN 'LCK_M_BU' THEN    'Bulk Update'
        WHEN 'LCK_M_RS_S' THEN  'Range-share-share'
        WHEN 'LCK_M_RS_U' THEN  'Range-share-Update'
        WHEN 'LCK_M_RI_NL' THEN 'Range-Insert-NULL'
        WHEN 'LCK_M_RI_S' THEN  'Range-Insert-Shared'
        WHEN 'LCK_M_RI_U' THEN  'Range-Insert-Update'
        WHEN 'LCK_M_RI_X' THEN  'Range-Insert-Exclusive'
        WHEN 'LCK_M_RX_S' THEN  'Range-exclusive-Shared'
        WHEN 'LCK_M_RX_U' THEN  'Range-exclusive-update'
        WHEN 'LCK_M_RX_X' THEN  'Range-exclusive-exclusive'
        ELSE wait_type END), '') AS [Wait type],
    ISNULL(wait_time, '') AS [Wait time],
    (CASE WHEN isBlocking=1 THEN 'Blocking' ELSE '' END)+
    (CASE WHEN isBlocking=1 AND isWaiting=1 THEN ', ' ELSE '' END)+
    (CASE WHEN isWaiting=1 THEN 'Blocked' ELSE '' END) AS [Blocking/blocked]
FROM rcte
WHERE lock_ord=(SELECT MAX(lock_ord) FROM rcte AS x
        WHERE x.spid=rcte.spid AND x.tran_id=rcte.tran_id AND
            x.[database]=rcte.[database] AND
            ISNULL(x.[object_id], -1)=ISNULL(rcte.[object_id], -1) AND
            ISNULL(x.index_id, -1)=ISNULL(rcte.index_id, -1) AND
            ISNULL(x.partition_number, -1)=ISNULL(rcte.partition_number, -1))
ORDER BY spid, tran_id, [database], [object_name], index_id, partition_number, mode;



-------------------------------------------------------------------------------
--- Clean up:
-------------------------------------------------------------------------------

IF (XACT_STATE()!=-1 AND EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_friendly_size'))
    DROP FUNCTION dbo.fn_friendly_size;

IF (XACT_STATE()!=-1 AND EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_format_number'))
    DROP FUNCTION dbo.fn_format_number;

IF (XACT_STATE()!=-1 AND EXISTS (SELECT [object_id] FROM sys.objects WHERE [name]='fn_friendly_age'))
    DROP FUNCTION dbo.fn_friendly_age;




-------------------------------------------------------------------------------
--- CATCH
-------------------------------------------------------------------------------

END TRY
BEGIN CATCH;

    IF (EXISTS (SELECT session_id FROM sys.dm_exec_cursors(@@SPID))) BEGIN;
        CLOSE #cur;
        DEALLOCATE #cur;
    END;

    THROW;

    IF (@@TRANCOUNT!=0)
        ROLLBACK TRANSACTION;
    RETURN;

END CATCH;

ROLLBACK TRANSACTION;