SQLBits 2020

Stop Tuning Queries and Start Tuning Your Workload

This session will discuss how to look at your workload holistically using a variety of methods, so that you can target and address patterns in your code and query plans, rather than troubleshooting one query at a time.

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 a few queries; it's comprised of hundreds, or thousands (or more?!) queries. The act of reviewing and tuning them individually is impossible. But we do have the ability to find trends and patterns across sets of queries, and then tune more holistically.

In this session we will discover how to programmatically identify common problems that can plague a SQL Server installation such as parameter sensitive queries, high-frequency ad hoc queries, and queries that have excessive tempdb use or memory grant issues.

Experience with the plan cache and query optimization is helpful, but definitely not required. Tools covered include Extended Events and Query Store, so this session is ideal for those of you running current versions of SQL Server and/or planning to upgrade, but options for earlier versions will also be provided.

Expect to walk away with practical knowledge you can apply to your environment once you convince your team what needs to be done!

2:44 Workload vs. individual queries
6:38 Common performance problems in SQL Server
9:30 Tools used for troubleshooting query performance
13:21 Troubleshooting queries that take too long to execute
19:25 Troubleshooting queries that execute frequently (with demo)
36:25 Troubleshooting queries that consume a lot of resources
41:04 Troubleshooting queries with variability in performance (with demo)
56:18 Summary and takeaways