Why the table scan when the inner join is on a clustered PK index?
I am trying to tune this query that runs close to 20 secs and noticed the big difference in the estimated and actual rows for the table scan on tblMMN_inspection_types. Can anyone help me understand why a table scan occured and not an index seek for the inner join on the PK predicate? Is it a simple case of updating statistics or what should I do to make this query run a little faster?
Your estimates are off everywhere – do you have statistics updates disabled? Also why doesn't the inspection types table have a clustered index? It's the only heap I spot on first glance, so I'm guessing there's a reason… in the meantime you could consider adding InspectionType to the non-clustered PK, or create a unique index instead and have it as an INCLUDE column, or create a second index with both columns. That column is needed in the output of the scan, and based on the number of estimated rows it is cheaper for SQL Server to perform one scan than a range scan of 1.3 million rows coupled with 1.3 million lookups to go get that other column.
Aaron nailed a ton of valid points. As I understand it, when you use an outer apply, the right side of the equation is always looked at 100%. So, if the statistics are not correct or the index needs tweaked (as Aaron said), it will default to doing a table scan by the pure definition of how the outer apply works. Not just the default of how the optimizer defaults it (which is typically the same way), out apply is coded that way. But, Aaron has it I believe.