{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": "Returns running sessions/requests; blocking information; sessions that have been granted locks or waiting for locks; SPs stats.\r\n\r\nChange Log:\r\n- 2012-09-10 Added extra information\r\n- 2013-02-02 Added extra information\r\n- 2013-04-12 Added page type information (PFS; GAM or SGAM) when wait type is PAGELATCH_ or PAGEIOLATCH_ .\r\n- 2013-05-23 Fixed parse page issue\r\n- 2013-09-16 Added mem grants information\r\n- 2013-10-17 Added statements to blocking and blocked sections, fixed head blocker info \r\n- 2013-12-09 Fixed blocking section showing non-blocked sessions also\r\n- 2014-02-04 Fixed conversion issue with blocking section\r\n- 2014-04-09 Added information to blocking section, and fixed conversion issue\r\n- 2014-12-09 Handle illegal characters in XML conversion\r\n- 11/16/2016 Added support for SQL Server 2016 SP1 and live query plan snapshot.\r\n- 12/2/2016 Fixed transport-level error issue with SQL Server 2016 SP1.\r\n- 2/16/2016 Added NOLOCK hints.\r\n- 3/28/2017 Fixed missing characters in offset fetches.\r\n- 10/11/2017 Commented out Stored procedure stats section to optimize for in-flight requests.\r\n- 10/20/2017 Added Query stats section.\r\n",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "Report current uptime",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nDECLARE @UpTime VARCHAR(12), @StartDate DATETIME, @sqlmajorver int, @sqlcmd NVARCHAR(500), @params NVARCHAR(500)\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver = 9\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @StartDateOUT = login_time, @UpTimeOUT = DATEDIFF(mi, login_time, GETDATE()) FROM master..sysprocesses WHERE spid = 1';\r\nEND\r\nELSE\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT @StartDateOUT = sqlserver_start_time, @UpTimeOUT = DATEDIFF(mi,sqlserver_start_time,GETDATE()) FROM sys.dm_os_sys_info';\r\nEND\r\n\r\nSET @params = N'@StartDateOUT DATETIME OUTPUT, @UpTimeOUT VARCHAR(12) OUTPUT';\r\n\r\nEXECUTE sp_executesql @sqlcmd, @params, @StartDateOUT=@StartDate OUTPUT, @UpTimeOUT=@UpTime OUTPUT;\r\n\r\nSELECT 'Uptime_Information' AS [Information], GETDATE() AS [Current_Time], @StartDate AS Last_Startup, CONVERT(VARCHAR(4),@UpTime/60/24) + 'd ' + CONVERT(VARCHAR(4),@UpTime/60%24) + 'h ' + CONVERT(VARCHAR(4),@UpTime%60) + 'm' AS Uptime",
            "metadata": {},
            "outputs": [],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": "Report Running Sessions/Requests",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SET NOCOUNT ON;\r\nDECLARE @sqlmajorver int, @sqlbuild int, @sqlcmd VARCHAR(8000)\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\nSELECT @sqlbuild = CONVERT(int, @@microsoftversion & 0xffff);\r\nIF @sqlmajorver = 9\r\nBEGIN\r\n\tSELECT @sqlcmd = N'SELECT ''Requests'' AS [Information], es.session_id, DB_NAME(er.database_id) AS [database_name], OBJECT_NAME(qp.objectid, qp.dbid) AS [object_name], -- NULL if Ad-Hoc or Prepared statements\r\n\t(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(\r\n\t\tqt.text,\r\n\t\tNCHAR(1),N''?''),NCHAR(2),N''?''),NCHAR(3),N''?''),NCHAR(4),N''?''),NCHAR(5),N''?''),NCHAR(6),N''?''),NCHAR(7),N''?''),NCHAR(8),N''?''),NCHAR(11),N''?''),NCHAR(12),N''?''),NCHAR(14),N''?''),NCHAR(15),N''?''),NCHAR(16),N''?''),NCHAR(17),N''?''),NCHAR(18),N''?''),NCHAR(19),N''?''),NCHAR(20),N''?''),NCHAR(21),N''?''),NCHAR(22),N''?''),NCHAR(23),N''?''),NCHAR(24),N''?''),NCHAR(25),N''?''),NCHAR(26),N''?''),NCHAR(27),N''?''),NCHAR(28),N''?''),NCHAR(29),N''?''),NCHAR(30),N''?''),NCHAR(31),N''?'') \r\n\t\tAS [text()]\r\n\t\tFROM sys.dm_exec_sql_text(er.sql_handle) AS qt\r\n\t\tFOR XML PATH(''''), TYPE) AS [running_batch],\r\n\t(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(\r\n\t\tSUBSTRING(qt2.text,\r\n\t\t1+(CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END),\r\n\t\t1+(CASE WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt2.text) ELSE er.statement_end_offset/2 END - (CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END))),\r\n\t\tNCHAR(1),N''?''),NCHAR(2),N''?''),NCHAR(3),N''?''),NCHAR(4),N''?''),NCHAR(5),N''?''),NCHAR(6),N''?''),NCHAR(7),N''?''),NCHAR(8),N''?''),NCHAR(11),N''?''),NCHAR(12),N''?''),NCHAR(14),N''?''),NCHAR(15),N''?''),NCHAR(16),N''?''),NCHAR(17),N''?''),NCHAR(18),N''?''),NCHAR(19),N''?''),NCHAR(20),N''?''),NCHAR(21),N''?''),NCHAR(22),N''?''),NCHAR(23),N''?''),NCHAR(24),N''?''),NCHAR(25),N''?''),NCHAR(26),N''?''),NCHAR(27),N''?''),NCHAR(28),N''?''),NCHAR(29),N''?''),NCHAR(30),N''?''),NCHAR(31),N''?'') \r\n\t\tAS [text()]\r\n\t\tFROM sys.dm_exec_sql_text(er.sql_handle) AS qt2\r\n\t\tFOR XML PATH(''''), TYPE) AS [running_statement],\r\n\t--ot.task_state AS [status],\r\n\ter.status,\r\n\t--er.command,\r\n\tqp.query_plan,\r\n\ter.percent_complete,\r\n\tCONVERT(VARCHAR(20),DATEADD(ms,er.estimated_completion_time,GETDATE()),20) AS [ETA_completion_time],\r\n\t(er.cpu_time/1000) AS cpu_time_sec,\r\n\t(er.reads*8)/1024 AS physical_reads_KB,\r\n\t(er.logical_reads*8)/1024 AS logical_reads_KB,\r\n\t(er.writes*8)/1024 AS writes_KB,\r\n\t(er.total_elapsed_time/1000)/60 AS elapsed_minutes,\r\n\ter.wait_type,\r\n\ter.wait_resource,\r\n\ter.last_wait_type,\r\n\t(SELECT CASE\r\n\t\tWHEN pageid = 1 OR pageid % 8088 = 0 THEN ''Is_PFS_Page''\r\n\t\tWHEN pageid = 2 OR pageid % 511232 = 0 THEN ''Is_GAM_Page''\r\n\t\tWHEN pageid = 3 OR (pageid - 1) % 511232 = 0 THEN ''Is_SGAM_Page''\r\n\t\tWHEN pageid IS NULL THEN NULL\r\n\t\tELSE ''Is_not_PFS_GAM_SGAM_page'' END\r\n\tFROM (SELECT CASE WHEN er.[wait_type] LIKE ''PAGE%LATCH%'' AND er.[wait_resource] LIKE ''%:%''\r\n\t\tTHEN CAST(RIGHT(er.[wait_resource], LEN(er.[wait_resource]) - CHARINDEX('':'', er.[wait_resource], LEN(er.[wait_resource])-CHARINDEX('':'', REVERSE(er.[wait_resource])))) AS int)\r\n\t\tELSE NULL END AS pageid) AS latch_pageid\r\n\t) AS wait_resource_type,\r\n\ter.wait_time AS wait_time_ms,\r\n\ter.cpu_time AS cpu_time_ms,\r\n\ter.open_transaction_count,\r\n\tDATEADD(s, (er.estimated_completion_time/1000), GETDATE()) AS estimated_completion_time,\r\n\tLEFT (CASE COALESCE(er.transaction_isolation_level, es.transaction_isolation_level)\r\n\t\tWHEN 0 THEN ''0-Unspecified''\r\n\t\tWHEN 1 THEN ''1-ReadUncommitted''\r\n\t\tWHEN 2 THEN ''2-ReadCommitted''\r\n\t\tWHEN 3 THEN ''3-RepeatableRead''\r\n\t\tWHEN 4 THEN ''4-Serializable''\r\n\t\tWHEN 5 THEN ''5-Snapshot''\r\n\t\tELSE CONVERT (VARCHAR(30), er.transaction_isolation_level) + ''-UNKNOWN''\r\n    END, 30) AS transaction_isolation_level,\r\n\tmg.requested_memory_kb,\r\n\tmg.granted_memory_kb,\r\n\t--mg.ideal_memory_kb,\r\n\tmg.query_cost,\r\n\tes.[host_name],\r\n\tes.login_name,\r\n\t--es.original_login_name,\r\n\tes.[program_name],\r\n\t--ec.client_net_address,\r\n\tes.is_user_process\r\nFROM sys.dm_exec_requests (NOLOCK) er\r\n\tLEFT OUTER JOIN sys.dm_exec_query_memory_grants (NOLOCK) mg ON er.session_id = mg.session_id AND er.request_id = mg.request_id\r\n\tLEFT OUTER JOIN sys.dm_db_session_space_usage (NOLOCK) ssu ON er.session_id = ssu.session_id\r\n\tLEFT OUTER JOIN sys.dm_exec_sessions (NOLOCK) es ON er.session_id = es.session_id\r\n\tOUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp\r\nWHERE er.session_id <> @@SPID AND es.is_user_process = 1\r\nORDER BY er.total_elapsed_time DESC, er.logical_reads DESC, [database_name], session_id'\r\nEND\r\nELSE IF @sqlmajorver IN (10,11,12) OR (@sqlmajorver = 13 AND @sqlbuild < 4000)\r\nBEGIN\r\n\tSET @sqlcmd = N';WITH tsu AS (SELECT session_id, SUM(user_objects_alloc_page_count) AS user_objects_alloc_page_count, \r\nSUM(user_objects_dealloc_page_count) AS user_objects_dealloc_page_count, \r\nSUM(internal_objects_alloc_page_count) AS internal_objects_alloc_page_count, \r\nSUM(internal_objects_dealloc_page_count) AS internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage (NOLOCK) GROUP BY session_id)\r\nSELECT ''Requests'' AS [Information], es.session_id, DB_NAME(er.database_id) AS [database_name], OBJECT_NAME(qp.objectid, qp.dbid) AS [object_name],\r\n\t(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(\r\n\t\tqt.text,\r\n\t\tNCHAR(1),N''?''),NCHAR(2),N''?''),NCHAR(3),N''?''),NCHAR(4),N''?''),NCHAR(5),N''?''),NCHAR(6),N''?''),NCHAR(7),N''?''),NCHAR(8),N''?''),NCHAR(11),N''?''),NCHAR(12),N''?''),NCHAR(14),N''?''),NCHAR(15),N''?''),NCHAR(16),N''?''),NCHAR(17),N''?''),NCHAR(18),N''?''),NCHAR(19),N''?''),NCHAR(20),N''?''),NCHAR(21),N''?''),NCHAR(22),N''?''),NCHAR(23),N''?''),NCHAR(24),N''?''),NCHAR(25),N''?''),NCHAR(26),N''?''),NCHAR(27),N''?''),NCHAR(28),N''?''),NCHAR(29),N''?''),NCHAR(30),N''?''),NCHAR(31),N''?'') \r\n\t\tAS [text()]\r\n\t\tFROM sys.dm_exec_sql_text(er.sql_handle) AS qt\r\n\t\tFOR XML PATH(''''), TYPE) AS [running_batch],\r\n\t(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(\r\n\t\tSUBSTRING(qt2.text,\r\n\t\t1+(CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END),\r\n\t\t1+(CASE WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt2.text) ELSE er.statement_end_offset/2 END - (CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END))),\r\n\t\tNCHAR(1),N''?''),NCHAR(2),N''?''),NCHAR(3),N''?''),NCHAR(4),N''?''),NCHAR(5),N''?''),NCHAR(6),N''?''),NCHAR(7),N''?''),NCHAR(8),N''?''),NCHAR(11),N''?''),NCHAR(12),N''?''),NCHAR(14),N''?''),NCHAR(15),N''?''),NCHAR(16),N''?''),NCHAR(17),N''?''),NCHAR(18),N''?''),NCHAR(19),N''?''),NCHAR(20),N''?''),NCHAR(21),N''?''),NCHAR(22),N''?''),NCHAR(23),N''?''),NCHAR(24),N''?''),NCHAR(25),N''?''),NCHAR(26),N''?''),NCHAR(27),N''?''),NCHAR(28),N''?''),NCHAR(29),N''?''),NCHAR(30),N''?''),NCHAR(31),N''?'') \r\n\t\tAS [text()]\r\n\t\tFROM sys.dm_exec_sql_text(er.sql_handle) AS qt2\r\n\t\tFOR XML PATH(''''), TYPE) AS [running_statement],\r\n\t--ot.task_state AS [status],\r\n\ter.status,\r\n\t--er.command,\r\n\tqp.query_plan,\r\n\ter.percent_complete,\r\n\tCONVERT(VARCHAR(20),DATEADD(ms,er.estimated_completion_time,GETDATE()),20) AS [ETA_completion_time],\r\n\t(er.cpu_time/1000) AS cpu_time_sec,\r\n\t(er.reads*8)/1024 AS physical_reads_KB,\r\n\t(er.logical_reads*8)/1024 AS logical_reads_KB,\r\n\t(er.writes*8)/1024 AS writes_KB,\r\n\t(er.total_elapsed_time/1000)/60 AS elapsed_minutes,\r\n\ter.wait_type,\r\n\ter.wait_resource,\r\n\ter.last_wait_type,\r\n\t(SELECT CASE\r\n\t\tWHEN pageid = 1 OR pageid % 8088 = 0 THEN ''Is_PFS_Page''\r\n\t\tWHEN pageid = 2 OR pageid % 511232 = 0 THEN ''Is_GAM_Page''\r\n\t\tWHEN pageid = 3 OR (pageid - 1) % 511232 = 0 THEN ''Is_SGAM_Page''\r\n\t\tWHEN pageid IS NULL THEN NULL\r\n\t\tELSE ''Is_not_PFS_GAM_SGAM_page'' END\r\n\tFROM (SELECT CASE WHEN er.[wait_type] LIKE ''PAGE%LATCH%'' AND er.[wait_resource] LIKE ''%:%''\r\n\t\tTHEN CAST(RIGHT(er.[wait_resource], LEN(er.[wait_resource]) - CHARINDEX('':'', er.[wait_resource], LEN(er.[wait_resource])-CHARINDEX('':'', REVERSE(er.[wait_resource])))) AS int)\r\n\t\tELSE NULL END AS pageid) AS latch_pageid\r\n\t) AS wait_resource_type,\r\n\ter.wait_time AS wait_time_ms,\r\n\ter.cpu_time AS cpu_time_ms,\r\n\ter.open_transaction_count,\r\n\tDATEADD(s, (er.estimated_completion_time/1000), GETDATE()) AS estimated_completion_time,\r\n\tCASE WHEN mg.wait_time_ms IS NULL THEN DATEDIFF(ms, mg.request_time, mg.grant_time) ELSE mg.wait_time_ms END AS [grant_wait_time_ms],\r\n\tLEFT (CASE COALESCE(er.transaction_isolation_level, es.transaction_isolation_level)\r\n\t\tWHEN 0 THEN ''0-Unspecified''\r\n\t\tWHEN 1 THEN ''1-ReadUncommitted''\r\n\t\tWHEN 2 THEN ''2-ReadCommitted''\r\n\t\tWHEN 3 THEN ''3-RepeatableRead''\r\n\t\tWHEN 4 THEN ''4-Serializable''\r\n\t\tWHEN 5 THEN ''5-Snapshot''\r\n\t\tELSE CONVERT (VARCHAR(30), er.transaction_isolation_level) + ''-UNKNOWN''\r\n    END, 30) AS transaction_isolation_level,\r\n\tmg.requested_memory_kb,\r\n\tmg.granted_memory_kb,\r\n\tmg.ideal_memory_kb,\r\n\tmg.query_cost,\r\n\t((((ssu.user_objects_alloc_page_count + tsu.user_objects_alloc_page_count) -\r\n\t\t(ssu.user_objects_dealloc_page_count + tsu.user_objects_dealloc_page_count))*8)/1024) AS user_obj_in_tempdb_MB,\r\n\t((((ssu.internal_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) -\r\n\t\t(ssu.internal_objects_dealloc_page_count + tsu.internal_objects_dealloc_page_count))*8)/1024) AS internal_obj_in_tempdb_MB,\r\n\tes.[host_name],\r\n\tes.login_name,\r\n\t--es.original_login_name,\r\n\tes.[program_name],\r\n\t--ec.client_net_address,\r\n\tes.is_user_process,\r\n\tg.name AS workload_group\r\nFROM sys.dm_exec_requests (NOLOCK) er\r\n\tLEFT OUTER JOIN sys.dm_exec_query_memory_grants (NOLOCK) mg ON er.session_id = mg.session_id AND er.request_id = mg.request_id\r\n\tLEFT OUTER JOIN sys.dm_db_session_space_usage (NOLOCK) ssu ON er.session_id = ssu.session_id\r\n\tLEFT OUTER JOIN sys.dm_exec_sessions (NOLOCK) es ON er.session_id = es.session_id\r\n\tLEFT OUTER JOIN tsu ON tsu.session_id = ssu.session_id\r\n\tLEFT OUTER JOIN sys.dm_resource_governor_workload_groups (NOLOCK) g ON es.group_id = g.group_id\r\n\tOUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp\r\nWHERE er.session_id <> @@SPID AND es.is_user_process = 1\r\nORDER BY er.total_elapsed_time DESC, er.logical_reads DESC, [database_name], session_id'\r\nEND\r\nELSE IF (@sqlmajorver = 13 AND @sqlbuild > 4000) OR @sqlmajorver > 13\r\nBEGIN\r\n\tSELECT @sqlcmd = N'WITH tsu AS (SELECT session_id, SUM(user_objects_alloc_page_count) AS user_objects_alloc_page_count, \r\nSUM(user_objects_dealloc_page_count) AS user_objects_dealloc_page_count, \r\nSUM(internal_objects_alloc_page_count) AS internal_objects_alloc_page_count, \r\nSUM(internal_objects_dealloc_page_count) AS internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage (NOLOCK) GROUP BY session_id)\r\nSELECT ''Requests'' AS [Information], es.session_id, DB_NAME(er.database_id) AS [database_name], OBJECT_NAME(qp.objectid, qp.dbid) AS [object_name],\r\n\t(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(\r\n\t\tqt.text,\r\n\t\tNCHAR(1),N''?''),NCHAR(2),N''?''),NCHAR(3),N''?''),NCHAR(4),N''?''),NCHAR(5),N''?''),NCHAR(6),N''?''),NCHAR(7),N''?''),NCHAR(8),N''?''),NCHAR(11),N''?''),NCHAR(12),N''?''),NCHAR(14),N''?''),NCHAR(15),N''?''),NCHAR(16),N''?''),NCHAR(17),N''?''),NCHAR(18),N''?''),NCHAR(19),N''?''),NCHAR(20),N''?''),NCHAR(21),N''?''),NCHAR(22),N''?''),NCHAR(23),N''?''),NCHAR(24),N''?''),NCHAR(25),N''?''),NCHAR(26),N''?''),NCHAR(27),N''?''),NCHAR(28),N''?''),NCHAR(29),N''?''),NCHAR(30),N''?''),NCHAR(31),N''?'') \r\n\t\tAS [text()]\r\n\t\tFROM sys.dm_exec_sql_text(er.sql_handle) AS qt\r\n\t\tFOR XML PATH(''''), TYPE) AS [running_batch],\r\n\t(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(\r\n\t\tSUBSTRING(qt2.text,\r\n\t\t1+(CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END),\r\n\t\t1+(CASE WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt2.text) ELSE er.statement_end_offset/2 END - (CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END))),\r\n\t\tNCHAR(1),N''?''),NCHAR(2),N''?''),NCHAR(3),N''?''),NCHAR(4),N''?''),NCHAR(5),N''?''),NCHAR(6),N''?''),NCHAR(7),N''?''),NCHAR(8),N''?''),NCHAR(11),N''?''),NCHAR(12),N''?''),NCHAR(14),N''?''),NCHAR(15),N''?''),NCHAR(16),N''?''),NCHAR(17),N''?''),NCHAR(18),N''?''),NCHAR(19),N''?''),NCHAR(20),N''?''),NCHAR(21),N''?''),NCHAR(22),N''?''),NCHAR(23),N''?''),NCHAR(24),N''?''),NCHAR(25),N''?''),NCHAR(26),N''?''),NCHAR(27),N''?''),NCHAR(28),N''?''),NCHAR(29),N''?''),NCHAR(30),N''?''),NCHAR(31),N''?'') \r\n\t\tAS [text()]\r\n\t\tFROM sys.dm_exec_sql_text(er.sql_handle) AS qt2\r\n\t\tFOR XML PATH(''''), TYPE) AS [running_statement],\r\n\t--ot.task_state AS [status],\r\n\ter.status,\r\n\t--er.command,\r\n\tqp.query_plan,\r\n\tCASE WHEN qes.query_plan IS NULL THEN ''Lightweight Query Profiling Infrastructure is not enabled'' ELSE qes.query_plan END AS [live_query_plan_snapshot],\r\n\ter.percent_complete,\r\n\tCONVERT(VARCHAR(20),DATEADD(ms,er.estimated_completion_time,GETDATE()),20) AS [ETA_completion_time],\r\n\t(er.cpu_time/1000) AS cpu_time_sec,\r\n\t(er.reads*8)/1024 AS physical_reads_KB,\r\n\t(er.logical_reads*8)/1024 AS logical_reads_KB,\r\n\t(er.writes*8)/1024 AS writes_KB,\r\n\t(er.total_elapsed_time/1000)/60 AS elapsed_minutes,\r\n\ter.wait_type,\r\n\ter.wait_resource,\r\n\ter.last_wait_type,\r\n\t(SELECT CASE\r\n\t\tWHEN pageid = 1 OR pageid % 8088 = 0 THEN ''Is_PFS_Page''\r\n\t\tWHEN pageid = 2 OR pageid % 511232 = 0 THEN ''Is_GAM_Page''\r\n\t\tWHEN pageid = 3 OR (pageid - 1) % 511232 = 0 THEN ''Is_SGAM_Page''\r\n\t\tWHEN pageid IS NULL THEN NULL\r\n\t\tELSE ''Is_not_PFS_GAM_SGAM_page'' END\r\n\tFROM (SELECT CASE WHEN er.[wait_type] LIKE ''PAGE%LATCH%'' AND er.[wait_resource] LIKE ''%:%''\r\n\t\tTHEN CAST(RIGHT(er.[wait_resource], LEN(er.[wait_resource]) - CHARINDEX('':'', er.[wait_resource], LEN(er.[wait_resource])-CHARINDEX('':'', REVERSE(er.[wait_resource])))) AS int)\r\n\t\tELSE NULL END AS pageid) AS latch_pageid\r\n\t) AS wait_resource_type,\r\n\ter.wait_time AS wait_time_ms,\r\n\ter.cpu_time AS cpu_time_ms,\r\n\ter.open_transaction_count,\r\n\tDATEADD(s, (er.estimated_completion_time/1000), GETDATE()) AS estimated_completion_time,\r\n\tCASE WHEN mg.wait_time_ms IS NULL THEN DATEDIFF(ms, mg.request_time, mg.grant_time) ELSE mg.wait_time_ms END AS [grant_wait_time_ms],\r\n\tLEFT (CASE COALESCE(er.transaction_isolation_level, es.transaction_isolation_level)\r\n\t\tWHEN 0 THEN ''0-Unspecified''\r\n\t\tWHEN 1 THEN ''1-ReadUncommitted''\r\n\t\tWHEN 2 THEN ''2-ReadCommitted''\r\n\t\tWHEN 3 THEN ''3-RepeatableRead''\r\n\t\tWHEN 4 THEN ''4-Serializable''\r\n\t\tWHEN 5 THEN ''5-Snapshot''\r\n\t\tELSE CONVERT (VARCHAR(30), er.transaction_isolation_level) + ''-UNKNOWN''\r\n    END, 30) AS transaction_isolation_level,\r\n\tmg.requested_memory_kb,\r\n\tmg.granted_memory_kb,\r\n\tmg.ideal_memory_kb,\r\n\tmg.query_cost,\r\n\t((((ssu.user_objects_alloc_page_count + tsu.user_objects_alloc_page_count) -\r\n\t\t(ssu.user_objects_dealloc_page_count + tsu.user_objects_dealloc_page_count))*8)/1024) AS user_obj_in_tempdb_MB,\r\n\t((((ssu.internal_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) -\r\n\t\t(ssu.internal_objects_dealloc_page_count + tsu.internal_objects_dealloc_page_count))*8)/1024) AS internal_obj_in_tempdb_MB,\r\n\tes.[host_name],\r\n\tes.login_name,\r\n\t--es.original_login_name,\r\n\tes.[program_name],\r\n\t--ec.client_net_address,\r\n\tes.is_user_process,\r\n\tg.name AS workload_group\r\nFROM sys.dm_exec_requests (NOLOCK) er\r\n\tLEFT OUTER JOIN sys.dm_exec_query_memory_grants (NOLOCK) mg ON er.session_id = mg.session_id AND er.request_id = mg.request_id\r\n\tLEFT OUTER JOIN sys.dm_db_session_space_usage (NOLOCK) ssu ON er.session_id = ssu.session_id\r\n\tLEFT OUTER JOIN sys.dm_exec_sessions (NOLOCK) es ON er.session_id = es.session_id\r\n\tLEFT OUTER JOIN tsu ON tsu.session_id = ssu.session_id\r\n\tLEFT OUTER JOIN sys.dm_resource_governor_workload_groups (NOLOCK) g ON es.group_id = g.group_id\r\n\tOUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp \r\n\tOUTER APPLY sys.dm_exec_query_statistics_xml(er.session_id) qes\r\nWHERE er.session_id <> @@SPID AND es.is_user_process = 1\r\nORDER BY er.total_elapsed_time DESC, er.logical_reads DESC, [database_name], session_id'\r\nEND\r\n--PRINT @sqlcmd\r\nEXECUTE (@sqlcmd)",
            "metadata": {},
            "outputs": []
        },
        {
            "cell_type": "markdown",
            "source": "Report Waiters and Blocking",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SELECT 'Waiter_Blocking_Report' AS [Information],\r\n\t-- blocked\r\n\tes.session_id AS blocked_spid,\r\n\tes.[status] AS [blocked_spid_status],\r\n\tot.task_state AS [blocked_task_status],\r\n\towt.wait_type AS blocked_spid_wait_type,\r\n\tCOALESCE(owt.wait_duration_ms, DATEDIFF(ms, es.last_request_start_time, GETDATE())) AS blocked_spid_wait_time_ms,\r\n\t--er.total_elapsed_time AS blocked_elapsed_time_ms,\r\n\t/* \r\n\t\tCheck sys.dm_os_waiting_tasks for Exchange wait types in http://technet.microsoft.com/en-us/library/ms188743.aspx.\r\n\t\t- Wait Resource e_waitPipeNewRow in CXPACKET waits – Producer waiting on consumer for a packet to fill.\r\n\t\t- Wait Resource e_waitPipeGetRow in CXPACKET waits – Consumer waiting on producer to fill a packet.\r\n\t*/\r\n\towt.resource_description AS blocked_spid_res_desc,\r\n\towt.[objid] AS blocked_objectid,\r\n\towt.pageid AS blocked_pageid,\r\n\tCASE WHEN owt.pageid = 1 OR owt.pageid % 8088 = 0 THEN 'Is_PFS_Page'\r\n\t\tWHEN owt.pageid = 2 OR owt.pageid % 511232 = 0 THEN 'Is_GAM_Page'\r\n\t\tWHEN owt.pageid = 3 OR (owt.pageid - 1) % 511232 = 0 THEN 'Is_SGAM_Page'\r\n\t\tWHEN owt.pageid IS NULL THEN NULL\r\n\t\tELSE 'Is_not_PFS_GAM_SGAM_page' END AS blocked_spid_res_type,\r\n\t(SELECT qt.text AS [text()] \r\n\t\tFROM sys.dm_exec_sql_text(COALESCE(er.sql_handle, ec.most_recent_sql_handle)) AS qt \r\n\t\tFOR XML PATH(''), TYPE) AS [blocked_batch],\r\n\t(SELECT SUBSTRING(qt2.text, \r\n\t\t1+(CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END),\r\n\t\t1+(CASE WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt2.text) ELSE er.statement_end_offset/2 END - (CASE WHEN er.statement_start_offset = 0 THEN 0 ELSE er.statement_start_offset/2 END))) AS [text()]\r\n\t\tFROM sys.dm_exec_sql_text(COALESCE(er.sql_handle, ec.most_recent_sql_handle)) AS qt2 \r\n\t\tFOR XML PATH(''), TYPE) AS [blocked_statement],\r\n\tes.last_request_start_time AS blocked_last_start,\r\n\tLEFT (CASE COALESCE(es.transaction_isolation_level, er.transaction_isolation_level)\r\n\t\tWHEN 0 THEN '0-Unspecified' \r\n\t\tWHEN 1 THEN '1-ReadUncommitted(NOLOCK)' \r\n\t\tWHEN 2 THEN '2-ReadCommitted' \r\n\t\tWHEN 3 THEN '3-RepeatableRead' \r\n\t\tWHEN 4 THEN '4-Serializable' \r\n\t\tWHEN 5 THEN '5-Snapshot'\r\n\t\tELSE CONVERT (VARCHAR(30), COALESCE(es.transaction_isolation_level, er.transaction_isolation_level)) + '-UNKNOWN' \r\n    END, 30) AS blocked_tran_isolation_level,\r\n\r\n\t-- blocker\r\n\ter.blocking_session_id As blocker_spid,\r\n    CASE \r\n        -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others\r\n        WHEN (er2.session_id IS NULL OR owt.blocking_session_id IS NULL) AND (er.blocking_session_id = 0 OR er.session_id IS NULL) THEN 1\r\n        -- session is either not blocking someone, or is blocking someone but is blocked by another party\r\n        ELSE 0\r\n    END AS is_head_blocker,\r\n\t(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(\r\n\t\tqt2.text,\r\n\t\tNCHAR(1),N'?'),NCHAR(2),N'?'),NCHAR(3),N'?'),NCHAR(4),N'?'),NCHAR(5),N'?'),NCHAR(6),N'?'),NCHAR(7),N'?'),NCHAR(8),N'?'),NCHAR(11),N'?'),NCHAR(12),N'?'),NCHAR(14),N'?'),NCHAR(15),N'?'),NCHAR(16),N'?'),NCHAR(17),N'?'),NCHAR(18),N'?'),NCHAR(19),N'?'),NCHAR(20),N'?'),NCHAR(21),N'?'),NCHAR(22),N'?'),NCHAR(23),N'?'),NCHAR(24),N'?'),NCHAR(25),N'?'),NCHAR(26),N'?'),NCHAR(27),N'?'),NCHAR(28),N'?'),NCHAR(29),N'?'),NCHAR(30),N'?'),NCHAR(31),N'?') \r\n\t\tAS [text()]\r\n\t\tFROM sys.dm_exec_sql_text(COALESCE(er2.sql_handle, ec2.most_recent_sql_handle)) AS qt2 \r\n\t\tFOR XML PATH(''), TYPE) AS [blocker_batch],\r\n\t(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(\r\n\t\tSUBSTRING(qt2.text, \r\n\t\t1+(CASE WHEN er2.statement_start_offset = 0 THEN 0 ELSE er2.statement_start_offset/2 END),\r\n\t\t1+(CASE WHEN er2.statement_end_offset = -1 THEN DATALENGTH(qt2.text) ELSE er2.statement_end_offset/2 END - (CASE WHEN er2.statement_start_offset = 0 THEN 0 ELSE er2.statement_start_offset/2 END))),\r\n\t\tNCHAR(1),N'?'),NCHAR(2),N'?'),NCHAR(3),N'?'),NCHAR(4),N'?'),NCHAR(5),N'?'),NCHAR(6),N'?'),NCHAR(7),N'?'),NCHAR(8),N'?'),NCHAR(11),N'?'),NCHAR(12),N'?'),NCHAR(14),N'?'),NCHAR(15),N'?'),NCHAR(16),N'?'),NCHAR(17),N'?'),NCHAR(18),N'?'),NCHAR(19),N'?'),NCHAR(20),N'?'),NCHAR(21),N'?'),NCHAR(22),N'?'),NCHAR(23),N'?'),NCHAR(24),N'?'),NCHAR(25),N'?'),NCHAR(26),N'?'),NCHAR(27),N'?'),NCHAR(28),N'?'),NCHAR(29),N'?'),NCHAR(30),N'?'),NCHAR(31),N'?') \r\n\t\tAS [text()]\r\n\t\tFROM sys.dm_exec_sql_text(COALESCE(er2.sql_handle, ec2.most_recent_sql_handle)) AS qt2 \r\n\t\tFOR XML PATH(''), TYPE) AS [blocker_statement],\r\n\tes2.last_request_start_time AS blocker_last_start,\r\n\tLEFT (CASE COALESCE(er2.transaction_isolation_level, es.transaction_isolation_level)\r\n\t\tWHEN 0 THEN '0-Unspecified' \r\n\t\tWHEN 1 THEN '1-ReadUncommitted(NOLOCK)' \r\n\t\tWHEN 2 THEN '2-ReadCommitted' \r\n\t\tWHEN 3 THEN '3-RepeatableRead' \r\n\t\tWHEN 4 THEN '4-Serializable' \r\n\t\tWHEN 5 THEN '5-Snapshot' \r\n\t\tELSE CONVERT (VARCHAR(30), COALESCE(er2.transaction_isolation_level, es.transaction_isolation_level)) + '-UNKNOWN' \r\n    END, 30) AS blocker_tran_isolation_level,\r\n\r\n\t-- blocked - other data\r\n\tDB_NAME(er.database_id) AS blocked_database, \r\n\tes.[host_name] AS blocked_host,\r\n\tes.[program_name] AS blocked_program, \r\n\tes.login_name AS blocked_login,\r\n\tCASE WHEN es.session_id = -2 THEN 'Orphaned_distributed_tran' \r\n\t\tWHEN es.session_id = -3 THEN 'Defered_recovery_tran' \r\n\t\tWHEN es.session_id = -4 THEN 'Unknown_tran' ELSE NULL END AS blocked_session_comment,\r\n\tes.is_user_process AS [blocked_is_user_process],\r\n\r\n\t-- blocker - other data\r\n\tDB_NAME(er2.database_id) AS blocker_database,\r\n\tes2.[host_name] AS blocker_host,\r\n\tes2.[program_name] AS blocker_program,\t\r\n\tes2.login_name AS blocker_login,\r\n\tCASE WHEN es2.session_id = -2 THEN 'Orphaned_distributed_tran' \r\n\t\tWHEN es2.session_id = -3 THEN 'Defered_recovery_tran' \r\n\t\tWHEN es2.session_id = -4 THEN 'Unknown_tran' ELSE NULL END AS blocker_session_comment,\r\n\tes2.is_user_process AS [blocker_is_user_process]\r\nFROM sys.dm_exec_sessions (NOLOCK) es\r\nLEFT OUTER JOIN sys.dm_exec_requests (NOLOCK) er ON es.session_id = er.session_id\r\nLEFT OUTER JOIN sys.dm_exec_connections (NOLOCK) ec ON es.session_id = ec.session_id\r\nLEFT OUTER JOIN sys.dm_os_tasks (NOLOCK) ot ON er.session_id = ot.session_id AND er.request_id = ot.request_id\r\nLEFT OUTER JOIN sys.dm_exec_sessions (NOLOCK) es2 ON er.blocking_session_id = es2.session_id\r\nLEFT OUTER JOIN sys.dm_exec_requests (NOLOCK) er2 ON es2.session_id = er2.session_id\r\nLEFT OUTER JOIN sys.dm_exec_connections (NOLOCK) ec2 ON es2.session_id = ec2.session_id\r\nLEFT OUTER JOIN \r\n(\r\n    -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as \r\n    -- waiting for several different threads.  This will cause that thread to show up in multiple rows \r\n    -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread, \r\n    -- and use it as representative of the other wait relationships this thread is involved in. \r\n    SELECT waiting_task_address, session_id, exec_context_id, wait_duration_ms, \r\n\t\twait_type, resource_address, blocking_task_address, blocking_session_id, \r\n\t\tblocking_exec_context_id, resource_description,\r\n\t\tCASE WHEN [wait_type] LIKE 'PAGE%' AND [resource_description] LIKE '%:%' THEN CAST(RIGHT([resource_description], LEN([resource_description]) - CHARINDEX(':', [resource_description], LEN([resource_description])-CHARINDEX(':', REVERSE([resource_description])))) AS int)\r\n\t\t\tWHEN [wait_type] LIKE 'LCK%' AND [resource_description] LIKE '%pageid%' AND ISNUMERIC(RIGHT(LEFT([resource_description],CHARINDEX('dbid=', [resource_description], CHARINDEX('pageid=', [resource_description])+6)-1),CHARINDEX('=',REVERSE(RTRIM(LEFT([resource_description],CHARINDEX('dbid=', [resource_description], CHARINDEX('pageid=', [resource_description])+6)-1)))))) = 1 THEN CAST(RIGHT(LEFT([resource_description],CHARINDEX('dbid=', [resource_description], CHARINDEX('pageid=', [resource_description])+6)-1),CHARINDEX('=',REVERSE(RTRIM(LEFT([resource_description],CHARINDEX('dbid=', [resource_description], CHARINDEX('pageid=', [resource_description])+6)-1))))) AS bigint)\r\n\t\t\tELSE NULL END AS pageid,\r\n\t\tCASE WHEN [wait_type] LIKE 'LCK%' AND [resource_description] LIKE '%associatedObjectId%' AND ISNUMERIC(RIGHT([resource_description],CHARINDEX('=', REVERSE([resource_description]))-1)) = 1 THEN CAST(RIGHT([resource_description],CHARINDEX('=', REVERSE([resource_description]))-1) AS bigint)\r\n\t\t\tELSE NULL END AS [objid],\r\n\t\tROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num\r\n    FROM sys.dm_os_waiting_tasks (NOLOCK)\r\n) owt ON ot.task_address = owt.waiting_task_address AND owt.row_num = 1\r\n--OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) est\r\n--OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) eqp\r\nWHERE es.session_id <> @@SPID AND es.is_user_process = 1 \r\n\t--AND ((owt.wait_duration_ms/1000 > 5) OR (er.total_elapsed_time/1000) > 5 OR er.total_elapsed_time IS NULL) --Only report blocks > 5 Seconds plus head blocker\r\n\tAND (es.session_id IN (SELECT er3.blocking_session_id FROM sys.dm_exec_requests (NOLOCK) er3) OR er.blocking_session_id IS NOT NULL OR er.blocking_session_id > 0)\r\nORDER BY blocked_spid, is_head_blocker DESC, blocked_spid_wait_time_ms DESC, blocker_spid;",
            "metadata": {},
            "outputs": []
        },
        {
            "cell_type": "markdown",
            "source": "Report stored procedure stats",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "DECLARE @sqlmajorver int, @sqlcmd VARCHAR(4000)\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver >= 11\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT  ''Sproc_Stats_Report'' AS [Information], CASE WHEN ps.database_id = 32767 THEN ''ResourceDB'' ELSE DB_NAME(ps.database_id) END AS DatabaseName, \r\n\tCASE WHEN ps.database_id = 32767 THEN NULL ELSE OBJECT_NAME(ps.[object_id], ps.database_id) END AS ObjectName,\r\n\ttype_desc,\r\n\t(SELECT qt.text AS [text()] \r\n\t\tFROM sys.dm_exec_procedure_stats (NOLOCK) ps2 CROSS APPLY sys.dm_exec_sql_text(ps2.sql_handle) qt \r\n\t\tWHERE ps2.database_id = ps.database_id AND ps2.[object_id] = ps.[object_id] \r\n\t\tFOR XML PATH(''''), TYPE) AS [sqltext],\r\n\tqp.query_plan,\r\n\tps.cached_time,\r\n\tps.last_execution_time,\r\n\tps.execution_count,\r\n\tps.total_elapsed_time/ps.execution_count AS avg_elapsed_time,\r\n\tps.last_elapsed_time,\r\n\tps.total_worker_time/ps.execution_count AS avg_cpu_time,\r\n\tps.last_worker_time AS last_cpu_time,\r\n\tps.min_worker_time AS min_cpu_time, ps.max_worker_time AS max_cpu_time,\r\n\tps.total_logical_reads/ps.execution_count AS avg_logical_reads,\r\n\tps.last_logical_reads, ps.min_logical_reads, ps.max_logical_reads,\r\n\tps.total_physical_reads/ps.execution_count AS avg_physical_reads,\r\n\tps.last_physical_reads, ps.min_physical_reads, ps.max_physical_reads,\r\n\tps.total_logical_writes/ps.execution_count AS avg_logical_writes,\r\n\tps.last_logical_writes, ps.min_logical_writes, ps.max_logical_writes\r\n FROM sys.dm_exec_procedure_stats (NOLOCK) ps\r\n CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp'\r\n\tEXEC (@sqlcmd);\r\n END;",
            "metadata": {},
            "outputs": []
        },
        {
            "cell_type": "markdown",
            "source": "Report query stats",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "DECLARE @sqlmajorver int, @sqlcmd VARCHAR(4000)\r\nSELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);\r\n\r\nIF @sqlmajorver >= 11\r\nBEGIN\r\n\tSET @sqlcmd = N'SELECT CASE WHEN CONVERT(int,pa.value) = 32767 THEN ''ResourceDB'' ELSE DB_NAME(CONVERT(int,pa.value)) END AS DatabaseName,\r\n\t(SELECT st.text AS [text()] FROM sys.dm_exec_sql_text(qs.plan_handle) AS st FOR XML PATH(''''), TYPE) AS [sqltext],\r\n\tqs.creation_time AS cached_time,\r\n\tqs.last_execution_time,\r\n\tqs.execution_count,\r\n\tqs.total_elapsed_time/qs.execution_count AS avg_elapsed_time,\r\n\tqs.last_elapsed_time,\r\n\tqs.total_worker_time/qs.execution_count AS avg_cpu_time,\r\n\tqs.last_worker_time AS last_cpu_time,\r\n\tqs.min_worker_time AS min_cpu_time, qs.max_worker_time AS max_cpu_time,\r\n\tqs.total_logical_reads/qs.execution_count AS avg_logical_reads,\r\n\tqs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads,\r\n\tqs.total_physical_reads/qs.execution_count AS avg_physical_reads,\r\n\tqs.last_physical_reads, qs.min_physical_reads, qs.max_physical_reads,\r\n\tqs.total_logical_writes/qs.execution_count AS avg_logical_writes,\r\n\tqs.last_logical_writes, qs.min_logical_writes, qs.max_logical_writes\r\nFROM sys.dm_exec_query_stats (NOLOCK) AS qs\r\nCROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa\r\nWHERE pa.attribute = ''dbid'''\r\n\tEXEC (@sqlcmd);\r\nEND;",
            "metadata": {},
            "outputs": [],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": "Report acquired locks",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SELECT tl.*, sp.[object_id], sp.index_id \r\nFROM sys.dm_tran_locks (NOLOCK) tl\r\nLEFT JOIN sys.partitions (NOLOCK) sp ON tl.resource_associated_entity_id = sp.[hobt_id];",
            "metadata": {},
            "outputs": []
        }
    ]
}