Why does my scalar UDF perform so differently on two different (but extremely similar) servers?

Question

Recently, I was troubleshooting a strange performance issue that impacted an application's production environment, but not any of the lower environments. I managed to replicate the issue in its simplest form with this query:

SELECT product_id, dbo.TranslateStatusToActive(status_id) FROM prod_Products

TranslateStatusToActive is a very simple scalar UDF, which basically just joins the value given to another table and returns 1 or 0 based on a case statement. I'd post the code, but it's a vendor written function and I'm not particularly interested in getting sued today. (Yes, the logic can be inlined. Yes, it fixes the performance issue. Yes, we've convinced the vendor to implement the change. This is not my question.)

When executing in production, the query would take between 10 and 20 seconds to return results. In development, the same query returns in less than 3 seconds. The execution plans are nearly identical, except for showing that CPU time was around 15000 ms in production and 3000 ms elsewhere.

I suspected there were some environmental differences, so I set up another server that replicated the conditions of production as closely as possible: I made sure the number of CPUs, the amount of memory given to SQL Server, and the specific patch level (13.0.4451) were the same.

I restored a copy of the production database to this new sandbox server, and to my surprise, the query executed about as quickly as it did in development. Once again, the plan and data were identical, save for the extra CPU time. The waits listed in the execution plan were the same types and within a few ms of each other on every environment.

At a loss for what to do next, I enabled optimize for ad hoc workloads on the production server. This fixed the performance issue! One thing, though: neither of the other environments had this setting enabled. I had been regularly clearing the procedure and system caches in each environment during testing, so I don't think it was the result of changing a setting causing a recompile.

Questions

• What could cause the UDF to run so differently in each environment despite identical plan and near identical systems?
• Why did the production environment need to have optimize for ad hoc workloads enabled to perform as well as the other environments, which did not have it enabled?
• Is there some setting I didn't think to check that might cause such a large difference?

Development is shared, while production is currently used only by this application. The third box's usage would be almost the same as the production one. I cleared pretty much every cache they give a DBCC command for. The development environment is used regularly as a training system, so I am fairly confident it wasn't a plan caching issue.

The only difference with the third box is that there isn't an app hooked up to it, but there was little to no app usage while I was testing the function in production, so the difference was, based on my experience working in this environment, negligible. The only thing I couldn't do was restart the production server, but Microsoft's documentation explicitly states that enabling optimize for ad hoc workloads doesn't clear or affect any existing plans, so I don't see what the difference would be.

Answer 1

The situation that you described can happen when there's some kind of monitoring enabled (trace, extended event session, some third party tool), which does some kind of logging or work per UDF execution (or even per statement inside the UDF).

If the UDF is executed many times in a query there can be a very large amount of overhead to do that monitoring. If the monitoring is only occurring on one server then you would see a large performance difference between them.

Answer 2

I'll try to answer with an analogy. Think of SQL Server as an automobile.

Consider the 'Optimize for Ad Hoc Workloads' setting as a sophisticated automatic gear box. When it notices off-road uphill, it sets to a different gear than straight highway. The passengers enjoy a smooth journey, no matter what terrain the automobile goes through.

But how does that explain the different behaviour of the same query in similar servers, when the setting isn't there?

In that case, the gear boxes are still automatic but not so sophisticated. They notice and differentiate between terrains, so (in automobile A) the first time it sees a specific terrain, say off-road uphill, it sets a specific gear. Problem is, they can miss certain details. The next time it sees a similar terrain (say off-road downhill, or highway just a bit uphill), it still uses the same gear as the first time. And the passengers complain because the gear is not the best.
The second automobile (B) started on a different route, first on the highway, then off-road, so the gear box made slightly different decisions the first time it met similar (to those from A) terrains. Luckily things went smoothly and passengers haven't complained. This could change of course, if the terrain after next turn, needs a different gear than the one already used for a similar one.

Terminology explained:

              automobile : SQL Server
"adhoc workload" setting : automatic gear box
                 terrain : query
         terrain details : parameters, table statistics, workload
                    gear : query plan
              passengers : users, developers, DBAs

Some more notes:

• The analogy is not perfect of course. There are many details on how plans are saved and reused (or not) with the "adhoc workloads" setting (on or off).

• The setting is not a magic button to solve all problems. While it is helpful in many cases, there are reasons why SQL Server has it set to OFF by default. I'm sure there are many cases where it won't make any difference at all or even degrades performance.

• For the specific problem, I can't say I'm sure that the above description was the cause. It's just one possible explanation that seems plausible (because changing this setting fixed it). There might have been other reasons for the different behaviour (eg. some other setting you missed between production and test server) or other services/programs running in the two environments. It's practically impossible to have 2 identical setups.

• If you could provide the query and (similar) plans, there are other users in the site that can show more light into the issue.

Answer 3

I would check that your (default) connection options are the same on both servers, when testing out queries that "should" run similarly:

/*
@@OPTIONS allows you to get the current values that are set for the current session.
When each connection is made, the default values are established and remain set,
unless they are overridden by some other process.

All Sessions Details DMV:

SELECT * FROM sys.dm_exec_sessions;
*/
DECLARE @options INT;
SET @options = @@OPTIONS;

PRINT ('--Options as Integer: ' + CONVERT(VARCHAR(20),@options));

PRINT 'SET DISABLE_DEF_CNST_CHK '   + CASE WHEN 0 < @options &     1 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET IMPLICIT_TRANSACTIONS '  + CASE WHEN 0 < @options &     2 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET CURSOR_CLOSE_ON_COMMIT ' + CASE WHEN 0 < @options &     4 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_WARNINGS '          + CASE WHEN 0 < @options &     8 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_PADDING '           + CASE WHEN 0 < @options &    16 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_NULLS '             + CASE WHEN 0 < @options &    32 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ARITHABORT '             + CASE WHEN 0 < @options &    64 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ARITHIGNORE '            + CASE WHEN 0 < @options &   128 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET QUOTED_IDENTIFIER '      + CASE WHEN 0 < @options &   256 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET NOCOUNT '                + CASE WHEN 0 < @options &   512 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_NULL_DFLT_ON '      + CASE WHEN 0 < @options &  1024 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_NULL_DFLT_OFF '     + CASE WHEN 0 < @options &  2048 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET CONCAT_NULL_YIELDS_NULL '+ CASE WHEN 0 < @options &  4096 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET NUMERIC_ROUNDABORT '     + CASE WHEN 0 < @options &  8192 THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET XACT_ABORT '             + CASE WHEN 0 < @options & 16384 THEN 'ON' ELSE 'OFF' END + ';';

SELECT @options Options
    ,CONVERT(BIT,@options &     1) AS [DISABLE_DEF_CNST_CHK]    -- Controls interim or deferred constraint checking.
    ,CONVERT(BIT,@options &     2) AS [IMPLICIT_TRANSACTIONS]   -- For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
    ,CONVERT(BIT,@options &     4) AS [CURSOR_CLOSE_ON_COMMIT]  -- Controls behavior of cursors after a commit operation has been performed.
    ,CONVERT(BIT,@options &     8) AS [ANSI_WARNINGS]           -- Controls truncation and NULL in aggregate warnings.
    ,CONVERT(BIT,@options &    16) AS [ANSI_PADDING]            -- Controls padding of fixed-length variables.
    ,CONVERT(BIT,@options &    32) AS [ANSI_NULLS]              -- Controls NULL handling when using equality operators.
    ,CONVERT(BIT,@options &    64) AS [ARITHABORT]              -- Terminates a query when an overflow or divide-by-zero error occurs during query execution.
    ,CONVERT(BIT,@options &   128) AS [ARITHIGNORE]             -- Returns NULL when an overflow or divide-by-zero error occurs during a query.
    ,CONVERT(BIT,@options &   256) AS [QUOTED_IDENTIFIER]       -- Differentiates between single and double quotation marks when evaluating an expression.
    ,CONVERT(BIT,@options &   512) AS [NOCOUNT]                 -- Turns off the message returned at the end of each statement that states how many rows were affected.
    ,CONVERT(BIT,@options &  1024) AS [ANSI_NULL_DFLT_ON]       -- Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.
    ,CONVERT(BIT,@options &  2048) AS [ANSI_NULL_DFLT_OFF]      -- Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.
    ,CONVERT(BIT,@options &  4096) AS [CONCAT_NULL_YIELDS_NULL] -- Returns NULL when concatenating a NULL value with a string.
    ,CONVERT(BIT,@options &  8192) AS [NUMERIC_ROUNDABORT]      -- Generates an error when a loss of precision occurs in an expression.
    ,CONVERT(BIT,@options & 16384) AS [XACT_ABORT]              -- Rolls back a transaction if a Transact-SQL statement raises a run-time error.
    ;