SQL Server MVP Erland Sommarskog has posted his latest article yesterday and I highly recommend printing it out/transferring it to your ebook reader and reading it.
Of course I think most of you are already familiar with Erland’s article The curse and blessings of dynamic SQL, this article I am sure will be linked in answers as much as the dynamic SQL one
Remember all those question you get where a query is fast in SSMS but slow from ADO.NET? Yep that pesky ARITHABORT setting which causes problem…this is covered in this article.
Here is the whole outline
How SQL Server Compiles a Stored Procedure
What is a Stored Procedure?
How SQL Server Generates the Query Plan
Putting the Query Plan into the Cache
Different Plans for Different Settings
The Default Settings
The Effects of Statement Recompile
The Story So Far
It’s Not Always Parameter Sniffing…
Replacing Variables and Parameters
Indexed Views and Indexed Computed Columns
Getting Information to Solve Parameter Sniffing Problems
Getting the Necessary Facts
Which is the Slow Statement?
Getting the Query Plans and Parameters with Management Studio
Getting the Query Plans and Parameters Directly from the Plan Cache
Getting Query Plans and Parameters from a Trace
Getting Table and Index Definitions
Finding Information About Statistics
Examples of How to Fix Parameter-Sniffing Issues
Best Index Depends on Input
Dynamic Search Conditions
The Case of the Application Cache
Fixing Bad SQL
How SQL Server Compiles Dynamic SQL
What Is Dynamic SQL?
Generating the Plan for Dynamic SQL
Dynamic SQL and the Plan Cache
Running Application Queries in SSMS
Addressing Parameter-sniffing Problems in Dynamic SQL
You can find the article here: Slow in the Application, Fast in SSMS?
Understanding Performance Mysteries….enjoy