1

I'm a bit puzzled by a query that runs slower in production than test (as always..)

Can't quite figure it out since both queries are identical in every way. Test query runs in 0,5 sec and prod takes 5 sec. Servers are identical regarding version. I tried the query in prod when it was idling at night, no difference. The test database was created a month ago, no big size difference.

The query plan is identical, looked at it in plan explorer and I can't spot the difference.

Adding some facts:

  • Prodserver and testserver on same storage and esx-host
  • Not database related, restored testdb to prod, proddb to test, proddb to proddb-copy
  • Identical SQL settings (apart from database mail and DAC)
  • Prodserver not under pressure when testing, testserver got 2 less cores and 15Gb less mem
  • Restored the db on my laptop, got the same good performance that I get on the test server.

Next step (Thanks Joe Obbish) I'll have a look att the code in the function. Gonna test to execute the code outside function and check what plan it produces, (SELECT dbo.cfn_diffworkingminutes('2016-10-01', GETDATE()

closed as off-topic by hot2use, Marco, Colin 't Hart, mustaccio, LowlyDBA May 16 '18 at 15:33

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, LowlyDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • You pasted the estimated plans, not the actual plans. SQL Server might estimate that they're the same - but in actuality, they're different. That's why we need the actual plans. :-D – Brent Ozar Nov 15 '17 at 12:30
  • Max memory same in both prod and test. If not, thats your problem. – Kin Shah Nov 15 '17 at 12:47
  • A query plan of a UDF won't help. Can you give us any information about the code that the function executes? Are the plans different if you run the code of the function outside of the function? Are wait events different? – Joe Obbish Nov 16 '17 at 1:44
  • Oh I missed that it was estimated, grabbed the wrong files! – Raadee Nov 16 '17 at 12:40
1

Memory and core count can go a long way towards affecting the plan choice.

You can force the dev instance to think it has the same core-count and memory in place, for the purposes of plan-creation, with DBCC OPTIMIZER_WHATIF.

The syntax is:

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

Properties you can specify include:

Status
CPUs
MemoryMBs

dbcc optimizer_whatif ('Status'); shows the current settings in effect. On SQL Server 2016, I see the following output:

---------------------------------------------------------
Optimizer what-if status
---------------------------------------------------------
property_number current_value default_value property_name
---------------------------------------------------------
         1                  8             0 CPUs
         2                  0             0 MemoryMBs
         3                  0             0 Bits
         4               1000          1000 ParallelCardThreshold
         5                  1             1 ParallelCostThreshold
         6                200           200 ParallelCardCrossProd
         7                 50            50 LowCEThresholdFactorBy10
         8                 12            12 HighCEThresholdFactorBy10
         9             100000        100000 CEThresholdFactorCrossover
        10                 10            10 DMLChangeThreshold

So, if your production server has 16 cores, but your dev server only has 4 cores, you can trick your dev server query optimizer to act like it has 16 cores by running this:

dbcc optimizer_whatif ('CPUs', 16);

Memory can be similarly "reconfigured", with:

dbcc optimizer_whatif ('MemoryMBs', 20480);

Now, when I run the Status command, I see:

---------------------------------------------------------
Optimizer what-if status
---------------------------------------------------------
property_number current_value default_value property_name
---------------------------------------------------------
         1                 16             0 CPUs
         2              20480             0 MemoryMBs
         3                  0             0 Bits
         4               1000          1000 ParallelCardThreshold
         5                  1             1 ParallelCostThreshold
         6                200           200 ParallelCardCrossProd
         7                 50            50 LowCEThresholdFactorBy10
         8                 12            12 HighCEThresholdFactorBy10
         9             100000        100000 CEThresholdFactorCrossover
        10                 10            10 DMLChangeThreshold

FYI, in order to see the output above, you need to enable the following trace flags:

dbcc traceon (2588);
dbcc traceon (3604);

2588 can be used to force DBCC HELP to display syntax for undocumented DBCC statements, such as optimizer_whatif.

3604 sends trace output to the client. Effective only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.

I would recommend setting the CPU and memory with optimizer_whatif to the same values you see in production, and re-run your queries. You may need to flush the plan cache prior to re-running your queries.

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