Microsoft® SQL Server® 2012 Bible

1070

Part VIII: Performance Tuning and Optimization

Disabling page level locks is useful for a couple of specifi c purposes. If a table frequently

causes waiting because of page locks, setting allowpagelocks to off forces rowlocks.

The decreased scope of the lock improves concurrency at the cost of requiring additional

memory usage to keep track of row locks. This may also increase the number of lock escala-

tion attempts you see. In addition, if a table is seldom updated but frequently read,

row-level and page-level locks may be inappropriate. Allowing only table locks may be suit-

able during the majority of table accesses. For the infrequent update, a table-exclusive lock

may not be a big issue.

Sp_indexoption is for fi ne-tuning the data schema; that's why it’s on an index level. To

restrict the locks on a table’s primary key, use sp_help tablename to fi nd the specifi c

name for the primary-key index.

The following commands confi gure the ProductCategory table as an infrequently

updated lookup table. First, sp_help reports the name of the primary key index:

sp_help ProductCategory

Result (abridged):

index index index

name description keys

----------------------------- ------------- ----------------

PK__ProductCategory__79A81403 nonclustered, ProductCategoryID

unique,

primary key

located

on PRIMARY

Having identifi ed the actual name of the primary key, the alter index command can be

set as shown previously.

Transaction Isolation Levels

Any study of how transactions impact performance must include transactional integrity,

which refers to the quality, or fi delity, of the transaction. There are three types of side-

effects of isolation levels that can violate transactional integrity: dirty reads, nonrepeat-

able reads, and phantom rows.

The level of isolation between transactions can be adjusted to control which transactional

faults are permitted. The ANSI SQL-92 committee specifi ed four isolation levels: Read

Uncommitted, Read Committed, Repeatable Read, and Serializable.

c47.indd 1070c47.indd 1070 7/31/2012 10:23:30 AM7/31/2012 10:23:30 AM

http://www.it-ebooks.info