If you have ever written any query with a predicate, you should have noticed, that, at first, the optimizer is trying to find the smallest indexes, and then try to push predicate down to the get data operators to minimize the amount of logical and physical reads. To illustrate this behavior, let’s look at the example below. I will create a small table with just two columns, put some data into it and create a clustered index on the datetime field, where the values are between January 1st 2016 and January 10th 2016.
create table dbo.seek_predicate_example ( dt datetime not null, some_data char(1000) null ); insert into dbo.seek_predicate_example (dt) select top (100000) dateadd(second, cast(floor(rand(checksum(newid())) * 3600 * 24 * 10) as int), '20160101') from sys.all_columns as t1 cross join sys.all_columns as t2; create clustered index ix_cl_seek_predicate_example on dbo.seek_predicate_example (dt);
Now we are ready to query this data, and we’ll start with a small example just to count the number of rows between two dates. Before executing our queries we also should enable execution time and IO statistics collection.
set statistics io on; set statistics time on; declare @from_v1 datetime = '20160101', @to_v3 datetime = '20160108'; select count(*) from [dbo].[seek_predicate_example] as t where t.[dt] >= @from_v1 and t.[dt] < @to_v3;
In my case, the query is returning about 70,000 rows, which is quite good because data distribution is uniform and we are searching for 7 of 10 days. The executions statistics tells, that it has spent about 16 milliseconds CPU time and about ten thousand logical reads. Index properties say that it has 14288 pages in total and 10 thousand is about 70% of it. So, everything looks perfect.
Table ‘seek_predicate_example’. Scan count 1, logical reads 10045, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 22 ms.
Moreover, let’s look at the query plan and Clustered Index Seek operator in particular.
The values we are searching are shown as Seek Predicates. It means that index is used and the query plan is utilizing it for seeking through the data.
Now, let’s make a query more complicated and try to confuse the optimizer by adding the third value.
declare @from_v1 datetime = '20160101', @from_v2 datetime = '20160105', @to_v3 datetime = '20160108'; select count(*) from [dbo].[seek_predicate_example] as t where t.[dt] >= @from_v1 and t.[dt] >= @from_v2 and t.[dt] < @to_v3;
It’s time to check the query plan now.
The new parameter is now in Predicate section, not in Seek Predicate. If you look at its value, it’s higher than the first one and limiting the amount of data to select from 70% to 30% approximately. And the query now returning about 30 thousand rows. But let us check the execution statistics.
Table ‘seek_predicate_example’. Scan count 1, logical reads 10045, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 22 ms.
Surprisingly or not, it’s the same as for the first query. Why? Because SQL Server cannot use too much parameters for the Seek Predicates to use index: one lower value and one upper. The third parameter goes to just Predicates section and will be used after getting rows from the index to further filter them. That is the main difference between Seek Predicates and Predicates.
But how can we force SQL Server to use the better expression? The answer is to make it simple and not to confuse optimizer. If you’re sending too many parameters to the query and can minimize them, do it, help the optimizer. For example, let’s rewrite our query like this and check the execution statistics again.
declare @from_v1 datetime = '20160101', @from_v2 datetime = '20160105', @to_v3 datetime = '20160108'; if @from_v2 > @from_v1 set @from_v1 = @from_v2 select count(*) from [dbo].[seek_predicate_example] as t where t.[dt] >= @from_v1 and t.[dt] < @to_v3;
And the execution statistics. The query plan is the same, as for the very first example. You can check it by yourself.
Table ‘seek_predicate_example’. Scan count 1, logical reads 4303, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
Now, you see the difference, the number of logical reads is lower and the query is faster.
You can come across this situation not only when using a complex predicate expression but in many other situations. Therefore, always check, whether your indexes are really used for searching, not just for reading all data and filter it afterwards.