Avoiding unnecessary overhead with stored procedures

In stored procedures, use the SET NOCOUNT ON notation even when there’s a requirement to return the current row count during execution, as in the following example:

CREATE OR ALTER PROCEDURE [dbo].[uspStocksPerWorkOrder] @WorkOrderID [int]
AS
BEGIN
SET NOCOUNT ON;
     SELECT wo.StockedQty, wor.WorkOrderID
     FROM Production.WorkOrder AS wo
     LEFT JOIN Production.WorkOrderRouting AS wor
ON wo.WorkOrderID = wor.WorkOrderID
     WHERE wo.WorkOrderID = @WorkOrderID;
END;

When SET NOCOUNT is ON, the count indicating the number of rows affected by a T-SQL statement is not returned to the application layer, which provides a performance boost.

Note

The @@ROWCOUNT function will still be incremented even with SET NOCOUNT ON.

To put this to a test, we can use the ostress utility and simulate a client application executing...