使用 NOCOUNT 提高過程性能

我希望提高某個程序的性能，我想從插入SET NOCOUNT ON.

我讀過幾篇關於這個主題的文章：

亞倫伯特蘭

SET NOCOUNt ON 提高 SQL Server SP 性能

但我不太明白的是，如果每個程序都需要一次，還是每次“開始/結束”時都需要插入

例如，在下面的過程中 - 我應該插入SET NOCOUNT ON正確的最後一個變數：set @PrintInfo = 'No Trip Number...

或者我是否需要SET NOCOUNT ON在程序中的每個“開始”之後插入：

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

每個過程只需要SET NOCOUNT ON;一次，最好是在過程本身的頂部。當然，在生成輸出的任何語句之前，您都需要它。

因此，例如，我會使用這樣的東西作為創建過程的模板：

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

線上圖書這樣說SET NOCOUNT ON：

停止顯示受 Transact-SQL 語句或儲存過程影響的行數的消息作為結果集的一部分返回。

SET NOCOUNT ON 防止為儲存過程中的每個語句向客戶端發送 DONE_IN_PROC 消息。對於包含多個不返回太多實際數據的語句的儲存過程，或者對於包含 Transact-SQL 循環的過程，將 SET NOCOUNT 設置為 ON 可以顯著提高性能，因為網路流量大大減少。

正如我上面所概述的，在過程主體的開頭設置此選項可以很容易地驗證語句實際上是否在過程中。

請注意，某些軟體（尤其是用於連結伺服器的 SQL Server 本身）使用行計數功能來確定執行的 DML 是否成功。設置NOCOUNT ON可能會導致出現您未預料到的錯誤，並且可能難以排除故障。另請注意， @AaronBertrand的以下評論和建議：

要記住的一件事（以及我在推薦 NOCOUNT 時給出的免責聲明）是它可能會干擾某些技術。例如，如果您有舊的 ADO 程式碼（在 ASP.NET 之前），它將 DONE_IN_PROC 消息解釋為獨立的結果集，因此您現有的程式碼可能已經有類似 rs.nextRecordSet() 的東西來跳過它們。此外，實體框架中的某些模組（可能還有其他 ORM）依賴這些消息來確定 DML 操作的成功。因此，如果您使用這些技術並且已經有工作程式碼，請不要盲目地將它們添加到您的所有程式碼中。

引用自：https://dba.stackexchange.com/questions/102823