Databases Reference
In-Depth Information
Figure 3-11:
Cardinality estimate with the independence assumption.
Because of the assumption of independence, SQL Server will multiply the cardinality of
both predicates, which was explained earlier in this chapter. The calculation, abbreviated
as (93 * 4,564) / 19,614, will give us the value 21.6403 shown in the previous plan (19,614 is
the total number of rows in the table).
However, the assumption of independence is incorrect in this example, as the columns
are statistically correlated. To help with this problem, you can create a filtered statistics
object for the state of California, as shown in the next statement.
CREATE
STATISTICS
california
ON
Person
.
Address
(
City
)
WHERE
StateProvinceID
=
9
Listing 3-41.
Clearing the cache and running the previous query again will now give a better estimate,
as shown on the following plan:
DBCC
FREEPROCCACHE
GO
SELECT
*
FROM
Person
.
Address
WHERE
City
=
'Los Angeles'
AND
StateProvinceID
=
9
Listing 3-42.
