The pain of Simple Parameterization in SQL Server

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

In today’s blog posting I want to talk about some specifics and side-effects of the Simple Parameterization mechanism used by SQL Server for ad-hoc SQL statements. First of all, Simple Parameterization will not happen if your SQL statements contain any of these:

  • JOIN
  • IN
  • BULK INSERT
  • UNION
  • INTO
  • DISTINCT
  • TOP
  • GROUP BY
  • HAVING
  • COMPUTE
  • Sub Queries

In general SQL Server only auto parameterizes your SQL statements if you deal with a so-called Safe Execution Plan: regardless of the provided input parameter values, the query must always lead to the same execution plan. This is for example not possible if you have Bookmark Lookups in your execution plans, because the Tipping Point defines if a Bookmark Lookup or if a complete Table Scan/Clustered Index Scan is performed.

Auto Parameterization is not that cool!

If SQL Server is able to auto parameterize your SQL statement, you still have to think about some side-effects that the auto parameterized SQL statement introduces in SQL Server. Let’s have a look at a concrete example. The following query creates a table, and executes a simple SQL statement that can be auto parameterized by SQL Server.

When you afterwards look into the Plan Cache, you can see that SQL Server was able to auto parameterize the SQL statement for you:

(@1 numeric(3,2))SELECT * FROM [Orders] WHERE [Price]=@1

But what was chosen as the data type of the parameter? The smallest possible one! In our case a NUMERIC(3, 2)! If you now execute the following 2 queries.

SQL Server is able to reuse the execution plan of the parameterized SQL statement for the first SQL statement with the value of 8.70. But what about the 2nd SQL statement with the value of 124.50? For this SQL statement the cached plan can’t be reused, because the value of 124.50 doesn’t fit into a NUMERIC(3, 2). In that case SQL Server generates a new parameterized version of your SQL statement with a data type of NUMERIC(5, 2). You just polluted your Plan Cache with an additional parameterized version of your SQL statement! It gets even worse when you execute the following statement:

This one gives you again a new parameterized version with a data type of NUMERIC(6, 2) – another version in the Plan Cache! When I show this specific behavior during my various SQL Server workshops, people always suggest to me afterwards that I should just execute the SQL statements in the opposite order. Let’s try that by first clearing the Plan Cache.

If you afterwards look again into the Plan Cache, nothing has changed: SQL Server has still generated 3 different parameterized SQL statements – with the smallest possible data type every time!

Simple Parameterization doesn't rock!

It doesn’t matter how you do it, and in which order you execute your SQL statements: SQL Server will always choose the smallest possible data type during the Auto Parameterization. Just think about that when you rely on that feature in SQL Server.

And what about VARCHAR? When SQL Server auto parameterizes a SQL statement that contains a character value (like VARCHAR), things change for the better. Let’s assume the following table definition and the following 2 queries:

In that case, SQL Server just generates 1 auto parameterized SQL statement with a data type of VARCHAR(8000) – the largest possible data type. That’s the preferred behavior that you would also have expected from the previous examples. Sometimes SQL Server does good and bad things at the same time…

Summary

Auto Parameterization can be great when you are working with simple SQL statements. But as you have seen in this blog posting, you have to know the side-effects that are introduced by SQL Server. Besides the Simple Parameterization feature SQL Server also offers you the Forced Parameterization functionality about which I will talk in a future blog posting.

Like or share to get the source code.

Thanks for your time,

-Klaus

3 Comments

  • courage

    I’d say that this behavior with numericss is a bug in SQL Server, which is worth reporting on Connect.

  • Krzysztof Białobrzeski

    Is there a way to prevent this situation in Sql Server?
    That there was only one plan and not more?

    • Klaus Aschenbrenner

      Hello Krzysztof,

      Thanks for your comment.
      The only thing that you can is that you cast/convert your input parameter to a larger value 😉

      Thanks,

      -Klaus

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top