Our API started slowing down after a recent data migration. Profiling shows most time is spent inside Dapper queries, and I suspect parameter sniffing on the SQL Server side. We're using SqlParameter objects with Dapper, but I don't know if there's a Dapper-specific setting to mitigate this.
Has anyone run into this? I know SQL Server caches execution plans based on the first parameter values, which can backfire when the distribution is skewed. Are there query hints I should be adding, or is this purely a database-level concern that I should tune with OPTION (RECOMPILE)?
Also, does Dapper's default behavior of creating a new DbParameter for every call interact badly with connection pooling? I want to make sure I'm not accidentally defeating the pool by instantiating parameters in a tight loop.
Looking for real-world tuning tips, not just textbook explanations.
Dapper usually isn’t the cause of parameter sniffing. It sends parameterized SQL to the database, and SQL Server may reuse a cached execution plan that was compiled for an earlier set of parameter values. That plan can be fast for one customer or date range and unexpectedly slow for another.
Start by checking the database, not by changing Dapper settings. Compare the slow and fast parameter values in an actual execution plan, and look for big differences between estimated and actual row counts, scans where seeks were expected, or spills. Query Store plan history can help confirm whether a plan change lines up with the slowdown.
Choose the fix based on the query
OPTION (RECOMPILE)can let SQL Server compile a plan for each execution. The tradeoff is extra compilation work, so avoid applying it indiscriminately.OPTIMIZE FOR UNKNOWNwhen a more average plan is acceptable. It may prevent a plan tailored to an unusually common or rare value, but it can also make typical executions slower.For example, a targeted recompile can be added to a Dapper query like this:
Use that only after confirming the plan varies badly by customer and the compile cost is reasonable. Dapper’s parameterization is generally useful for plan reuse; disabling it or generating a different SQL string for every value can create more problems than it solves. If you’re on SQL Server 2022 or later, also check whether Parameter Sensitive Plan optimization is available and enabled for the database and query.
In an ASP.NET Core app, capture the exact SQL, parameter values, and timings for slow calls, then reproduce the query in SQL Server with representative values. That makes it much easier to distinguish parameter sniffing from application-side delays such as connection-pool waits or excessive result materialization.