This is a summary of the parameterization blog post series. If you read the previous posts in the series, then this summary can be a good way for you to recap what you’ve learned. If you haven’t read the previous posts yet, then this summary can also serve as a starting point, from which you can go to individual posts and dive deeper into the details. I’m also going to provide here the script to set up the database, which I have been using throughout the series, so that you can try all the code examples yourself.

Ready? Let’s summarize…

Part 1: Plan Caching

In this post I explained how plan caching works in SQL Server. Whenever a batch needs to be executed, the query processor first checks whether a plan already exists in cache for this batch. If such a plan exists and it’s valid, then the plan is reused. If not, a new plan is generated and placed in cache for later reuse. If a lot of similar queries with only different predicate values are sent from the application, then SQL Server will have to generate a lot of similar plans, and this will lead to higher resource consumption, longer execution times and poor performance.

Part 2: Seven Ways to Execute Your Query

In this post I demonstrated how the same query can be executed in 7 different ways in terms of parameterization, and how each way of execution affects the plan cache and the reuse of the plan.

The 7 ways are:

1. Non-Parameterized T-SQL Query
2. Non-Parameterized Dynamic T-SQL Query
3. Parameterized Dynamic T-SQL Query
4. Client-Side Non-Parameterized T-SQL Query
5. Client-Side Parameterized T-SQL Query
6. Stored Procedure
7. Looks-Like-Parameterized T-SQL Query

Part 3: Parameter Sniffing

In this post I explained what parameter sniffing is. This is a fundamental concept. It means that the optimizer can “sniff” the parameter values when an execution plan is generated for the parameterized batch. This is helpful, because the optimizer can estimate the number of rows returned from the query based on the parameter values, and then generate a good plan based on this estimation. The problem is that in some cases the plan, which was based on a specific set of parameter values, might be a poor choice for other sets of values. So sometimes parameter sniffing is a good thing, but sometimes it’s not.

Part 4: Handling Non-Uniform Data Distribution

In this post I explained why parameter sniffing might be a bad thing in some cases, and I also demonstrated a few possible solutions to this problem. When data in some column is not uniformly distributed, then different plans might be optimal for different values. But since parameter sniffing is applied only when the plan is generated, the plan will be good for a specific value, and subsequent executions with different values will reuse the plan, although it might not be optimal.

So I presented 5 different solutions that can be used to overcome this problem. Each solution has its own pros and cons, and you should consider which solution is best for your specific scenario. The solutions are:

1. sp_recompile
2. WITH RECOMPILE
3. OPTION (RECOMPILE)
4. OPTIMIZE FOR
5. The Best of All Worlds

Part 5: Two Common Mistakes

This post was dedicated to two common mistakes that developers do when it comes to handling parameter values inside stored procedures.

The first mistake is to modify the value of a parameter and then use the modified value in a query. Since parameter sniffing occurs at compile-time, the optimizer uses the original parameter value when generating the execution plan for the stored procedure, while the actual value being used in the query is completely different.

The second mistake is to declare a local variable, copy the value of the parameter into the local variable, optionally modify the value of the local variable, and then use the local variable in a query instead of the parameter. In this case, the optimizer treats the value of the local variable as unknown at compile-time, and parameter sniffing is effectively disabled, because the parameters are not used at all.

The post concludes with several options to handle these scenarios the right way.

Part 6: Simple vs. Forced Parameterization

This was the last post in the series, and it was dedicated to the parameterization property, which can be either “Simple” or “Forced”. This is a database property that dictates in which cases SQL Server will try to parameterize a query. Under the “Simple” parameterization mode, SQL Server will try to parameterize only queries with trivial plans, where there is no risk of reusing the wrong plan for some parameter value. Under the “Forced” parameterization, SQL Server will try to parameterize every query in the database, although there are some limitations to that. This might be risky, because for some queries, forcing parameterization might actually do worse.

This is why it is better to control parameterization at a more granular level, the query template level, by using template plan guides. The problem with template plan guides is that they are hidden and tend to be forgotten when troubleshooting performance.

So this is it. This concludes the series about parameterization. I hope you enjoyed reading it and learned a few things along the way. As promised, here is the script to set up the “ParameterizationExample” database, so that you can test all the scripts that appear in the series:

Happy parameterization!

The post Parameterization Part 7: Summary appeared first on .