Level: Introductory to Intermediate
T-SQL programming makes wide use of parameterization in stored procedures, dynamic statements and ad hoc queries. Many developers aren't aware of the significant influence they have on query performance. Incorrect use or lack of parameters is a common reason for query performance issues. Have you experienced a query that always ran fast, but suddenly (and for "no reason") become slow? And with nothing changing? Yep! If this is a new topic to you, you'll leave this session with takeaways galore.
You will learn:
- About how the plan cache deals with queries that are (and are not) parameterized
- What parameter sniffing is and how it's handled in the older and newest versions of SQL
- About the problem of mismatched data types and implicit conversions -- and how that ties in with ORM-generated code
- Value list handling from applications, and how to use a table valued parameter as an input instead