Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Are Your SQL Server Application Queries Wasting Memory


By:   |   Read Comments (2)   |   Related Tips: More > Query Optimization

Attend a SQL Server Conference for FREE >> click to learn more


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


Last Update:


signup button

next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 Hi

 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

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?


Learn more about SQL Server tools