Using NOCOUNT to Improve Procedure Performance

Question

I am looking to improve the performance of a certain procedure and I wanted to begin with the insertion of the SET NOCOUNT ON.

I've read a couple of articles on the subject:

Aaron Bertrand

SET NOCOUNt ON Improves SQL Server SP Performance

But what I don't really understand is if this is needed once per procedure, or would it need to be inserted every time you have "begin / end"

For example, in the procedure below - should I insert the SET NOCOUNT ON right have the last variable is set: set @PrintInfo = 'No Trip Number...

Or would I need to insert the SET NOCOUNT ON after every "begin" in the procedure:

Create procedure [dbo].SSIS_UpdateDriveResults
(
@RSADriveID nvarchar (50),
@ProcedureID int,
@Registered int,
@Performed  int,
...
)
as 

set @ResultError = 0
set @ResultMessage = ''

declare @Id int
declare @Name varchar(64)
declare @DriveId int
declare @ErrorMessage nvarchar(255)
...

Set @ExternalIDs = cast(@RSADriveID as nvarchar(50))
set @IsFixedSite = 'N'
set @CurrentDate = getdate()
set @UpdateWho = -1
set @PrintInfo = ' No Trip Number   ' + convert(varchar(8), @RSADriveID, 1) 

select @UpdateWho = personid from db_name.[dbo].peoplelogindetail where loginid = 'RMADMIN'

if @UpdateWho <= 0
begin
set @ResultError = 1
set @ResultMessage = 'ERROR:Unable to locate employee with login RMADMIN for inserts. ' + @PrintInfo
print @ResultMessage
return
end


--Get the account or fixed site    
select top 1 @Id = a.accountid, @Name = a.name 
from db_name.[dbo].accounts a 
where a.accountid in
    (Select accountid from db_name.[dbo].drivemaster where deleted = 0 and statusid not in (5) and driveid in
        (select driveid from db_name.[dbo].DriveShiftDetail where ShiftID = (@RSADriveID))) --ShiftID

if(@Id is null or @Id <=0 )
begin
-- See if this is a fixed site
select @Id = dm.centerid, @Name = cd.desclong 
    from db_name.[dbo].drivemaster dm 
    join db_name.[dbo].centerdetail cd on dm.centerid = cd.centerid 
    where dm.deleted = 0 and dm.statusid not in (5) and dm.driveid in
        (select driveid from db_name.[dbo].DriveShiftDetail where ShiftID = (@RSADriveID)) --ShiftID

if(@Id > 0 )
begin
    set @IsFixedSite = 'Y'
end
end

-- Locate the drive
select
@DriveId = dm.driveid,
@DriveDate = dm.fromdatetime,
@Name=case when dm.drawid>0 then cd.desclong else a.name end

from db_name.[dbo].drivemaster dm
left outer join db_name.[dbo].accounts a on a.accountid=dm.accountid
left outer join db_name.[dbo].centerdetail cd on cd.centerid=dm.centerid
where dm.deleted = 0 and dm.statusid not in (5) and dm.driveid in
    (select driveid from db_name.[dbo].DriveShiftDetail where ShiftID = (@RSADriveID))

if(@DriveId is null or @DriveId <=0 )
begin
--For Historical Drives
select top 1 @DriveId = dm.driveid, @DriveDate = dm.fromdatetime, @Name=case when dm.drawid>0 then cd.desclong else a.name end, @ShiftID = dsd.ShiftID 
from db_name.[dbo].drivemaster dm
join db_name.[dbo].DriveShiftDetail dsd on dsd.DriveID=dm.DriveID
left outer join db_name.[dbo].accounts a on a.accountid=dm.accountid
left outer join db_name.[dbo].centerdetail cd on cd.centerid=dm.centerid

where dm.deleted = 0
and dm.statusid not in (5) 
and dm.externalid like @ExternalIds
and isnumeric(dm.ExternalID)=1

order by dsd.ShiftStart asc, dsd.ShiftID asc

if(@DriveId is null or @DriveId <=0 )
begin
    set @ResultError = 1
    set @ResultMessage = 'Unable to locate drive.' + @PrintInfo
    print @ResultMessage
    return
end
end
set @PrintInfo = '  Trip Number:( ' + convert(varchar(8), @RSADriveID, 1) + ' )  Name: ' + @Name
set @PrintInfo = @PrintInfo + '  Date: ' + convert(varchar(10), @DriveDate, 101)

...

if not exists (select * from db_name.[dbo].DriveShiftActualDetail where ShiftID=@ShiftId)
begin
--Insert Missing DriveShiftActualDetail Rows
INSERT INTO db_name.[dbo].DriveShiftActualDetail
    (ShiftID,FirstTimeDonors,Registered,Voids,QNS,Deferrals,Collected,Contaminated,Cancellations,TurnAways,WalkIns,SelfDeferrals,NoShows,ShiftStart,
    ShiftEnd,HadLunch,LunchStart,LunchEnd,ActualStaff,SignupReduction,UpdateWho,UpdateWhen,UniqueKey,DonorsScheduled,MildReactions,ModReactions,SevReactions)

select
    dsd.ShiftID,0,0,0,0,0,0,0,0,0,0,0,0,dsd.ShiftStart,dsd.ShiftEnd,dsd.HasLunch,dsd.LunchStart,dsd.LunchEnd,
    isnull((select count(distinct personid) from db_name.[dbo].peoplestaffingdetail where shiftid in (select shiftid from db_name.[dbo].staffingeventshiftdetail where driveshiftid=dsd.ShiftID)),0),
    dsd.SignupReduction,dbo.HemasphereUserNo(),getdate(),newid(),dsd.DonorsScheduled,0,0,0

from db_name.[dbo].DriveShiftDetail dsd

where not exists
(
    select dsad.* from db_name.[dbo].DriveShiftActualDetail dsad where dsad.shiftid=dsd.shiftid
)
and dsd.ShiftID=@ShiftId

if(@@Error <> 0)
begin
    select @ErrorMessage = description from master.dbo.sysmessages where error = @@Error
    set @ResultError = 1
    set @ResultMessage = 'ERROR:Error Inserting the Drive Shift Actual record. ' + @PrintInfo
end 
end

Answer 1

You only need to SET NOCOUNT ON; once per procedure, preferably at the top of the body of the procedure itself. Certainly you'd need it prior to any statement that generates output.

So, for instance, I'd use something like this as a template for creating procedures:

CREATE PROCEDURE dbo.MyProc
AS
BEGIN
    SET NOCOUNT ON;
    ....
END
GO

Books Online says this about SET NOCOUNT ON:

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Setting this option at the start of the procedure body, as I've outlined above, makes it easy to verify the statement is actually in the procedure.

Be aware, the row count feature is used by certain software, not least of which is SQL Server itself for Linked Servers, to ascertain whether or not the executed DML was successful. Setting NOCOUNT ON may cause errors to occur that you are not expecting, and that can be difficult to troubleshoot. Also note, the following comment and advice from @AaronBertrand:

One thing to keep in mind (and a disclaimer I give whenever I recommend NOCOUNT), is that it can interfere with certain technologies. For example if you have old ADO code (prior to ASP.NET), it interprets the DONE_IN_PROC messages as independent resultsets, so your existing code may already have things like rs.nextRecordSet() to skip them. Also certain modules in Entity Framework (and probably other ORMs) rely on those messages to determine success of DML operations. So don't just blindly add them to all of your code if you use these technologies and already have working code.