This article outlines a serverless architecture for monitoring SQL Server blocking chains and long-running queries on Amazon RDS. It utilizes a Lambda function to poll system dynamic management views, identify root blockers via recursive CTEs, and trigger email alerts via Amazon SES.
Key points
Use sys.dm_exec_requests to identify sessions where blocking_session_id is non-zero.
Recursive Common Table Expressions (CTEs) are essential for tracing blocking chains back to the root session.
Aligning polling frequency with performance thresholds helps minimize noise and false positives in automated alerts.
System background processes should be explicitly filtered out when querying for long-running queries to ensure signal accuracy.