SQL Server: generate CPU load for a test
Two T-SQL blocks that keep a session busy for 30 or 60 seconds, to test CPU monitoring.
Two T-SQL blocks that keep one logical processor busy for a given time, to test a CPU alert or dashboard. The loop runs in a single session, so it loads one logical processor, not the whole server. The duration is set in the DATEADD function.
30-second load
-- Query to Keep CPU Busy for 30 Seconds
DECLARE @T DATETIME, @F BIGINT;
SET @T = GETDATE();
WHILE DATEADD(SECOND,30,@T)>GETDATE()
SET @F=POWER(2,30);
60-second load
-- Query to Keep CPU Busy for 60 Seconds
DECLARE @T DATETIME, @F BIGINT;
SET @T = GETDATE();
WHILE DATEADD(SECOND,60,@T)>GETDATE()
SET @F=POWER(2,30);
Notes
- Use only on a test instance.