Background: I'm in the process of optimizing a Data Warehouse style query with big dimension tables. The optimizer does not like to pick the typical star schema join plan at the moment. When I force this plan (by using an index hint) I can see that this star join plan is just a little more expensive than the plan I'm using right now. I suspect that is because the dimensions do not fit into RAM on my development machine. The version is SQL Server 2014 but I hope that the question can be generalized to all recent versions.

Question: Is there a way to make the optimizer believe that it has a lot of RAM at its disposal? For testing purposes I would like to obtain the plan that it would generate if it had 1TB of RAM available (instead of 8GB at the moment).


1 Answer 1


The DBCC WHATIF command (also see http://devondba.blogspot.co.uk/2014/03/dbcc-optimizerwhatif.html) can be used to simulate a certain hardware configuration for the current session:

dbcc OPTIMIZER_WHATIF ({property/cost_number | property_name} [, {integer_value | string_value} ])

The MemoryMBs property must be set to some value.

After that it appears to be necessary to recompile the query in question (for example using DBCC FREEPROCCACHE on a dev box, or using OPTION (RECOMPILE) if that hint is acceptable for later production use).

It is easy to tell that the WHATIF value "took" by the fact that it influences the <OptimizerHardwareDependentProperties> values in the XML execution plan.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.