How to Use Parameters Like a Pro and Boost Performance by Guy Glantser
Parameters are a fundamental part of T-SQL programming, whether they are used in stored procedures, in dynamic statements or in ad-hoc queries. Although widely used, most people aren't aware of the crucial influence they have on query performance. In fact, wrong use of parameters is one of the common reasons for poor application performance. In this session we will learn about plan caching and how the query optimizer handles parameters. We will talk about the pros and cons of parameter sniffing as well as about simple vs. forced parameterization. But most important – we will learn how to identify performance problems caused by poor parameter handling, and we will also learn many techniques for solving these problems and boosting your application performance.