SET NOCOUNT ON Improves SQL Server Stored Procedure Performance

By:   |   Comments (16)   |   Related: > Stored Procedures


Problem

One of the biggest things that DBAs try to do on a daily basis is to ensure that their database systems run as fast as possible. As more and more users access the databases and the databases continue to grow, performance slow downs are almost inevitable. Based on this, DBAs and developers should do everything they possibly can to keep performance related issues in mind early in the database lifecycle. This is not always easy to do, because of the unknowns and the changes that occur over time, but there are some simple things that can be done and we will touch upon one of these in this tip.

Solution

Sometimes even the simplest things can make a difference. One of these simple items that should be part of every stored procedure is SET NOCOUNT ON. This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

Here is the old template style available in SQL Server 2000 without the SET NOCOUNT ON.

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name 
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>' 
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

CREATE PROCEDURE <procedure_name, sysname, proc_test> 
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, 
<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
SELECT @p1, @p2
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <value_for_param2, , 2>
GO

Here is the new template style available in SQL Server 2005 with the SET NOCOUNT ON.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END GO

As you can see even simple little things such as this can make an overall improvement for your database environment. Stay tuned for other simple tricks and techniques to improve performance.

Next Steps
  • Review your code to see if you are using the SET NOCOUNT ON
  • Modify your code to add this simple one line of code
  • Check out these commands and functions in books online


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, September 19, 2019 - 10:30:20 AM - Greg Robidoux Back To Top (82508)

Hi Uchaich,

Here are some suggestions.

You probably need some indexes on the columns used in your WHERE clause.

Using an IN clause with several values is very efficient.  You could create a temp table with the values and join to the temp table.

You shoudn't use the line below, this will cause the need to look at more rows than needed. Take a look at this tip https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/

   convert(varchar,convert(date, [Date], 103),101) between @StartDate and @EndDate

I would also not use the CONVERT function in the ORDER BY if possible.

Also, you should try not to use SELECT * unless you really need all columns.


Thursday, September 19, 2019 - 2:03:20 AM - uchaiah swamy N J Back To Top (82504)
USE [RMS]
GO
/****** Object:  StoredProcedure [dbo].[GetRawDataForPowerAndFuel]    Script Date: 19-09-2019 11:30:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[GetRawDataForPowerAndFuel] --1171,'08-01-2019 00:00:00','08-31-2019 23:59:59',330
(
   @SiteId INT,
   @StartDate DATETIME,
   @EndDate DATETIME,
   @TimeZone int
)
As
Begin
SET NOCOUNT ON
declare @sitePid varchar(50)
select @sitePid=sitenumber from sites where siteId=@siteId 
 
--select*,(Cast(Li_BattCurrentCharge1 as float)+(cast(Li_BattCurrentCharge2 as float))+(cast(Li_BattCurrentCharge3 as float))+(cast (Li_BattCurrentCharge4 as float))) as LiCurrentCharge, (Cast(Li_BattCurrentDischarge1 as float)+(cast(Li_BattCurrentDischarge2 as float))+(cast(Li_BattCurrentDischarge3 as float))+(cast(Li_BattCurrentDischarge4 as float)))as LiCurrentDischarge from rawdata where SiteId=@sitePid  and CommType='00'
--select * from RawData   where SiteId=@sitePid  and CommType='00' and convert(varchar,convert(date, [Date], 103),101) between @StartDate and @EndDate order by [Date],[Time]
 
 select * from RawData 
 where SiteId=@sitePid   
   and CommType IN('00' ,'01' ,'02' ,'03','04','05','06','07','08','09','0A','0B','0C','0D','0E','0F','10' ,'11' ,'12' ,'13','14','15','16','17','18','19','1A','1B','1C','1D','1E','1F','20' ,'21' ,'22' ,'23','24','25','26','27','28','29','2A','2B','2C','2D','2E','2F','30' ,'31' ,'32' ,'33','34','35','36','37','38','39','3A','3B','3C','3D','3E','3F') 
   and (AlarmString !='8000000000000000') 
   and convert(varchar,convert(date, [Date], 103),101) between @StartDate and @EndDate 
 order by [Date], CONVERT(Time,Time,108)
--select * from RawData   where SiteId=@sitePid   and (CommType='00' or CommType='01' or CommType='02' or CommType='03') and (AlarmString !='8000000000000000') and convert(varchar,convert(date, [Date], 103),101) between @StartDate and @EndDate order by [Date],CONVERT(Time,Time,108)
--select * from RawData   where SiteId=@sitePid   and CommType='00' and convert(varchar,convert(date, [Date], 103),101) between @StartDate and @EndDate order by [Date],[Time]
              
End 

this code was taking executin time 16 minites, how to improve...


Tuesday, July 30, 2019 - 9:09:55 AM - Greg Robidoux Back To Top (81899)

Hi Ganesh,

since you are doing each insert individuall you are only getting back 1 row at a time on the messages tab in SSMS.  Also, the @@ROWCOUNT only shows the last insert which was only 1 row that is why this shows a value of 1 as well.

Try this and you should get back a value of 2 rows.

set nocount off
insert into E2 values (9,'lakshamn','Hyd','11-10-1804'), (10,'Sachin','Mumbai','12-10-1854')
PRINT @@ROWCOUNT


Tuesday, July 30, 2019 - 3:09:37 AM - ganesh Back To Top (81897)

set nocount off

insert into E2 values (9,'lakshamn','Hyd','11-10-1804')

insert into E2 values (10,'Sachin','Mumbai','12-10-1854')

PRINT @@ROWCOUNT

here am trying to insert values into particular table , but results table shows one row affected. but i have inserted couple of rows. and

my question is when in inserted values, results table show only one row affected. please check and let me know , what the excatly problem accuring.

regars,

ganesh


Wednesday, September 6, 2017 - 8:48:35 AM - Greg Robidoux Back To Top (65940)

Hi Himanshu,

what did you do to analyze the slow down to make a comparison?

For individual executions you probably wont' see a difference, but if the procedure is called a lot then the overal resource usage would be noticeable.

-Greg


Wednesday, September 6, 2017 - 3:52:25 AM - Himanshu Saxena Back To Top (65928)

Hi,

I have use SET NOCOUNT functionality in my procedure but after the analyzing that the speed is slow down as compare with previous.

Any suggestion?

Regards,

 

Himanshu Saxena


Saturday, January 30, 2016 - 5:36:51 AM - abhi Back To Top (40552)

good


Wednesday, May 13, 2015 - 4:32:33 PM - Bob Elander Back To Top (37168)

Thanks for sharing this valuable information.  Even 8 years after it was written the point still is enjoyed and useful.  I just want to say we all appreciate you Greg.  Keep up the posting :)


Sunday, March 1, 2015 - 4:58:52 AM - mohammad mahdi Back To Top (36391)

tnx.good tips.


Monday, October 15, 2012 - 3:34:04 AM - aly Fathy Back To Top (19921)

Hii All,

SET NOCOUNT ON  put it at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements.


Tuesday, September 18, 2012 - 10:39:26 AM - hedecan Back To Top (19548)

Hello,

 

I want to set nocount value for all stored procedure t-sql code outomatcily.

 

What can i do ?

 

Please share script for this article.


Tuesday, January 31, 2012 - 12:00:19 PM - Patricia Cons Back To Top (15854)

Great tip-advice!, your point was pretty clear, the effects of missing this line are directly proportional to the number of transactions running in a database, I guess Joshua Guttman was not thinking in a big company database!


Thanks Greg!

 


Friday, December 23, 2011 - 4:54:46 PM - Zalek Bloom Back To Top (15441)

I have a big problem with "set nocount on".

We are running Fujitsu Cobol on Windows with MS SQL Server. All out programs declare cursors inside Cobol. We were told that in order to improve performence we need to declare cursor in Stored Procedure. 

One of my programs has one main cursor to get all each customer accoount and for each customer I opened/process/close another cursor to get custorer transactions.

So I moved the main cursor to the Stored Procedure. Now - if the Stored Procedure does NOT includes "set nocount on" - my program runes correctly. When I put "set nocount on" inside the Stored Procedure - on fetch of the cutomer cursor (the one defined in my Cobol program) I am getting SQL error code -999999700 and the message: "The cursor is not opened".

Any ideas why?

Thanks,

Zalek Bloom


Thursday, December 23, 2010 - 1:07:02 PM - Greg Robidoux Back To Top (12464)

Here is another article that has actual benchmarks:

http://www.sqlservercentral.com/articles/Performance+Tuning/2751/


Thursday, December 23, 2010 - 1:04:44 PM - Greg Robidoux Back To Top (12463)

For one database call this does not make a big difference, but if you have this in every call the ***ulative impact does add up.  This was first brought to my attention by the performance tool Indepth for SQL Server from Precise Software.

By returning the rowcount for every SQL command you are adding additional network traffic that you don't really need.


Thursday, December 23, 2010 - 12:05:25 PM - Joshua Guttman Back To Top (12461)

How do you define "significant improvment?" Do you have any tests? I seriously doubt having nocount on or off makes any difference.















get free sql tips
agree to terms