Are Your SQL Server Application Queries Wasting Memory

By: Armando Prato | Updated: 2008-11-21 | Comments (2) | Related: > Query Optimization

Problem

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?

Solution

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
go

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.

Next Steps

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

About the author

Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2008-11-21

Comments For This Article

Saturday, December 27, 2008 - 10:18:06 AM - aprato

I'm sorry if my wording confused you. In a nutshell, parameterized sql added in-line is superior to ad-hoc sql that is not parameterized. Stored procedures compiled into your database are a way to create a re-usable plan for a series of T-SQL statements. The benefit of stored procedures and prepared queries is that they both allow for plan re-use, thus conserving server memory and reducing CPU activity.

In my opinion, stored procedures are a better approach to data access. They offer a way to abstract the database from the code calling it. This abstraction gives you the freedom to modify the database model as you see fit without having to dig through your application logic to find all references to a table that may be littered throughout the code. Stored procedures also aid in securing access to data. You can GRANT access to stored procedures to only those users that require access.

I highly recommend you read this article from Microsoft

http://msdn.microsoft.com/en-us/library/ms973918.aspx

Saturday, December 27, 2008 - 6:58:52 AM - zhaorui	Back To Top (2452)
I have some question about stored procedures. I notice in the article, you describe "stored procedures" is parameterized sql statement in the "code", in the other hand, i prefer use store procedure in "database", my question is what different between two this way? are they same in performance?