How do I determine the correct index on MS-SQL-Server based on a select?

Question

I have two questions about the index management of MS-SQL-Server 2019

1. If the term "table scan" appears in the execution plan, is that an indication of a "full table scan", and that means that no index has been used here?

2. I'm never sure if MS-SQL will use my indexes. But I also don't know how to find out which index it actually uses.

For this join, I create an index on the F_Skonto table with the three columns gueltig_ab, gueltig_bis and CD_Kunde. Is that correct?

LEFT OUTER JOIN dbo.F_Skonto 
    ON S631_BSA.SPTAG >= dbo.F_Skonto.gueltig_ab 
    AND S631_BSA.SPTAG <= dbo.F_Skonto.gueltig_bis 
    AND S631_BSA.PKUNRG = dbo.F_Skonto.CD_Kunde

And for this join I create an index with the two columns Monat and CD_Kunde on the table F_Umstellkosten. Is that correct?

LEFT OUTER JOIN F_Umstellkosten AS u1 
    ON CONVERT(varchar(6), v1.Tag, 112) = u1.Monat 
    AND v1.CD_Kunde = u1.CD_Kunde

Answer 1

You are unlikely to ever see index use because of this in the query:

CONVERT(VARCHAR(6), v1.Tag, 112)

Because it has to calculate the values from v1.Tag, it can't simply use the histogram in the statistics to determine row counts. It must scan the entire table to find all the values that match, and it has to do the conversion each time.

If u1.Monat and v1.Tag are not the same data type, adding a column that is the same data type might be necessary (on either u1 or v1, whichever).

However, the bigger issue is, you're moving all the data. The query has no WHERE clause to filter anything. Without a filter, the most efficient way to move everything is through scans. Index seeks wouldn't make sense. You might see some index usage on the JOINs between tables (once you fix the CONVERT problem), but possibly not since all the rows are being moved.

Answer 2

The order of columns in an index is important. Index (gueltig_ab, gueltig_bis, CD_Kunde) is different to index (CD_Kunde, gueltig_ab, gueltig_bis). The second is more likely to be useful as the first column is used in an "equals" comparison. However there are many more factors affecting index usage.

Answer 3

Looking over the XML of the query plan, the fact that sixteen different fields have implicit datetime data conversions is a red flag to me. This indicates that the data types across multiple fields are inconsistent, and will slow your processing significantly.

Also, since the tables appear to be HEAP tables (that is, they have no clustered index) will frequently result in slower processing, especially with large tables. Combined, these two factors result in a totally non-indexed table scan.

Using correct data types (and since I don't know why the existing field data types are what they are, and I don't actually know the existing field data types, I can't conclude definitely that the data fields should be converted to a datetime data type of some sort) -- my first instincts in this case say that the differing data types are a big part of the slow processing. This includes the VARCHAR(6) conversion.

NOTE: Any data type conversion in a comparison will stop indexes from being used. Since essentially all of your comparisons use data type conversions, your indexes will not be used.

My recommendation: These tables need to be refactored to use correct data types. Whether this will be ensuring that textual dates need to be formatted consistently and all fields used are varchar/nvarchar, or they all are converted to datetime data type fields is not my call, but they should be consistent. As for the varchar(6) conversion - if this is being done to limit the comparison to the first six characters, use the LEFT() or SUBSTRING() functions to be able to use an index (this is an exception - if you are only comparing the first N characters of a textual field, your index will still be used for the comparison). If this is being done to compare a varchar value with an nvarchar value, then normalize the data if you can.