C# strings silently kill your SQL Server indexes in Dapper
5 hours ago
- #Dapper
- #Performance Optimization
- #SQL Server
- C# strings passed to Dapper queries can cause SQL Server to perform implicit conversions (CONVERT_IMPLICIT) from varchar to nvarchar, leading to full index scans instead of seeks.
- The default mapping of C# strings in Dapper is nvarchar(4000), which mismatches with varchar columns in SQL Server, degrading performance.
- Implicit conversions can significantly increase CPU usage and logical reads, especially in queries executed frequently.
- The fix involves explicitly specifying the parameter type as DbType.AnsiString for varchar columns using DynamicParameters or DbString.
- Matching parameter types to column types and sizes is crucial for optimal query performance and efficient use of cached query plans.
- Auditing Dapper queries for string parameters passed to varchar columns can uncover hidden performance issues.
- Adding comments to explain the use of DynamicParameters or DbString can prevent future refactors from reintroducing the performance problem.
- Tools like SQL Server Query Store and execution plans can help identify queries suffering from implicit conversions.