The Meaning of Stored Procedures Part 6 — NOCOUNT, ROWCOUNT and @@ROWCOUNT

If you remember, in the second part of this series (“Avoiding recompilations due to plan stability-related reasons”) I was talking about a drawback of setting plan reuse affecting SET option inside stored procedures. Nevertheless, this time I will show you two SET options that really should be set to on. We will start easily — with NOCOUNT one, but then we are going to discuss even more dangerous consequences related to ROWCOUNT SET option and misusing @@ROWCOUNT function.

NOCOUNT

By default SQL Server sends not only the result of a Transact-SQL statement to the client, but also a message indicating the number of rows affected by this statement. This applies as well to stored procedures — even if the client instantaneously discards this additional “(X row(s) affected)” message, it still has to be sent and received. And this can lead to two problems:

· As more than necessary data is sent over a network, if the network was already saturated, SQL Server overall performance would suffer.

· If the execution ratio is high, the additional data stored in the client machine memory will affect its performance.

To see what I mean, lets create this sample stored procedure:

USE [AdventureWorks2008R2]
GO

CREATE PROCEDURE uspCount AS
    SELECT 1;
GO

 

Then turn on Task Manager, go to the Processes tab and note the current memory allocated by SSMS:

image

In my case SSMS takes about 140 MB of RAM.

Then execute our stored procedure, let’s say 600 times, and observe the memory allocated by SSMS:

EXEC uspCount;
GO 600

 

It will need about 140 MB to store results:

image

If you don’t care about your clients’ machines memory, take notice of the execution time — in my case, the execution of this stored procedure in a loop took 12 seconds.

This default SQL Server behavior can be changed be setting NOCOUNT option. To see the effect of that change, create the second stored procedure and again, execute it six hundred times while observing the memory used by SSMS:

CREATE PROCEDURE uspNoCount AS
    SET NOCOUNT ON;
    SELECT 1;
GO

EXEC uspNoCount; 
GO 600

 

image

Memory consumption (on my machine) dropped to 85 MB (by almost 40%) and the execution time shortened from 12 to 8 seconds (by more then 30%).

Lesson learned — even if you don’t want to be nice to your clients, setting NOCOUNT in each and every of your stored procedure is an easy way to improve their performance.

ROWCOUNT and @@ROWCOUNT

For compatibility reason SQL Server allows us to limit the number of rows affected by queries by setting SET option. Unfortunately this setting is honored by every statement executed in a session, not only directly, but indirectly as well. This means that stored procedures and triggers executed in a session which has this option set will modify and return a different number of rows.

Imagine that a table has several triggers defined. In this case, by setting ROWCOUNT option a user might change their behavior which can lead to data inconsistency. Take this simple example:

USE [AdventureWorks2008R2];
GO

CREATE TABLE TableLog
(Name nvarchar(50));
GO

CREATE TRIGGER HumanResources.TrTest 
ON [HumanResources].[Department]
FOR UPDATE
AS
IF @@ROWCOUNT>0
BEGIN
    INSERT INTO TableLog (Name)
    SELECT Name
    FROM deleted
    UNION ALL
    SELECT Name
    FROM inserted;
END
GO

 

If we update two rows, four rows should be inserted into the TableLog table — two for each update, one with the previous and one with the new Name values:

UPDATE [HumanResources].[Department]
SET Name = LOWER(Name)
WHERE DepartmentID <3;
GO

 

And this is exactly the case:

SELECT *
FROM TableLog;
GO
--------
Name
Tool design
Engineering
tool design
engineering

 

But what happens if a user sets ROWCOUNT to 1? To see this, let me truncate the TableLog table, change this session option and update those two rows again:

TRUNCATE TABLE TableLog;
GO

SET ROWCOUNT 1
GO

UPDATE [HumanResources].[Department]
SET Name = UPPER(Name)
WHERE DepartmentID <3;
GO

 

If we check our log table, we will find that only one row was inserted — only the first one, with the old Name value:

SET ROWCOUNT 0
GO
SELECT *
FROM TableLog;
------
Name
engineering

 

By simply setting this one option a regular user changed our trigger behavior. Now think about your databases and the possible consequences of this. Fortunately, BOL for SQL Server 2012 says:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax.

Nonetheless, until the next SQL Server release we have to deal with this behavior. And the only way is to override ROWCOUNT setting inside each trigger, like this:

ALTER TRIGGER HumanResources.TrTest 
ON [HumanResources].[Department]
FOR UPDATE
AS
SET ROWCOUNT 0;
IF @@ROWCOUNT>0
BEGIN
    INSERT INTO TableLog (Name)
    SELECT Name
    FROM deleted
    UNION ALL
    SELECT Name
    FROM inserted;
END
GO

 

Now, if we try to update those two rows again, the trigger will insert for rows, won’t it?

TRUNCATE TABLE TableLog;
GO

SET ROWCOUNT 1
GO

UPDATE [HumanResources].[Department]
SET Name = UPPER(Name)
WHERE DepartmentID <3;
GO

SET ROWCOUNT 0
GO

SELECT *
FROM TableLog;
GO
---
Name

 

Hm, looks like setting ROWCOUNT inside triggers might be a little more complicated. In this case we incidentally changed this trigger’s logic — remember that @@ROWCOUNT returns the number of rows affected by the last statement. And now the last statement is the SET ROWCOUNT one:

ALTER TRIGGER HumanResources.TrTest 
ON [HumanResources].[Department]
FOR UPDATE
AS
SET NOCOUNT ON;
SET ROWCOUNT 0;
INSERT INTO TableLog (Name)
SELECT Name
FROM deleted
UNION ALL
SELECT Name
FROM inserted;
GO

 

And from now on, the trigger works as expected, regardless of user session settings:

SET ROWCOUNT 1
GO

UPDATE [HumanResources].[Department]
SET Name = LOWER(Name)
WHERE DepartmentID <3;
GO

SET ROWCOUNT 0
GO

SELECT *
FROM TableLog;
-----
Name
ENGINEERING
engineering

 

Lesson learned —writing robust code is essential for database developers. And one aspect of it which is often overlooked is that the database code (stored procedures, triggers and so on) can be executed under different session settings. Some of them, i.e. ROWCOUNT, can be compensated only by resetting to the default values.

Hope that helps

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



Comments are closed.