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.
But, 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.
Now SQL Server by default 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.
For example consider the following SQL Query:-
SELECT fname, lname, address, city, state, zip FROM custinfo WHERE zip = ‘65742’
When SQL Server looks at this simple SELECT statement, it considers that ‘65742’ is a parameter, and that it is 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 & cache the query plan so that next time a similar query, 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 custinfo WHERE zip = ‘10005’
SELECT fname, lname, address, city, state, zip FROM custinfo WHERE zip = ‘99686’
But 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 might be more than adequate to meet your performance expectations.
But, if application uses mostly complex queries, queries that cannot be automatically parameterized by SQL Server using simple parameterization.
This is where a new feature introduced from SQL Server 2005 comes into the picture. 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.
Enabling Forced Parameterization
-Enabling Forced Mode
ALTER DATABASE YOUR_DATABASE SET PARAMETERIZATION FORCED
-Enabling Simple Mode
ALTER DATABASE YOUR_DATABASE SET PARAMETERIZATION SIMPLE
Alternatively Parameterization can also be set from the Management Console: –
Select the Particular Database for which you want to change the Parameterization.
Right Click and go to Properties. Go to Database Options & change Parameterization from SIMPLE to FORCED.