Databases Reference
In-Depth Information
execution plans, the occasional reuse of some of these plans can also be a performance
problem. I will demonstrate how you can identify problems related to the parameter
sniffing behavior, and I will provide a few recommendations on how to avoid
them, including the use of the
OPTIMIZE
FOR
or
RECOMPILE
hints. Finally, the auto-
parameterization behavior is explained, along with forced parameterization, a more
drastic choice to parameterize queries, which was introduced with SQL Server 2005.
Updates
Even when performing an update involves some other areas of SQL Server, such as
transactions, concurrency control or locking, update processing is still totally integrated
within the SQL Server query processor framework. Update operations are also optimized
so they can be performed as quickly as possible. So, in this section I will talk about
updates from the point of view of the Query Optimizer. As mentioned earlier, for the
purposes of this section, I'll refer to any operation performed by the
INSERT
,
DELETE
,
UPDATE
, or
MERGE
statements as updates.
Update plans can be complicated, as they need to update existing indexes alongside
data and, because of objects like check constraints, referential integrity constraints
and triggers, those plans may also have to access multiple tables and enforce existing
constraints. Updates may also require the updating of multiple tables when cascading
referential integrity constraints or triggers are defined. Some of these operations, such
as updating indexes, can have a big impact on the performance of the entire update
operation, and we'll take a deeper look at that now.
Update operations are performed in two steps, which can be summarized as a read
section followed by the update section. The first step provides the details of the changes
to apply and which records will be updated. For
INSERT
operations, this includes the
values to be inserted and, for
DELETE
operations, it includes obtaining the keys of the
records to be deleted, which could be the clustering keys for clustered indexes or the RIDs
for heaps. Just to keep you on your toes, for update operations, a combination of both the
