Query slower in Prod than Test 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()
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.