The Parallelism Paradox: Why SQL Server 2025 IQP is Triggering High-CPU Thread Starvation

The Automated Scheduler Trap

For a decade, Microsoft has been on a crusade to make SQL Server 'intelligent.' From the introduction of Intelligent Query Processing (IQP) in 2017 to the feedback loops in 2022 and now the AI-integrated features of SQL Server 2025, the promise is the same: the engine will handle the math so you don't have to.

However, in high-density production environments—specifically virtual machines running 64, 96, or 128 cores—we are seeing a dangerous trend. The very features designed to optimize performance are frequently the catalyst for total scheduler exhaustion. When SQL Server 2025’s IQP decides a query needs more parallelism based on historical telemetry, it often fails to account for the physical reality of the underlying hypervisor. The result is the Parallelism Paradox: the more 'intelligent' the engine becomes at scaling, the more likely it is to trigger thread starvation.

Why IQP is Aggressive on Modern Infrastructure

In SQL Server 2025, features like Memory Grant Feedback and DOP (Degree of Parallelism) Feedback have matured. The engine looks at a query, realizes it’s spilling to disk or running slowly, and adjusts the execution plan for the next run. On a 64-core box, if the MAXDOP is set to the default (0) or even the semi-conservative recommended '8', the IQP mechanism might still allow a single, massive hash join to span dozens of threads across multiple NUMA nodes.

This becomes a liability when you consider how SQLOS manages workers. Every parallel branch in an execution plan requires a worker thread. If IQP keeps 'learning' that a query benefits from higher resources, and you have multiple complex queries hitting the system simultaneously, you hit the THREADPOOL wait state faster than you can open a DMV. We discussed similar scaling issues in our look at sql-server-2025-adr-snapshot-sprawl-debt, where automated background processes consume the very resources they are trying to optimize.

The Failure of Default Max Worker Threads

SQL Server calculates Max Worker Threads automatically based on the number of logical processors. For a 64-bit system with more than 4 processors, the formula is roughly 512 + ((logical_processors - 4) * 16). On a 128-core machine, that gives you 2,504 threads.

That sounds like plenty. But it isn't.

When IQP triggers a DOP increase or a massive memory grant, it isn't just taking one thread; it’s taking a coordinator thread plus 'n' worker threads. If those threads get blocked—perhaps due to I/O latency or a latch on a system table—they don't go away. They sit in a SUSPENDED state. In high-concurrency environments, especially those using contained-availability-groups-sql-server, the overhead of managing these parallel tasks starts to exceed the actual work being done. This is the definition of thrashing.

Returning to Manual Governance

You cannot trust the 'Intelligent' defaults on large core-count VMs. If you are seeing SOS_SCHEDULER_YIELD or THREADPOOL waits, it is time to ignore the marketing and return to hard-coded governance.

1. Restrict MAXDOP at the Server Level: For 64+ core VMs, don't just follow the '8' rule. Test with '4'. It sounds counter-intuitive to limit a query to 4 cores on a 64-core machine, but reducing the surface area of a single query’s footprint prevents a 'rogue' IQP-adjusted plan from hijacking every scheduler on a single NUMA node.

2. Hard-code Max Worker Threads: Instead of letting SQL Server calculate the limit, cap it manually based on your observed peak concurrency. This forces SQL Server to queue tasks earlier, which is far better than a total system hang where even the DAC (Dedicated Administrative Connection) cannot log in.

3. Use Resource Governor: This is the most underutilized tool in the DBA arsenal. You should be using sql-server-resource-governor-fundamentals to place hard caps on CPU percentages for specific workload groups, preventing 'reporting' queries from starving the 'OLTP' workers.

The Reality of Virtualization Overhead

We often see customers who migrated from physical hardware to Azure or AWS and kept their legacy SQL Server settings. Virtual CPUs are not the same as physical cores. There is a 'ready time' tax at the hypervisor level. When SQL Server 2025 tries to fire off 64 threads for a single query, it’s asking the hypervisor to co-schedule 64 vCPUs simultaneously. If the host is over-provisioned, the query stalls not because of SQL Server, but because the hypervisor is waiting for physical cores to open up. The 'Intelligent' engine doesn't know this; it just sees a slow query and tries to throw more threads at it next time, worsening the cycle.

Final Takeaway

Automation is not a replacement for architecture. While SQL Server 2025 offers impressive gains in many areas, its default behavior on large VMs is far too aggressive. To maintain stability, you must move back to a posture of manual resource restriction. Stop letting the engine 'learn' its way into a thread starvation crisis.

If you're struggling with CPU spikes or scheduler pressure after an upgrade, book-consult with our team to audit your configuration. You can also run our sql-server-spblitz-report-generator to identify common misalignments between hardware and SQLOS settings.