26
Part I: Laying the Foundations
However, queries cannot overcome the errors of a poor physical schema and won’t solve the
performance issues of poorly written code. It’s simply impossible to fi x a clumsy database
design by throwing code at it. Poor database designs tend to require extra code, which
performs poorly and is diffi cult to maintain. Unfortunately, poorly designed databases
also tend to have code that is tightly coupled (refers directly to tables), instead of code
that accesses the database’s abstraction layer (stored procedures and views). This makes it
harder to refactor the database.Indexing
An index is an organized pointer used to locate information in a larger collection. An index
is only useful when it matches the needs of a question. In this case, it becomes the short-
cut between a question and the right answer. The key is to design the fewest number of
shortcuts between the right questions and the right answers.A sound indexing strategy identifi es a handful of queries that represent 90 percent of the
workload and, with judicious use of clustered indexes and covering indexes, solves the que-
ries without expensive lookup operations.An elegant physical schema, well-written set-based queries, and excellent indexing reduce
transaction duration, which implicitly improves concurrency and sets up the database for
scalability.Nevertheless, indexes cannot overcome the performance diffi culties of iterative code. Poorly
written SQL code that returns unnecessary columns is diffi cult to index and will likely not
take advantage of covering indexes. Moreover, it’s diffi cult to properly index an overly com-
plex or non-normalized physical schema.Concurrency
SQL Server, as an ACID-compliant database engine, supports transactions that are atomic,
consistent, isolated, and durable. Whether the transaction is a single statement or an
explicit transaction within BEGIN TRAN...COMMIT TRAN statements, locks are typi-
cally used to prevent one transaction from seeing another transaction’s uncommitted
data. Transaction isolation is great for data integrity, but locking and blocking hurt
performance.Multi-user concurrency can be tuned by limiting the extraneous code within logical
transactions, setting the transaction isolation level no higher than required, and keeping
trigger code to a minimum.Chapter 47, “Managing Transactions, Locking, and Blocking,” provides an excellent overview of the
different transaction isolation levels available in SQL Server 2012.c02.indd 26c02.indd 26 7/30/2012 4:07:53 PM7/30/2012 4:07:53 PM
http://www.it-ebooks.info