SelecTViews
With Stephen Wynkoop
A pet peeve – what is up with the silence about the massive cloud data breach??? Also on the show today – Michael Crump and some great applications for Silverlight.
[Watch the Show]
$$SWYNK$$
Plan Guides
In response to yesterdays tip using sp_execute_SQL I received the following comment from Leif regarding a database configuration that can cause a database to translate static queries into parameterized queries.
This may have discussed previously but regarding your article and as a best practice I recommend the activation of Forced Parameterization to allow literals to be converted to parameters to take advantage of plan reuse whenever possible. As you know a prerequisite is that all table references be qualified with the schema. This allows the plans of dynamic SQL to be reused without explicitly defining the parameters and allows reuse when literals are used within the SQL sting or variables are dynamically concatenated into the SQL string. As always there are some instances where Forced Parameterization will not apply (see BOL).
Why would you consider this option? You don’t always have control of the applications attached to your database server. Some third party applications work with many different database engines and do not take advantage of some of the more modular features such as stored procedures or parameterized queries. In this case you have three options.
- In SQL Server 2008 you can configure the server to be optimized for Ad-Hoc queries. This is a newer feature not available in older versions…run a search for this option to see if it fits your situation
- You can follow the recommendation provided by Leif…set Forced Parameterization to true on the database(s) for the specified application
- You can take a more granular approach and look into using Plan Guides
The third option is the most difficult because you have to actually find the queries you wish to optimize and establish a plan for those queries. Conversely, it grants you more granular capabilites when establishing an optimization plan, and provides many more options beyond parameterization of scalar values. There’s a lot more too it. So, if the first two options don’t have the desired effect, you can take a little more time and work with Plan Guides..
Have you used Plan Guides to optimize your system? Share your tips with our readers by sending your comments to btaylor@sswug.org.
Cheers,
Ben
Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000
More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)
Featured Script
T-SQL SSN validator (for Swedish personnummer)
This proc validates the control digit in Swedish SSN numbers. (personnummer)… (read more)