Improve SQL Server Stored Procedure performance with SET NOCOUNT ON

By default the value of SET NOCOUNT is OFF in SQL Server, and for each SELECT, INSERT, UPDATE, and DELETE statement inside the stored procedure a message with the number of affected rows by that statement is returned. When stored procedures are executed there is usually no need for this information, and by setting SET NOCOUNT ON, we can disable it to be passed back to the stored procedure caller (application). Setting SET NOCOUNT ON can provide a performance benefit because network traffic is reduced, especially in stored procedures that contain many DML statements.

Here is an example for creating a stored procedure with SET NOCOUNT ON option:

CREATE PROC dbo.usp_test_procedure
SELECT Column1, Column2, Column3 FROM dbo.test_table

Function @@ROWCOUNT can be used inside your stored procedures to identify the number of rows affected by the last statement (if the number of rows is more than 2 billion, use ROWCOUNT_BIG).
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

See article: @@ROWCOUNT with SET NOCOUNT ON in SQL Server