mercredi 28 janvier 2015

SQL Server : Index Seek with very high Scan Count


I have a query that joins 2 tables:



  • Documents table, that has DocID as Unique Clustered Index

  • DocumentsRows table, that has DocID, 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