Why are these columns specified in the include clause of an index rather than as key columns

Question

For the below query, I'm trying to figure out why the index suggested by SQL Server on the sysjobhistory table, which is also the index that results in a seek, was created on the Job_Id column with the other columns included:

Job_Id includes (Run_date, Run_time, Instance_id)

To my understanding, columns in a where clause should be present in the index key for seeks to be possible. Run_date and Run_time are present in the where clause of the below query, so how come we can get away with putting them in the include of an index rather than have them as key columns?

The query:

SELECT TOP 10000 run_date
     FROM msdb.dbo.sysjobhistory sh
     WHERE EXISTS (SELECT 1 FROM msdb.dbo.sysjobs sj WHERE sh.job_id = sj.job_id
     AND EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules sjs WHERE sjs.job_id = sj.job_id
     AND EXISTS (SELECT 1 FROM msdb.dbo.sysschedules ss WHERE ss.schedule_id = sjs.schedule_id 
     AND ss.freq_subday_type = 2
     AND ss.freq_subday_interval = 10 ))) 
     AND CAST(CAST([run_date] AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) < dateadd(hh,-12,getdate()) 

Note: I'm not using joins because this query is part of a cte that I then delete from, so joins are not possible.

Answer 1

Why oh why oh why

Because the missing index recommendations kind of suck, that's why.

The only columns that will end up in the key of an index recommendation are columns in the where clause. Other columns used in the query that could benefit from index-ordering are relegated to the includes.

• Join
• Group by
• Order by

Making matters worse, the order of key columns gets absolutely no love or affection. Sure, they get grouped into equality (=, IS NULL), and inequality (>, >=, <, <=, <>, IS NOT NULL) predicates, but the order of columns in each group is based on ordinal position in the table definition.

• How does SQL Server determine key column order in missing index requests?

Missing indexes may provide little to no benefit when comparing the recommendation to the slowest parts of a query plan, and may show up for queries that finish very quickly without an additional object to maintain in your database.

• Limitations of the Missing Indexes Feature

Missing index requests will also be missing when SQL Server goes out of its way to create an index for you.

• Eager Index Spool Annoyances In SQL Server

To your question a bit more though, is that non-SARGable where clause expressions are not considered for index key columns, because no strategy exists to seek to values in those columns, even if they were in the key of the index.

Here are some example queries:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
AND   p.LastEditDate >= '20080101';

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
AND   ISNULL(p.LastEditDate, '19000101') >= '20080101';

The missing index requests differ because in the second query, the predicate on LastEditDate is wrapped in the ISNULL function:

In other words, a missing index request is sort of like when a kid says they're hungry and then asks for candy.

Answer 2

Job_Id is included and indexed because it is used in JOIN condition.

Run_date, Run_time are included because they are used in the expression of the most inner subquery condition, and Run_date is also included into the output list, so they not needed to be sorted. Their values should be extracted from the index, and server don't need to access the table body while executing this query.

I don't know why Instance_id is included - it is not used in the query. Maybe the same index also improves some another query where this column is used?

PS. You use TOP 10000 but there is no ORDER BY clause... do you really need ANY 10k rows from all selected by the query?

To my understanding, columns in a where clause should be present in the index key for seeks to be possible.

You do not use these columns directly, they are used in the expression. This prevents any index seek, only index scan is possible. So they may be either indexed or included. But when they're indexed then the server must perform additional and unneeded work while changing the data because of additional sorting steps.

PS. Reversing your condition - the columns are used immediately whereas referenced values obtained from GETDATE() are extracted and formatted with according expressions. This will improve your query. Maybe this will make the indexing of these columns to be used. From the other side you'll obtain a condition which combines AND and OR, so index seek may be problematic. You'd test in practice.

PPS. Storing the date and the time components of a timestamp in different columns is not good practice. Storing the value as one DATETIME and separate components calculation in the query or in generated column may be more useful.