One of the main benefits of using a stored procedure to execute Transact-SQL code is that once a stored procedure is compiled and executed the first time, the query plan is cached by SQL Server. So the next time the same stored procedure is run (assuming the same connection parameters are used), SQL Server does not have to recompile the stored procedure again, instead reusing the query plan created during the first compilation of the stored procedure. If the same stored procedure is called over and over again, with the query plan being reused each time, this can help reduce the burden on SQL Server’s resources, boosting its overall performance.
But as we all know, not all SQL Server-based applications use stored procedures to communicate with SQL Server. Many times the Transact-SQL is sent from the application to SQL Server in the form of a static or dynamic statement. Because SQL Server is fairly smart, by default, it has the ability to do something called simple parameterization.
Essentially, simple parameterization means that SQL Server can take a look at the static or dynamic Transact-SQL being sent to it from an application, and if it finds any values that it considers to be a parameter, it will parameterize the Transact-SQL, which allows the resulting query plan to be reused, much like how SQL Server can reuse the query plans of stored procedures. Let’s take a look at an example of what I am talking about.
Let’s say SQL Server receives the following SELECT query:
SELECT fname, lname, address, city, state, zip FROM mailinglist WHERE zip = ‘65742’
When SQL Server looks at this simple SELECT statement, it is smart enough to realize that ‘65742’ is a parameter, and that it is very possible that another, very similar query will be run later, but with a different value for the parameter. Because of this, when SQL Server compiles the query, it will parameterize it and cache the query plan so that if it does see a similar query, it will be able to re-use the cached query plan to execute it instead of having to recompile the statement each time it is to be executed.
For example, if the following two queries are run after the first one above, then SQL Server will be able to re-use the cached query plan, saving SQL Server resources and boosting overall performance.
SELECT fname, lname, address, city, state, zip FROM mailinglist WHERE zip = ‘10005’
SELECT fname, lname, address, city, state, zip FROM mailinglist WHERE zip = ‘99686’
While this is all good news, there is only one small problem: By default, only relatively simple queries can be parameterized. If your application uses mostly simple queries, the default simple parameterization of SQL Server 2005 might be more than adequate to meet your performance expectations.
But what if your application uses mostly complex queries, queries that cannot be automatically parameterized by SQL Server using simple parameterization? This is where a new feature of SQL Server 2005 comes to the rescue. This new feature is called forced parameterization. When forced parameterization is turned on, it tells SQL Server to force the parameterization of virtually all SELECT, INSERT, UPDATE and DELETE statements. There are types of queries that cannot be forced, but these exceptions are few.
With forced parameterization turned on, SQL Server will perform fewer compilations of statements because it now has the ability to re-use more query plans than before, helping to reduce resource usage and boosting performance.
But I know what you are thinking, there is no such thing as a free lunch. Am I right? And you are right. Like most aspects of SQL Server performance tuning, there are tradeoffs to be considered.
Here are some of those tradeoffs:
- Since SQL Server has to force parameterization on virtually all statements, it has to perform a little extra more work up front to perform the parameterization.
- Some queries that have widely changing parameters may end up using inappropriate query plans, reducing performance.
- Because literal constants in a query are changed to parameters, the Query Optimizer might not always choose the best plan, reducing performance.
- The Query Optimizer might not choose the ideal plan for queries on partitioned tables and distributed partitioned views.
- The Query Optimizer is less likely to match a query to an index view or an index on a computed column.
Essentially, Microsoft recommends that forced parameterization is best used in limited situations, such as in applications that experience high volumes of concurrent queries that use static or dynamic Transact-SQL, and not stored procedures. This is the situation that best lends itself to forced parameterization. If your application does not fall into this category, and you turn forced parameterization on, it is very possible that the performance could get worse.
Forced parameterization is turned on and off at the database level. In other words, it is an all or nothing setting. Either all the static and dynamic Transact-SQL uses simple parameterization or forced parameterization. But there are exceptions. If you want, you can use SQL Server 2005 Plan Guides either to turn on forced parameterization for a group of like queries (assuming it is off for the database) or to tell SQL Server not to force parameterization on specific queries (assuming it has been turned on for the database). But if you start doing the above, you are just asking for a lot of extra work.
To turn forced parameterization on, use the following command:
ALTER DATABASE FORCED
And to turn it off, use:
ALTER DATABASE SIMPLE
If you think your database might benefit from using forced parameterization, I highly recommend that you first try it on a test database to see what the results are. If it helps, then great. But in many cases, using forced parameterization will make little or no difference in performance, or may make it worse.
]]>