adding blocking queries and documentation for all queries

cmgoffena13 · Mar 10, 2023 · a969923 · a969923
1 parent b7728f3
commit a969923
Show file tree

Hide file tree

Showing 8 changed files with 80 additions and 0 deletions.
diff --git a/Active_Jobs.sql b/Active_Jobs.sql
@@ -1,6 +1,11 @@
 USE [msdb];
 GO
 
+/*====================================================================================================
+Active Jobs - Cortland Goffena
+
+Shows current running jobs, status, their runtime, and the last step that could execute
+====================================================================================================*/
 
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 SELECT

diff --git a/Active_Queries.sql b/Active_Queries.sql
@@ -1,6 +1,12 @@
 USE [master];
 GO
 
+/*====================================================================================================
+Active Queries - Cortland Goffena
+
+Shows current running queries and a multitude of details 
+====================================================================================================*/
+
 /*
 KILL 194                        --Kills session
 KILL 194 WITH STATUSONLY        --Shows progress on rollback after kill

diff --git a/Blocking_Queries.sql b/Blocking_Queries.sql
@@ -0,0 +1,39 @@
+USE [master];
+GO
+
+/*====================================================================================================
+Blocking Queries - Cortland Goffena
+
+Shows current blocking queries and corresponding information about blockages
+====================================================================================================*/
+
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
+SELECT
+DB_NAME( tl.resource_database_id ) AS DatabaseName,
+er.blocking_session_id AS BlockingSessionID,
+tll.request_type AS BlockingRequestType,
+ss.text AS BlockingSQLScript,
+tl.request_session_id AS WaitingSessionID,
+tl.request_type AS WaitingRequestType,
+s.text AS WaitingSQLScript,
+wt.resource_description AS ResourceDescription,
+er.wait_type AS WaitType,
+CASE
+    WHEN wt.wait_duration_ms > 360000000 THEN 'Too Long'
+    ELSE RIGHT('0' + CAST( ( wt.wait_duration_ms / 1000 ) / 3600 AS VARCHAR(2) ), 2 ) + ':' +
+            RIGHT('0' + CAST( ( ( wt.wait_duration_ms / 1000 ) / 60 ) % 60 AS VARCHAR(2) ), 2 ) + ':' + 
+            RIGHT('0' + CAST( ( wt.wait_duration_ms / 1000 ) % 60 AS VARCHAR(2) ), 2 )
+END AS WaitTime,
+tl.resource_associated_entity_id AS WaitingAssociatedEntity,
+tl.resource_type AS WaitingResourceType
+FROM sys.dm_tran_locks AS tl
+INNER JOIN sys.dm_os_waiting_tasks AS wt
+    ON wt.resource_address = tl.lock_owner_address
+INNER JOIN sys.dm_exec_requests AS er
+    ON er.session_id = tl.request_session_id
+OUTER APPLY sys.dm_exec_sql_text ( er.sql_handle ) AS s
+LEFT JOIN sys.dm_exec_requests AS b
+    ON b.session_id = wt.blocking_session_id
+OUTER APPLY sys.dm_exec_sql_text ( b.sql_handle ) AS ss
+LEFT JOIN sys.dm_tran_locks AS tll
+    ON tll.request_session_id = b.session_id
diff --git a/Job_Report.sql b/Job_Report.sql
@@ -1,6 +1,11 @@
 USE [msdb];
 GO
 
+/*====================================================================================================
+Job Report - Cortland Goffena
+
+Shows current status of job and history of the job for comparisons
+====================================================================================================*/
 
 DECLARE @JobName NVARCHAR(255) = 'JobName'
 

diff --git a/Performance/Stored_Procedure_Performance.sql b/Performance/Stored_Procedure_Performance.sql
@@ -1,6 +1,12 @@
 USE {database_name};
 GO
 
+/*====================================================================================================
+Stored Procedure Performance - Cortland Goffena
+
+Shows procedures with highest total logical reads to help pinpoint performance tuning opportunities
+Note: stats may be reset by some sql server backend actions, as well as a restart
+====================================================================================================*/
 
 SELECT TOP 50
 CONCAT( (

diff --git a/Performance/Stored_Procedure_Performance_Breakdown.sql b/Performance/Stored_Procedure_Performance_Breakdown.sql
@@ -1,6 +1,13 @@
 USE {database_name};
 GO
 
+/*====================================================================================================
+Stored Procedure Performance Breakdown - Cortland Goffena
+
+Shows distribution breakdowns for a stored procedure and its query statements
+This helps pintpoint the problem query in a stored procedure. Use in tandem with Stored_Procedure_Performance.
+Note: stats may be reset by some sql server backend actions, as well as a restart
+====================================================================================================*/
 
 DECLARE @ProcedureName VARCHAR(120) = 'schema.name'
 

diff --git a/Performance/Table_Indexes.sql b/Performance/Table_Indexes.sql
@@ -1,6 +1,12 @@
 USE {database_name}; 
 GO
 
+/*====================================================================================================
+Table Indexes - Cortland Goffena
+
+Shows all indexes on table and corresponding information such as usage, size, and object definition
+Note: stats may be reset by some sql server backend actions, as well as a restart
+====================================================================================================*/
 
 DECLARE @TableName VARCHAR(100) = 'schema.tablename'
 

diff --git a/TableColumns_SpaceUsed.sql b/TableColumns_SpaceUsed.sql
@@ -1,6 +1,12 @@
 USE {database_name};
 GO
 
+/*====================================================================================================
+Table Columns Space Used - Cortland Goffena
+
+Shows datatype of each column and the space used by each column. 
+It must scan entire table for the results.
+====================================================================================================*/
 
 DECLARE @TableName sysname = 'schema.name';