Database Reference
In-Depth Information
Excessive Blocking and Deadlocks
Because SQL Server is fully atomicity, consistency, isolation, and durability (ACID) compliant, the database engine
ensures that modifications made by concurrent transactions are properly isolated from one another. By default,
a transaction sees the data either in the state before another concurrent transaction modified the data or after the
other transaction completed—it does not see an intermediate state.
Because of this isolation, when multiple transactions try to access a common resource concurrently in a
noncompatible way,
blocking
occurs in the database. Two processes can't update the same piece of data the same
time. Further, since all the updates within SQL Server are founded on a page of data, 8KB worth of rows, you can
see blocking occurring even when two processes aren't updating the same row. Blocking is a good thing in terms of
ensuring proper data storage and retrieval, but too much of it in the wrong place can slow you down.
Related to blocking, but actually a separate issue, a
deadlock
occurs when two resources attempt to escalate
or expand locked resources and conflict with one another. The query engine determines which process is the least
costly to roll back and chooses it as the
deadlock victim.
This requires that the database request be resubmitted for
successful execution. Deadlocks are a fundamental performance problem even though many people think of them as
a structural issue. The execution time of a query is adversely affected by the amount of blocking and deadlocks, if any,
it faces.
For scalable performance of a multiuser database application, properly controlling the isolation levels and
transaction scopes of the queries to minimize blocking and deadlocks is critical; otherwise, the execution time of
the queries will increase significantly, even though the hardware resources may be highly underutilized. I cover this
problem in depth in Chapters 20 and 21.
Non-Set-Based Operations
Transact-SQL is a set-based scripting language, which means it operates on sets of data. This forces you to think
in terms of columns rather than in terms of rows. Non-set-based thinking leads to excessive use of cursors and
loops rather than exploring more efficient joins and subqueries. The T-SQL language offers rich mechanisms for
manipulating sets of data. For performance to shine, you need to take advantage of these mechanisms rather than
force a row-by-row approach to your code, which will kill performance. Examples of how to do this are available
throughout the topic; also, I address T-SQL best practices in Chapter 18 and cursors in Chapter 22.
Inappropriate Database Design
A database should be adequately normalized to increase the performance of data retrieval and reduce blocking.
For example, if you have an undernormalized database with customer and order information in the same table, then
the customer information will be repeated in all the order rows of the customer. This repetition of information in every
row will increase the number of page reads required to fetch all the orders placed by a customer. At the same time,
a data writer working on a customer's order will reserve all the rows that include the customer information and thus
could block all other data writers/data readers trying to access the customer profile.
Overnormalization of a database can be as bad as undernormalization. Overnormalization increases the number
and complexity of joins required to retrieve data. An overnormalized database contains a large number of tables with
a small number of columns. Overnormalization is not a problem I've run into a lot, but when I've seen it, it seriously
impacts performance. It's much more common to be dealing with undernormalization or improper normalization of
your structures.
Having too many joins in a query may also be because database entities have not been partitioned distinctly or the
query is serving a complex set of requirements that could perhaps be better served by creating a new stored procedure.
Database design is a large subject. I will provide a few pointers in Chapter 18 and throughout the rest of the topic.
Because of the size of the topic, I won't be able to treat it in the complete manner it requires. However, if you want to
read a book on database design with an emphasis on introducing the subject, I recommend reading
Pro SQL Server
2012 Relational Database Design and Implementation
by Louis Davidson et al. (Apress, 2012).
