THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: stored procedures, SQL batches, and CPU usage

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.

Published Friday, July 22, 2011 12:47 AM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

GrumpyoldDBA said:

nice simple example, thanks

July 22, 2011 2:49 AM
 

Andrew Kelly said:

You might want to point out that if you didn't SET NOCOUNT ON it would have been quite different. Worse that is :).

July 22, 2011 7:48 AM
 

Linchi Shea said:

Good point Andy! I added a line to highlight that fact.

July 22, 2011 9:06 AM
 

Jeff Moden said:

Build an 11,000 row Tally table and then run this code.  It'll peg a CPU for ya. ;-)

DECLARE @BitBucket FLOAT

SELECT TOP 121000000

       @BitBucket = cos(2.5)

  FROM dbo.Tally t1

 CROSS JOIN dbo.Tally t2

February 13, 2013 1:38 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement