Databases Reference
In-Depth Information
Chapter 6: Additional Topics
So far we have focused on how the Query Optimizer solves
SELECT
queries with
mostly joins and aggregations. In this chapter, we'll see some additional SQL features
that traditionally are not covered in query optimization topics, including updates and
data warehouse queries, plus some other topics related to query parameters including
parameter sniffing, auto-parameterization, and forced parameterization.
Update operations are an intrinsic part of database operations, and they also need to be
optimized so that they can be performed as quickly as possible. Just to be clear, when I say
"updates," in truth I'm referring to any operation performed by the
INSERT
,
DELETE
or
UPDATE
statements, as well as the
MERGE
statement, which is new in SQL Server 2008.
In this chapter, I'll explain the basics of update operations, and how they can quickly
become complicated, as they need to update existing indexes, access multiple tables and
enforce existing constraints. I will show how the Query Optimizer can select per-row
and per-index plans to optimize
UPDATE
statements, and I will describe the Halloween
protection problem, as well as how SQL Server avoids it.
Data warehouses are becoming increasingly popular as decision support systems for
organizations of all sizes. Not only are more organizations building data warehouses,
those databases are also growing in size, and multi-terabyte environments are very
common today. In this chapter, I will introduce data warehousing and briefly compare
it to online transaction processing systems. I will explain the basic concepts of data
warehousing, including fact and dimension tables, as well as star and snowflake schemas.
After that, we'll focus on how SQL Server optimizes star join queries, as well as how it
can automatically detect star and snowflake schemas, and reliably identify fact and
dimension tables.
We'll end the chapter with two sections discussing the use of query parameters. In the
first section, I will talk about the parameter sniffing behavior and explain that, although
looking at the parameters of a query helps the Query Optimizer to produce better
