Why is the table scanned?

Question

SELECT X.ID, X.Field4 FROM #TaxInvoiceData T INNER JOIN xxx X ON T.Id = X.Id AND Field2 = @VAR AND Field3 = 'S' 

When I run the query, scan the full table in table X. I do not understand why, since the primary key of table X

 ID INT ASC Field3 CHAR(2) ASC Field2 DATETIME ASC Unique Non-clustered 

There is also an index on

 Field2 DATETIME ASC Non-Unique Non-clustered 

Execution only

 SELECT ID FROM xxx WHERE Field2 = @VAR AND Field3 = 'S' 

Is index search in progress?

Thanks in advance.

Answer 1

Short answer: because the optimizer believes that it will be faster.

However, try to read the mind of the optimizer.

Since you did not specify the full table schema, I am going to assume that the clustered index is at xxx.ID and that #TaxInvoiceData is a bunch. You expect a plan in which the PK index is examined for each row in #TaxInvoiceData , but you select xxx.Field4 , which requires a bookmark search for each match. This can result in 29,000 random I / O requests. Uch.

Conversely, SQL Server can (and apparently am going to) just perform more efficient sequential I / O operations that perform table scans, and probably makes a quick hash match against #TaxInvoiceData .

So what can you do? You can create a coverage index, including Field4 . Or you can use pointers and join hints to force the plan you are looking for (but I suspect that the performance will not be as good as you hope). Is this query used often enough that it gives problems with application performance or do you just want to eliminate the table scan in principle? If the latter, you may find that the overhead of getting rid of the scan is not worth it in the end.

---

Edit:

Since you mentioned that the table does not have a clustered index, this can also affect the effective search of the index. If the extremely heavy activity of the insert is not visible in this table, think about changing your PC in the cluster. This in itself can change the plan, and even if it does not accelerate other operations due to reduced overhead costs.

Answer 2

Perhaps rewriting the request will help:

 SELECT X.ID, X.Field4 FROM xxx X, #TaxInvoiceData T WHERE X.Id = T.Id AND X.Field2 = @VAR AND X.Field3 = 'S'