In sqlserver, how does the optimizer use statistics to estimate the number of returned rows? (3)

Let's see how multi-Column Filtering estimates the number of returned rows.

Use adventureworksgo -- set all milliseconds to 0, so that you can see the update DBO effect. databaselog set posttime = cast (convert (varchar, posttime, 120) as datetime) update statistics databaselog with fullscan -- create an event index create index idx_event on DBO. databaselog (event) with (online = on)

 

1. Take a look at the following SQL statements. The filter fields are indexed separately.

select * from dbo.DatabaseLog where PostTime='2006-04-26 11:44:31.000' and event='CREATE_TABLE'OPTION(    QUERYTRACEON 3604,    QUERYTRACEON 9292,    QUERYTRACEON 9204)

The following execution plan:

 

The number of rows returned by idx_posttime is estimated to be 56, from the histogram of statistics:

The estimated number of returned lines of idx_event is 69, which is also from the histogram:

How can we calculate the estimated number of rows returned by merge join? The result returned by merge join is the idx_posttime result set,

The intersection of idx_event result sets. The number of databaselog rows is 389.

Number of rows returned by merge join: (56./389) * (69./389) * 389 = 9.933052046574. The optimizer estimates the number of rows returned by merge join.

The number of rows returned by the operator.

During running, the optimizer loads statistics on idx_posttime/idx_event, as shown in:

 

At the same time, note that, in the key_lookup operator, the estimated number of returned rows is one row, and the number of returned rows is one operation, and the key_lookup operation is executed 56 times, so the actual

The number of returned rows is 56, and the estimated number of rows is the actual number of rows, as shown in:

 

2. Check the number of rows returned by the following SQL statement:

dbcc FreeProcCacheselect * from dbo.DatabaseLog where PostTime='2006-04-26 11:44:31.000' or event='CREATE_TABLE'OPTION(    QUERYTRACEON 3604,    QUERYTRACEON 9292,    QUERYTRACEON 9204)

This is a full table scan ,:

 

The estimated number of rows: 56 + events returned by posttime, part of the 69-intersection, 9.933052046574 = 115.067 rows,

When scanning the entire table, statistics manually created on posttime/event are also loaded ,:

 

 

Summary: For indexes created on Individual columns, the optimizer estimates the number of returned rows based on the statistical information of each column, regardless of whether to access the index or access the base table to obtain data during subsequent execution!