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..

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.

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.

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, onky 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.

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..

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.