Wednesday, 10. September
9:12:29 PM
DA Development Teletext
What happened?
The CPU load of a specific MSSQL server rose continuously to ~98% over several weeks, and we had no idea what had happened.After some investigation we've found out that there was a new varchar column in some table in which integers were stored (wasn't me :D).
That fact alone is not a big deal but under certain circumstances it can be one. This field was used for a WHERE condition. This table had more than 5M rows and a size of ~1.5GB.
The Reason?
Because of SQL Server’s type precedence (NVARCHAR > VARCHAR), SQL Server applied an implicit conversion on the column (CONVERT_IMPLICIT on the field) to perform the comparison.Although an index existed, that conversion made the predicate non-SARGable, so the optimizer couldn’t use the index seek on the field and instead performed index/table scans over ~5M rows ON EVERY QUERY regardless of what is being searched for. All lines needed to be scanned / converted.
One amplifying factor was that comparing text is more CPU-intensive than comparing integers, and this column stored numeric values as text.
© 2025 David Angenendt