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: 1. Set the flag to the state under test with `DBCC TRACEOFF` or `DBCC TRACEON(2861, -1)`. 2. Clear the plan cache with `DBCC FREESYSTEMCACHE(N'SQL Plans')`. 3. Run 1,000 “sentinel” statements of each of three kinds through `EXEC`: a variable assignment, `SET ROWCOUNT 0`, and `BEGIN TRAN; COMMIT TRAN;`. Each carries a unique comment tag, so each gets its own plan. 4. Count how many sentinel plans are in `sys.dm_exec_cached_plans`. 5. Apply memory pressure by running more distinct statements of the same three kinds, with a different tag. 6. Count the sentinel plans again, and record `DBCC TRACESTATUS(2861)` before and after. The core of the workload looks like this: Transact-SQL 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); 1234567891011121314151617181920212223242526272829303132 | USE [tf2861_scratch]; DECLARE @i int = 1;DECLARE @s nvarchar(400); WHILE @i <= 1000BEGIN 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: Transact-SQL DBCC TRACESTATUS(-1); 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 1. DBCC TRACEON (Transact-SQL) - Microsoft Learn. States that the command requires membership in the sysadmin fixed server role. ↩ 2. DBCC TRACEON - Trace Flags (Transact-SQL) - Microsoft Learn. The list of documented trace flags; it has no entry for 2861. ↩ 3. 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. ↩ 4. 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. ↩ ### Share this: * * Share on Bluesky (Opens in new window) Bluesky * Share on Mastodon (Opens in new window) Mastodon * Email a link to a friend (Opens in new window) Email * More * * Share on Threads (Opens in new window) Threads * Share on Reddit (Opens in new window) Reddit * * Share on WhatsApp (Opens in new window) WhatsApp * Print (Opens in new window) Print * ### _Related_

Microsoft has offered 27% of employees voluntary redundancy

Microsoft buyouts are open to about 60,000 employees in the US, UK, Canada and India, at level 70 and below. The package matches standard severance.

La suite bureautique Euro-Office est désormais disponible au téléchargement sous la forme d'un véritable client bureau natif pour Windows, macOS et Linux. De quoi accélérer le remplacement de Microsof...

AI-powered phishing detection is coming to Microsoft Teams

Reach 39.6K+ tracked companies with a Microsoft Dynamics CRM users list covering 247K+ contacts. Target sales, service and RevOps leaders. Get a sample.

Things are constantly being updated in the Microsoft Copilot offering, and it’s not unusual to see Microsoft retire or alter specific features as time progresses. In this retirement, Microsof…

The problem? Understanding how AI coding agents interact with the software you build. "Whether you're maintaining an SDK, building a command-line interface, or publishing an MCP server or skills... you need to understand whether agents can discover and use what you ship, and what your team can change." Results can vary, and configurations keep changing, with behaviors influenced by everything from custom instructions and MCP servers to skills and plugins. So after a year of development Microsoft has created Scope, "an open-source platform built on Kubernetes for turning those interactions into evidence product teams can act on." Details are available on... the Microsoft open source blog: Available under the MIT License, Scope supports a continuous Developer Agent Experience (DevAx) learning loop grounded in repeatable experiments. The Azure Cosmos DB team's experience shows what that loop can deliver... "The evaluations led to 20 Agent Kit issues and 27 documentation issues resolved," [says Jay Gordon, Senior Program Manager, Azure Cosmos DB]... These results connect evaluation to decisions a product team can make: which documentation needs clarification, where tooling needs a fix, and whether an update changes agent behavior.... [The gains] demonstrate how targeted changes, informed by observed behavior, can improve the way agents use a product. For more on the shift from building for human developers to supporting agents, watch Your Product Was Built for Humans. Is It Ready for Agents? or join us at AI DevCon NYC conference in November. Scope surfaces everything from incorrect assumptions in documentation (or recurring missed instructions) to whether agents select the intended SDK, initialize it correctly, and use its APIs, leading to clearer descriptions for command-line tool and more actionable errors. Microsoft's open source blog also explains why AI agent evaluation needs an open-source foundation: The challenges behind agent experience aren't unique to one product or organization. SDK maintainers, tool authors, researchers, and agent developers are all asking how agents behave and what helps them succeed. No single team can represent every language, workflow, or integration. An open project can bring more scenarios, more failure cases, and more viewpoints into the evaluation process... Opening Scope's execution and evaluation code gives contributors a way to question the methods, investigate unexpected results, and improve the evaluation itself. A shared foundation also reduces duplicated infrastructure. Agent integrations, reusable criteria, and trace-analysis improvements can help other teams study their own products without rebuilding the same machinery. The goal isn't one universal benchmark or definition of success. It's a common starting point that teams can adapt to their needs. The MIT License lets teams use and extend Scope, while self-managed deployments let them evaluate private workloads without publishing their code or run data.... Scope streams activity as the agent works and retains interactions and workspace snapshots for later review. Your team can follow tool calls, inspect generated files, and connect a failed requirement to the steps that preceded it. Those traces provide observable evidence of what the agent did, helping turn a failure into a concrete documentation, SDK, or tooling improvement. Read more of this story at Slashdot.

Microsoftが、クラウドベースの印刷サービス「Universal Print」に関する複数の問題を認めており、一部の環境では現在も根本的な解決に至っていないことが明らかになりました。 Universal Printは、Microsof

Microsoft hat mit dem kumulativen Oktober-Sicherheitsupdate KB5127648 einen schwerwiegenden Fehler behoben, der zuvor auf zahlreichen Systemen mit Windows 11 zu Spiel- und Anwendungsabstürzen führte. Die Aktualisierung erschien im Rahmen des regulären Patch-Dienstags am 13. Oktober 2026 und adressiert die Windows-11-Versionen 24H2, 25H2 sowie 26H2. Auslöser der Systemabstürze war das optionale September-Preview-Update KB5124010. Dieses beschädigte die […] The post Windows 11 KB5127648: Microsoft behebt Spielabstürze, neue Fehler bleiben appeared first on BornCity.

Microsoft could be hit with an SMS designation

Nscale data center's only publicly announced customer has pulled out