Avoiding unnecessary overhead with stored procedures

In stored procedures, use the SET NOCOUNT ON notation even when there's a requirement to return current row count during execution, like 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.

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 that executes the...