Actual memory consumption during SQL Server Query

Perhaps the most common error found in application code is a query request that uses a non-parameter special query to request data from the database instead of a prepared query or program.

If you do not prepare your query or do not use the stored procedure, unnecessary SQL Server plan cache will be added. What is plan cache? Simply put, it is part of the SQL Server shared memory pool. Here, after these queries are parsed, compiled, and executed for optimization, the query execution plan is still saved. Whenever a query is executed, this area of the memory will be searched to determine whether an existing plan can be reused to meet a query request. The reuse plan saves potential CPU intensive work for the database engine. For example, if the only difference is that the value is being used in the WHERE clause, we have to re-parse and re-compile it again and again, optimize the query again. This will speed up the query response time and reduce the CPU pressure on the server.

The following Java code snippet puts forward a series of non-parameter ad hoc queries to the AdventureWorks database to obtain the user's sales order data. It cyclically obtains information from the first 20 orders in the AdventureWorks SalesOrderHeader table.

 

Let's use SQL Server 2005 DMVs to check the specific query results 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

After running the query, we can see from the figure below that each query execution stores a very specific plan in the memory, which is neither parameterized nor reused by the database engine. Because these plans are so specific, it is very unlikely that any of these plans can be reused. It is easy to see that if this is an application that is frequently used, the server memory will be quickly consumed.

 

Now we will adjust the Java code to prepare this query statement. Before execution, I run the DBCC FREEPROCCACHE command to clear the cache of the plan, and then run the java class again using a prepared statement:

Review the cache of this plan. We can see that the query has been successfully compiled and re-used for all executions, so the server memory is effectively used and saved and the CPU usage is limited.

 

Now, considering that the planned cache is part of the Memory Sharing pool, eliminating redundant plans can free up more available memory for other caches so that other caches can use this shared pool, for example, you can store the SQL Server data cache that has read data from the hard disk to the data in the memory and index pages.

Although the prepared query is a better method than the query request with special parameters, I am more inclined to use stored procedures than the two methods. Security risks exist when you allow direct access to your core database tables. Data is extracted from the logic through the stored procedure to reduce maintenance. When business needs change, it can also reduce data model changes. Regardless of the data access method you choose, remember to make sure that your query plan can be reused to save your application from potential memory and CPU problems.

1. How to batch import data in the SQL Server database
2. Output data from SQL Server to a text file
3. Optimize SQL Server database query methods