A customer’s SQL Server instance reached 100% CPU utilization. Blocking increased, response times deteriorated, and unrelated workloads slowed down. The cause was a Camunda worker query executed roughly 100 times per second in one database and 40-50 times per second in two others.
Each execution consumed about 120 ms of CPU. At 100 executions per second, the query demanded roughly 12 CPU-seconds every second more than the eight-core server could provide. The result was sustained CPU saturation and SOS_SCHEDULER_YIELD waits.
.png)
.png)
The query searched ACT_RU_EXT_TASK by TOPIC_NAME_ using LIKE @P1, although this operation required an exact match. SQL Server could locate a range in the index, but it still had to walk through candidate rows and evaluate the string predicate.
This is why the Index Seek shown in the plan was misleading at first glance. The operator was not performing a selective equality lookup; it was followed by a broader ordered traversal with a residual LIKE comparison.
The runtime numbers exposed the real cost: the operator read 200 rows, returned none, generated 799 logical reads, used 126 ms of CPU, and took 2.4 seconds to complete. The operator icon alone did not tell the full story.
.png)
.png)
Rows and logical reads are different measurements. Rows show how many index entries were examined; logical reads show how many 8 KB pages SQL Server accessed in memory. Here, only 200 rows were examined, but they were spread across enough leaf pages to require 799 logical reads.
The incident began as the queue table grew. Logical reads increased from a normal baseline of a few pages to hundreds per execution. We could not prove whether the initial trigger was a higher insert rate, slower task processing, or both, but the accumulated queue clearly made the query more expensive.
.png)
The affected index also showed 99% fragmentation and a larger page footprint than expected for the current row count. This amplified the I/O cost, but it was not the primary cause of CPU saturation.
We solved the incident in three steps:
After replacing LIKE with equality, CPU time dropped to about 1 ms even when SQL Server selected different physical operators. After the final index change, production executions used approximately 2-10 logical reads and less than 1 ms of CPU time.
DBA takeaway: Do not judge a query by the operator icon alone. Check rows read versus rows returned, logical reads, CPU time, execution frequency, and residual predicates. In this case, the rebuild reduced the immediate I/O cost, but the real fix was changing the predicate and supporting it with the right index.
Michal is a technically proficient SQL Server Specialist with a proven track record in resolving incidents and implementing changes within large-scale database infrastructures, ensuring maximum availability of services. Concurrently, as a Digital Content and Marketing Specialist, his priority is building strong online brand identities through strategic communication and creative storytelling. He consistently seeks new ways to enhance digital interaction, believing quality digital communication is key to success in today's connected world.
After some time, we have finally released big set of changes and fixes for currently known issues. Most visible is new Waiting Task report for operational analysis, added timeline button for Index Usage & recommendation, new functionality to search for an
Read moreNew version has been released and more features than we planned initially made it in.
Read moreA few weeks ago a had task to create procedure for testing workload from production server on test environment.Itsurely wasn't a big deal, but I need to learnmanycustomer employees to repeat this task on regular base. I started to look if somehow I could
Read more
