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.
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
WHERE name = N'<procedure_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
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>
SELECT @p1, @p2
-- example to execute the store procedure
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <value_for_param2, , 2>
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
SET QUOTED_IDENTIFIER ON
-- 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>
-- 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
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.
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
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!
Friday, December 23, 2011 - 4:54:46 PM - Zalek Bloom
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?
Thursday, December 23, 2010 - 1:07:02 PM - Greg Robidoux
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