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.
Recently, we have got a case where our customer requested to implement archiving strategy for their DWH. We wanted to share with you how we approached this and what was the final output.
Read moreSometimes when you plan to change your index design is good to know the columns of your tables. But not just a data type and max size.
Read moreRecently I had an issue with this error during upgrade of SSISDB AlwaysOn Support using wizard.
Read more
