This is the second post in my series on parameterization. In the first post I wrote about plan caching and about the phases that each batch goes through before execution. I ended the previous post with a query example that demonstrates the importance of parameterization. For this post I’m going to use the following query:

SELECT
Id ,
Name ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = N'IL';

This is a simple query that retrieves all the customers from a given country, in this case – Israel.

Paul Simon has a great song – “50 Ways to Leave Your Lover”. One of my favorites. I encourage you to click that link and listen to the song while you continue reading the rest of this post.

While Paul Simon is singing about ways to leave your lover, I say there are seven ways to execute your query. I don’t have a song about it, but I’ll try to explain it through code. Yeah, I know it’s not as sexy as a song by Paul Simon, but that’s the best I can come up with. Sorry…

Now, I’m going to take the test query mentioned above and show you how it can be executed in seven different ways. I’m also going to show you how each execution method affects the plan cache and plan reuse.

In order to monitor the impact of each execution on the plan cache, I’m going to use the following view:

CREATE VIEW
dbo.CachedPlans
(
QueryText ,
QueryPlan ,
ExecutionCount ,
ObjectType ,
Size_KB ,
LastExecutionTime
)
AS
SELECT
QueryText= QueryTexts.text ,
QueryPlan= QueryPlans.query_plan ,
ExecutionCount= CachedPlans.usecounts ,
ObjectType= CachedPlans.objtype ,
Size_KB= CachedPlans.size_in_bytes / 1024 ,
LastExecutionTime= last_execution_time
FROM
sys.dm_exec_cached_plans AS CachedPlans
CROSS APPLY
sys.dm_exec_query_plan (plan_handle) AS QueryPlans
CROSS APPLY
sys.dm_exec_sql_text (plan_handle) AS QueryTexts
INNER JOIN
sys.dm_exec_query_stats AS QueryStats
ON
CachedPlans.plan_handle = QueryStats.plan_handle;

This view retrieves all the plans currently in the plan cache including the text and the plan itself of each batch, and also the last execution time of each plan. Now, after each execution, I’m going to use the following query in order to examine the contents of the plan cache, but only plans that are related to our test:

SELECT
*
FROM
dbo.CachedPlans
WHERE
QueryText LIKE N'%Customers%'
AND
QueryText NOT LIKE N'%sys.dm_exec_cached_plans%'
ORDER BY
LastExecutionTime ASC;

So, the first and the most trivial way to execute our test query is just like that:

SELECT
Id ,
Name ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = N'IL';

This is called a non-parameterized T-SQL query. Although the same query might be executed again with a different country code, the query does not make use of parameters, and each instance of the query with a different country is treated as a separate query with its own execution plan. If you try to execute this query with different countries, you’ll see there is a separate plan in cache for each execution with an execution count of 1.

The “Adhoc” object type means it’s a non-parameterized query.

The second way to execute the same query is with a non-parameterized dynamic SQL query. It looks like this:

DECLARE
@Country      AS NCHAR(2)                = N'IL' ,
@QueryText    AS NVARCHAR(MAX);
SET @QueryText =
N'
SELECT
Id ,
Name ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = N''' + @Country + N''';
';
EXECUTE (@QueryText);

In this case, we build the query dynamically in a text variable (@QueryText). The dynamic part in this case is the concatenation of the country, which is stored in the @Country variable. Then we execute the query using the EXECUTE statement. The query that is actually being passed to the query processor is the same as the previous query (non-parameterized T-SQL). In fact, if we build the dynamic query with the exact same white spaces as the previous T-SQL query, we will notice a reuse of the previously compiled plan instead of a new separate plan in cache. But just like the non-parameterized T-SQL query, this query also doesn’t use parameters, so if you try it with different countries, you’ll get different plans.

Now, let’s make it more interesting. We are going to build the query dynamically again, but this time we will use a parameter for the country, and we will pass the parameter value to the query using the sys.sp_executesql system stored procedure…

DECLARE
@Country      AS NCHAR(2)                = N'IL' ,
@QueryText    AS NVARCHAR(MAX) ,
@Parameters   AS NVARCHAR(MAX);
SET @QueryText =
N'
SELECT
Id ,
Name ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = @pCountry;
';
SET @Parameters = N'@pCountry AS NCHAR(2)';
EXECUTE sys.sp_executesql
@statement    = @QueryText ,
@params       = @Parameters ,
@pCountry     = @Country;

The @pCountry is a parameter defined in the scope of the dynamic batch. The @Parameters variable holds the definition of all the parameters defined for this batch. This batch results in a parameterized plan, also called a “prepared” plan. If you try to run this code with different countries, you will see there is a reuse of the same prepared plan, because each execution is the same batch with only a different value of the parameter.

Notice that the query text includes the definition of the parameters at the beginning, before the query itself.

Next, let’s look at an execution of the same query from the application. Using C#, we can build the text of the query, pass it to a command of type “text” and execute the command.

SqlConnection Connection = new SqlConnection(Properties.Settings.Default.ConnectionString);
SqlCommand Command = new SqlCommand();
Command.CommandType = CommandType.Text;
Command.CommandText = "SELECT Id , Name , LastPurchaseDate FROM Marketing.Customers WHERE Country = N'" + textBox1.Text + "';";
Command.Connection = Connection;
Connection.Open();
Command.ExecuteReader();
Connection.Close();

This is essentially the same as executing a non-parameterized dynamic query with the EXECUTE statement, and the content of the plan cache after two executions with different countries is the same:

But we can also embed parameters in the dynamic query, and define those parameters, just like we did with the sys.sp_executesql stored procedure.

SqlConnection Connection = new SqlConnection(Properties.Settings.Default.ConnectionString);
SqlCommand Command = new SqlCommand();
Command.CommandType = CommandType.Text;
Command.CommandText = "SELECT Id , Name , LastPurchaseDate FROM Marketing.Customers WHERE Country = @pCountryId;";
Command.Parameters.Add("@pCountryId", SqlDbType.NChar);
Command.Parameters["@pCountryId"].Size = 2;
Command.Parameters["@pCountryId"].Value = textBox1.Text;
Command.Parameters["@pCountryId"].Direction = ParameterDirection.Input;
Command.Connection = Connection;
Connection.Open();
Command.ExecuteReader();
Connection.Close();

In fact, when we run this code from the application, it is translated to the exact same execution of sys.sp_executesql we’ve seen before…

What about a stored procedure?

CREATE PROCEDURE
Marketing.usp_CustomersByCountry
(
@Country AS NCHAR(2)
)
AS
SELECT
Id ,
Name ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = @Country;
GO

This is the classic way to execute a parameterized query. It is similar to the parameterized dynamic query, only in this case the text of the query is stored in the database along with the definition of the parameters. We can use it again and again without having to pass the text and the parameters definition each time. Actually, using sys.sp_executesql is like using an ad-hoc stored procedure.

In the plan cache, the plan of a stored procedure is stored with an object type “Proc”. Stored procedure plans have priority over other plans in cache in terms of the duration they will stay in cache. When there is memory pressure, other plans will be evicted from cache before stored procedure plans are (statistically speaking).

The last way to leave your lover… Sorry, the last way to execute your query is what I call – “Looks-Like-Parameterized T-SQL Query”…

DECLARE
@Country AS NCHAR(2) = N'IL';
SELECT
Id ,
Name ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = @Country;

In this case, we declare a local variable, set its value to “IL” and use it directly in the query. If you look only at the query without the variable declaration, it looks exactly the same as a parameterized query with a parameter named @Country. In fact, it is identical to the query inside the stored procedure. The confusing thing here is that both parameters and local variables start with “@”, while they are completely different things.

First, this query is not parameterized at all, because the whole batch is compiled, including the DECLARE statement, and for each different country, we get a different batch and a different plan.

See the “Adhoc” object type? This means no parameterization.

Second, there is a potential performance problem in this query. In order to understand this, let’s go back to the previous methods…

When the query specifies a constant value for the country, whether it is hard-coded as in the first method or built dynamically, the optimizer knows the value in compile-time and can use it in order to estimate the number of rows that will return for the query. This estimation helps the optimizer choose the best plan for the query. When the value is known to the optimizer, it can use statistics to estimate the number of rows, and in most cases, it can come up with a pretty good estimation.

When the query uses a parameter for the country, the optimizer uses a method called “parameter sniffing” (more about it in the next post in this series). Parameter sniffing allows the optimizer to “sniff” the values of the parameters at compile-time, so it actually knows the values when optimizing the query, just like if they were hard-coded values. This method works with parameters, but not with local variables. The declaration and value assignment of local variables happen at run-time, so at compile-time the optimizer knows nothing about the values of the local variables, and it treats them as unknown. The optimizer has rules for handling unknown values in different scenarios. Generally speaking, it uses average statistics, which in some cases might lead to very wrong estimations.

We covered the seven ways to execute a query, and we saw the difference between a parameterized query and a non-parameterized one. In the next post I’m going to write a lot more about parameter sniffing and whether it’s a good thing or a bad thing…

The post Parameterization Part 2: Seven Ways to Execute Your Query appeared first on .