There is a big difference between the estimated rows & actual rows. A number of operations are clustered index scans, but the predicates are the full index columns. These appear to be scan rather than seeks due to the parallelism of the bitmap probe operator being used. Can this query performance be improved further? Will statistics or other indexes get better estimated row counts and hence better performance?
I've done quite a bit of optimisation, reducing the original query time from 90mins to 9mins. Covering indexes & query reformulation using exists & outer apply had been quite helpful.
By ajc 0 asked Feb 03, 2016 at 06:31 AM