For me, seeing is believing when it comes to SQL Server performance issues. During the past few years, I've tried a lot of tips that have yielded amazing results, whereas other tips have offered barely a whisper of performance gains in real-world situations. Implementing all the techniques that are supposed to give you optimal performance is almost impossible, which inevitably leads you to implement arcane tips that achieve only trivial gains while you miss out on simple-to-implement solutions that yield truly substantial performance improvements.
SET NOCOUNT ON is a technique that I know can yield amazing results. Unfortunately, it's so simple that many people ignore it. SQL Server Books Online (BOL) says this about SET NOCOUNT ON: "When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft SQL Server(tm) to execute queries, the results prevent 'nn rows affected' from being displayed at the end \[of\] Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE. For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced."
"Significant performance boost"—Microsoft's words, not mine. How significant? You can use the following procedure to demonstrate the gain you can achieve using SET NOCOUNT ON inside stored procedures that issue many SQL statements. The procedure accepts two parameters: @NoCount and @NumRows. A value of 1 for @NoCount causes the procedure to issue SET NOCOUNT ON, and the value of @NumRows tells the procedure how many rows to insert into a simple local temporary table the procedure creates.
CREATE PROC NoCountOnTest
@NoCount int
,@NumRows int
AS
CREATE TABLE #NoOrderByClauseOnGUID
(
RowIdent int identity
,RowGUID uniqueidentifier default newid()
)
IF @NoCount = 1
BEGIN
SET NOCOUNT ON
END
ELSE
BEGIN
SET NOCOUNT OFF
END
DECLARE @CurrInsert int
SET @CurrInsert = 0
BEGIN TRAN
WHILE @CurrInsert < @NumRows
BEGIN
INSERT INTO #NoOrderByClauseOnGUID default values
SET @CurrInsert = @CurrInsert +1
END
COMMIT TRAN
GO
I executed this procedure a few times for SET NOCOUNT ON and SET NOCOUNT OFF, using a @NumRows of 10,000 each time. Results varied from machine to machine, but my laptop ran the procedure in about 800ms with SET NOCOUNT ON and took close to 8 seconds with the setting OFF. In some cases, you could achieve a HUGE performance difference.
What accounts for the significant difference in performance? Nothing more than the network traffic and latency associated with sending the DONE_IN_PROC messages back to the client after executing each statement in the procedure. These time differences happened on a self-contained laptop. The performance differences could be even greater on a computer running across a network with a Web browser in the middle. Now that you've seen the proof, consider implementing this simple tuning technique in your own application architectures.
Although SET NOCOUNT ON can be your friend, it's not a panacea for all tuning problems. Performance tuning is part art, part science, and part experimentation. Put tuning tips to the test in your environment so you can see with your own eyes what they can do. Doing so will help you cut through the myriad tuning hints that are irrelevant and focus on the techniques that really matter in your environment.
