How to Add Clippy Support to SSMS

Step 1: Install the Clippy digital assistant SSMS add-on

Go to github and grab the code. Clippy wants you to be on a recent version of SQL Server so only SQL Server 2022+ is supported.

Step 2: Create your first Clippy alert and schedule it

For illustrative purposes, below is a very basic check that throws an alert if CPU >= 80%

DECLARE
    @CPUFromBuffer INT,
    @LogMessage VARCHAR(100);

select TOP (1) @CPUFromBuffer = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
from (
    select timestamp, convert(xml, record) as record
    from sys.dm_os_ring_buffers
    where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
    and record like '%<SystemHealth>%'
) as RingBufferInfo
ORDER BY timestamp DESC;

IF @CPUFromBuffer >= 80
BEGIN
    SET @LogMessage = 'Your SQL Server is using ' + CAST(@CPUFromBuffer AS VARCHAR(10)) + '% of available CPU.'
    EXEC dbo.AddPaperClipToErrorLog @LogMessage;
END;

Step 3: Trigger the alert

On my local machine with 8 physical cores I can simply run the following query and wait:

 WITH vCTE AS (
      SELECT v.v
      FROM
      (VALUES
      (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95)
      ) v(v)
)
SELECT MIN(v0.v + ca.v)
FROM vCTE v0
CROSS APPLY (
      SELECT MIN(v1.v + v2.v + v3.v + v4.v + v5.v) v
      FROM vCTE v1
      CROSS JOIN vCTE v2
      CROSS JOIN vCTE v3
      CROSS JOIN vCTE v4
      CROSS JOIN vCTE v5
      WHERE v1.v > (v0.v - 96)
) ca
OPTION (NO_PERFORMANCE_SPOOL, MAXDOP 8, USE HINT (N'ENABLE_PARALLEL_PLAN_PREFERENCE'));

Step 4: Check for alerts in the error log

a69 paperclip

The post How to Add Clippy Support to SSMS appeared first on Darling Data.