2

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.

2 Answers 2

9

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.

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.

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

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:

NUTS

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

2
  • Thank you so much! In regards to non-SARGables, why was a seek possible for the run_time and run_date predicate if it was A) non-SARGable and B) neither column was in the key of the index? I can't quite wrap my head around it and I feel like I'm missing something fundamental here.
    – Marcus
    Nov 30, 2023 at 8:03
  • 1
    @Miranda it’s not possible under those conditions. You could seek to job_id and apply residual predicates to those columns. Nov 30, 2023 at 12:01
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.