Using NOCOUNT to Improve Procedure Performance

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 @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 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.ac

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.

StackExchange Database Administrators Q#102823, answer score: 9