I have a query that joins 2 tables:
Documentstable, that has
DocIDas Unique Clustered Index
DocumentsRowstable, that has
DocID, RowIDas Unique Clustered Index
When I join those 2 tables, I usually do:
SELECT <somefields> FROM Documents INNER JOIN DocumentsRows ON Documents.DocID = DocumentsRows.DocID WHERE <something>
If I run the query with
STATISTICS IO ON and
SHOWPLAN, I see that the engine choose Index Seek on
DocumentsRows but the Number of Executions is equal to the Scan Count for the same table.
Table 'DocumentsRows'. Scan count 2277, logical reads 12591, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Documents'. Scan count 5, logical reads 17526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I know that since I'm not joining on the entire index, the seek does not retrieve only 1 value (in fact it retrieves all the rows for a specific document), then why does SQL Server choose to seek on that index instead of just scan it?
Why should an Index Seek have a so high scan count? How can I solve this?