#SSMS
SSMS Tips and Tricks: Making sense of the colors in the SSMS scroll bar

blog.greglow.com/2026/11/16/s...
November 16, 2026 at 4:00 AM
"hey guys this is mamba 2, it performs better than naive linear attention because it maps better to tensor core hardware"

oh fr? are the resulting models good?

"not really no"
Transformers are SSMs: Generalized Models and Efficient Algorithms Through Structured State Space Duality
While Transformers have been the main architecture behind deep learning's success in language modeling, state-space models (SSMs) such as Mamba have recently been shown to match or outperform Transfor...
arxiv.org
November 12, 2026 at 6:54 PM
SSMS 22.7.1 Schema Compare: Five More Reasons to Look Before You Apply

In my earlier post, SSMS 22.7 Schema Compare: Before You Click Apply..............

www.sqlfingers.com/2026/11/ssms...
SSMS 22.7.1 Schema Compare: Five More Reasons to Look Before You Apply
In my earlier post, SSMS 22.7 Schema Compare: Before You Click Apply , I explored some of the things that can happen when you trust Sche...
www.sqlfingers.com
November 10, 2026 at 2:35 AM
How to Set Background Images & Movies in SSMS v22

So, uh, you can do this: In dark mode too: Here's how: Download the free ClaudiaIDE extension for Visual Studio. It's a VSIX file. Put it in your downloads folder. Technically, extensions aren't supported by SSMS v22 yet, so you'll have to run this…
How to Set Background Images & Movies in SSMS v22
So, uh, you can do this: In dark mode too: Here's how: Download the free ClaudiaIDE extension for Visual Studio. It's a VSIX file. Put it in your downloads folder. Technically, extensions aren't supported by SSMS v22 yet, so you'll have to run this PowerShell script to install it. SSMS will relaunch with ClaudiaIDE's default background image. Go into C:\Program Files\Microsoft SQL Server Management Studio 22\Release\Common7\IDE\Extensions\ClaudiaIDE\Images - or wherever your SSMS is installed - and drop the images you'd like to use there.
www.brentozar.com
November 11, 2026 at 1:41 PM
Visual Studio now has hot exit: close it with unsaved changes and they're still there when you come back.

It's off by default. Search Tools > Options for "Reopen unsaved documents". Coming to SSMS too.

@dot.net #dotnetconf
November 11, 2026 at 5:32 PM
LIVE: Trump CRASHES OUT ALL DAY!!! 11/12/2026

www.youtube.com/watch?v=Ssms...
LIVE: Trump CRASHES OUT ALL DAY!!! 11/12/2026
YouTube video by MeidasTouch
www.youtube.com
November 12, 2026 at 10:34 PM
Attention to Mamba: A Recipe for Cross-Architecture Distillation
Discussion | hackernews | Author: zerop

#MachineLearning
Attention to Mamba: A Recipe for Cross-Architecture Distillation
State Space Models (SSMs) such as Mamba have become a popular alternative to Transformer models, due to their reduced memory consumption and…
machinelearning.apple.com
November 11, 2026 at 3:52 PM
Postdoctoral Scholar Abby Cunniff on how incarcerated workers became the backbone of California’s environmental workforce. Thu, Nov 19, 4–5:30 PM, SSMS 3145 at UCSB. Co-sponsored with the Organizing Knowledges Project. RSVP by Nov 18: https://bit.ly/4eodCBg #UCSB #BlumCenter
November 10, 2026 at 7:01 PM
In Visual Studio, Ctrl+Shift+F searches all files. In SSMS it silently switches to "Results to File": your next query asks where to save.

Find in Files: Ctrl+Shift+H
Back to grid: Ctrl+D

RenSQL for SSMS: run SQL without selecting, query history, SQL library. rowdyne.com/rensql

#SSMS #SQLServer
November 9, 2026 at 6:47 PM
Introducing Performance Studio: A Cross-Platform Environment for SQL Server Query Plan Analysis

Performance Studio Stop clicking through SSMS execution plans like it's...
Introducing Performance Studio: A Cross-Platform Environment for SQL Server Query Plan Analysis
Performance Studio Stop clicking through SSMS execution plans like it's 2005. Performance Studio is a free, open-source plan analyzer that tells you what's wrong, where it's wrong, and how bad...
erikdarling.com
November 10, 2026 at 6:10 PM
Happy birthday, Arthur Bowley (b. 1869), pioneer of survey sampling.

#SQLServer tip: TABLESAMPLE samples pages, not rows.

Our 10% sample: 187 runs of ~56 adjacent rows. A cluster sample.

For random rows (full scan + sort):
SELECT TOP (10) PERCENT *
FROM t
ORDER BY NEWID();

#SSMS #Statistics
November 6, 2026 at 4:14 PM
Idempotent SQL Server Agent Job Deployment Across Many Instances

Why the scripted job fails on a second run, a deploy script you can rerun on every instance, and a query that finds job drift.

https://www.sqlserverscience.com/sql-server-agent/agent-job-deployment/
Idempotent SQL Server Agent Job Deployment Across Many Instances
Say the same maintenance job runs on forty instances. Last month someone changed step 1 on SQLPROD07 by hand to work around a problem, and nobody wrote it down. Now the job has to change everywhere, and the only deployment script is one that SQL Server Management Studio (SSMS) produced two years ago, from “Script Job as” and then “CREATE To”. I wanted an idempotent script: one I can run on every instance, as often as I like, and get the same end state each time. I tested what the SSMS script does on a second run, wrote a deploy script that can be rerun, and wrote a query that shows which instances have drifted. ## What the Scripted Job Does on a Second Run SSMS builds its scripts with SQL Server Management Objects (SMO), the .NET library that Management Studio uses to script an operation instead of running it.[1] I called SMO’s `Script()` method from PowerShell on two jobs, `sat_deploy_demo` and `sat_deploy_demo_b`, on SQL Server 2019. Both jobs share one schedule. Your version of SSMS may produce slightly different output. The script doesn’t hard-code the `job_id`. It declares `@jobId` and gets the value back from `sp_add_job` as an `OUTPUT` parameter. It does hard-code two other values: Transact-SQL @active_start_date=20261030, @schedule_uid=N'ebf27d06-9ddc-4212-8d6e-8b8631ee9e61' 12 | @active_start_date=20261030,@schedule_uid=N'ebf27d06-9ddc-4212-8d6e-8b8631ee9e61' ---|--- The start date is the day the schedule was created on the source. The `schedule_uid` is the source schedule’s GUID. I ran the script again on the instance it came from. Each job’s batch stopped at `sp_add_job`, and the script’s `GOTO QuitWithRollback` rolled back the transaction: Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 60 The specified @name ('sat_deploy_demo') already exists. Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 60 The specified @name ('sat_deploy_demo_b') already exists. 1234 | Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 60The specified @name ('sat_deploy_demo') already exists.Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 60The specified @name ('sat_deploy_demo_b') already exists. ---|--- Nothing changed, and nothing was duplicated. The script also can’t update a job that exists, so it’s no use for pushing a change. The “DROP And CREATE” form fails in a different way. SMO wrote the drop as a name check followed by a delete by ID: Transact-SQL IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'sat_deploy_demo') EXEC msdb.dbo.sp_delete_job @job_id=N'e9541ea4-383f-4b35-80a8-93339f5bf08d', @delete_unused_schedule=1 12 | IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'sat_deploy_demo')EXEC msdb.dbo.sp_delete_job @job_id=N'e9541ea4-383f-4b35-80a8-93339f5bf08d', @delete_unused_schedule=1 ---|--- That GUID belongs to the job on the source instance. On SQL Server 2025 the job had the same name and a different `job_id`, so the delete raised Msg 14262 (“The specified @job_id … does not exist”) and the create then raised Msg 14261. On the source instance, where that ID matches, the drop would also delete the job’s history, because `sp_delete_job` defaults `@delete_history` to 1.[2] ## The schedule_uid Has a Side Effect To see what happens on an instance that doesn’t have the job, I renamed the jobs in the script to `sat_ssms_copy` and `sat_ssms_copy_b` and ran it on SQL Server 2025. The first copy created a new schedule with the source’s `schedule_uid`. The second copy didn’t create another one. It attached to the first, because `sp_add_schedule` looks up an existing schedule by `schedule_uid` and updates and reuses it. A comment in the procedure’s source says a target server (TSX) uses this to match schedules downloaded from a master server. The `sp_add_schedule` documentation lists `@schedule_uid` as an `OUTPUT` parameter and doesn’t mention that an incoming value matches an existing schedule.[3] The documentation for `sp_add_jobschedule`, the procedure the script calls, describes the parameter only as “a unique identifier for the schedule”. The same page recommends `sp_add_schedule` and `sp_attach_schedule` instead.[4] When the GUID matches, the procedure’s source sets the existing schedule’s name and its frequency and time settings from the values the script passes. An old script run again could put back a schedule that someone has edited since. I read that in the source and didn’t test it. That instance already had a schedule called `sat_nightly_0200`, created by my deploy script with its own GUID. After the copy ran, it had two schedules with that name. Schedule names don’t have to be unique. The only index on `msdb.dbo.sysschedules` is the primary key on `schedule_id`. After that, `sp_attach_schedule` with `@schedule_name` failed: Msg 14371, Level 16, State 1, Procedure msdb.dbo.sp_verify_schedule_identifiers, Line 113 There are two or more schedules named "sat_nightly_0200". Specify @schedule_id instead of @schedule_name to uniquely identify the schedule. 12 | Msg 14371, Level 16, State 1, Procedure msdb.dbo.sp_verify_schedule_identifiers, Line 113There are two or more schedules named "sat_nightly_0200". Specify @schedule_id instead of @schedule_name to uniquely identify the schedule. ---|--- I removed the two copy jobs before going on. ## A Deploy Script You Can Rerun The script below finds the job by name in `msdb.dbo.sysjobs` and calls `sp_add_job` or `sp_update_job`. It adds or updates each step by `step_id`, deletes steps it no longer defines, attaches one named schedule, and targets the job at the local server. It runs in one transaction, so a failure leaves the job as it was. The msdb update procedures keep a column’s current value when its parameter is NULL, so the script passes every setting that the drift query further down compares, including the ones left at their defaults. A setting the script leaves out keeps any hand change through the next deploy. For the second job I changed only `@job_name`. Transact-SQL USE [msdb]; SET NOCOUNT ON; DECLARE @job_name sysname = N'sat_deploy_demo'; DECLARE @job_description nvarchar(512) = N'Deployed by deploy-job.sql. Change the script, not the job.'; DECLARE @category_name sysname = N'sat_Deployed'; DECLARE @owner_login sysname = N'sa'; DECLARE @schedule_name sysname = N'sat_nightly_0200'; DECLARE @notify_eventlog int = 2; DECLARE @notify_email int = 0; DECLARE @delete_level int = 0; DECLARE @job_id uniqueidentifier; DECLARE @schedule_id int; DECLARE @step_id int; DECLARE @max_step_id int; DECLARE @owns_transaction bit = 0; DECLARE @steps TABLE ( [step_id] int NOT NULL PRIMARY KEY , [step_name] sysname NOT NULL , [database_name] sysname NOT NULL , [command] nvarchar(max) NOT NULL , [database_user_name] sysname NULL , [proxy_name] sysname NULL , [on_success_action] tinyint NOT NULL , [on_success_step_id] int NOT NULL , [on_fail_action] tinyint NOT NULL , [on_fail_step_id] int NOT NULL , [retry_attempts] int NOT NULL , [retry_interval] int NOT NULL ); /* on_success_action / on_fail_action: 1 = quit with success, 2 = quit with failure, 3 = go to next step, 4 = go to the step in on_success_step_id / on_fail_step_id */ INSERT INTO @steps ( [step_id] , [step_name] , [database_name] , [command] , [database_user_name] , [proxy_name] , [on_success_action] , [on_success_step_id] , [on_fail_action] , [on_fail_step_id] , [retry_attempts] , [retry_interval] ) VALUES (1, N'Check', N'sat_agent_deploy', N'SELECT 1;', NULL, NULL, 3, 0, 2, 0, 0, 0) , (2, N'Report', N'sat_agent_deploy', N'SELECT DB_NAME();', NULL, NULL, 1, 0, 2, 0, 1, 1); IF @@TRANCOUNT = 0 BEGIN SET @owns_transaction = 1; BEGIN TRANSACTION; END; BEGIN TRY IF NOT EXISTS ( SELECT 1 FROM [dbo].[syscategories] AS [c] WHERE [c].[name] = @category_name AND [c].[category_class] = 1 ) BEGIN EXEC [dbo].[sp_add_category] @class = N'JOB' , @type = N'LOCAL' , @name = @category_name; PRINT N'Category added.'; END; SELECT @job_id = [j].[job_id] FROM [dbo].[sysjobs] AS [j] WHERE [j].[name] = @job_name; IF @job_id IS NULL BEGIN EXEC [dbo].[sp_add_job] @job_name = @job_name , @enabled = 1 , @description = @job_description , @category_name = @category_name , @owner_login_name = @owner_login , @notify_level_eventlog = @notify_eventlog , @notify_level_email = @notify_email , @delete_level = @delete_level , @job_id = @job_id OUTPUT; PRINT N'Job added.'; END ELSE BEGIN EXEC [dbo].[sp_update_job] @job_id = @job_id , @enabled = 1 , @description = @job_description , @category_name = @category_name , @owner_login_name = @owner_login , @notify_level_eventlog = @notify_eventlog , @notify_level_email = @notify_email , @delete_level = @delete_level; PRINT N'Job updated.'; END; SELECT @max_step_id = MAX([s].[step_id]) FROM @steps AS [s]; SET @step_id = 1; /* The step_id values in @steps must run from 1 with no gaps */ WHILE @step_id <= @max_step_id BEGIN DECLARE @step_name sysname; DECLARE @database_name sysname; DECLARE @command nvarchar(max); DECLARE @database_user_name sysname; DECLARE @proxy_name sysname; DECLARE @on_success_action tinyint; DECLARE @on_success_step_id int; DECLARE @on_fail_action tinyint; DECLARE @on_fail_step_id int; DECLARE @retry_attempts int; DECLARE @retry_interval int; SELECT @step_name = [s].[step_name] , @database_name = [s].[database_name] , @command = [s].[command] , @database_user_name = [s].[database_user_name] , @proxy_name = [s].[proxy_name] , @on_success_action = [s].[on_success_action] , @on_success_step_id = [s].[on_success_step_id] , @on_fail_action = [s].[on_fail_action] , @on_fail_step_id = [s].[on_fail_step_id] , @retry_attempts = [s].[retry_attempts] , @retry_interval = [s].[retry_interval] FROM @steps AS [s] WHERE [s].[step_id] = @step_id; IF EXISTS ( SELECT 1 FROM [dbo].[sysjobsteps] AS [js] WHERE [js].[job_id] = @job_id AND [js].[step_id] = @step_id ) BEGIN /* On update, NULL keeps the current value. An empty string clears it. */ SET @database_user_name = ISNULL(@database_user_name, N''); SET @proxy_name = ISNULL(@proxy_name, N''); EXEC [dbo].[sp_update_jobstep] @job_id = @job_id , @step_id = @step_id , @step_name = @step_name , @subsystem = N'TSQL' , @database_name = @database_name , @command = @command , @database_user_name = @database_user_name , @proxy_name = @proxy_name , @on_success_action = @on_success_action , @on_success_step_id = @on_success_step_id , @on_fail_action = @on_fail_action , @on_fail_step_id = @on_fail_step_id , @retry_attempts = @retry_attempts , @retry_interval = @retry_interval; END ELSE BEGIN EXEC [dbo].[sp_add_jobstep] @job_id = @job_id , @step_id = @step_id , @step_name = @step_name , @subsystem = N'TSQL' , @database_name = @database_name , @command = @command , @database_user_name = @database_user_name , @proxy_name = @proxy_name , @on_success_action = @on_success_action , @on_success_step_id = @on_success_step_id , @on_fail_action = @on_fail_action , @on_fail_step_id = @on_fail_step_id , @retry_attempts = @retry_attempts , @retry_interval = @retry_interval; END; SET @step_id = @step_id + 1; END; /* Remove steps the script no longer defines, highest first */ SELECT @step_id = MAX([js].[step_id]) FROM [dbo].[sysjobsteps] AS [js] WHERE [js].[job_id] = @job_id; WHILE @step_id > @max_step_id BEGIN EXEC [dbo].[sp_delete_jobstep] @job_id = @job_id , @step_id = @step_id; SET @step_id = @step_id - 1; END; EXEC [dbo].[sp_update_job] @job_id = @job_id , @start_step_id = 1; IF ( SELECT COUNT_BIG(1) FROM [dbo].[sysschedules] AS [ss] WHERE [ss].[name] = @schedule_name ) > 1 BEGIN RAISERROR(N'More than one schedule is named %s. Fix that before deploying.', 16, 1, @schedule_name); END; SELECT @schedule_id = [ss].[schedule_id] FROM [dbo].[sysschedules] AS [ss] WHERE [ss].[name] = @schedule_name; IF @schedule_id IS NULL BEGIN EXEC [dbo].[sp_add_schedule] @schedule_name = @schedule_name , @enabled = 1 , @freq_type = 4 , @freq_interval = 1 , @freq_subday_type = 1 , @freq_subday_interval = 0 , @freq_relative_interval = 0 , @freq_recurrence_factor = 0 , @active_start_time = 20000 , @active_end_time = 235959 , @active_end_date = 99991231 , @owner_login_name = @owner_login , @schedule_id = @schedule_id OUTPUT; PRINT N'Schedule added.'; END ELSE BEGIN EXEC [dbo].[sp_update_schedule] @schedule_id = @schedule_id , @enabled = 1 , @freq_type = 4 , @freq_interval = 1 , @freq_subday_type = 1 , @freq_subday_interval = 0 , @freq_relative_interval = 0 , @freq_recurrence_factor = 0 , @active_start_time = 20000 , @active_end_time = 235959 , @active_end_date = 99991231 , @owner_login_name = @owner_login; END; IF NOT EXISTS ( SELECT 1 FROM [dbo].[sysjobschedules] AS [jsc] WHERE [jsc].[job_id] = @job_id AND [jsc].[schedule_id] = @schedule_id ) BEGIN EXEC [dbo].[sp_attach_schedule] @job_id = @job_id , @schedule_id = @schedule_id; PRINT N'Schedule attached.'; END; IF NOT EXISTS ( SELECT 1 FROM [dbo].[sysjobservers] AS [jsv] WHERE [jsv].[job_id] = @job_id ) BEGIN EXEC [dbo].[sp_add_jobserver] @job_id = @job_id , @server_name = N'(local)'; PRINT N'Job server added.'; END; IF @owns_transaction = 1 BEGIN COMMIT TRANSACTION; END; END TRY BEGIN CATCH IF @owns_transaction = 1 AND @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; THROW; END CATCH; 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330 | USE [msdb]; SET NOCOUNT ON; DECLARE @job_name sysname = N'sat_deploy_demo';DECLARE @job_description nvarchar(512) = N'Deployed by deploy-job.sql. Change the script, not the job.';DECLARE @category_name sysname = N'sat_Deployed';DECLARE @owner_login sysname = N'sa';DECLARE @schedule_name sysname = N'sat_nightly_0200';DECLARE @notify_eventlog int = 2;DECLARE @notify_email int = 0;DECLARE @delete_level int = 0;DECLARE @job_id uniqueidentifier;DECLARE @schedule_id int;DECLARE @step_id int;DECLARE @max_step_id int;DECLARE @owns_transaction bit = 0; DECLARE @steps TABLE( [step_id] int NOT NULL PRIMARY KEY , [step_name] sysname NOT NULL , [database_name] sysname NOT NULL , [command] nvarchar(max) NOT NULL , [database_user_name] sysname NULL , [proxy_name] sysname NULL , [on_success_action] tinyint NOT NULL , [on_success_step_id] int NOT NULL , [on_fail_action] tinyint NOT NULL , [on_fail_step_id] int NOT NULL , [retry_attempts] int NOT NULL , [retry_interval] int NOT NULL); /* on_success_action / on_fail_action: 1 = quit with success, 2 = quit with failure, 3 = go to next step, 4 = go to the step in on_success_step_id / on_fail_step_id*/INSERT INTO @steps( [step_id] , [step_name] , [database_name] , [command] , [database_user_name] , [proxy_name] , [on_success_action] , [on_success_step_id] , [on_fail_action] , [on_fail_step_id] , [retry_attempts] , [retry_interval])VALUES (1, N'Check', N'sat_agent_deploy', N'SELECT 1;', NULL, NULL, 3, 0, 2, 0, 0, 0) , (2, N'Report', N'sat_agent_deploy', N'SELECT DB_NAME();', NULL, NULL, 1, 0, 2, 0, 1, 1); IF @@TRANCOUNT = 0BEGIN SET @owns_transaction = 1; BEGIN TRANSACTION;END; BEGIN TRY IF NOT EXISTS ( SELECT 1 FROM [dbo].[syscategories] AS [c] WHERE [c].[name] = @category_name AND [c].[category_class] = 1 ) BEGIN EXEC [dbo].[sp_add_category] @class = N'JOB' , @type = N'LOCAL' , @name = @category_name; PRINT N'Category added.'; END; SELECT @job_id = [j].[job_id] FROM [dbo].[sysjobs] AS [j] WHERE [j].[name] = @job_name; IF @job_id IS NULL BEGIN EXEC [dbo].[sp_add_job] @job_name = @job_name , @enabled = 1 , @description = @job_description , @category_name = @category_name , @owner_login_name = @owner_login , @notify_level_eventlog = @notify_eventlog , @notify_level_email = @notify_email , @delete_level = @delete_level , @job_id = @job_id OUTPUT; PRINT N'Job added.'; END ELSE BEGIN EXEC [dbo].[sp_update_job] @job_id = @job_id , @enabled = 1 , @description = @job_description , @category_name = @category_name , @owner_login_name = @owner_login , @notify_level_eventlog = @notify_eventlog , @notify_level_email = @notify_email , @delete_level = @delete_level; PRINT N'Job updated.'; END; SELECT @max_step_id = MAX([s].[step_id]) FROM @steps AS [s]; SET @step_id = 1; /* The step_id values in @steps must run from 1 with no gaps */ WHILE @step_id <= @max_step_id BEGIN DECLARE @step_name sysname; DECLARE @database_name sysname; DECLARE @command nvarchar(max); DECLARE @database_user_name sysname; DECLARE @proxy_name sysname; DECLARE @on_success_action tinyint; DECLARE @on_success_step_id int; DECLARE @on_fail_action tinyint; DECLARE @on_fail_step_id int; DECLARE @retry_attempts int; DECLARE @retry_interval int; SELECT @step_name = [s].[step_name] , @database_name = [s].[database_name] , @command = [s].[command] , @database_user_name = [s].[database_user_name] , @proxy_name = [s].[proxy_name] , @on_success_action = [s].[on_success_action] , @on_success_step_id = [s].[on_success_step_id] , @on_fail_action = [s].[on_fail_action] , @on_fail_step_id = [s].[on_fail_step_id] , @retry_attempts = [s].[retry_attempts] , @retry_interval = [s].[retry_interval] FROM @steps AS [s] WHERE [s].[step_id] = @step_id; IF EXISTS ( SELECT 1 FROM [dbo].[sysjobsteps] AS [js] WHERE [js].[job_id] = @job_id AND [js].[step_id] = @step_id ) BEGIN /* On update, NULL keeps the current value. An empty string clears it. */ SET @database_user_name = ISNULL(@database_user_name, N''); SET @proxy_name = ISNULL(@proxy_name, N''); EXEC [dbo].[sp_update_jobstep] @job_id = @job_id , @step_id = @step_id , @step_name = @step_name , @subsystem = N'TSQL' , @database_name = @database_name , @command = @command , @database_user_name = @database_user_name , @proxy_name = @proxy_name , @on_success_action = @on_success_action , @on_success_step_id = @on_success_step_id , @on_fail_action = @on_fail_action , @on_fail_step_id = @on_fail_step_id , @retry_attempts = @retry_attempts , @retry_interval = @retry_interval; END ELSE BEGIN EXEC [dbo].[sp_add_jobstep] @job_id = @job_id , @step_id = @step_id , @step_name = @step_name , @subsystem = N'TSQL' , @database_name = @database_name , @command = @command , @database_user_name = @database_user_name , @proxy_name = @proxy_name , @on_success_action = @on_success_action , @on_success_step_id = @on_success_step_id , @on_fail_action = @on_fail_action , @on_fail_step_id = @on_fail_step_id , @retry_attempts = @retry_attempts , @retry_interval = @retry_interval; END; SET @step_id = @step_id + 1; END; /* Remove steps the script no longer defines, highest first */ SELECT @step_id = MAX([js].[step_id]) FROM [dbo].[sysjobsteps] AS [js] WHERE [js].[job_id] = @job_id; WHILE @step_id > @max_step_id BEGIN EXEC [dbo].[sp_delete_jobstep] @job_id = @job_id , @step_id = @step_id; SET @step_id = @step_id - 1; END; EXEC [dbo].[sp_update_job] @job_id = @job_id , @start_step_id = 1; IF ( SELECT COUNT_BIG(1) FROM [dbo].[sysschedules] AS [ss] WHERE [ss].[name] = @schedule_name ) > 1 BEGIN RAISERROR(N'More than one schedule is named %s. Fix that before deploying.', 16, 1, @schedule_name); END; SELECT @schedule_id = [ss].[schedule_id] FROM [dbo].[sysschedules] AS [ss] WHERE [ss].[name] = @schedule_name; IF @schedule_id IS NULL BEGIN EXEC [dbo].[sp_add_schedule] @schedule_name = @schedule_name , @enabled = 1 , @freq_type = 4 , @freq_interval = 1 , @freq_subday_type = 1 , @freq_subday_interval = 0 , @freq_relative_interval = 0 , @freq_recurrence_factor = 0 , @active_start_time = 20000 , @active_end_time = 235959 , @active_end_date = 99991231 , @owner_login_name = @owner_login , @schedule_id = @schedule_id OUTPUT; PRINT N'Schedule added.'; END ELSE BEGIN EXEC [dbo].[sp_update_schedule] @schedule_id = @schedule_id , @enabled = 1 , @freq_type = 4 , @freq_interval = 1 , @freq_subday_type = 1 , @freq_subday_interval = 0 , @freq_relative_interval = 0 , @freq_recurrence_factor = 0 , @active_start_time = 20000 , @active_end_time = 235959 , @active_end_date = 99991231 , @owner_login_name = @owner_login; END; IF NOT EXISTS ( SELECT 1 FROM [dbo].[sysjobschedules] AS [jsc] WHERE [jsc].[job_id] = @job_id AND [jsc].[schedule_id] = @schedule_id ) BEGIN EXEC [dbo].[sp_attach_schedule] @job_id = @job_id , @schedule_id = @schedule_id; PRINT N'Schedule attached.'; END; IF NOT EXISTS ( SELECT 1 FROM [dbo].[sysjobservers] AS [jsv] WHERE [jsv].[job_id] = @job_id ) BEGIN EXEC [dbo].[sp_add_jobserver] @job_id = @job_id , @server_name = N'(local)'; PRINT N'Job server added.'; END; IF @owns_transaction = 1 BEGIN COMMIT TRANSACTION; END;END TRYBEGIN CATCH IF @owns_transaction = 1 AND @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; THROW;END CATCH; ---|--- * **Owner.** The job and the schedule both belong to `sa`. `sp_attach_schedule` requires the schedule and the job to have the same owner.[5] The owner section below covers why the owner shouldn’t be a person. * **Category.** `sp_add_job` with a category that doesn’t exist raised Msg 14262 (“The specified @category_name (‘sat_NoSuchCategory’) does not exist.”) on both versions, so the script creates the category first. A category of its own also gives the drift query an easy way to find the deployed jobs. * **Schedule.** The script finds the schedule by name and stops with an error if more than one has that name. I tested that guard while the duplicate schedule existed, and the transaction rolled back. `schedule_id` and `schedule_uid` were different on each instance (366 and 45 for the same schedule), because the script lets `sp_add_schedule` assign them. A script could pass a fixed `@schedule_uid`, as the SMO script does, but then it gets the side effects in the previous section, so I match on the name. * **Job server.** `sp_add_jobserver` with `@server_name = N'(local)'` targets the job at the local instance.[6] Without it, `sp_start_job` refuses to start the job. On a test job with no job server it raised Msg 14256: “Cannot start the job … because it does not have any job server or servers defined.” * **Enabled.** The script sets `@enabled = 1` every time. If someone disables a job on one instance on purpose, the next deploy turns it back on. Decide whether the script or the DBA owns that flag, and remove the parameter from `sp_update_job` if it’s the DBA. ## Running It Twice I ran the script twice for each job on both instances: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer), on one workstation. On the 2025 instance I removed the jobs, their schedule and the category first, so the first run added the category, job, schedule and job server. The second run printed only “Job updated”. The `job_id` didn’t change, both jobs still shared one schedule, and each job still had one job server row (`server_id` 0, the local server). Next I checked the settings the script passes at their default values. On the 2019 instance I changed the job’s `notify_level_eventlog`, step 1’s `on_fail_action`, `on_fail_step_id` and `database_user_name`, and the shared schedule’s `freq_subday_type`, `freq_subday_interval` and `active_end_time`. An earlier version of the script passed only the parameters it needed for this job. It set `on_fail_action` back and left the other six changes in place, and the drift query still reported both jobs. The script above set all of them back, and both jobs had the same hashes as on the 2025 instance. The second run isn’t a no-op. On the 2025 instance, `version_number` in `sysjobs` went from 4 to 8 and `date_modified` moved, because the script calls `sp_update_job` and `sp_update_jobstep` every time. The definition didn’t change, but the metadata records an update. If you audit on `date_modified`, compare the definition before you update. SQL Server Agent was stopped on the 2025 instance, and I left it stopped. The msdb procedures still created and updated the jobs. Several printed “SQLServerAgent is not currently running so it cannot be notified of this action.” and the script still exited with code 0. `sp_start_job` returned the same message as an error and started nothing. On the 2019 instance I started `sat_deploy_demo` once, and both steps succeeded. ## Job Owners People leave, and their logins get disabled or removed, so a person’s login is a poor job owner. Use `sa`, or a dedicated login that you create on every instance before the jobs. The job history for my `sa`-owned job said “Executed as user: NT AUTHORITY\NETWORK SERVICE”, which is the Agent service account on that instance. Agent runs the T-SQL steps of a job owned by a sysadmin as the Agent service account, and uses `EXECUTE AS LOGIN` only when the owner isn’t a sysadmin. Why Resource Governor Ignores Your SQL Server Agent Job Owner explains how Agent connects, and why the job owner doesn’t decide which login the session uses. `sp_add_job` documents that only sysadmin members can set the owner, and that Agent runs T-SQL job steps in the owner’s security context.[7] * A login name that doesn’t exist, such as `CONTOSO\sat_no_such_user`, raised Msg 14234 (“The specified ‘@owner_login_name’ is invalid”) from both `sp_add_job` and `sp_update_job`. No job was created and the existing owner didn’t change. * A Windows account that exists in Windows but has no SQL Server login was accepted. The check in `sp_verify_job` rejects a name only when it doesn’t resolve to a SID, or when it’s a Windows group login. When I started that job, it failed before any step ran: “The owner (SQLTEST01\DefaultAccount) of job sat_owner_nologin does not have server access.” I replaced the machine name in that message. * `DROP LOGIN` on a SQL login that owned a job raised Msg 15170: “This login is the owner of 1 job(s). You must delete or reassign these jobs before the login can be dropped.” In the second case, the deploy script succeeds, and the job fails the first time it runs. ## Shared Schedules A shared schedule is one row in `msdb.dbo.sysschedules` attached to more than one job. Changing it changes every job attached to it, and those jobs use the new settings immediately.[8] I changed `sat_nightly_0200` from 02:00 to 03:00 with `sp_update_schedule` on the 2019 instance. The drift query reported both jobs as changed, although only the schedule was edited. Running the deploy script for `sat_deploy_demo` alone set it back for both jobs. That’s useful when the jobs should move together. It also means two deploy scripts that define the same shared schedule differently can change it back and forth, one deploy at a time. Keep each shared schedule’s definition in one place. ## Finding Drift Between Instances The query below builds a JSON document from the parts of each job that should match on every instance, and hashes it with SHA-256. It leaves out IDs, dates, `version_number` and the schedule’s `active_start_date`, because those differ by instance. For the same reason it uses the proxy’s name from `msdb.dbo.sysproxies` rather than the step’s `proxy_id`. Transact-SQL USE [msdb]; SET NOCOUNT ON; SELECT [j].[name] AS [job_name] , CONVERT(char(64), HASHBYTES('SHA2_256', [d].[definition]), 2) AS [definition_sha256] , [d].[definition] FROM [dbo].[sysjobs] AS [j] CROSS APPLY ( SELECT [definition] = ( SELECT [j].[name] , [j].[enabled] , [j].[description] , [c].[name] AS [category] , SUSER_SNAME([j].[owner_sid]) AS [owner] , [j].[start_step_id] , [j].[notify_level_eventlog] , [j].[notify_level_email] , [j].[delete_level] , [steps] = ( SELECT [js].[step_id] , [js].[step_name] , [js].[subsystem] , [js].[database_name] , [js].[database_user_name] , [js].[command] , [js].[on_success_action] , [js].[on_success_step_id] , [js].[on_fail_action] , [js].[on_fail_step_id] , [js].[retry_attempts] , [js].[retry_interval] , [p].[name] AS [proxy_name] FROM [dbo].[sysjobsteps] AS [js] LEFT OUTER JOIN [dbo].[sysproxies] AS [p] ON [p].[proxy_id] = [js].[proxy_id] WHERE [js].[job_id] = [j].[job_id] ORDER BY [js].[step_id] FOR JSON PATH ) , [schedules] = ( SELECT [ss].[name] , [ss].[enabled] , [ss].[freq_type] , [ss].[freq_interval] , [ss].[freq_subday_type] , [ss].[freq_subday_interval] , [ss].[freq_relative_interval] , [ss].[freq_recurrence_factor] , [ss].[active_start_time] , [ss].[active_end_time] , [ss].[active_end_date] FROM [dbo].[sysjobschedules] AS [jsc] INNER JOIN [dbo].[sysschedules] AS [ss] ON [ss].[schedule_id] = [jsc].[schedule_id] WHERE [jsc].[job_id] = [j].[job_id] ORDER BY [ss].[name] FOR JSON PATH ) , [targets] = ( SELECT COUNT_BIG(1) FROM [dbo].[sysjobservers] AS [jsv] WHERE [jsv].[job_id] = [j].[job_id] ) FROM [dbo].[syscategories] AS [c] WHERE [c].[category_id] = [j].[category_id] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER ) ) AS [d] INNER JOIN [dbo].[syscategories] AS [cat] ON [cat].[category_id] = [j].[category_id] WHERE [cat].[name] = N'sat_Deployed' ORDER BY [j].[name]; 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100 | USE [msdb]; SET NOCOUNT ON; SELECT [j].[name] AS [job_name] , CONVERT(char(64), HASHBYTES('SHA2_256', [d].[definition]), 2) AS [definition_sha256] , [d].[definition]FROM [dbo].[sysjobs] AS [j]CROSS APPLY( SELECT [definition] = ( SELECT [j].[name] , [j].[enabled] , [j].[description] , [c].[name] AS [category] , SUSER_SNAME([j].[owner_sid]) AS [owner] , [j].[start_step_id] , [j].[notify_level_eventlog] , [j].[notify_level_email] , [j].[delete_level] , [steps] = ( SELECT [js].[step_id] , [js].[step_name] , [js].[subsystem] , [js].[database_name] , [js].[database_user_name] , [js].[command] , [js].[on_success_action] , [js].[on_success_step_id] , [js].[on_fail_action] , [js].[on_fail_step_id] , [js].[retry_attempts] , [js].[retry_interval] , [p].[name] AS [proxy_name] FROM [dbo].[sysjobsteps] AS [js] LEFT OUTER JOIN [dbo].[sysproxies] AS [p] ON [p].[proxy_id] = [js].[proxy_id] WHERE [js].[job_id] = [j].[job_id] ORDER BY [js].[step_id] FOR JSON PATH ) , [schedules] = ( SELECT [ss].[name] , [ss].[enabled] , [ss].[freq_type] , [ss].[freq_interval] , [ss].[freq_subday_type] , [ss].[freq_subday_interval] , [ss].[freq_relative_interval] , [ss].[freq_recurrence_factor] , [ss].[active_start_time] , [ss].[active_end_time] , [ss].[active_end_date] FROM [dbo].[sysjobschedules] AS [jsc] INNER JOIN [dbo].[sysschedules] AS [ss] ON [ss].[schedule_id] = [jsc].[schedule_id] WHERE [jsc].[job_id] = [j].[job_id] ORDER BY [ss].[name] FOR JSON PATH ) , [targets] = ( SELECT COUNT_BIG(1) FROM [dbo].[sysjobservers] AS [jsv] WHERE [jsv].[job_id] = [j].[job_id] ) FROM [dbo].[syscategories] AS [c] WHERE [c].[category_id] = [j].[category_id] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )) AS [d]INNER JOIN [dbo].[syscategories] AS [cat] ON [cat].[category_id] = [j].[category_id]WHERE [cat].[name] = N'sat_Deployed'ORDER BY [j].[name]; ---|--- A short PowerShell script runs the query on each instance and groups the results by job name: PowerShell param( [string[]] $Instances = @('SQLPROD01', 'SQLPROD02'), [string] $QueryPath = (Join-Path $PSScriptRoot 'drift-check.sql') ) $query = Get-Content -Path $QueryPath -Raw $rows = foreach ($instance in $Instances) { $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $builder['Data Source'] = $instance $builder['Integrated Security'] = $true $builder['Encrypt'] = $true # Test instances only: skipping certificate validation allows a man-in-the-middle attack. $builder['TrustServerCertificate'] = $true $conn = New-Object System.Data.SqlClient.SqlConnection $builder.ConnectionString $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = $query $reader = $cmd.ExecuteReader() while ($reader.Read()) { [pscustomobject]@{ Instance = $instance JobName = $reader['job_name'] Hash = $reader['definition_sha256'] Definition = $reader['definition'] } } $conn.Close() } $rows | Group-Object JobName | ForEach-Object { $hashes = @($_.Group.Hash | Sort-Object -Unique) [pscustomobject]@{ JobName = $_.Name Instances = $_.Count Hashes = $hashes.Count Status = if ($_.Count -lt $Instances.Count) { 'MISSING' } elseif ($hashes.Count -gt 1) { 'DRIFT' } else { 'OK' } } } | Format-Table -AutoSize $rows | Select-Object Instance, JobName, Hash | Format-Table -AutoSize 12345678910111213141516171819202122232425262728293031323334353637383940 | param( [string[]] $Instances = @('SQLPROD01', 'SQLPROD02'), [string] $QueryPath = (Join-Path $PSScriptRoot 'drift-check.sql')) $query = Get-Content -Path $QueryPath -Raw$rows = foreach ($instance in $Instances) { $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $builder['Data Source'] = $instance $builder['Integrated Security'] = $true $builder['Encrypt'] = $true # Test instances only: skipping certificate validation allows a man-in-the-middle attack. $builder['TrustServerCertificate'] = $true $conn = New-Object System.Data.SqlClient.SqlConnection $builder.ConnectionString $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = $query $reader = $cmd.ExecuteReader() while ($reader.Read()) { [pscustomobject]@{ Instance = $instance JobName = $reader['job_name'] Hash = $reader['definition_sha256'] Definition = $reader['definition'] } } $conn.Close()} $rows | Group-Object JobName | ForEach-Object { $hashes = @($_.Group.Hash | Sort-Object -Unique) [pscustomobject]@{ JobName = $_.Name Instances = $_.Count Hashes = $hashes.Count Status = if ($_.Count -lt $Instances.Count) { 'MISSING' } elseif ($hashes.Count -gt 1) { 'DRIFT' } else { 'OK' } }} | Format-Table -AutoSize $rows | Select-Object Instance, JobName, Hash | Format-Table -AutoSize ---|--- The script builds each connection string with `SqlConnectionStringBuilder`, so an instance name can’t add keywords of its own, and it encrypts the connection. `TrustServerCertificate=True` keeps the encryption but tells the driver not to validate the server’s certificate. Microsoft marks that setting as not recommended, and it’s the gap that strict encryption closes to prevent a man-in-the-middle attack.[9] I set it only because I ran this against local test instances. On a real network, give each server a certificate from a certificate authority your clients trust, and leave `TrustServerCertificate` off. After both deploy runs, every job had the same hash on both instances. I then changed step 1 of `sat_deploy_demo` by hand on the 2025 instance with `sp_update_jobstep`: JobName Instances Hashes Status ------- --------- ------ ------ sat_deploy_demo 2 2 DRIFT sat_deploy_demo_b 2 1 OK 1234 | JobName Instances Hashes Status------- --------- ------ ------sat_deploy_demo 2 2 DRIFTsat_deploy_demo_b 2 1 OK ---|--- After one more run of the deploy script on that instance, both jobs reported `OK` again. The `definition` column holds the JSON, so you can compare the two documents to see what differs. Find-and-Replace for SQL Server Agent Jobs changes many jobs on one instance. If you use it, run it on every instance or run the drift check afterward. ## Test Limits * Two instances on one workstation, T-SQL steps only. No proxies, so the proxy name in the drift query was always NULL. No other subsystems, operators or alerts, and the drift query doesn’t include notification operators. * The “script as” output came from SMO 17 in PowerShell, not the SSMS dialog. * No multiserver (MSX/TSX) administration, where `@server_name` names a target server. * Jobs ran on the 2019 instance only, because Agent was stopped on the 2025 instance. For an older approach to scripting every job on an instance, see Tool for scripting SQL Server Agent Jobs. Related: Reading an Inherited SQL Server Agent Job: Step Flow, Retries and History _(available 2026-11-10)_. If you deploy Agent jobs another way, or you’ve found drift your checks didn’t catch, I’d like to hear about it. Find me on Bluesky or LinkedIn. ## References 1. Overview (SMO) - Microsoft Learn. Describes SMO’s capture of Transact-SQL statements and states that Management Studio uses this capability to script an operation instead of running it. ↩ 2. sp_delete_job (Transact-SQL) - Microsoft Learn. States that @delete_history defaults to 1, which deletes the job’s history. ↩ 3. sp_add_schedule (Transact-SQL) - Microsoft Learn. Lists @schedule_uid as an OUTPUT parameter described as a unique identifier for the schedule, with no mention of matching an existing schedule on input. ↩ 4. sp_add_jobschedule (Transact-SQL) - Microsoft Learn. Describes @schedule_uid as a unique identifier for the schedule, and recommends sp_add_schedule with sp_attach_schedule to add a schedule to a job. ↩ 5. sp_attach_schedule (Transact-SQL) - Microsoft Learn. States that the schedule and the job must have the same owner, and that a schedule can be set for more than one job. ↩ 6. sp_add_jobserver (Transact-SQL) - Microsoft Learn. States that @server_name can be (LOCAL) for the local server or the name of an existing target server. ↩ 7. sp_add_job (Transact-SQL) - Microsoft Learn. States that only sysadmin members can set @owner_login_name, and that SQL Server Agent runs Transact-SQL job steps in the job owner’s security context. ↩ 8. sp_update_schedule (Transact-SQL) - Microsoft Learn. States that all jobs that use the schedule immediately use the new settings. ↩ 9. TDS 8.0 - Microsoft Learn. Describes TrustServerCertificate=true as not recommended because the driver then doesn’t validate the server TLS/SSL certificate, and states that strict encryption doesn’t allow TrustServerCertificate=true, in order to prevent a man-in-the-middle attack. ↩ ### 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_
www.sqlserverscience.com
November 7, 2026 at 2:19 PM
How pity its #SSMS 21 is a mess (a half baked product) with lots of "preview" features.
Dear #Microslop, SSMS is a mission critical software, please do not fucking vibecoding that shit!
On ##SSMS 22 even Brent Ozar gives you HEs very negative feedback. And thats says a lot...
November 4, 2026 at 9:16 AM
Twenty years living inside SSMS. Query windows, execution plans, the whole cozy routine. Now I'm writing Python scripts that run through MCP connections. Same database brain, brand new tools. Turns out an old SQL guy can still learn a few tricks.
November 3, 2026 at 3:28 PM
Selecting a query in SSMS before you press F5 is muscle memory. And when you miss a line, the wrong thing runs.

In RenSQL: put the cursor anywhere in the statement, press Ctrl+Shift+E. It finds the statement and runs just that one.

#SSMS #SQLServer
November 4, 2026 at 6:57 AM
Visual Studio Copilot and SSMS Remote Code Execution (CVE

This article focuses heavily on a specific file path artifact as the root cause, but it downplays the systemic failure in input validation that allowed such arbitrary file manipulation. If the core issue is fundamentall…

via Embrace The Red
huntaegis.com
November 4, 2026 at 1:34 PM
THREADPOOL Waits: Reproducing Worker Thread Exhaustion in SQL Server

Reproducing THREADPOOL waits on SQL Server 2019: how max worker threads is sized, what new logins see, and finding the blocker through the DAC.

https://www.sqlserverscience.com/troubleshooting/threadpool-waits/
THREADPOOL Waits: Reproducing Worker Thread Exhaustion in SQL Server
Picture an instance that is running, where CPU might be low, but new connections time out and SSMS might not be able to open a query window. Restarting the service makes the problem go away, and it also resets the wait statistics and other DMVs that would have shown the cause. One cause that fits this pattern is worker thread exhaustion, which shows up in the wait statistics as THREADPOOL. I reproduced it on a test instance to see what each symptom looks like from inside SQL Server, and what a new connection sees from outside. ## Workers and the THREADPOOL Wait SQL Server runs each unit of work, called a task, on a worker thread. A worker is SQL Server’s representation of an operating system thread. A serial query has one task and uses one worker. A parallel query has a parent task and several child tasks, and each task uses its own worker.[1] A task can also be a login or a logout. When a task is ready to run and no worker is available, the task waits with the wait type THREADPOOL.[2] Microsoft’s description of THREADPOOL says the worker setting can be misconfigured. It says the more common cause is batches that run for an unusually long time and keep their workers, and it lists blocking and parallelism among the things to reduce.[2] ## How Many Workers You Get The `max worker threads` option defaults to 0, which lets SQL Server size the pool at startup. On 64-bit SQL Server 2016 SP2 and later, with more than 4 and up to 64 logical CPUs, the formula is 512 + ((logical CPUs - 4) * 16). Above 64 CPUs, the multiplier is 32 instead of 16.[3] Microsoft’s table gives these defaults for 64-bit SQL Server 2016 SP2, SQL Server 2017 and later: Logical CPUs | Default max workers ---|--- 4 or fewer | 512 8 | 576 16 | 704 32 | 960 64 | 1,472 128 | 4,480 My two test instances are SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer), on one workstation with 14 logical CPUs. The formula gives 512 + (10 * 16) = 672, and both instances report 672: Transact-SQL USE [master]; SELECT [si].[cpu_count] , [si].[scheduler_count] , [si].[max_workers_count] FROM [sys].[dm_os_sys_info] AS [si]; 12345678 | USE [master]; SELECT [si].[cpu_count] , [si].[scheduler_count] , [si].[max_workers_count]FROM [sys].[dm_os_sys_info] AS [si]; ---|--- The limit doesn’t cover every thread. System threads such as the lazy writer, checkpoint and log writer are created outside it.[3] ## Reproducing It I ran the test on SQL Server 2019 only. On both versions, `sys.configurations` shows `is_dynamic = 1` for `max worker threads`, so a new value takes effect at `RECONFIGURE` without a restart. I lowered it to 128, the lowest value allowed, so the test needed fewer connections.[3] The scratch database has one table with 1,000,000 rows. A blocker session opened a transaction, updated every 1,000th row, and left the transaction open: Transact-SQL USE [sat_threadpool]; BEGIN TRANSACTION; UPDATE [dbo].[orders] SET [customer_id] = [customer_id] WHERE [order_id] % 1000 = 0; 12345678910 | USE [sat_threadpool]; BEGIN TRANSACTION; UPDATE [dbo].[orders]SET [customer_id] = [customer_id]WHERE [order_id] % 1000 = 0; ---|--- Then a PowerShell script lowered the setting and opened new connections one at a time, with connection pooling off. Each connection started this query asynchronously, and the open transaction blocked it: Transact-SQL USE [sat_threadpool]; SELECT COUNT_BIG(1) FROM [dbo].[orders] WHERE [pad] <> 'y' OPTION (MAXDOP 8); 123456789 | USE [sat_threadpool]; SELECT COUNT_BIG(1)FROM [dbo].[orders]WHERE [pad] <> 'y'OPTION (MAXDOP 8); ---|--- After each new connection, the script counted THREADPOOL waiters through the dedicated administrator connection (DAC), described below. I ran the test once with `MAXDOP 1` and twice with `MAXDOP 8`. Each time, the setting stayed at 128 for less than 50 seconds before I set it back to 0. The `RECONFIGURE` also flushed the Object Plans, SQL Plans and Bound Trees stores of the plan cache, according to the error log. The error log reports these flushes in batches. The next set of flush messages, at 13:15:26, reported 6 occurrences for each store. That count matches my five later `RECONFIGURE` statements for `max worker threads`, including the ones that set it back to 0, plus one change to another setting at 13:15:26. So the `RECONFIGURE` that sets the value back flushes the plan cache too. ## What Happened With `MAXDOP 1`, each blocked query held one worker. The first THREADPOOL waiter appeared after the 125th blocked session, while the 126th was connecting. Ten sessions later, a new connection failed. It had a 5-second connect timeout, and it gave up after about 10 seconds with this error: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=10013; handshake=0; 1 | Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=10013; handshake=0; ---|--- The error doesn’t mention workers, and it reads like a network problem. A `sqlcmd` login with a 15-second login timeout hadn’t connected after 15 seconds. With `MAXDOP 8`, the first 12 queries ran at DOP 8 and each took 9 tasks, which is 8 child tasks plus the parent. That’s 108 workers for 12 queries. The 13th query ran at DOP 6, and every query after it ran at DOP 1. Microsoft says a lack of workers can reduce the DOP a query gets at runtime.[1] Run | MAXDOP | Session opening when THREADPOOL first seen | Seconds | New connections ---|---|---|---|--- 1 | 1 | 126 | 19.0 | Session 136 timed out after 10 s. `sqlcmd` hadn’t connected after 15 s. 2 | 8 | 52 | 6.6 | Session 53 failed after 13.5 s. `sqlcmd` connected after 5.1 s. 3 | 8 | 56 | 9.3 | All 60 sessions opened. `sqlcmd` connected in 143 ms. The seconds column counts from the first blocked session. The script opened six to eight sessions per second, so that column mostly reflects the script’s speed. In run 2 the failed connection reported `A network-related or instance-specific error occurred while establishing a connection to SQL Server`, with `The wait operation timed out`. Both failed connections took longer than the 5-second connect timeout I set. I observed that, and I can’t explain it. In run 3, five or six tasks were waiting on THREADPOOL and a new login still got in. In my runs, with only a few tasks in the queue, a new connection may get through or may time out. The worker counts in `sys.dm_os_schedulers` didn’t stop at 128. When the first THREADPOOL waiter showed up in run 1, active workers were at 165 and current workers were at 178. Current workers were already 139 before I opened a single session, because that count includes idle workers.[4] I don’t know how SQL Server counts workers against the limit, so I wouldn’t expect `current_workers_count` to line up with `max_workers_count`. ## Looking in Through the DAC The DAC is a connection reserved for members of sysadmin, for when normal connections can’t get in.[5] It runs on its own hidden scheduler, which has workers set aside at startup.[4] Connect with `sqlcmd -S SQLPROD01 -A -E -N -C`, or put `admin:` in front of the server name. `-N` asks for an encrypted connection, and `-C` trusts the server certificate without validating it.[6] That skipped validation allows a man-in-the-middle attack,[7] so drop `-C` where the server has a certificate your clients trust. By default the DAC only accepts connections from the server itself. Setting `remote admin connections` to 1 allows it from another computer.[8] Only one DAC can be open at a time, and a second attempt gets error 17810. The DAC can’t run parallel queries. Microsoft recommends snapshot-based isolation if possible, and otherwise `READ UNCOMMITTED` with a short `LOCK_TIMEOUT`, such as 2000 ms, so the DAC session doesn’t get blocked itself.[5] During run 3, I ran a diagnostic script through the DAC, and it returned in 163 ms. This is a trimmed version of the script I ran: Transact-SQL USE [master]; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET LOCK_TIMEOUT 2000; /* Tasks waiting for a worker. */ SELECT COUNT_BIG(1) AS [threadpool_waiters] , MAX([wt].[wait_duration_ms]) AS [longest_wait_ms] FROM [sys].[dm_os_waiting_tasks] AS [wt] WHERE [wt].[wait_type] = N'THREADPOOL'; /* Worker totals across the visible schedulers. */ SELECT SUM([s].[current_workers_count]) AS [current_workers] , SUM([s].[active_workers_count]) AS [active_workers] , SUM([s].[work_queue_count]) AS [work_queue] FROM [sys].[dm_os_schedulers] AS [s] WHERE [s].[status] = N'VISIBLE ONLINE'; /* Blocking per task, which includes the child tasks of parallel queries. */ SELECT TOP (5) [wt].[blocking_session_id] , COUNT_BIG(1) AS [blocked_tasks] , COUNT_BIG(DISTINCT [wt].[session_id]) AS [blocked_sessions] FROM [sys].[dm_os_waiting_tasks] AS [wt] WHERE [wt].[blocking_session_id] IS NOT NULL AND [wt].[blocking_session_id] <> [wt].[session_id] GROUP BY [wt].[blocking_session_id] ORDER BY COUNT_BIG(1) DESC; /* Head blockers: they block another session's task, and none of their own tasks is blocked. */ SELECT [s].[session_id] , [s].[status] , [s].[program_name] , [s].[open_transaction_count] , DATEDIFF(SECOND, [s].[last_request_end_time], GETDATE()) AS [idle_seconds] FROM [sys].[dm_exec_sessions] AS [s] WHERE EXISTS ( SELECT 1 FROM [sys].[dm_os_waiting_tasks] AS [wt] WHERE [wt].[blocking_session_id] = [s].[session_id] AND [wt].[session_id] <> [s].[session_id] ) AND NOT EXISTS ( SELECT 1 FROM [sys].[dm_os_waiting_tasks] AS [wt2] WHERE [wt2].[session_id] = [s].[session_id] AND [wt2].[blocking_session_id] IS NOT NULL AND [wt2].[blocking_session_id] <> [s].[session_id] ); 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970 | USE [master]; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SET LOCK_TIMEOUT 2000; /* Tasks waiting for a worker. */SELECT COUNT_BIG(1) AS [threadpool_waiters] , MAX([wt].[wait_duration_ms]) AS [longest_wait_ms]FROM [sys].[dm_os_waiting_tasks] AS [wt]WHERE [wt].[wait_type] = N'THREADPOOL'; /* Worker totals across the visible schedulers. */SELECT SUM([s].[current_workers_count]) AS [current_workers] , SUM([s].[active_workers_count]) AS [active_workers] , SUM([s].[work_queue_count]) AS [work_queue]FROM [sys].[dm_os_schedulers] AS [s]WHERE [s].[status] = N'VISIBLE ONLINE'; /* Blocking per task, which includes the child tasks of parallel queries. */SELECT TOP (5) [wt].[blocking_session_id] , COUNT_BIG(1) AS [blocked_tasks] , COUNT_BIG(DISTINCT [wt].[session_id]) AS [blocked_sessions]FROM [sys].[dm_os_waiting_tasks] AS [wt]WHERE [wt].[blocking_session_id] IS NOT NULL AND [wt].[blocking_session_id] <> [wt].[session_id]GROUP BY [wt].[blocking_session_id]ORDER BY COUNT_BIG(1) DESC; /* Head blockers: they block another session's task, and none of their own tasks is blocked. */SELECT [s].[session_id] , [s].[status] , [s].[program_name] , [s].[open_transaction_count] , DATEDIFF(SECOND, [s].[last_request_end_time], GETDATE()) AS [idle_seconds]FROM [sys].[dm_exec_sessions] AS [s]WHERE EXISTS ( SELECT 1 FROM [sys].[dm_os_waiting_tasks] AS [wt] WHERE [wt].[blocking_session_id] = [s].[session_id] AND [wt].[session_id] <> [s].[session_id] ) AND NOT EXISTS ( SELECT 1 FROM [sys].[dm_os_waiting_tasks] AS [wt2] WHERE [wt2].[session_id] = [s].[session_id] AND [wt2].[blocking_session_id] IS NOT NULL AND [wt2].[blocking_session_id] <> [s].[session_id] ); ---|--- threadpool_waiters longest_wait_ms ------------------ --------------- 6 3313 current_workers active_workers work_queue --------------- -------------- ---------- 209 201 6 blocking_session_id blocked_tasks blocked_sessions ------------------- ------------- ---------------- 67 141 59 54 8 1 session_id status program_name open_transaction_count idle_seconds ---------- -------- -------------- ---------------------- ------------ 54 sleeping sat_tp_blocker 1 12 12345678910111213141516 | threadpool_waiters longest_wait_ms------------------ ---------------6 3313 current_workers active_workers work_queue--------------- -------------- ----------209 201 6 blocking_session_id blocked_tasks blocked_sessions------------------- ------------- ----------------67 141 5954 8 1 session_id status program_name open_transaction_count idle_seconds---------- -------- -------------- ---------------------- ------------54 sleeping sat_tp_blocker 1 12 ---|--- `work_queue_count` is the number of tasks waiting for a worker to pick them up, so it matched the THREADPOOL count here.[4] I first wrote the blocking part against `sys.dm_exec_requests`, and it pointed at the wrong session. In run 3 it reported session 67 blocking 47 requests. Session 67 was the first parallel query, itself blocked. Parallelism didn’t cause that. In serial run 1, the same DMV reported session 71, the first blocked query, blocking 134 requests, and the real blocker, session 68, blocking 1. The later lock waiters appear to report an earlier queued waiter as their blocker, instead of the session that holds the lock. That’s my inference, and I haven’t tested it. Parallelism does hide the real blocker from that DMV. The 13 parallel requests all showed `CXPACKET` with a `blocking_session_id` of 0. For a parallel request, `sys.dm_exec_requests` shows the wait of the coordinating thread only, and Microsoft points to `sys.dm_os_waiting_tasks` for the waits of each task.[9] The only tasks session 54 blocked were the child tasks of session 67, so session 54 never showed up as a blocker there. It wasn’t in `sys.dm_exec_requests` as a request either, because it was sleeping with no request. At the task level, session 54 blocks 8 tasks, and the head-blocker query finds it: sleeping, one open transaction, idle for 12 seconds. Blocked process reports can capture the same chain if the blocked process threshold is set. I covered them in Blocked Process Reports: From Capture to Root Cause. ## Raising max worker threads Cleared THREADPOOL, Not the Blocking The `max worker threads` page says a performance problem is probably not about how many workers there are. It says the more likely cause is activity that occupies workers and doesn’t release them, such as long-running queries or blocking, and recommends finding the root cause before changing the setting.[3] The same page also suggests connecting with the DAC and killing the process when the instance stops responding, and raising the setting to prevent it.[3] Run 3 shows the limit of that second suggestion. I set `max worker threads` back to 0, which made the limit 672, while the blocker still held its transaction. Over the next 9 seconds the current worker count went from 209 to 274, and the THREADPOOL waiters dropped to 0. Those waiters weren’t my test queries. Just before the restore, the 60 queries had 162 tasks: 149 waiting on `LCK_M_S` and 13 waiting on `CXPACKET`. Every query task already had a worker. Run 1 looked the same: 135 sessions, 135 tasks waiting on `LCK_M_S`, and 12 more tasks waiting on THREADPOOL. I sampled `LCK_M_S` once after the restore, 3 seconds later, and all 149 tasks were still waiting. Whatever was waiting on THREADPOOL got a worker within about 9 seconds, but none of my 60 queries could finish until the blocker rolled back. Rolling back the blocker’s transaction released all 60 queries, and they finished within 3 seconds. In my test, a higher limit just let more sessions queue behind the same blocker, and each one keeps its worker while it waits. A parallel query can keep one worker per task. The fix in my test was to end the head blocker’s transaction. On a production system, I’d also find out why that session left a transaction open. ## What to Watch `sys.dm_os_wait_stats` is cumulative since the last restart or manual reset.[2] Before any test, the 2019 instance had 145 THREADPOOL waits totalling 63 ms, and the 2025 instance had 2,370 totalling 1,007 ms. Neither instance was showing symptoms at the time, so those counts didn’t indicate a problem. On the 2019 instance, `max_wait_time_ms` for THREADPOOL went from 5 ms before run 1 to 10,007 ms partway through it, and to 33,150 ms by the end of it. I’d look at wait time over an interval and the longest wait, not the count. Wait Stats, Deadlocks, and Blocking Chains: AI-Assisted Diagnosis covers capturing wait stats over an interval instead of reading the totals since startup. The tool in Performance Monitor: Free SQL Server Monitoring That Doesn’t Phone Home monitors THREADPOOL directly. If you plan to use the DAC from another computer, Vulnerability Assessment recommends disabling Remote Admin Connections covers the security side. ## Limits of This Test * I tested on one workstation with 14 logical CPUs, and I reproduced the problem on SQL Server 2019 only. On SQL Server 2025 I only read the worker settings. * I lowered `max worker threads` to 128. At the default of 672, it would take more blocked sessions. I didn’t test that. * The blocking was synthetic: one open transaction holding 1,000 row locks. * I didn’t test a DAC connection from another computer. Related: Finding the Queries That Use the Most CPU, and What Each Source Misses _(available 2026-11-22)_ , Spinlock Contention for DBAs: Measure a Delta, Not a Total _(available 2026-11-23)_ and Connection Pool Exhaustion: Reproducing the Timeout and What SQL Server Sees _(available 2026-11-17)_. If you’ve found THREADPOOL waits behind something other than blocking, I’d like to hear what it was. Find me on Bluesky or LinkedIn. ## References 1. Thread and task architecture guide - Microsoft Learn. Defines tasks and worker threads, describes the parent and child tasks of a parallel request, and states that a lack of workers can reduce the DOP available at runtime. ↩ 2. sys.dm_os_wait_stats (Transact-SQL) - Microsoft Learn. Describes THREADPOOL as a task (query or login/logout) waiting for a worker thread, names long-running batches as the most common cause, lists blocking and parallelism among the bottlenecks to reduce, and states that the counters are cumulative since the last reset or restart. ↩ 3. Server Configuration: max worker threads - Microsoft Learn. The default of 0, the sizing formula and table, the 128 to 65,535 range, system threads outside the limit, and the recommendation to find the root cause before changing the setting. ↩ 4. sys.dm_os_schedulers (Transact-SQL) - Microsoft Learn. Defines current_workers_count (which includes workers not assigned a task), active_workers_count and work_queue_count, and describes the hidden DAC scheduler and the workers allocated to it at startup. ↩ 5. Diagnostic Connection for Database Administrators - Microsoft Learn. How to connect with sqlcmd -A or admin:, local-only by default, one DAC per instance (error 17810), no parallel queries, and the advice to use snapshot-based isolation if possible, otherwise READ UNCOMMITTED with a short LOCK_TIMEOUT. ↩ 6. sqlcmd utility - Microsoft Learn. -N requests an encrypted connection (o for optional, m for mandatory, s for strict), and -C trusts the server certificate without validation, equivalent to the ADO.NET option TRUSTSERVERCERTIFICATE = true. ↩ 7. TDS 8.0 - Microsoft Learn. Describes TrustServerCertificate=true as not recommended because the driver then doesn’t validate the server TLS/SSL certificate, and states that strict encryption doesn’t allow TrustServerCertificate=true, in order to prevent a man-in-the-middle attack. ↩ 8. Server Configuration: remote admin connections - Microsoft Learn. The DAC accepts only local connections by default, and setting the option to 1 allows remote DAC connections. ↩ 9. sys.dm_exec_requests (Transact-SQL) - Microsoft Learn. For parallel requests, only the coordinator thread is visible and its wait columns are the ones updated, and sys.dm_os_waiting_tasks shows the wait of each task. ↩ ### 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_
www.sqlserverscience.com
November 6, 2026 at 2:33 PM