Databases Reference
In-Depth Information
Finally, do not forget to set parameterization back to its default value by running the
following statement:
ALTER
DATABASE
AdventureWorks
SET
PARAMETERIZATION
SIMPLE
Listing 6-33.
Summary
The focus of the topic so far has been on optimizing
SELECT
queries with joins and
aggregates. This chapter is the first time we've considered additional optimization topics
related to updates, data warehousing, parameter sniffing and auto-parameterization.
We've seen how the Query Optimizer decides between per-row and per-index plans
to optimize
UPDATE
statements, and we've examined how updates need to perform
additional operations like updating existing indexes, accessing additional tables, and
enforcing existing constraints.
Basic data warehousing concepts, including fact and dimension tables as well as star and
snowflake schemas, were introduced, with the focus being on how SQL Server optimizes
star join queries. Some optimizations, such as Cross products of dimension tables with
multi-column index lookups, and bitmap filtering, were also explained.
We've ended the chapter with topics related to the use of query parameters, and how
they affect both the query optimization process and the reuse of plans by the plan cache.
Topics like parameter sniffing, auto-parameterization and forced parameterization have
also been introduced.
