Benjamin Nevarez : Performance

Fooling the Query Optimizer

Ben Nevarez — Thu, 14 Jan 2010 04:57:00 GMT

Did you ever wanted to know which execution plans the Query Optimizer would generate for your queries should your tables have millions of records? You can actually generate those plans by using the undocumented ROWCOUNT and PAGECOUNT options of the UPDATE STATISTICS statement. These options can be used on small or empty tables and can be helpful for testing in some scenarios where you may not want to spent time or disk space creating big tables.

By using this method you are tricking the Query Optimizer as it will generate execution plans using cardinality estimations as if the table really had millions of records. Note that this option, available since SQL Server 2005, only helps in creating the execution plan for your queries. Actually running the query will use the real data and of course will execute faster than a table with millions of records.

UPDATE STATISTICS WITH ROWCOUNT, PAGECOUNT does not change the table statistics, only the counter of number of rows and pages of a table. But the Query Optimizer uses this information to estimate the cardinality of queries as I will show later. Also keep in mind that these are undocumented and unsupported options and should not be used in any production environment.

Let us see an example. Run the following query to create a new table on the AdventureWorks database

select * into dbo.Address

from Person.Address

Inspect the number of rows by running the following queries. It must show 19,614 rows.

select * from sys.partitions

where object_id = object_id('dbo.Address')

select * from sys.dm_db_partition_stats

where object_id = object_id('dbo.Address')

Run the following query

select * from dbo.Address

where city = 'London'

Running this query will create new statistics for the city column and will show the following plan. Note that the estimated number of rows is 434 and it is using a simple Table Scan operator

We can see where the Query Optimizer is getting the estimated number of rows by inspecting the statistics object. Run this query to see the name of the statistics object

select * from sys.stats

where object_id = object_id('dbo.Address')

Then use the displayed statistics object name in the following statement (the name may be different in your case)

dbcc show_statistics('dbo.Address', _WA_Sys_00000004_46136164)

By looking at the histogram you can find the value 434 on EQ_ROWS for the RANGE_HI_KEY value ‘London’ (Statistics and histograms are explained on previous posts in this blog)

Now run the UPDATE STATISTICS WITH ROWCOUNT, PAGECOUNT (you can specify any other value for rowcount and pagecount)

update statistics dbo.Address with rowcount = 1000000, pagecount = 100000

If you inspect the number of rows again from sys.partitions or sys.dm_db_partition_stats, as shown previously, it will now show 1,000,000 rows. sys.dm_db_partition_stats also shows the new number of pages. Clear the plan cache and run the query again

dbcc freeproccache

select * from dbo.Address

where city = 'London'

Note that the estimated number of rows has changed from 434 to 22,127.1 and that a different plan was generated using this new cardinality estimation. The Query Optimizer decided to parallelize this plan. But this is a very simple query, more dramatic plan changes can happen with more complex queries.

After execution the actual number of rows obviously is still is 434 but the Query Optimizer is not able to see this value.

If you look at the statistics object again, using DBCC SHOW_STATISTICS as shown before, the histogram has not changed. One way to obtain the estimated number of rows shown in the new execution plan is calculating the percentage or fraction of rows for the value ‘London’ from the statistics sample, which in this case is 19,614, as shown on the header of the statistics object. So the fraction is 434 / 19,614 or 022127052. Then obtain the same percentage from the new “current” number of rows which is 1,000,000 calculated as 1,000,000 * 0.022127052 and we get 22,127.1 which is the estimated number of rows displayed in the plan.

Finally, drop the table you just created

drop table dbo.Address

Database Engine Tuning Advisor and the Query Optimizer

Ben Nevarez — Thu, 12 Nov 2009 03:47:00 GMT

Did you know that the Database Engine Tuning Advisor (DTA) uses the Query Optimizer to help you to create indexes, indexed views, and partitions for your databases? The DTA uses the Query Optimizer to estimate the cost of queries so it can select the choices with the lowest estimated cost. But, how can the Query Optimizer estimate the cost of a query using, for example, an index that does not exist yet?

Creating indexes on a DTA session could be very expensive and can create some other performance problems in your database. In addition to that, when the Query Optimizer uses indexes to estimate the cost of a query, it uses only the index statistics; it does not need to access the index data.

So, to avoid creating real indexes during a DTA session, SQL Server has a special kind of indexes called hypothetical indexes. Hypothetical indexes are not real indexes, they only contain statistics and can be created with the undocumented command CREATE INDEX WITH STATISTICS_ONLY. This command only creates the statistics for the index.

You may not be able to see these indexes during a DTA session because they are dropped automatically. But you can see the CREATE INDEX WITH STATISTICS_ONLY and DROP INDEX commands if you run Profiler to see what the DTA is doing. You can also create these indexes manually as I will show you later.

Hypothetical index have been available in previous versions of SQL Server where they were used by the DTA predecessor, the Index Tuning Wizard.

Let us take a quick tour to some of these concepts here. Create a new table on the AdventureWorks database

select *

into dbo.SalesOrderDetail

from sales.SalesOrderDetail

Copy the following query and save it to a file

select * from dbo.SalesOrderDetail

where ProductID = 897

Open a new DTA session. You can optionally run a Profiler session if you want to inspect what the DTA is doing. On workload file select the file containing the SQL statement that you just created. Specify AdventureWorks both for the database to tune and for the database for workload analysis. Click the Start Analysis button.

When the DTA analysis finishes run this query to inspect the contents of the msdb..DTA_reports_query table

select * from msdb..DTA_reports_query

Notice that the table contains some information like the query that was tuned and the current and recommended cost. The current cost, 1.2434, is easy to obtain by directly requesting an estimated execution plan for the query

Since the DTA analysis was completed, the needed hypothetical indexes were already dropped. In the next statement I will create the index recommended by the DTA, but instead of a regular index I will create it as a hypothetical index by adding WITH STATISTICS_ONLY.

create clustered index cix_ProductID on dbo.SalesOrderDetail (ProductID)

with statistics_only

You can validate that a hypothetical index and statistics were created by running this (notice that the index is defined as hypothetical on the index_description field)

sp_helpindex 'dbo.SalesOrderDetail'

select * from sys.stats

where object_id = object_id('dbo.SalesOrderDetail')

However, at the moment I am not aware of a way outside the DTA to ask the Query Optimizer to consider these hypothetical indexes on an estimated execution plan. So I am not able to see where the previous recommended cost is coming from.

Remove the hypothetical index by running this

drop index dbo.SalesOrderDetail.cix_ProductID

Implement the DTA recommendation this time as a regular clustered index

create clustered index cix_ProductID on dbo.SalesOrderDetail (ProductID)

After implementing the recommendation and running the query, the clustered index is in fact being used by the Query Optimizer and this time the estimated cost I got was 0.0033652, very close to the recommended cost listed before on the msdb..DTA_reports_query.

Finally, drop the dbo.SalesOrderDetail table you just created.

Are You Using Scalable Shared Databases?

Ben Nevarez — Fri, 30 Oct 2009 07:26:00 GMT

Did you know that you can share read-only databases between several instances of SQL Server?

Scalable Shared Databases is a very interesting SQL Server feature that many of us seem to almost have forgotten about it. Available for the first time in SQL Server 2005 and originally described on the Microsoft KB article 910378, it was later fully documented on Books Online. This Enterprise edition-only feature allows a read-only database to be accessed at the same time by two or more SQL Server instances (maximum recommended is 8). This configuration offers some performance benefits by allowing each of these instances to use its own resources like memory, CPU, and tempdb database. Scalable Shared Databases are used as reporting databases.

The concept behind Scalable Shared Databases is very simple: you copy a detached database to a volume, configure that volume as read-only, and then you can attach this database by several SQL Server instances. A shared database will look like a regular read-only database in Management Studio.

By the way, it would be interesting to know if Microsoft plans to add additional functionality to this scalability feature or to extend it to read-write databases, perhaps something similar to what Oracle RAC is already doing.

For more details, especially on restrictions and configuration, see the ‘Deploying a Scalable Shared Database’ entry on Books Online.

Rebuilding Indexes vs. Updating Statistics

Ben Nevarez — Tue, 06 Oct 2009 07:04:00 GMT

One of the questions I was asked recently while speaking at user groups, was regarding the order that jobs like rebuilding indexes or updating statistics should be performed as part of the database maintenance activities. Then I started writing this post about this topic on the weekend but was interrupted several times, including one of them to watch the premiere on VH1 of the movie Anvil: The Story of Anvil.

In general, the order should not matter, at least if you carefully consider these important points:

1) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

2) By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

3) Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update column statistics.

4) Reorganizing an index, for example using ALTER INDEX … REORGANIZE, does not update any statistics.

So depending on your maintenance jobs and scripts several scenarios can exist.

The simplest scenario is if you want to rebuild all the indexes and update all the statistics. As mentioned before, if you rebuild all your indexes then all the index statistics will also be updated by scanning all the rows on the table. Then you just need to update your column statistics by running UPDATE STATISTICS WITH FULLSCAN, COLUMNS. Since the first job only updates index statistics and the second one only updates column statistics, it does not matter which one you execute first.

Some other more complicated scenarios include when you have a job which rebuilds your indexes depending on their fragmentation level. In these cases perhaps you want to update only those index statistics that were not touched by the index rebuild job, plus all the column statistics.

Of course, the worst case scenario would be if you first rebuild your indexes, which also updates the index statistics by scanning the entire table, and later you run UPDATE STATISTICS using the default values, which again updates the index statistics but this time with a default sample. Not only are you updating your index statistics twice but you are overwriting the better of the two choices.

Let me show you how these commands work with some examples using the AdventureWorks database. Create a new table dbo.SalesOrderDetail

select * into dbo.SalesOrderDetail

from sales.SalesOrderDetail

The next query uses the sys.stats catalog view and shows that there are no statistics objects for the new table.

select name, auto_created, stats_date(object_id, stats_id) as update_date from sys.stats

where object_id = object_id('dbo.SalesOrderDetail')

Use this query again to inspect the status of the statistics after each of the following commands. Now run the following query

select * from dbo.SalesOrderDetail

where SalesOrderID = 43670 and OrderQty = 1

Use the previous sys.stats query to verify that two statistics objects were created, one for the SalesOrderID column and another one for the OrderQty column (they both have names starting with _WA_Sys as shown in the next figure). Now create the following index and again run the query to verify that a new statistics object for the ProductID column has been created. Notice the value of the auto_created column which tells if the statistics were created by the query optimizer.

create index ix_ProductID on dbo.SalesOrderDetail(ProductID)

Run the next command to update the column statistics only. You can validate that only the column statistics were updated by looking at the update_date column which uses the STATS_DATE function to display the last date the statistics were updated.

update statistics dbo.SalesOrderDetail with fullscan, columns

This command will do the same for the index statistics

update statistics dbo.SalesOrderDetail with fullscan, index

These commands will update both index and column statistics

update statistics dbo.SalesOrderDetail with fullscan

update statistics dbo.SalesOrderDetail with fullscan, all

See how an index rebuild only updates index statistics

alter index ix_ProductID on dbo.SalesOrderDetail rebuild

Here you can verify that reorganizing an index does not update statistics

alter index ix_ProductID on dbo.SalesOrderDetail reorganize

Finally, remove the table you have just created

drop table dbo.SalesOrderDetail

The Missing Indexes Feature

Ben Nevarez — Fri, 25 Sep 2009 06:19:00 GMT

Since I will be speaking about the Query Optimizer at the coming PASS Summit, I have been preparing my presentation and at the same time blogging about it. This time I will describe the Missing Indexes feature, seen from the point of view of the Query Optimizer.

We know that it is the job of the Query Optimizer to find an efficient execution plan for a query. But we rarely see the Query Optimizer directly giving us indications about what it needs to produce a better execution plan. One of these cases is the Missing Indexes feature, which was introduced with SQL Server 2005.

The Query Optimizer defines what the best indexes for a query are, and if these indexes do not exist, it will make this information available in the XML plan and the sys.dm_db_missing_index DMVs. And of course, by showing this information the Query Optimizer is also warning you that it might not be selecting an efficient plan. This information shows which indexes may be helpful to improve the performance of your query. You can even use SQL Server 2008 Management Studio to display the CREATE INDEX commands needed to create these indexes, as shown later.

However, although this information about missing indexes is very helpful, this feature should not be used as a tuning tool and should not replace your own index analysis. Database administrators and developers should be aware of its limitations, as described on the Books Online entry ‘Limitations of the Missing Indexes Feature’.

So let us take a quick look to see how this feature works. Create a dbo.SalesOrderDetail table on the AdventureWorks database with the following command

select *

into dbo.SalesOrderDetail

from sales.SalesOrderDetail

Run this query and ask for a graphical or XML execution plan

select *

from dbo.SalesOrderDetail

where SalesOrderID = 43670

This query can benefit from an index on the SalesOrderID column but no missing indexes information is shown this time. One limitation of the Missing Indexes feature is that it does not work on a trivial plan optimization, like in this case. You can verify that this is a trivial plan by looking at the graphical plan properties (Optimization Level shows as TRIVIAL) or by looking at the XML plan (StatementOptmLevel="TRIVIAL).

You can avoid the trivial plan optimization by using more complex features. In our case we are just going to create a non related index

create index ix_ProductID on dbo.SalesOrderDetail(ProductID)

Note that the index created will not be used by our previous query but the query will no longer qualify for a trivial plan. Run the query again. This time the XML plan will contain something like this

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

And if you look at the graphical plan (only SQL Server 2008 Management Studio) you will see a Missing Index warning and a CREATE INDEX command

You can right-click on the graphical plan and select Missing Index Details to see the CREATE INDEX command that can be used to create this index

Missing Index Details from SQLQuery1.sql

The Query Processor estimates that implementing the following index could improve the query cost by 99.703%.

USE [AdventureWorks]

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[SalesOrderDetail] ([SalesOrderID])

Create the recommended index after you provide a name to it. This time if you run the same query again and look at the execution plan you will see that an Index Seek operator is using the index you have just created and both the Missing Index warning and the MissingIndex element of the XML plan are gone.

Finally, remove the dbo.SalesOrderDetail table you have just created.

drop table dbo.SalesOrderDetail

How the Query Optimizer Uses Statistics

Ben Nevarez — Thu, 03 Sep 2009 06:00:00 GMT

This post shows how the Query Optimizer uses statistics to estimate the selectivity of expressions during query optimization.

You can also use this as a second part of my last post, The Query Optimizer and Parameter Sniffing. Here I will show you the advantage of the use of statistics when the Query Optimizer can “sniff” the parameter values compared to just guessing the selectivity of expressions when local variables are used. So I will be using the same query as in that previous post.

Notice that there are many other details that can not be covered here so this post will focus on a very simple example to show how the Query Optimizer creates and uses statistics.

To start open your AdventureWorks database and run this to display the current statistics on the ProductID column of the Sales.SalesOrderDetail table

dbcc show_statistics('Sales.SalesOrderDetail', IX_SalesOrderDetail_ProductID)

This will display the header, density vector and histogram of the statistics object.

1) Understanding the Histogram

First I will explain the meaning of the values of a histogram’s steps. Let us take a look at step 86, shown here

RANGE_HI_KEY is the upper boundary of a histogram step. The value 826 is the upper boundary for step 85 and 831 is the upper boundary for step 86. This means that step 86 may contain only values from 827 to 831.

Run the following query to obtain the real number of records for ProductIDs 827 to 831 to compare them against the histogram

select ProductID, COUNT(*) as Total

from Sales.SalesOrderDetail

where ProductID between 827 and 831

group by ProductID

This produces the following result

Going back to the histogram, EQ_ROWS is the estimated number of rows whose column value equals RANGE_HI_KEY. In our example RANGE_HI_KEY is 831 and the number of records with ProductID 831 is 198. The same value is shown on the histogram.

RANGE_ROWS is the estimated number of rows whose column value falls inside the range of the step, excluding the upper boundary. In our example, this is the number of records with values from 827 to 830 (831, the upper boundary, is excluded). The histogram shows 110 records and we could obtain the same value by getting the sum of 31 records for 827, 46 records for 828, 0 records for 829 and 33 records for 830.

DISTINCT_RANGE_ROWS is the estimated number of rows with a distinct column value inside this range, excluding the upper bound. In our example we have records for three distinct values: 827, 828, and 830, so DISTINCT_RANGE_ROWS is 3. There are no records for ProductID 829 and 831 is again excluded.

Finally, AVG_RANGE_ROWS is the average number of rows per distinct value and it is calculated as RANGE_ROWS / DISTINCT_RANGE_ROWS. In our example, we have a total of 110 records for 3 DISTINCT_RANGE_ROWS, so that gives, 110 / 3 = 36.6667 also shown on the histogram for step 86.

Now let us see how the statistics are used to estimate the selectivity of the queries.

2) When the Query Optimizer knows the value

Let us see the first query

select * from Sales.SalesOrderDetail

where ProductID = 831

Since 831 is a RANGE_HI_KEY on step 86, the Query Optimizer will use the EQ_ROWS value and the estimated number of rows will be 198.

Now run the same query with the value 828

This time the value is inside the range of step 86 but it is not a RANGE_HI_KEY so the Query Optimizer uses the value calculated before as AVG_RANGE_ROWS. Actually, we get the same estimated number of rows for any of the other values in the range (except RANGE_HI_KEY). This also includes 829, even when there are no records for this ProductID.

Let us try now a nonequality operator and try to find the number of records less than 714. For these we need to calculate the sum of the values of both RANGE_ROWS and EQ_ROWS for steps 1 thru 7, which give us a total of 13,223 rows.

This is the query and the estimated number of rows is shown on the execution plan

select * from Sales.SalesOrderDetail

where ProductID < 714

3) When the Query Optimizer does not know the value

In the case when the Query Optimizer does not know the value used in the expression, like when local variables are used, the Query Optimizer can not use the histogram so it will use some other information including the statistics density information or it will try to guess the selectivity using some fixed percentages. First, let us try using the equality operator.

declare @pid int = 897

select * from Sales.SalesOrderDetail

where ProductID = @pid

The Query Optimizer is not able to see the value 897 in this query. As explained in my previous post, the Query Optimizer does not know the value of the @pid local variable at optimization time. So it will use the density value of the ProductID column, 0.003759399, as listed on the second section, density vector, of the DBCC SHOW_STATISTICS output. If we multiply this value by the total number of records, 121,317, we will get 456.079 which will be shown in the next execution plan.

Finally, run this query with a nonequality operator

declare @pid int = 897

select * from Sales.SalesOrderDetail

where ProductID < @pid

Same as before, the value 897 does not matter; any other value will give you the same estimated number of rows and execution plan. The estimated number of rows is always 30% of the total number of records for a nonequality operator. In this case the 30% of 121,317 is 36,395.1 as shown next.

The Query Optimizer and Parameter Sniffing

Ben Nevarez — Fri, 28 Aug 2009 02:42:00 GMT

As we all know, the SQL Server Query Optimizer uses statistics to estimate the cardinality and selectivity of predicates of a query to produce an optimal execution plan. The Query Optimizer accomplishes this by first inspecting the values of the query parameters. This behavior is called parameter sniffing and it is a good thing. Getting an execution plan tailored to the current parameters of a query improves the performance of your applications.

We also know that the plan cache can store these execution plans so they can be reused the next time the same query needs to be executed again. This saves optimization time and CPU resources as the query does not need to be compiled again.

However, although the Query Optimizer and the plan cache work fine together most of the times, occasionally some performance problems can appear. Since the Query Optimizer may produce different execution plans for the same query, depending on its parameters, caching and reusing only one of these plans may be a performance issue for some other instance of this query that could benefit from a better plan.

This is a known problem with queries using explicit parameterization like in, for example, stored procedures. So let us see an example of what the problem is and a few recommendations on how to fix it.

1) The problem

Let us write a simple stored procedure using the Sales.SalesOrderDetail table on the AdventureWorks database

create procedure test (@pid int)

select * from Sales.SalesOrderDetail

where ProductID = @pid

Run this to display the amount of disk activity generated by the query

set statistics io on

and execute the stored procedure

exec test @pid = 897

The Query Optimizer estimates that only a few records will be returned by this query and decides to use an existing index, so if you look at the actual execution plan you will see an Index Seek and a Key Lookup operators. The I/O information will be similar to this

logical reads 10, physical reads 0, read-ahead reads 0

Now clear the plan cache to remove this plan and run the stored procedure again using a new parameter (Note: Be careful not to clear the plan cache of a production environment)

dbcc freeproccache

exec test @pid = 870

This time you will get 4,688 rows and the execution plan will show a Clustered Index Scan. The I/O information will be similar to this

logical reads 1240, physical reads 0, read-ahead reads 0

In this example each execution created its own optimal execution plan. Now see what happen when the plan cache is not cleared before the second execution so they both use the same cached plan

dbcc freeproccache

exec test @pid = 870

exec test @pid = 897

This time the Query Optimizer will compile the first execution of the stored procedure and will create an optimal execution plan for the value 870. This will use a Clustered Index Scan and around 1,240 logical reads. Since this plan is cached, it will also be used for the second execution, using the value 897 and it will also show a Clustered Index Scan and around 1,240 logical reads. This second execution is using 124 times more reads than its optimal plan, as shown previously.

You can try the other combination as well, clearing the plan cache, and running these two executions of the stored procedure, but this time using the value 897 on the first one and 870 on the second one.

2) Optimize for a typical parameter

There might be cases when most of the executions of a stored procedure use the same execution plan and/or you want to avoid the optimization cost. For these cases you can use the OPTIMIZE FOR hint. Use this hint when an optimal plan is generated for the majority of values used for the parameter. Only the few executions using an atypical parameter will not have an optimal plan.

Suppose that almost all of the executions of our stored procedure would benefit from the previous plan using an Index Seek and a Key Lookup. You could write the stored procedure this way

alter procedure test (@pid int)

select * from Sales.SalesOrderDetail

where ProductID = @pid

option (optimize for (@pid = 897))

When you run the stored procedure it will be optimized for the value 897, no matter what parameter value was specified for the execution. Test the following case

exec test @pid = 870

You can find this in the XML plan

</ParameterList>

This clearly shows which value was used during optimization and which one during execution. In this case the stored procedure is optimized only once, and the plan is stored on the plan cache and reused as many times as needed. The benefit of using this hint is that you have total control on which plan is stored on the plan cache.

3) Optimize on every execution

If you want the best performance for every query the solution might be to optimize for every execution. You will get an optimal plan on every execution but will pay for the optimization cost. To do this use the RECOMPILE hint as shown here.

alter procedure test (@pid int)

select * from Sales.SalesOrderDetail

where ProductID = @pid

option (recompile)

The XML plan for this execution

exec test @pid = 897

will show

</ParameterList>

Some other solution that has been traditionally implemented before has been using local variables but usually this is not a good idea. By doing this you are not only disabling parameter sniffing but also disabling the choice of the Query Optimizer to use the statistics histogram to find an optimal plan for the query. This solution will use the same execution plan for all the executions but may not be the optimal plan for any of them.

The Query Optimizer and Computed Columns

Ben Nevarez — Mon, 10 Aug 2009 06:39:00 GMT

In my previous post I talked about contradiction detection as one of the early steps in query optimization. Another interesting step performed during query optimization is the automatic matching of computed columns. Although computed columns have been available in previous versions of SQL Server, the automatic matching feature was introduced until SQL Server 2005. In this post I will show you how this feature works and explain how computed columns can help you improve the performance of your queries.

A problem faced by queries using scalar expressions is that they usually can not take benefit of column statistics. Statistics provide cardinality estimation to help the Query Optimizer to create better execution plans. Without statistics, the Query Optimizer will use a 30% selectivity on inequality comparisons which may produce inefficient execution plans.

A solution to this problem is the use of computed columns as SQL Server can automatically create and update statistics on these columns. The great benefit of this solution is that you do not need to specify the name of the computed column in your queries for SQL Server to use its statistics. The Query Optimizer automatically matches the computed column definition to an existing scalar expression in a query, so your applications do not need to be changed.

Indexes can also be created on computed columns but be aware of the requirements described on the Books Online entry ‘Creating Indexes on Computed Columns’.

I will show you one example; run this query against the AdventureWorks database

select * from Sales.SalesOrderDetail

where OrderQty * UnitPrice > 10000

The estimated number of rows is 36,395.1, which is 30% of the total number of rows, 121,317. But the query only returns 772 records.

Now create a computed column

alter table Sales.SalesOrderDetail

add cc as OrderQty * UnitPrice

Run the previous query again. Note that this time the estimated number of rows has changed and it is close to the actual number of rows returned by the query. Replace the 10000 with some other values like 10, 100, 1000 and 5000 and compare the actual and the estimated number of rows.

Note that creating the computed column does not create statistics. These statistics are created only until the query is executed. You can run this to see information about its statistics, which name starts with _WA_Sys_.

select * from sys.stats

where object_id = object_id('Sales.SalesOrderDetail')

Also note that the auto_created field is 1 which means that the statistics were automatically created by SQL Server. Use the following command to display these statistics

dbcc show_statistics ('Sales.SalesOrderDetail', _WA_Sys_00000013_2645B050)

Unfortunately, for automatic matching to work the expression must be exactly the same, if I change the query to UnitPrice * OrderQty, instead of OrderQty * UnitPrice, it will show an estimated number of rows of 30% again.

select * from Sales.SalesOrderDetail

where UnitPrice * OrderQty > 10000

Finally, drop the created computed column

alter table Sales.SalesOrderDetail

drop column cc

The Query Optimizer and Contradiction Detection

Ben Nevarez — Tue, 04 Aug 2009 06:56:00 GMT

One of the many interesting topics I found while working as technical editor of the SQL Server 2008 Internals book was the concept of contradiction detection. So, in this post I will to try to expand on this topic and provide some additional examples.

Contradiction detection is performed by SQL Server during the early steps of the query optimization process. During this process the Query Optimizer tries to find contradictions that can be removed and can make the query perform better. Since these parts of the query are not executed at all, SQL Server saves resources like I/O, locks, memory and CPU, making the query to be executed faster. For example, the Query Optimizer may know that no records can satisfy a predicate even before touching any page of data.

A contradiction may be related to a check constraint, or may be related to the way the query is written, that is, the query itself contains a contradiction. Let us explore both scenarios and analyze the execution plans that are generated on each case.

First, I need to a table with a check constraint. The following code creates a table that stores information about classic rock albums and its check constraint validates that every record entered was released before 1990.

-- create table

create table albums (

name varchar(40),

band varchar(40),

album_year smallint,

constraint check_year check (album_year < 1990))

-- add some records

insert into albums values ('Machine Head', 'Deep Purple', 1972)

insert into albums values ('A Night at the Opera', 'Queen', 1975)

insert into albums values ('British Steel', 'Judas Priest', 1980)

So, if I run a query to list all the records released, let us say before 1980, the execution plan uses a Table Scan operator, which is exactly what I expected for this scenario.

select * from albums

where album_year < 1980

Check Constraint

But what about if somebody tries to request all the records released, for example, after 1995, like in the following query? Does the Query Optimizer know that no records will be returned even before looking at the data on the table?

select * from albums

where album_year > 1995

In this case the Query Optimizer should know that because of the existing check constraint the previous query returns no records. So if I execute this query there is no need for SQL Server to access the data on the table, right? But surprisingly, the resulting execution plan still shows the Table Scan operator. Why?

Actually, there is another optimization called autoparameterization, which is closely related to the trivial plan feature and that overrides contradiction detection. To avoid this optimization I can use some features like query hints, joins, etc. like in the following example

select * from albums

where album_year > 1995

option (maxdop 1)

Once I change the query to use a query hint, the execution plan does not access the table at all, and instead it is just using a Constant Scan operator.

Now, see what happens if I disable the check constraint

-- disable constraint

alter table albums nocheck constraint check_year

This time running the last query uses a Table Scan operator again as the optimizer can not longer use the check constraint.

Finally, there are two choices to enable back the existing check constraint. If I ask SQL Server to enable the constraint and validate the existing data, the constraint will be enabled only if all the data complies with the check constraint definition. If I ask not to validate the existing data, the check constraint will be enabled but only the new records will be validated.

Of course the resulting execution plan of my query will be different depending on the selected choice. If the check constraint is enabled validating the existing data, running the same query again (and including the query hint) will use the Constant Scan operator. But enabling the constraint without validating the existing data will again result in a Table Scan even when no records are returned. Basically, in this last case the query optimizer can not trust the check constraint.

You can test yourself the resulting execution plans of the query after enabling the check constraint with any of these two commands

-- enable constraint validating existing data

alter table albums with check check constraint check_year

-- enable constraint without validating existing data

alter table albums with nocheck check constraint check_year

By the way, you can use the following query to see if a check constraint is not trusted, that is, if it has not been verified for all the records on the table.

select name, is_not_trusted from sys.check_constraints

Contradicting Predicate

The second type of contradiction case is when the query itself contains a contradiction. Take a look at this query

select * from albums

where album_year < 1970 and album_year > 1980

option (maxdop 1)

Same as before the execution plan for this query will use a Table Scan when the query hint is not used even when it is so obvious that there is a contradiction and no records should be returned.

Once I add the query hint, the Table Scan again is gone and only a Constant Scan operator is used.