Once a day or so, I'll get "SQL Server Blocking Detected" alerts where SQL Sentry is getting blocked, not any queries from my application. Should I care? I don't see any 'important' queries from my applications getting blocked. Example SQL Sentry blocked query is shown below as spelled out in the email alert.
Blocked SPID:
SPID [ecid]: 111 [0]
Blocked by SPID: 171
Client Machine: [** REDACTED **]
Client Process ID: 4000
Application: SolarWinds SQL Sentry 2023.3-Server
Login Name: [** REDACTED **]
Last Batch: 5/29/2024 3:00:00 PM
Wait Type: LCK_M_SCH_S
Wait Resource:
Wait Time: 00:00:15
Database: master
Command Text:
WITH tempdbObjects
AS
(
SELECT
ObjectType =
CASE o.type_desc
WHEN 'USER_TABLE' THEN
CASE
WHEN LEFT(o.name, 1) = '#'
THEN
CASE
WHEN LEFT(o.name, 2) = '##'
THEN 'GLOBAL_TEMP_TABLE'
WHEN LEN(o.name) = 9
THEN 'QUERY_OBJECT'
ELSE
'USER_TEMP_TABLE'
END
ELSE 'USER_TABLE'
END
ELSE
o.type_desc
END
,ps.row_count
,ps.used_page_count
,ps.reserved_page_count
FROM tempdb.sys.indexes AS i WITH (NOLOCK)
INNER JOIN tempdb.sys.objects o WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN tempdb.sys.dm_db_partition_stats AS ps WITH (NOLOCK)
ON i.[object_id] = ps.[object_id]
AND i.index_id = ps.index_id
)
SELECT
ObjectType
,ObjectCount = COUNT(1)
,[RowCount] = SUM(row_count)
,UsedSpaceKB = SUM(used_page_count) * 8
,ReservedSpaceKB = SUM(reserved_page_count) * 8
FROM tempdbObjects
GROUP BY
ObjectType
ORDER BY
ObjectType ASC
OPTION (KEEPFIXED PLAN)
