By: Armando Prato | Last Updated: 2008-11-21 | Comments (2) | Query Optimization
I am running an application that issues queries directly to the database from within its source code. I've noticed that the database server will get low on available memory and that CPU activity is higher than I'd like. What could be the problem?
Probably the most common mistake I find in application code are query requests that doesn't make use of either prepared queries or stored procedures and instead use non-parameterized ad-hoc queries to request data from the database.
Not preparing your queries or using stored procedures can unnecessarily bloat SQL Server's plan cache. What is the plan cache? Simply speaking, it's a part of the SQL Server's pool of shared memory where query execution plans are kept after queries have been parsed, compiled, and optimized for execution. This area of memory is searched whenever a query is executed in order to determine if an existing plan can be re-used to satisfy a query request. Re-using plans saves the database engine the potentially CPU intensive work of having to re-parse, re-compile, and re-optimize the query over and over again even if the only difference is the values being used in the WHERE clause. This leads to quicker query response times and lessens the chance of CPU pressure on the server.
The following Java code snippet makes a series of non-parameterized ad-hoc queries to the AdventureWorks database to get customer sales order data. It loops through and retrieves information for the first 20 orders from the AdventureWorks SalesOrderHeader table:
Using the SQL Server 2005 DMVs, let's examine the effect of the ad-hoc queries on 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
Note: the following query output display is modified to only show pertinent data in the text column
After running this query we can see below, each query execution stores a very specific plan in memory that is not parameterized and is not re-used by the database engine. Because the plans are so specific, chances are slim that any of these plans would ever be re-used. It's easy to see that server memory would be consumed very quickly if this was a very heavily used application.
Now I'll tweak the Java code to prepare the query statement. Prior to execution, I clear out the plan cache using command DBCC FREEPROCCACHE. I then re-run the Java class using a prepared statement:
Re-examining the plan cache, we see that the query was successfully compiled and then re-used for all executions, thus efficiently using and conserving server memory and limiting CPU involvement:
Now consider that since the plan cache is part of a shared pool of memory, eliminating superfluous plans leaves more memory available for the other caches that make use of this pool such as the SQL Server data cache which stores data and index pages that have been read into memory from disk.
While prepared queries are a better approach to query execution than using non-parameterized ad-hoc queries, my personal preference is to use stored procedures over both of these. Allowing direct access to your core database tables is a security risk and abstracting the data from the logic via stored procedures eases the maintenance and evolvement of the data model as business requirements change. Regardless of your chosen method of database access, save your application from potential memory and CPU issues by ensuring your query plans can be re-used.
- If your application is performing non-parameterized ad-hoc queries to access the database, consider modifying your logic to use prepared queries or consider moving this logic to stored procedures
- Read more about the benefits of stored procedures in the .NET Framework Developer Center
- Read more about the Execution Plan Caching and Reuse in the SQL Server 2005 Books Online
- If you're a C# developer, read SQL Server MVP's Dan Guzman's important blog entry about plan cache bloat in C# when using parameterized queries with varying varchar sizes
Last Updated: 2008-11-21
About the author
View all my tips