Is your SQL Server application query wasting memory?

Perhaps the most common mistake found in the application code is such a query request: instead of using a prepared query or program, instead of requesting data from the database using a nonparametric ad hoc query.

Not preparing your query or not using stored procedures can add unnecessary SQL Server plan caching. What is a plan cache? Simply put, it is part of the SQL Server shared memory pool, where the query execution plan is saved after parsing, compiling, and executing the optimizations. Whenever a query is executed, this area of memory is searched to determine whether an existing plan can be reused to satisfy a query request. The reuse plan saves potential CPU intensive work for the database engine, for example, if the only difference is the value being used in the WHERE clause, we have to parse it again and again, recompile, and then refine the query. This will result in faster query response times and lower CPU pressure in the server.

The following Java code fragment presents a series of nonparametric ad hoc queries to the AdventureWorks database to obtain user sales order data. It uses loops to get information from the first 20 orders in the AdventureWorks SalesOrderHeader table.

Figure I

Let's use SQL Server DMVs to verify the effect of ad hoc queries in the plan cache.

select qs.usecounts, cacheobjtype, objtype, qt.text
from sys.dm_exec_cached_plans qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as qt
order by qt.text
go

Note: The following query output is modified to display only the corresponding data in the text field.

After running the query, we can see from the figure below that each query execution stores a very specific plan in memory, which is not parameterized and is not being recycled by the database engine. Because these plans are so specific, there is little likelihood that any of these plans can be reused. It is easy to see that if this is a very high frequency application, then server memory will be consumed quickly.

Figure II