SQL Server Agent and SET NOCOUNT ON

26 Apr 2007 7:55 AM

Stephen Ahn

Using SQL Server 2000 sp4.

Hi,

Consider the following code :
==
create table dbo.t2 (id int primary key, c1 int)
-- there are no rows in t2
GO

CREATE proc dbo.p2 as
declare @i int
set @i = 0
while @i < 1000 begin
update t2 set c1 = 1 where id = 2
set @i = @i + 1
end

GO
CREATE proc dbo.p2_noc as
SET NOCOUNT ON
declare @i int
set @i = 0
while @i < 1000 begin
update t2 set c1 = 1 where id = 2
set @i = @i + 1
end
==

The only difference between dbo.p2 and dbo.p2_noc, is that p2_noc has SET
NOCOUNT ON as the first line (as is best practice). What I'm curious about,
is that when run as an SQL Agent Job, p2 is *much* more slower than p2_noc.
eg. on my system, p2 takes 11 seconds, whereas p1 is virtually
instantaneous. In a tool like SQL Query Analyzer, the difference is much
less. eg. p2 takes about 1 second.

Can anyone shed any light into why SQL Server Agent is so much slower ? I'm
more curious than anything.

Thanks,
Stephen

26 Apr 2007 9:26 PM

Russell Fields

Stephen,

Each UPDATE in the loop sends a response to the client, namely the count of
how many rows were updated. This means that the client is handling each
message, which is extra overhead. In Query Analyzer, these messages appear
in the message tab.

So, you are asking the job to handle messages for which it can do nothing
but throw them away.

Now, why is it so much slower in SQL Agent than Query Analyzer? I don't
know.

FWIW, some client applications (probably those that are now getting aged) do
not gracefully process these messages either, which can result in
application problems.

RLF
Show quote

"Stephen Ahn" <noaddress> wrote in message
news:%236bvFh9hHHA.1244@TK2MSFTNGP04.phx.gbl...
> Using SQL Server 2000 sp4.
>
> Hi,
>
> Consider the following code :
> ==
> create table dbo.t2 (id int primary key, c1 int)
> -- there are no rows in t2
> GO
>
> CREATE proc dbo.p2 as
> declare @i int
> set @i = 0
> while @i < 1000 begin
> update t2 set c1 = 1 where id = 2
> set @i = @i + 1
> end
>
> GO
> CREATE proc dbo.p2_noc as
> SET NOCOUNT ON
> declare @i int
> set @i = 0
> while @i < 1000 begin
> update t2 set c1 = 1 where id = 2
> set @i = @i + 1
> end
> ==
>
> The only difference between dbo.p2 and dbo.p2_noc, is that p2_noc has SET
> NOCOUNT ON as the first line (as is best practice). What I'm curious
> about, is that when run as an SQL Agent Job, p2 is *much* more slower than
> p2_noc. eg. on my system, p2 takes 11 seconds, whereas p1 is virtually
> instantaneous. In a tool like SQL Query Analyzer, the difference is much
> less. eg. p2 takes about 1 second.
>
> Can anyone shed any light into why SQL Server Agent is so much slower ?
> I'm more curious than anything.
>
> Thanks,
> Stephen
>
>
>

27 Apr 2007 4:16 AM

Stephen Ahn

Russell,

Thanks for your response.

I also performed the test using the SQL Agent that comes with SQL2005. This
version of SQL Agent seemed much better behaved than the one that comes with
SQL2000 - p2 now returns almost straight away.

Interesting...

Show quote

"Russell Fields" <russellfie***@nomail.com> wrote in message
news:OwBcImEiHHA.1624@TK2MSFTNGP06.phx.gbl...
> FWIW, some client applications (probably those that are now getting aged)
> do not gracefully process these messages either, which can result in
> application problems.
>