SQL Query Connection Examples

Useful cases for the SQL Query Connection feature.

1. Business KPI Monitoring (extension of existing example)

Use case:
Monitoring the number of business operations, such as the number of orders per day.
The goal is to detect a drop in activity that may indicate a process or integration failure.

Example script:

SELECT COUNT(*) AS OrdersCount
FROM dbo.Orders
WHERE OrderDate >= CAST(GETDATE() AS DATE);

How to use:
Set an alert when the value is below a threshold (for example, less than 100).

---

2. Locks / Blocking by Database

Use case:
Detect a situation where there is an unusual number of blocked sessions within a specific database – this may indicate load issues, deadlocks, or heavy queries.

Example script:

SELECT COUNT(*) AS blocked_sessions
FROM sys.dm_exec_requests r
WHERE r.database_id = DB_ID('YourDatabaseName')
  AND r.blocking_session_id <> 0;

How to use:
Set an alert when the value is above a threshold (for example, greater than 3 or 5).

---

3. “Dirty” / Bloated Tables in Priority Systems

Use case:
In Priority systems, there are many working tables (such as staging / interface / temporary tables) that can grow significantly if a process gets stuck or cleanup is not performed.
Monitoring these tables helps detect early abnormal data accumulation (“bloated tables”), which may impact performance and even cause issues.

Example script:

WITH cte AS
(
    SELECT  
        t.name AS TableName,
        p.rows AS RowCounts
    FROM sys.tables t
    INNER JOIN sys.indexes i 
        ON t.object_id = i.object_id
    INNER JOIN sys.partitions p 
        ON i.object_id = p.object_id 
       AND i.index_id = p.index_id
    GROUP BY t.name, p.rows
)
SELECT TOP (1)
    CASE 
        WHEN RowCounts > 2000000 THEN 1 
        ELSE 0 
    END AS table_over_size
FROM cte
-- You can exclude known tables if needed
-- WHERE TableName NOT IN ('TABLE_NAME_TO_EXCLUDE')
ORDER BY RowCounts DESC;

How to use:
Set an alert when the value is greater than 0.