What is the simplest way to drive a CPU to 100% using T-SQL? If it’s a language like C++ or C#, the simplest way is to create a tight loop and perform some work that uses CPU cycles inside the loop. For instance, the following tight loop in C# can peg a CPU to 100%:
while (true)
Math.Sin(2.5);
Note that on a multi-processor machine, this code may end up being executed on more than one processor, and you may not see any particular processor being pegged at 100%. But the cumulative effect is equivalent to keeping a single processor busy.
What about T-SQL? Does the following T-SQL code keep a CPU 100% busy?
set nocount on
go
declare @a float
while(1=1)
begin
select @a = cos(2.5);
end
If you try the above SQL batch as is in SQL Server Management Studio from a remote client, it’s unlikely that it would peg a CPU on the server side at 100%. When I ran the above code, it kept a single CPU at about 20~30%. So apparently, the loop in this little piece of T-SQL code is not tight enough to keep a CPU fully occupied.
Note To help see the CPU usage clearly, for all the T-SQL tests in this post I explicitly set the affinity mask option on the SQL Server instance to use only a single processor.
Is there a way to execute a tight loop in T-SQL? Yes, all you need to do is to put the above code inside a stored procedure:
create proc p_test
as
set nocount on
declare @a float
while(1=1)
begin
select @a = cos(2.5);
end
If you execute proc p_test, you would see a CPU being pegged at 100%.
What is the difference between executing a loop in a SQL batch and inside a stored procedure for them to exhibit such dramatically different CPU usage?
The difference is that while executing the loop in a batch, SQL Server has to yield numerous times to chat with the client, whereas when it’s executing the loop inside a stored procedure, it doesn’t. [Updated. Note that as Andy Kelly pointed out, this was when SET NOCOUNT ON was in effect. All bets are off if you don't do SET NOCOUNT ON.]
An example should make this abundantly clear. Let’s modify the loop a bit to iterate a finite number of times (say 10,000,000 times):
The batch:
set nocount on
go
declare @a float,
@i int = 1
while(@i<=10000000)
begin
select @a = cos(2.5);
select @i += 1;
end
The stored procedure:
create proc p_test
as
set nocount on
declare @a float,
@i int = 1
while(@i<=10000000)
begin
select @a = cos(2.5);
select @i += 1;
end
In my test environment, the batch finished in about 33 seconds, whereas the stored procedure finished in about 4 seconds. The stored procedure was about eight times faster!
It was most revealing to look at the client statistics. With the batch, the client sent one TDS packet to the server, but received 151,623 TDS packets from the server. In contrast, with the stored procedure, the client sent one TDS packet and received one TDS packets.
It’s dramatically chattier to execute the batch than the stored procedure. In fact, it’s so chatty that it is unable to keep the CPU busy doing useful work.