Database Reference
In-Depth Information
Stay Away from Nesting Views
A nested view exists when one view calls another view, which calls more views, and so on. This can lead to confusing
code for two reasons. First, the views are masking the operations being performed. Second, the query may be simple,
but the execution plan and subsequent operations by the SQL engine can be complex and expensive. This occurs
because the optimizer doesn't have time to simplify the query, eliminating tables and columns it doesn't need;
instead, the optimizer assumes that all tables and columns are needed. The same rule applies to nesting user-defined
functions.
Ensure No Implicit Data Type Conversions
When you create variables in a query, be sure those variables are of the same data type as the columns that they will
be used to compare against. Even though SQL Server can and will convert, for example, a
VARCHAR
to a
DATE
, that
implicit conversion can prevent indexes from being used. You have to be just as careful in situations like table joins so
that the primary key data type of one table matches the foreign key of the table being joined. You may occasionally see
a warning in the execution plan to help you with this, but you can't count on this.
Minimize Logging Overhead
SQL Server maintains the old and new states of every atomic action (or transaction) in the transaction log to ensure
database consistency and durability. This can place tremendous pressure on the log disk, often making the log disk
a point of contention. Therefore, to improve database performance, you must try to optimize the transaction log
overhead. In addition to the hardware solutions discussed later in the chapter, you should adopt the following query-
design best practices:
•
Choose table variables over temporary tables for small result sets, less than 20 to 50 rows,
where possible. Remember: If the result set is not small, you can encounter serious issues.
The performance benefit of table variables is explained in detail in the “Using Table Variables”
section of Chapter 17.
•
Batch a number of action queries in a single transaction. You must be careful when using this
option because if too many rows are affected within a single transaction, the corresponding
database objects will be locked for a long time, blocking all other users trying to access the
objects.
•
Reduce the amount of logging of certain operations by using the Bulk Logged recovery model.
This rule applies primarily when dealing with large-scale data manipulation. You also will use
minimal logging when Bulk Logged is enabled, and you use the
WRITE
clause of the
UPDATE
statement or drop or create indexes.
Adopt Best Practices for Reusing Execution Plans
The best practices for optimizing the cost of plan generation can be broadly classified into these two categories:
•
Caching execution plans effectively
•
Minimizing recompilation of execution plans
