So often when we talk about performance tuning we focus on a specific query, or maybe a few problematic queries.  But your workload isn't made up of just a few queries, it's comprised of hundreds, or thousands (or more?!) queries.  The act of reviewing and tuning them individually is impossible.  But imagine having the ability to find trends and patterns across sets of queries, and tuning more holistically.

In this full day workshop we will first walk through a methodology to understand the characteristics of your workload.  We'll discuss real-world environments including pure OLTP, reporting, and hybrid, and the challenges that exist when trying to ensure that all queries in a given workload perform well.  We will also cover how to programmatically identify adhoc vs. stored procedure workloads, and considerations for managing and optimizing each one.

We’ll then move into identification of common problems that can plague a SQL Server installation such as excessive tempdb use, data skew and parameter sensitive queries, memory grant issues, and more.  SQL Server continues to evolve to create faster solutions (In-Memory OLTP, Columnstore indexes) and the Query Processing team has done significant work to enable the optimizer to help your queries when you do not have plan stability (e.g. Adaptive Joins, Memory Grant Feedback).  However, this doesn't mean that the work of the developer is done.  You still have to architect, design, and code thoughtfully, and we will round out the day discussing options for managing and addressing these issues.

Experience with the plan cache and query plans, query optimization, and performance monitoring recommended.  Ideally suited for those running later versions of SQL Server (2016+) and/or anyone planning to upgrade, but options for earlier versions will also be presented. 

Expect discussion, demos, and practical knowledge that you can apply to your environment as soon as you can convince your team of what needs to be done!

Experience with using DMVs to look at query performance metrics. Familiar with TSQL and query plans.
Laptop Required:No

Presented by Erin Stellato at SQLBits XVIII