I have a query that joins 2 tables:
Documents
table, that hasDocID
as Unique Clustered IndexDocumentsRows
table, that hasDocID, RowID
as 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?
Aucun commentaire:
Enregistrer un commentaire