Database Reference
In-Depth Information
Use SET NOCOUNT
You need to consider one more factor when executing a batch or a stored procedure. After every query in the batch or
the stored procedure is executed, the server reports the number of rows affected.
(<Number> row(s) affected)
This information is returned to the database application and adds to the network overhead. Use the T-SQL
statement
SET NOCOUNT
to avoid this overhead.
SET NOCOUNT ON <SQL queries> SET NOCOUNT OFF
Note that the
SET NOCOUNT
statement doesn't cause any recompilation issue with stored procedures, unlike some
SET
statements, as explained in Chapter 17.
Reducing the Transaction Cost
Every action query in SQL Server is performed as an
atomic
action so that the state of a database table moves from
one
consistent
state to another. SQL Server does this automatically, and it can't be disabled. If the transition from one
consistent state to another requires multiple database queries, then atomicity across the multiple queries should
be maintained using explicitly defined database transactions. The old and new states of every atomic action are
maintained in the transaction log (on the disk) to ensure
durability,
which guarantees that the outcome of an atomic
action won't be lost once it completes successfully. An atomic action during its execution is
isolated
from other
database actions using database locks.
Based on the characteristics of a transaction, here are two broad recommendations to reduce the cost of the
transaction:
•
Reduce logging overhead.
•
Reduce lock overhead.
Reduce Logging Overhead
A database query may consist of multiple data manipulation queries. If atomicity is maintained for each query
separately, then a large number of disk writes are performed on the transaction log. Since disk activity is extremely
slow compared to memory or CPU activity, the excessive disk activity can increase the execution time of the database
functionality. For example, consider the following batch query:
--Create a test table
IF (SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1 (C1 TINYINT);
GO
--Insert 10000 rows
DECLARE @Count INT = 1;
