Kendra Little

17 December 2020

Performance tuning with SQL Monitor: tempdb usage, parameter sniffing, and blocking chains

I got to work with some of my favorite folks from the SQL Monitor team last week to build some new workload patterns for our online monitoring demo site. Here are three videos sharing some highlights of performance tuning features in SQL Monitor.

See what’s happening in tempdb with SQL Monitor (5 minutes)

SQL Monitor now shows you insights on what’s using up space in tempdb. Is it the version store? Internal objects like hash joins or sorts? User objects like temp tables? Which sessions are involved, and how are your tempdb files being used? Join me to look over an example workload.

Chapters:

• 00:00 Navigating to the tempdb monitoring data
• 00:40 The overview graph of tempdb and types of usage you can see
• 02:14 Top sessions using tempdb and their related queries
• 03:00 Looking at the program names for tempdb usage
• 03:19 Finding which databases are using the most Version Store in tempdb
• 03:50 Viewing tempdb usage by file

Find and Interpret Blocking Chains in SQL Monitor (5 minutes)

Blocking is like a traffic pileup: when it occurs, it’s helpful to find the “lead blocker” and then step through the chain of who blocked who. Join me to step through an example blocking chain and give tips on how to find useful information in SQL Monitor.

Chapters:

• 00:00 Navigating directly to blocking processes
• 00:45 Viewing the head blocker
• 01:18 Getting more information from the query plan for the lead blocker
• 02:50 Stepping through who is blocking who in the chain
• 03:35 What to think about when resolving blocking situations (instead of NOLOCK)

Finding Unstable Query Plans in SQL Monitor – Also known as “Bad” Parameter Sniffing (11 minutes)

A common but tricky issue to identify in SQL Server is when something called “parameter sniffing” goes bad. In this video, I show how to identify if a query has an unstable execution plan in SQL Monitor, where to find the parameters which were “sniffed” for each query plan, and give tips on where to start to resolve the issue.

Chapters:

• 00:00 High level overview of parameter sniffing
• 00:40 Navigating to top queries on the instance and sorting by logical reads
• 01:20 Finding the query fragment and calling stored procedure/function/trigger (if applicable)
• 01:50 Reading the query history graph and comparing plan changes and performance
• 02:53 Examining the “fast” plan and identifying the compiled (“sniffed”) parameters
• 04:10 A warning in the plan about indexes, and how to interpret it
• 05:24 Examining the “slow” plan and identifying it’s compiled (“sniffed”) parameters
• 06:14 Comparing the IO behavior in the slow plan
• 07:13 Summing up why the “slow” plan is behaving differently
• 07:45 Digging into “output” columns in the slow plan to confirm index changes that might help
• 08:35 Big picture description of parameter sniffing and options to stabilize plans when it goes “bad”