SQLCLR scalar function spins on DIAG_OBJECT at high DOP: CPU per call 36 times higher at DOP 24 than at DOP 4

Question

SQLCLR scalar function spins on DIAG_OBJECT at high DOP: CPU per call 36 times higher at DOP 24 than at DOP 4

Brien Send 0

On SQL Server 2025 CU10 (17.0.5035.5, KB5125471, also seen on CU4), on Linux with 24 schedulers, a trivial SQLCLR scalar function called once per row in a parallel plan costs far more CPU per call as DOP rises, and the query is slower at DOP 24 than at DOP 1. The same check written in T-SQL scales normally.

The function is a SAFE assembly method that returns its integer argument plus one, marked deterministic and precise with no data access. The test counts the rows of a 2,000,000-row heap for which the function returns 0, with a MAXDOP query hint and the ENABLE_PARALLEL_PLAN_PREFERENCE hint.

CPU per call and elapsed time:

DOP 1: 0.17 microseconds, 350 ms
DOP 4: 0.22 microseconds, 116 ms
DOP 8: 1.95 microseconds, 496 ms
DOP 24: 7.94 microseconds, 702 ms

At DOP 24, sys.dm_os_spinlock_stats shows about 173 million spins on DIAG_OBJECT per run, against 0.9 million at DOP 4, and nothing comparable for the T-SQL version. Turning lightweight query profiling or Query Store off in the database makes no difference. Only the default Extended Events sessions are running.

A second effect: if the function's string parameters are declared nvarchar(max) rather than nvarchar(4000), the call costs about 6 times as much even at DOP 1, and from DOP 2 up it spins on SOS_SELIST_SIZED_SLOCK and SOS_CACHESTORE instead, up to 24 microseconds per call at DOP 24. Those are the spinlocks KB2887899 describes for spatial index builds.

My questions:

What takes the DIAG_OBJECT spinlock on each SQLCLR scalar call, and is there a setting or trace flag that avoids it?
Is the nvarchar(max) contention the issue KB2887899 fixed for spatial index builds, and does that fix apply to user-written SQLCLR functions?
Is capping DOP for statements that call SQLCLR functions heavily, and declaring string parameters with a size rather than max, the recommended mitigation for now?

The reproduction scripts, the C# source and the full results for CU4 and CU10 are on GitHub at github.com/coderb/sql-clr-perf-bug, and the issue is reported on the SQL Server feedback forum.

Brien Send 0 Reputation points

2026-10-27T11:44:32.5133333+00:00

Most of the follow-up is on DBA Stack Exchange. There, Sean Gallardy's profile puts the spinning in CDiagnostics::StoreMaxUdfElapsedTicks: 41,385 of the 45,529 samples under UDFInvokeExternalImpl. Two further tests in the repo fit that: every thread is busy for the whole query, its CPU within 8 ms of its elapsed time, with the rows divided close to evenly (data); and from DOP 12 up, spreading the rows over 24 separately declared functions changes nothing (data). Also, contrary to the AI answer, DOP 1 was not the best result: AddOne took 116 ms at DOP 4 against 350 ms at DOP 1, and only got slower past DOP 4 (data).
Erland Sommarskog 138.5K Reputation points MVP Volunteer Moderator

2026-10-27T19:46:25.72+00:00

If you think the AI answer is off-the-mark, you should be able to delete it.

2 answers

Your answer

Brien Send 0 Reputation points

2026-10-27T11:44:32.5133333+00:00

Most of the follow-up is on DBA Stack Exchange. There, Sean Gallardy's profile puts the spinning in CDiagnostics::StoreMaxUdfElapsedTicks: 41,385 of the 45,529 samples under UDFInvokeExternalImpl. Two further tests in the repo fit that: every thread is busy for the whole query, its CPU within 8 ms of its elapsed time, with the rows divided close to evenly (data); and from DOP 12 up, spreading the rows over 24 separately declared functions changes nothing (data). Also, contrary to the AI answer, DOP 1 was not the best result: AddOne took 116 ms at DOP 4 against 350 ms at DOP 1, and only got slower past DOP 4 (data).
Erland Sommarskog 138.5K Reputation points MVP Volunteer Moderator

2026-10-27T19:46:25.72+00:00

If you think the AI answer is off-the-mark, you should be able to delete it.

Answer 1

Erland Sommarskog 138.5K MVP Volunteer Moderator

I was able to reproduce your results. I also tested on SQL 2012, and the outcome was largely the same. Thus, this does not seem to be any recent regression.

I don't want to venture to answer the first two questions as they require knowledge from the inside that I don't posses. As for the third question, I think the results speak for themselves. I note that in terms of elapsed time, MAXDOP 4 appears to be the sweet spot. The fact that queries do not always benefit from higher DOP is not unique to CLR queries, but this can be seen with other queries as well. This is why Microsoft introduced DOP Feedback which first came in SQL 2022, but did not become a mainstream feature until SQL 2025.

However, that feature does not seem to help in this case. I tried, but nothing happened. (Running queries as such without any hints.) When I set up an Extended Events session with the events for dop_feedback, the query came up with eligible=false. This does not seem to have to do with the CLR as such, though, because I got the same result with your control query, that is

select count(*) /*clrspin IsMarkedMax dop8 run3*/ from dbo.T where substring(Code, 4, 1) = 'M'

I also tried adding two T-SQL functions for which I had turned off inlining. Here the results were entirely different. And worse. That is, a T-SQL UDF prevents parallelism entirely. TSQL4000 was also considerably slower than IsMarked4000.

I suspect that this is just what things are, and I would be surprised if Microsoft accepts this as a bug. While you have reported this on the feedback site, this is only to let Microsoft know. If this is an issue that is blocking you and you would need it to be fixed, you would have to open a support case. I think in this case, you also would need to argue from a business perspective. That is, what is the actual business impact of this performance issue?

Brien Send 0 Reputation points

2026-10-27T11:43:39.2566667+00:00

Thank you for reproducing it, and for checking SQL 2012 and DOP feedback. The impact for us: a production statement calling a CLR function for 1.4 million rows took 36.1 s of CPU at DOP 24 against 4.4 s at DOP 4, and finished sooner (report). Capping DOP has its own cost: every statement that calls a CLR function per row has to be found and capped, and the cap holds back the rest of that statement too. The follow-up, including a profile of where the CPU goes, is on DBA Stack Exchange.
Erland Sommarskog 138.5K Reputation points MVP Volunteer Moderator

2026-10-27T15:51:58.1933333+00:00
I played a little more with it, and added a datetime2 column to the test table:

drop table T go create table dbo.T ( ID int not null, GroupName varchar(255) not null, Code varchar(255) not null, dtcol datetime2(3) not null ); with N as ( select top (2000000) n = row_number() over (order by (select null)) from sys.all_columns a cross join sys.all_columns b cross join sys.all_columns c ) insert dbo.T with (tablock) select n, 'G' + right('0' + convert(varchar(2), n % 67), 2), stuff(right('000000000000' + convert(varchar(12), n), 12), 4, 1, case when n % 20 = 0 then 'M' else 'X' end), dateadd(second, 1, sysdatetime()) from N;

Then I added two more variants:

(6, 'convert', N'convert(varchar(16), dtcol, 121) = Code'), (7, 'format', N'format(dtcol, ''yyyy-MM-dd HH:ss'') = Code');

The difference here is that convert is native. format is CLR. The output is below. The difference is akin to your other examples, if not equally pronounced. Presumably, this is because for built-ins they can skip some of the overhead in the call to the CLR, but not all.

As I said in my initial post, it is far better than with a T-SQL UDF that cannot be inlined, but it is never going to be like a native function.I noticed that Joe Obbish had some interesting ideas in his post on StackExchange with passing data from many rows at the same time to the CLR function, but it is certainly not a trivial exercise.

Answer 2

Cap MAXDOP at the measured break-even point for SQLCLR-heavy statements and use bounded string parameters when the data is genuinely bounded; no published SQL Server 2025 setting or trace flag is documented as disabling the reported DIAG_OBJECT acquisition.

Recommended mitigation

Apply MAXDOP 4 to the affected statements based on the supplied measurements. Use the lowest DOP that gives the best elapsed time under representative concurrency:
```
   SELECT COUNT_BIG(*)
   FROM dbo.Repro AS r
   WHERE dbo.ClrPlusOne(r.Value) = 0
   OPTION (MAXDOP 4);
```
A query-level MAXDOP hint overrides the server setting, subject to a lower Resource Governor limit, so it isolates the workaround to affected statements rather than reducing parallelism instance-wide.^1^ Do not retain ENABLE_PARALLEL_PLAN_PREFERENCE in production unless it is required for a separate, validated reason. Your DOP 1 result indicates that forcing a parallel alternative is counterproductive for this particular per-row SQLCLR workload.
Declare bounded strings as nvarchar(n), not nvarchar(max), whenever the business maximum is known. SQLCLR scalar invocation occurs once per row and includes generated marshaling code based on the exact parameter types; managed/native transitions also carry execution-environment and security overhead.^2^
If a parameter must support true LOB values, keep nvarchar(max) but expose it to the CLR routine as SqlChars rather than SqlString. SqlChars streams the LOB instead of copying the complete value into managed memory.^3^

These are workload mitigations, not confirmation that either contention path is expected behavior.

What `DIAG_OBJECT` is protecting

Microsoft’s public documentation does not define the internal structure protected by DIAG_OBJECT, nor establish which SQLCLR call-path component acquires it in SQL Server 2025. The spinlock name and DMV deltas alone cannot identify the owner. Spinlocks serialize access to internal structures, and disproportionate growth in spins and CPU relative to throughput is the documented pattern that warrants call-stack investigation.^4^

There is also no documented SQL Server 2025 trace flag that removes or partitions DIAG_OBJECT. In particular:

Trace flag 8134 only exposes spinlock activity through the SPINLOCK_EXT wait type on SQL Server 2025; it is diagnostic, not a fix.^5^
Trace flags 8099 and 8101 apply specifically to SQL Server 2019 builds and are not documented for SQL Server 2025.^5^

Since disabling lightweight profiling and Query Store made no difference, there is no basis for changing those settings further.

Why KB2887899 does not apply directly

KB2887899 fixed contention on SOS_CACHESTORE and SOS_SELIST_SIZED_SLOCK while building spatial indexes in SQL Server 2012 and 2014. It explains that these locks protected CLR procedure-cache entries and could become a bottleneck when parallel spatial plans invoked a spatial function per row.^6^

That mechanism is relevant circumstantial evidence, but the fix’s published applicability is limited to spatial-index builds on those versions. It does not document a fix for user-written SQLCLR functions or SQL Server 2025.

Also, KB2887899 is not the spatial fix requiring a trace flag. The related KB2896720 introduced trace flag 6530 for many parallel spatial operations on SQL Server 2012/2014.^7^ That flag should not be extrapolated to SQL Server 2025 SQLCLR workloads.

Escalation evidence

This needs a Microsoft Support investigation to map the internal call path and determine whether it is a SQL Server 2025 defect. Add these artifacts to the existing report:

Before/after deltas from sys.dm_os_spinlock_stats for each tested DOP.
SPINLOCK_EXT wait deltas collected with trace flag 8134 during a short controlled reproduction.
Extended Events spinlock_backoff and spinlock_backoff_warning call stacks for DIAG_OBJECT, SOS_CACHESTORE, and SOS_SELIST_SIZED_SLOCK.
Actual plans, CPU/elapsed results, parameter declarations, assembly source, Linux distribution/kernel, and results from both affected CUs.
The T-SQL control case showing that the contention is specific to the managed invocation path.

Microsoft documents Extended Events call stacks as the way to identify the operation acquiring a contended spinlock; interpreting the resulting internal stacks generally requires engine expertise and may require a support case.^4^

References

SQLCLR scalar function spins on DIAG_OBJECT at high DOP: CPU per call 36 times higher at DOP 24 than at DOP 4

2 answers

Recommended mitigation

What DIAG_OBJECT is protecting

Why KB2887899 does not apply directly

Escalation evidence

Your answer

What `DIAG_OBJECT` is protecting