Testing Trace Flag 2861 and Zero-Cost Plans on SQL Server 2019 and 2025
A monitoring tool asks for sysadmin on every instance it watches. You ask why, and part of the answer is a trace flag that the tool turns on at startup. DBCC TRACEON needs sysadmin, so the tool needs sysadmin.[1]
I ran into this with SolarWinds Database Performance Analyzer (DPA), which turns on trace flag 2861. A trace flag is a switch that changes how SQL Server behaves. The flag isn’t in Microsoft’s list of documented trace flags.[2] One person who says he works with SQL Server at Microsoft wrote that it did nothing after the SQL Server 2000 service packs. I wanted to know whether it changes anything on current versions, so I tested it.
What SolarWinds Says the Flag Does
A SolarWinds blog post from 2016 explains the idea.[3] DPA samples sessions every second by default. It only retrieves statement text (with fn_get_sql, using the SQL handle) about every two minutes. The plan cache is the memory where SQL Server keeps compiled query plans. A zero-cost plan is a cached plan with no compile cost, such as one for an ad-hoc statement, a SET statement or a COMMIT TRAN. Under memory pressure, SQL Server can evict these plans first. If that happens before DPA asks for the text, the text is gone.
The post says DPA turns on trace flag 2861 by default to keep those plans in the cache longer. It also says the flag can add plan cache use on busy ad-hoc workloads, such as those from an object-relational mapper (ORM). SolarWinds recommends turning it off for those systems.
What the Other Side Says
Sean Gallardy, who says in his post that he works with SQL Server at Microsoft, wrote that the flag existed only in SQL Server 2000 SP3 and SP4. He wrote that Microsoft removed it before SQL Server 2005 shipped.[4] He says he reached that conclusion by reading the source code from 2005 RTM through 2019. His 2021 update says DPA still runs a command to turn it on.
I can’t read SQL Server source code. What I can do is measure what happens to zero-cost plans with the flag off and on.
The Test
I used two instances on one workstation: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer). Each ran with max server memory at 1024 MB, which I raised back to 2048 MB afterward. Each test used a scratch database, and I dropped it at the end.
Each run followed the same steps:
- Set the flag to the state under test with
DBCC TRACEOFForDBCC TRACEON(2861, -1). - Clear the plan cache with
DBCC FREESYSTEMCACHE(N'SQL Plans'). - Run 1,000 “sentinel” statements of each of three kinds through
EXEC: a variable assignment,SET ROWCOUNT 0, andBEGIN TRAN; COMMIT TRAN;. Each carries a unique comment tag, so each gets its own plan. - Count how many sentinel plans are in
sys.dm_exec_cached_plans. - Apply memory pressure by running more distinct statements of the same three kinds, with a different tag.
- Count the sentinel plans again, and record
DBCC TRACESTATUS(2861)before and after.
The core of the workload looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
USE [tf2861_scratch]; DECLARE @i int = 1; DECLARE @s nvarchar(400); WHILE @i <= 1000 BEGIN SET @s = N'DECLARE @v int; SELECT @v = ' + CONVERT(nvarchar(12), @i) + N' OPTION (MAXDOP 1); /*S1:' + CONVERT(nvarchar(12), @i) + N'*/'; EXEC (@s); SET @s = N'SET ROWCOUNT 0 /*S2:' + CONVERT(nvarchar(12), @i) + N'*/'; EXEC (@s); SET @s = N'BEGIN TRAN; COMMIT TRAN; /*S3:' + CONVERT(nvarchar(12), @i) + N'*/'; EXEC (@s); SET @i += 1; END; SELECT SUBSTRING([t].[text], CHARINDEX(N'/*S', [t].[text]) + 2, 2) AS [kind], COUNT_BIG(*) AS [sentinel_plans] FROM [sys].[dm_exec_cached_plans] AS [cp] CROSS APPLY [sys].[dm_exec_sql_text]([cp].[plan_handle]) AS [t] WHERE [cp].[cacheobjtype] = N'Compiled Plan' AND [t].[text] LIKE N'%/*S[123]:%' GROUP BY SUBSTRING([t].[text], CHARINDEX(N'/*S', [t].[text]) + 2, 2); |
I ran 240 runs in three sets. Every run recorded the flag state, and DBCC TRACESTATUS(2861) reported 1 during every flag-on run and 0 during every flag-off run. So the flag was set during the flag-on runs. That doesn’t show it does anything.
Results
Heavy pressure. With 120,000 pressure statements per run, no sentinel plan survived in any run, on either version, with the flag on or off. That’s 10 runs per setting per instance.
Graded pressure. Next I ran five alternating pairs at each of four pressure levels. The table shows the average number of surviving sentinel plans out of 1,000 per statement kind. “n” is the number of loop iterations, and each iteration runs three pressure statements.
| Instance | n | Flag off | Flag on |
|---|---|---|---|
| SQL Server 2019 | 2,000 | 800 | 812 |
| SQL Server 2019 | 5,000 | 0 | 95 |
| SQL Server 2019 | 10,000 | 0 | 0 |
| SQL Server 2019 | 20,000 | 0 | 0 |
| SQL Server 2025 | 2,000 | 1,000 | 984 |
| SQL Server 2025 | 5,000 | 942 | 866 |
| SQL Server 2025 | 10,000 | 0 | 0 |
| SQL Server 2025 | 20,000 | 0 | 0 |
On SQL Server 2019 at n = 5,000, the flag-on runs retained plans and the flag-off runs didn’t. Only two of the five flag-on runs kept any plans (334 and 140 for the first statement kind). The other three kept none, and the flag-off runs kept none. Five runs per setting can’t tell a real effect from variation, so I repeated this pressure level with more runs.
Randomized order. To remove that pattern I ran n = 5,000 again, 30 runs per setting per instance, in random order. The table shows how many of the 30 runs kept at least one sentinel plan, and the average number kept per 1,000.
| Instance | Flag | Runs with survivors | Average kept |
|---|---|---|---|
| SQL Server 2019 | off | 9 of 30 | 130 |
| SQL Server 2019 | on | 5 of 30 | 86 |
| SQL Server 2025 | off | 22 of 30 | 662 |
| SQL Server 2025 | on | 25 of 30 | 682 |
The counts vary a lot from run to run, and the two settings overlap on both versions. Flag-on was lower on 2019 and higher on 2025. I can’t call either difference real.
What This Does and Doesn’t Show
On these two builds, with this workload, I didn’t see a reliable difference. Zero-cost plans stayed in the plan cache about as often with the flag on as with it off. That fits what Gallardy says, but my test can’t confirm his claim about the source code. One workload can’t prove a negative.
Some limits to keep in mind:
- I tested one build of each version, on one workstation, with a synthetic workload.
- I didn’t run DPA. I tested the flag, not DPA’s behaviour. I can’t say what DPA does on your instances, or which version of DPA you run.
- Eviction depends on the memory state at the time. I only cleared the plan cache before each run.
- The SolarWinds post is from 2016. I tested SQL Server 2019 and 2025 only, so I can’t say anything about the versions that were current then.
- I counted surviving plans. I didn’t measure how much statement text DPA would lose, or how often.
What This Means for Permissions
If DPA turns the flag on with DBCC TRACEON, the login it uses needs sysadmin. If sysadmin is requested only so the tool can set this flag, ask the vendor what else needs it. I’d also ask which DPA features rely on the flag before touching the login, and test narrower permissions on a non-production instance first. The SolarWinds post says you can turn the flag off if plan cache growth is a problem. It says the cost is that DPA won’t capture the text of some statements.
A quick way to see what a monitored instance is running:
|
1 |
DBCC TRACESTATUS(-1); |
If 2861 shows up in the output, something turned it on. I’d check whether it’s a startup parameter, or something a monitoring tool runs after it connects.
Try It Yourself
Run the test on a non-production instance. Set max server memory low enough to see eviction, but not so low that you lock yourself out. I used 1024 MB on a machine with 31 GB of RAM. Note your current value first. A setting that’s too low can lock you out, and recovery then means starting the instance in single-user mode. Write down your build number, and run the flag-on and flag-off cases in random order. If your numbers differ from mine, I’d like to see them.
If you’ve tested this flag, or run DPA with narrower permissions, I’d like to hear what you found. Find me on Bluesky or LinkedIn.
References
- DBCC TRACEON (Transact-SQL) - Microsoft Learn. States that the command requires membership in the sysadmin fixed server role. ↩
- DBCC TRACEON - Trace Flags (Transact-SQL) - Microsoft Learn. The list of documented trace flags; it has no entry for 2861. ↩
- Why DPA Uses SQL Server Trace Flag 2861 and Zero Cost Plans - Thomas LaRock, SolarWinds blog, December 7, 2016. The explanation of why DPA turns the flag on and when to turn it off. ↩
- Trace Flag 2861: The Trace Flag, The Myth, The Legend - Sean Gallardy. The claim that the flag existed only in SQL Server 2000 SP3 and SP4, with notes dated April 30 and July 4, 2021. ↩