 |
|
SQL Product Highlight
Idera - SQL safe backup
Idera's SQL safe provides a high-performance backup and recovery solution for Microsoft SQL Server. SQL safe saves money by reducing database backup time by up to 50% over native backups and reducing backup disk space requirements by up to 95%. SQL safe also enables complete "hands-free" automated backup of your entire SQL Server infrastructure and ensures compliance with your organization's backup and recovery policies. From implementations with tens of SQL servers to enterprises with hundreds of servers spread around the globe, SQL safe is the only SQL Server backup and recovery solution that scales to meet the challenge.Learn more!
Are Your SQL Server Application Queries Wasting Memory
|
By: Armando Prato | Read Comments (2) | Print Armando has over 24 years of industry experience and has been working with SQL Server since version 6.5. Related Tips: 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: 11/21/2008
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Saturday, December 27, 2008 - 6:58:52 AM - zhaorui | Read The Tip |
|
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? |
|
| Saturday, December 27, 2008 - 10:18:06 AM - aprato | Read The Tip |
|
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 |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |