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

/*
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

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

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

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