I recently spent time digging into a production performance issue. The application was running hot — CPU averaging over 50% and spiking into the 90s. We pulled a diagnostic snapshot and started working through the top queries by CPU time.
The number one offender? A straightforward Dapper query. Simple WHERE clause on an indexed column. Should have been lightning fast. Instead, it was averaging thousands of milliseconds of CPU per execution across hundreds of thousands of executions per day.
A two-character type mismatch that was completely invisible in the C# code. I stared at the query for way too long before I figured out what was happening.
So What’s Actually Happening?
Here’s a pattern you’ll find in almost every .NET project that uses Dapper:
const string sql = " SELECT * FROM Products WHERE ProductCode = @productCode " ; var result = await connection . QueryFirstOrDefaultAsync < Product >( sql , new { productCode });
Clean. Simple. And if ProductCode is a varchar column in your database, it’s silently destroying your query performance.
When you pass a C# string through an anonymous object, Dapper maps it to nvarchar(4000) . That’s the default mapping for System.String in ADO.NET — and honestly, it makes sense from a “safe default” perspective. But if your column is varchar , SQL Server has to convert every single value in the column to nvarchar before it can compare. This is called CONVERT_IMPLICIT, and it means SQL Server can’t use your index. Full scan. Every time.
You can see it hiding in your execution plans:
CONVERT_IMPLICIT(nvarchar(255), [Sales].[ProductCode], 0)
... continue reading