When SQL Server Performance starts to degrade, the symptoms often appear without an obvious cause. CPU usage climbs, the plan cache grows crowded with similar entries, and queries that once ran quickly begin to slow down. Examining the query text in isolation rarely reveals the issue, because the problem lies not in the logic itself but in how the statements reach the database engine.
Developers frequently send queries that embed literal values directly into the text rather than using parameters. This practice creates unique query strings for every combination of inputs, forcing SQL Server to compile fresh execution plans repeatedly. Over time the pattern becomes invisible in day-to-day development yet produces measurable drag on system resources.
Non-parameterized queries represent a classic code smell that blends performance overhead with security exposure. Because the structure and data remain intertwined, both plan reuse and protection against injection suffer. The sections that follow examine why this pattern appears so often and how it can be identified and corrected in existing codebases.
Your SQL Server is running hot. CPU is elevated, the plan cache is bloated, and queries that should be instant are taking seconds. You look at the query text and everything seems reasonable. The problem is not what the queries are doing. It is how they are being sent.
Non-parameterized queries are one of the most common performance problems in SQL Server environments, and one of the easiest to overlook because the queries themselves often look completely normal.
What is a non-parameterized query?
A parameterized query separates the query structure from the data values it operates on. The query is compiled once into an execution plan, and that plan is reused every time the query runs regardless of the specific values passed in.
A non-parameterized query bakes the actual data values directly into the query text. This comes in two common forms.
Literal values embedded in query text
The most straightforward form. A value like a customer ID, a date, or a status code is written directly into the WHERE clause rather than passed as a parameter:
-- Non-parameterized: literal value baked inSELECT * FROM Orders WHERE CustomerID = 10045-- Non-parameterized: every unique value produces a new planSELECT * FROM Orders WHERE CustomerID = 10046SELECT * FROM Orders WHERE CustomerID = 10047-- Parameterized: one plan, reused for every valueSELECT * FROM Orders WHERE CustomerID = @CustomerIDDynamic SQL built by string concatenation
The second form is more dangerous. Query text is assembled at runtime by concatenating strings together, often inside a stored procedure or application layer:
-- Dynamic SQL with concatenation: a new plan for every executionSET @sql = 'SELECT * FROM Customers WHERE CustomerID = ''' + @id + ''''EXEC(@sql)-- Parameterized dynamic SQL: one plan, and safe from injectionSET @sql = 'SELECT * FROM Customers WHERE CustomerID = @CustomerID'EXEC sp_executesql @sql, N'@CustomerID VARCHAR(20)', @CustomerID = @idIn both cases the fix is the same: separate the structure of the query from the values it uses.
Why does it exist in code?
Non-parameterized queries are rarely written with bad intentions. They show up for predictable reasons:
- Quick scripts and one-off queries written for immediate use that eventually find their way into production code or scheduled jobs.
- ORMs and reporting tools that generate query text dynamically and do not always use parameterized queries by default, depending on how they are configured.
- Application code where string formatting was used to build queries because it was the most familiar approach at the time.
- Dynamic SQL written to handle flexible search conditions, where parameterization was not considered or the developer was not aware of sp_executesql.
In many cases these queries have been running for years. They work, they return correct results, and nobody has looked closely at the execution plan or the plan cache.
Why is it a problem?
Non-parameterized queries create two separate problems: one is a performance problem, and the other is a security problem.
Plan cache bloat and CPU waste
Every time SQL Server receives a query, it checks the plan cache to see if it has already compiled an execution plan for that exact query text. With parameterized queries, the same plan is found and reused. With non-parameterized queries, each unique combination of literal values produces a different query string, which SQL Server treats as a completely new query requiring a full compilation.
On a busy system, this means SQL Server is compiling thousands of plans that will each be used once and then discarded. The plan cache fills up with near-identical plans. CPU spikes from constant compilation. Memory that should be used for data pages gets consumed by redundant cached plans instead.
SQL injection exposure
When query text is built by concatenating user-supplied or externally sourced values, the door opens for SQL injection. The value being concatenated does not have to be a simple ID. It can be anything, including SQL statements of its own:
-- The application expects a customer ID like 'C123'SELECT * FROM Customers WHERE CustomerID = 'C123'-- An attacker supplies this as the input value:C123'; DROP TABLE Customers; ---- The concatenated query becomes:SELECT * FROM Customers WHERE CustomerID = 'C123'; DROP TABLE Customers; --'-- Parameterized: the injected text is treated as a literal value, not SQLSELECT * FROM Customers WHERE CustomerID = @CustomerIDA parameterized query cannot be injected in this way. The value passed in is always treated as data, never as executable SQL. Parameterization is not just a performance optimization, it is one of the most fundamental defenses against SQL injection.
How to find it with Database Health Monitor
Database Health Monitor gives you two direct paths to finding non-parameterized queries in your environment.
The Needs Parameters report
The Needs Parameters report is the most direct tool for this smell. It queries the plan cache and surfaces queries that SQL Server has flagged as candidates for parameterization, specifically those where multiple near-identical plans exist with different literal values baked in.
The report shows you the query text, the number of plans in the cache for that query pattern, and the execution count. A query pattern with dozens or hundreds of plans in the cache is a clear signal that literal values are being embedded rather than parameters being used.
CPU by Query report
A second angle comes from the CPU by Query report. Non-parameterized queries frequently surface here because the repeated compilation overhead registers as elevated CPU usage tied to queries that individually look inexpensive. If you see a query pattern consuming disproportionate CPU relative to what it is actually doing, plan cache churn from missing parameters is a likely contributor.
Together these two reports give you a clear picture: the Needs Parameters report identifies the smell directly, and the CPU by Query report shows you the performance cost it is creating.
Tip: If you are seeing elevated CPU on a SQL Server instance and cannot immediately identify a single expensive query as the cause, check the Needs Parameters report first. Plan cache churn from non-parameterized queries is a frequent contributor to CPU pressure that does not always show up as an obvious slow query.
How to fix it
The fix depends on where the non-parameterized query is coming from.
Application code sending literal values
If queries are being built in application code with literal values substituted in, switch to parameterized queries or prepared statements at the application layer. Most modern database libraries and ORMs support this directly. The query structure stays the same and the values are passed separately:
-- Before: literal value in query stringSELECT * FROM Orders WHERE CustomerID = 10045-- After: parameterizedSELECT * FROM Orders WHERE CustomerID = @CustomerID-- Pass 10045 as the value of @CustomerID at execution timeDynamic SQL inside stored procedures
For dynamic SQL inside T-SQL, replace EXEC(@sql) with sp_executesql and declare the parameters explicitly. This preserves the flexibility of dynamic SQL while allowing the plan to be cached and reused:
-- Before: concatenated dynamic SQLSET @sql = 'SELECT * FROM Customers WHERE CustomerID = ''' + @id + ''''EXEC(@sql)-- After: parameterized with sp_executesqlSET @sql = N'SELECT * FROM Customers WHERE CustomerID = @CustomerID'EXEC sp_executesql @sql, N'@CustomerID VARCHAR(20)', @CustomerID = @idAs a short-term measure: Optimize for Ad Hoc Workloads
If you have a large volume of non-parameterized queries that cannot be fixed immediately at the source, enabling the Optimize for Ad Hoc Workloads server setting is a useful stopgap. Instead of caching a full plan on the first execution, SQL Server stores only a small plan stub. The full plan is only cached if the same query runs a second time, which significantly reduces plan cache bloat without requiring any query changes.
-- Enable optimize for ad hoc workloadsEXEC sp_configure 'optimize for ad hoc workloads', 1RECONFIGUREThis does not fix the underlying problem, but it reduces the memory and CPU impact while a proper parameterization effort is underway.
Find these in your environment today
Non-parameterized queries are one of those problems that tend to accumulate quietly over time. Each individual query looks harmless, but at scale the plan cache churn adds up to real CPU pressure and memory waste.
The Needs Parameters report in Database Health Monitor gives you an immediate view of where this is happening in your environment without any manual plan cache inspection. Pair it with the CPU by Query report to understand the performance cost, and you have everything you need to prioritize which queries to fix first.
Download Database Health Monitor free at DatabaseHealth.com and start finding code smells in your environment today.
Summary
- Non-parameterized queries cause excessive CPU usage and plan cache bloat in SQL Server.
- Literal values and dynamic SQL string concatenation prevent execution plan reuse.
- These queries can also create serious SQL injection security risks.
- Database Health Monitor helps identify these issues with the Needs Parameters and CPU by Query reports.
- Using parameterized queries and
sp_executesqlimproves performance, reduces memory waste, and increases security.
