Don't Fear Dynamic SQL

Abstract

Why All the Fusst Not reusing query plans can take its toll on system resources such as CPU and memory. Because compiling a query plan can be one of the most CPU-intensive operations, you want to minimize this for peak performance.

Headnote

When done correctly, it can be used effectively

One comment that I hear repeatedly but that's totally without merit is "The use of dynamic SQL is bad." So what does "bad" really mean in this case? It can mean many things, depending on who made the comment. When used incorrectly, dynamic SQL certainly has some downsides. But when used properly, it has many positive attributes, including the ability to enhance performance in several ways, as you'll see shortly. Let's sec if I can make a believer out of you.

What Does It All Mean?

To begin, let's define two terms: dynamic SQL and ad-hoc statement. Dynamic SQL occurs when one or more T-SQL statements are dynamically pieced together inside a stored procedure or T-SQL code block and executed with cither the EXECUTE command (which can be shortened to EXEQ or the sp_executesql system stored procedure. An adhoc statement is a T-SQL statement that's built by a client application and executed directly as a batch call to SQL Server. However, for the most part, you can equate the ad-hoc building of T-SQL statements to how the EXEC command is used.

I'll explain the two methods for executing dynamic SQL in a little more detail, but keep in mind that both EXEC and sp_executesql are fully documented in SQL Server Books Online (BOL). Both methods are commonly used to dynamically build a statement based on a set of parameters passed into a stored procedure or a set of values obtained by querying metadata. To simplify things, I'll use a series of variables to simulate a set of values.

First, let's talk about the EXEC command. It has only one parameter, which is a string or variable that denotes the statement you want to execute, as shown in the example in Listing 1. The code in Listing 1 would be functionally equivalent to running this SELECT statement:

The sp_executesql stored procedure takes three parameters in the following order: the statement itself, the parameter definition list, and the assignment of the values to the parameters defined in the list. (Again, see BOL for more details.) As Listing 2 shows, the code that uses sp_executesql looks similar to the code that uses the EXEC command, except you build a parameter in the WHERE clause instead of hard-coding the AddressID value. The code in Listing 2 would be functionally equivalent to running this SELECT statement:

Forget What You Know

Now that I have shown you both methods, you should essentially forget about EXEC when it comes to executing statements dynamically. Sp_ executesql is the preferred way, clear and simple, because you have the potential to reuse the query plans from previously executed statements by passing different values in the parameter list. Except for very simple cases, EXEC is unlikely to reuse a previously generated plan and will force SQL Server to compile a new one each time you call it with a different value.

Why All the Fusst

Not reusing query plans can take its toll on system resources such as CPU and memory. Because compiling a query plan can be one of the most CPU-intensive operations, you want to minimize this for peak performance. In addition, if you don't properly parameterize statements, you can waste a lot of memory storing query plans that will only be used once. (The amount depends on how much memory SQL Server has available) For more information about how the database engine uses the plan cache, see the white paper "Plan Caching in SQL 2008" at msdn .microsofl.com/en-us/library/ee343986( v=SQL. 100) aspx and the article "Fine-Tuning Plan Reuse" (April 2011, InstantDoc ID 129462).

I can't stress the importance of plan reuse enough. Too many new development efforts use a framework that centers on the ad-hoc building of T-SQL statements that are submitted to the database engine as a nonparameterized batch. Although this might make the development effort slightly easier, it negatively affects database performance and scalability.

Here Is the Difference

I can hear you saying that even when you use sp_ executesql correctly, you'll still get multiple query plans because the cores of the statements aren't always the same. Yes, this is true, and there's no getting around it. If you run the following two statements, you'll get two plans in the cache:

In a nutshell, anytime there's a difference in any of the characters (including spaces) in the statement, you'll get another plan. However, you're likely to call the same statement many times, each with a different value for one or more search arguments, just as you would if it were a stored procedure. And if you parameterize the applicable portions of the statement that will change with each call, you can avoid having to create a new plan for each one. You can't parameterize the list of columns or the table names but you can create parameters for the WHERE clause and aspects such as the TOP clause. Then you can execute these statements with sp_executesql and pass in the appropriate values for the parameters each time, allowing SQL Server to reuse the query plan from the first time it executed that statement.

The Choice Is Yours

My goal here isn't to make you an expert on sp_executesql. as there are already many good resources you can use to achieve that goal. My goal is to call your attention to how important it is to think about using sp_executesql instead of executing a traditional ad-hoc statement with EXEC. Time and time again, I've seen the mistake of not taking a little bit of extra time and effort upfront to properly parameterize the calls to SQL Server. As a result, when the database and application are scaled, they suffered from performance problems.

I hope this very simplistic overview of dynamic SQL gets you thinking about how dynamic SQL can be a good thing for your system if done correctly. You should feel comfortable knowing that executing dynamically generated T-SQL statements using sp_executcsql with parameters is virtually no different in terms of performance than if you executed a custom stored procedure with the same parameters. However, there can be a huge performance difference between executing dynamically generated T-SQL statements using sp.exccutesql with parameters and submitting total ad-hoc statements with hard-coded values in the WHERE clause. The choice is yours, and so will be the rewards.

InstantDoc ID 140265