Database Reference
In-Depth Information
Running this query with the
DBCC SQLPERF()
function before and after showed less than 4 percent growth of the
used space within the log, and it ran in 41ms as compared to more than 2s for the
WHILE
loop.
One area of caution, however, is that by including too many data manipulation queries within a transaction, the
duration of the transaction is increased. During that time, all other queries trying to access the resources referred
to in the transaction are blocked. Rollback duration and recovery time during a restore increase because of long
transactions.
Reduce Lock Overhead
By default, all four SQL statements (
SELECT
,
INSERT
,
UPDATE
, and
DELETE
) use database locks to isolate their work from
that of other SQL statements. This lock management adds a performance overhead to the query. The performance of
a query can be improved by requesting fewer locks. By extension, the performance of other queries are also improved
because they have to wait a shorter period of time to obtain their own locks.
By default, SQL Server can provide row-level locks. For a query working on a large number of rows, requesting a
row lock on all the individual rows adds a significant overhead to the lock-management process. You can reduce this
lock overhead by decreasing the lock granularity, say to the page level or table level. SQL Server performs the lock
escalation dynamically by taking into consideration the lock overheads. Therefore, generally, it is not necessary to
manually escalate the lock level. But, if required, you can control the concurrency of a query programmatically using
lock hints as follows:
SELECT * FROM <TableName> WITH(PAGLOCK) --Use page level lock
Similarly, by default, SQL Server uses locks for
SELECT
statements besides those for
INSERT
,
UPDATE
, and
DELETE
statements. This allows the
SELECT
statements to read data that isn't being modified. In some cases, the data may
be quite static, and it doesn't go through much modification. In such cases, you can reduce the lock overhead of the
SELECT
statements in one of the following ways:
Mark the database as
•
READONLY
.
ALTER DATABASE
<DatabaseName>
SET READ_ONLY
This allows users to retrieve data from the database, but it prevents them from modifying the data. The setting
takes effect immediately. If occasional modifications to the database are required, then it may be temporarily
converted to
READWRITE
mode.
ALTER DATABASE
<DatabaseName>
SET READ_WRITE
<Database modifications>
ALTER DATABASE
<DatabaseName>
SET READONLY
•
Use one of the snapshot isolations.
SQL Server provides a mechanism to put versions of data into
tempdb
as updates are occurring, radically reducing
locking overhead and blocking for read operations. You can change the isolation level of the database by using an
ALTER
statement.
ALTER DATABASE AdventureWorks2012 SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT;
•
Prevent
SELECT
statements from requesting any lock.
SELECT * FROM <TableName> WITH(NOLOCK)
