Fast query runs really poorly on VM Server

jlj 2017-04-19 16:15:48

I have tried tweaking the MDOP and threshold to no effect.
Closer examination of queries, indicate estimates are off despite rebuilding indices.
Can see spillovers in actual plan.
This is an SQL generated by the SSAS processor, so I cannot change it.
I have experimented with indices but to no avail.

Aaron Bertrand 2017-04-26 16:28:28
You have a seek that returns 61 million rows. That seems to be a very odd choice for the optimizer to make, and you can see that it expected 30 rows there, not 61 million – is it possible this query was compiled when the data looked very different? Have you tried explicitly updating statistics? Have you tried adding OPTION (RECOMPILE) to the query just as a test to see if the optimizer makes a better decision when forced to look again?