4

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).

2
  • 4
    Not something I've tried myself but MemoryMBs is a param for dbcc OPTIMIZER_WHATIF. Also see devondba.blogspot.co.uk/2014/03/dbcc-optimizerwhatif.html Nov 30 '14 at 13:28
  • 4
    @MartinSmith this works! Very easy to do. The WHATIF value influences the <OptimizerHardwareDependentProperties> values in the plan. Using this facility I discovered that I already have enough RAM on my dev box. Lowering the amount of RAM I get the star plan (somewhat surprisingly). This is a very valuable optimization tool.
    – usr
    Nov 30 '14 at 13:38
4

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, privacy policy and cookie policy

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