Ad-hoc Query and the evil of dynamic SQL [translate]

This article is a translation of the article, the original address: https://www.simple-talk.com/blogs/2009/08/03/ stolen-pages-ad-hoc-queries-and-the-sins-of-dynamic-sql-in-the-application/

　　

　　Objective

　　Development on SQL Server servers with very limited hardware resources has the advantage that errors are visualized, that you will soon be punished for your mistakes. For example, the problem of excessive use of ad-hoc dynamic queries in applications is quickly exposed. The developer may complain to you that the database is running more and more slowly. You will also find the server CPU usage is very high, even close to 100%, but in poor performance time, but no blocking occurs.

In extreme cases, you may even receive the following error:

　　error:701, Severity:17, state:1
There is insufficient system memory to run this query.

Or

　　MSG 8645, Level A, State 1, Procedure, line 1
A time out occurred when waiting for memory resources to execute the query. Re-run the query.　

　　Analysis

You will find that running too many ad-hoc queries all the bad effects. High CPU utilization is due to the fact that the query optimizer compiles a large number of ad-hoc queries. Memory pressure is due to some memory used to cache the execution plan generated by the Ad-hoc query. In other words, developers use AD-HOC queries instead of stored procedures or parameterized queries. It is very foolish.

A compiled execution plan takes up approximately 70KB of space, and the execution plan for a stored procedure takes approximately 2 to 3 times times as much space, depending on its complexity. The difference is that each stored procedure has only one execution plan. Using Ad-hoc queries, you will risk each query with a separate execution plan.

The execution plan is cached for reuse, and SQL Server needs to consume memory to store the execution plan, which is called stolenpages in the form of the request. Other objects that occupy stolenpages include some memory consumer, such as connections, Locks, and Transaction context, as well as memory consumed by threads and third-party code. This is a simple way to allocate memory for daily tasks, but when the database receives a lot of ad-hoc queries, it can cause trouble. SQL Server rebuilds an execution plan unless SQL Server can determine that the query can be automatically parameterized or that the new query is consistent with the cached query. It may not be long before you see a bottleneck in the data cache

　　So how to determine if the system has this problem

1. Check the number of compiled query plans. SQL Server Performance Monitor will display a higher numeric value for compilations/sec. Theoretically, the ratio of SQL recompilations/sec to Batch requests/sec should be very low

2. DBCC Memorystatus will also show that the number of stolen pages will rise

　　After you determine the problem, how to resolve

1. Use stored procedures to perform

2. Using parameterized queries

　　Example demo slightly specific see the original URL

If there is no place, welcome to shoot Bricks, thank you! O (∩_∩) o