Otterpohl

IF(OBJECT_ID('tempdb..#Tempxel') IS NOT NULL)
BEGIN
	Drop Table #Tempxel
END
CREATE TABLE #Tempxel(XEL_XML XML)

IF(OBJECT_ID('tempdb..#Tempxml') IS NOT NULL)
BEGIN
	DROP TABLE #Tempxml
END

Otterpohl

SELECT CAST(RIGHT(JH.run_date, 2) AS CHAR(2)) + '/' + CAST(SUBSTRING(CAST(JH.run_date AS CHAR(8)), 5, 2) AS CHAR(2)) + '/' + CAST(LEFT(JH.run_date, 4) AS CHAR(4)) AS date_run
      ,LEFT(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)), 6), 2) + ':' + SUBSTRING(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)), 6), 3, 2) + ':'
       + LEFT(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)), 6), 2) AS time_run
      ,JS.step_name
      ,JH.run_duration
      ,CASE
           WHEN JSL.log IS NULL THEN JH.message
           ELSE JSL.log
       END AS log_output
FROM msdb.dbo.sysjobsteps AS JS

Otterpohl

SELECT run_duration
      ,msdb.dbo.agent_datetime(run_date, run_time) AS datetime
FROM msdb.dbo.sysjobhistory
WHERE step_name = 'Enter Job Step Name Here'
ORDER BY msdb.dbo.agent_datetime(run_date, run_time) ASC;

Otterpohl

/*
https://blog.sqlauthority.com/2020/04/20/sql-server-blocking-tree-identifying-blocking-chain-using-sql-scripts/
*/

IF OBJECT_ID('tempdb..#Blocks') IS NOT NULL
    DROP TABLE #Blocks

SELECT   spr.spid
        ,spr.blocked
        ,REPLACE (REPLACE (st.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS batch

Otterpohl

/*
https://blog.sqlauthority.com/2010/06/17/sql-server-data-pages-in-buffer-pool-data-stored-in-memory-cache/
*/

SELECT COUNT(*) AS cached_pages_count
      ,obj.name
      ,obj.index_id
      ,i.name AS IndexName
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN

Otterpohl

DECLARE @temp_sp_who2 TABLE
(
    SPID INT NULL,
    Status VARCHAR(1000) NULL,
    Login sysname NULL,
    HostName sysname NULL,
    BlkBy sysname NULL,
    DBName sysname NULL,
    Command VARCHAR(1000) NULL,
    CPUTime INT NULL,

Otterpohl

SELECT DB_NAME(a.database_id) AS [Database Name],
       b.name + N' [' + b.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + N']' AS [Logical File Name],
       UPPER(SUBSTRING(b.physical_name, 1, 2)) AS [Drive],
       CAST(((a.size_on_disk_bytes / 1024.0) / (1024.0 * 1024.0)) AS DECIMAL(9, 2)) AS [Size (GB)],
       a.io_stall_read_ms AS [Total IO Read Stall],
       a.num_of_reads AS [Total Reads],
       CASE
           WHEN a.num_of_bytes_read > 0 THEN
               CAST(a.num_of_bytes_read / 1024.0 / 1024.0 / 1024.0 AS NUMERIC(23, 1))
           ELSE

Otterpohl

SELECT total_physical_memory_kb / 1024 AS [Physical Memory (MB)],
       available_physical_memory_kb / 1024 AS [Available Memory (MB)],
       total_page_file_kb / 1024 AS [Total Page File (MB)],
       available_page_file_kb / 1024 AS [Available Page File (MB)],
       system_cache_kb / 1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory;

Otterpohl

/*
https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/SQLServer_Memory_Information.sql
*/

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET LOCK_TIMEOUT 10000;

DECLARE @ServiceName NVARCHAR(100);
SET @ServiceName = CASE

Otterpohl

SELECT dp.name AS [user_name],
       dp.type_desc AS [user_type],
       ISNULL(sp.name, 'Orphaned!') AS [login_name],
       sp.type_desc AS [login_type]
FROM sys.database_principals AS dp
    LEFT JOIN sys.server_principals AS sp
        ON (dp.sid = sp.sid)
WHERE dp.type IN ( 'S', 'U', 'G' )
      AND dp.principal_id > 4
ORDER BY sp.name;