Lesson 49. Boost Performance When Calling A Stored Proc From SSIS

If you are calling a proc from an SSIS package, there is no need to get the row count of the records impacted by your query. By setting NOCOUNT to ON, you can significantly boost performance due to the drop in network traffic.

Examples

Turning NOCOUNT On

In [ ]:

USE AdventureWorks2016

DROP PROCEDURE IF EXISTS dbo.usp_NoCountExample
GO

CREATE PROCEDURE dbo.usp_NoCountExample

AS
BEGIN

SET NOCOUNT ON;

SELECT *
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID

SET NOCOUNT OFF;

END
GO