SQL Server seems not to use processor enough

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ferenc Szidarovszky (ferenc.szidarovszky_at_szidarovszky.com)
Date: 06/24/04 

Date: Thu, 24 Jun 2004 05:04:18 -0700

We had some performance problems with one of our systems,
and our investigation came to the following.

We created a stored procedure:
create procedure xxx as
begin
  declare @i integer
  set @i = 0
  while @i < 100000000
  begin
    set @i = @i + 1
  end
end
go

Executing this procedure on several different computers we
mostly get <20% worth of processor usage, but on some we
get 100%.
Also, the latter finishes much faster (the ratio of times
is about in conjuction with the ratio of processor usage).

Notes:
------
The term "worth of processor usage" means one processor.
So <20% worth of processor usage means <20% on a one-
processor and <5% on a four processor machine.
We measured processor usage with task manager.
We tested this behaviour on different versions and
releases of SQL Server with same results.
The operating systems were all versions of Windows 2000.