Can I get the query to use an Index Seek instead of Clustered Index Scan

Howard Diesel 2014-07-01 13:46:43

SQL seems to be doing an INDEX SCAN on all the SUB Tables that are part of the INNER JOINS. How do I change the query to get an INDEX SEEK

Aaron Bertrand 2014-07-01 15:03:43
Hard to give any solid recommendations here because the query text has been stripped due to anonymizing. One thing you may look at (and which may be leading to at least one of the scans) is the implicit convert on BT.TransactionState. Seems like a BIT or TINYINT column is being converted to varchar(20) for some reason.
Howard Diesel 2014-07-01 15:33:17
I can post the complete query – I dont really need to Anonymize – didnt realize the impact,
Howard Diesel 2014-07-01 15:38:02
Does that help?
Aaron Bertrand 2014-07-01 16:47:59
Any better estimates if you update statistics on these tables: BankTransactions, Devices and DeviceProducts? These are the tables with the largest skew of estimated rows vs. actual rows, and having more accurate cardinality may be enough to encourage seeks or different access strategies for some of these indexes. Are there indexes on any/all of the join columns (particularly the ones with the fk prefix)?
Howard Diesel 2014-07-01 18:03:47
Ran "UPDATE STATISTICS" on the 3 tables and the INDEXES involved and the ESTIMATES on Bank Transactions is still very low. Even if I increase the DATE period to a year and return a lot more rows – attached the new sqlplan
SQLkiwi 2014-07-03 06:34:32
This looks like a pretty reasonable execution plan to me. The only table that has more than a few thousand rows is BankTransactions and that is the one using an index seek already. Scans are not always a bad thing.