Hi Gail, thanks for posting.
GilaMonster (4/18/2013)
Why do you think it has unrelated tables in? )
Because those tables were not in the query. Why on Earth would other tables need to be scanned? Surely it would be more efficient to go to pages the appropriate partition and get the first 1000 rows ?
The optimiser will NOT use a plan for one query for another query.)
Agreed, my misunderstanding. Except in the case of Parameter Sniffing.
I am aware of the purpose of
sp_recompile
DBCC FREEPROCCACHE
WITH RECOMPILE
These were used in an attempt to generate a plan specific to the adhoc query (which you are saying would have been the case anyway). The plan just seems inefficient. In this case using other tables to filter the main fact table is taking too long.
Post the plan?
Attached. Sorry it's not the xml, Corporate Paranoia. Hopefully there is enough there. Basically
[FactTable] is what I am querying, Table scan on [Dim_Objective] (75%) is what I am unhappy with. I know about the benefits of the Bitmap operator but in this case it is at the cost of another table scan.
p.s. Nolock? You do know the effects of that? (and I don't mean just dirty reads)
Other than dirty reads, No. Please elaborate.