Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Using SET NOCOUNT ON in Stored Procedures

Author  Topic  Garth SQLTeam Author 119 Posts Posted - 2001-08-10 : 09:19:12 The performance tuning tip shown below was taken from an editorial written by Brian Moran of SQL Server Magazine. It's good stuff... Garth www.SQLBook.com "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."