Perhaps the application code to find the most common mistakes is one such query: it is not to use prepared queries or procedures, but the ad hoc use of non-parameter query requests data from the database.
Not prepared to your query or stored procedure does not use unnecessary SQL Server program cache. What is the plan cache it? Simply put, it is the SQL Server part of the shared memory pool, where, analysis, compilation and implementation of the optimization of these queries, the query execution plan is still preserved. Whenever you execute a query, the memory will be to find the region in order to determine whether an existing plan can be reused to satisfy a query request. Re-use plan for the database engine, the potential savings of CPU-intensive work, for example, if the only difference is that WHERE clause is used in the value, we have once again re-analysis, re-compile, re-optimize the query. This will lead to speed up query response time, server CPU pressure is reduced.
The following Java code snippet a series of ad hoc queries to the non-parametric AdventureWorks database in order to get users to sales order data. It is through the cycle before the table from the AdventureWorks SalesOrderHeader order to obtain information 20.
Figure 1
Let us use the SQL Server 2005 DMVs to test the plan cache ad hoc query results.
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
Run a query, we can see in the picture below, each query execution are stored in memory in a very specific plan, the plan is not parameterized, and were not re-use database engine. Because these programs are so specific, so any of these programs can be less likely to re-use. Is easy to see, if this is a very high frequency applications, the server will quickly consume memory.
Figure 2
Java code will now be adjusted to prepare for this query. Prior to implementation, the command DBCC FREEPROCCACHE I clear the plan cache, and then through a prepared statement to re-run the java class:
Figure 3
Re-examine the plan cache, we see that the query has been successfully compiled and re-used for all and, therefore, the effective use and preservation of the server memory and CPU use restrictions.
Figure 4
Now, taking into account the scheme is the memory as part of the shared pool, then the elimination of redundant programs can make more available for other cache memory, so that others can use the shared buffer pool, such as storage has been read from the hard disk to memory data and index pages of SQL Server data cache.
Although the parameters relative to the use of non-ad hoc queries, the prepared query is a better way, but compared to these two methods, I am more inclined to use stored procedures. Allows direct access to your core database tables a security risk, the data through stored procedures drawn from the logic can reduce maintenance, and when business needs change, it also can reduce the data model changes. No matter which data access method you choose, remember the query plan by ensuring that you can re-use, and thus your application from the potential problem of memory and CPU rescue.