SQL Server › Dynamic SQL

What are the performance tradeoffs of using dynamic SQL compared to static, precompiled SQL statements?

Advanced

Answer

Dynamic SQL can sometimes generate a large number of slightly different query strings, each requiring its own separate execution plan to be compiled and cached, which can lead to plan cache bloat and reduced plan reuse, whereas well parameterized static SQL or properly parameterized dynamic SQL through sp_executesql allows SQL Server to reuse a single cached plan much more effectively.

-- Poorly parameterized dynamic SQL creates many unique plans
EXEC ('SELECT * FROM Orders WHERE CustomerId = ' + CAST(@Id AS VARCHAR));

-- Properly parameterized version reuses a single plan
EXEC sp_executesql N'SELECT * FROM Orders WHERE CustomerId = @Id', N'@Id INT', @Id;

Real-world example A team notices their server's plan cache filling up with thousands of nearly identical dynamic SQL statements, and after switching to properly parameterized sp_executesql calls, the plan cache size and overall performance improve significantly.

Common follow-ups: How do you check the plan cache for signs of excessive unparameterized dynamic SQL?;What tools help identify queries that are not reusing execution plans effectively?

Query Optimization & Plans;SQL Server Profiler & Extended Events