SQL Server Sleuthing: Unmasking SQL Server Performance Issues

Description

Dive into SQL Server performance like a true data detective. Learn how to analyze query plans, uncover hidden bottlenecks, and chase down the real culprits behind slow-running queries. We’ll break down key performance metrics, decode what they actually mean in the real world, and show you how to turn chaos into clarity. By the end, you’ll have the tools and confidence to keep your SQL Server environment running fast, stable, and drama-free. Join us for “SQL Unmasked” and start solving performance mysteries the smart (and fun) way.

Key Takeaways

• Monica Morehouse (Rathbun)
• 2. Resize & crop so the key part
• IMPORTANT: Only resize photos
• maintain proportions (never have
• Use that to get the HEIGHT you
• bottom of the slide. Use CROP

My Notes

Action Items

• [ ]

Resources & Links

Slides

📥 Download Slides

SQL Server Sleuthing:
Unmasking SQL Server
Performance Issues
Monica Morehouse (Rathbun)
Consultant, Denny Cherry and Associates Consulting
1. Drag & drop your photo onto
the slide.
2. Resize & crop so the key part
of the photo will show up
here. You will want the
picture to go to ALL THE WAY
to the top, bottom, and left
side. On the right, position it
so it only just barely covers
the red line.
IMPORTANT: Only resize photos
using the CORNER SQUARE to
maintain proportions (never have
distorted photos in your slides).
Use that to get the HEIGHT you
need (so it touches the top and
bottom of the slide. Use CROP
(double click the photo and it will
show up under “picture format”
tab) to get the photo to the
WIDTH you need.
Presentation Rules
Always Ask Questions
Interrupt me
This is a two-way conversation
let’s learn from each other’s
experiences
THE CRIME
SCENE
DON’T GUESS INVESTIGATE!!!!
Symptoms are not root causes
Collect evidence before making changes
Performance tuning is data-driven
THE DETECTIVE MINSET
Users say: The Database is slow?
Code
Release
Time Outs
CPU
SPIKES
Blocking
WHEN PERFROMANCE GOES WRONG
Query Store – historical evidence
Execution Plans – how SQL thinks
Wait Stats – what SQL is waiting on
DMVs – real-time insight
Extended Events – deep diagnostics
Your Detective Toolkit
DISCOVERING THE CLUES
Glenn Berry’s Diagnostic Scripts –
A treasure trove of checks across performance,
memory, I/O, and more.
DISCOVERING THE CLUES
sp_whoisactive (Adam Machanic) –
Like Scooby’s nose, it tells you
exactly what’s running right now
and where the slowdown is.
DISCOVERING THE CLUES
Paul Randal’s Wait Stats Library –
Velma’s encyclopedia of villains.
This library helps you decode what each
wait type really means, so you know
whether it’s a red herring or the
real culprit.
DISCOVERING THE CLUES
Query Store –
A built-in case file of past queries, letting you compare
before-and-after plans.
WHICH TOOL DO I USE? …….IT DEPENDS
What problem are you trying to solve?
Best metrics, at the least cost
Time needed to
analyze the data
FIND CLUES-
DON’T FORGET THE ERROR LOGS!
Example:
Error 9002: “The transaction log for database is full due to
LOG_BACKUP.”
Sounds simple, right? Just back up the log and you’re
done. But here’s the trick—sometimes the real villain isn’t
missing backups at all. It could be:
COMMON
PERFORMANCE
CULPRITS
DON’T OVER COMPLICATE
Parameter
sniffing
Outdated
statistics
Missing or
ineffective
indexes
Implicit
conversions
TempDB
contention
Blocking &
long
transactions
Compliation
Timeouts
Memory
Grants
Pending
TURNING
CHAOS INTO
CLARITY
Query Store: Your
Security Camera
Footage
Top resource-consuming queries
Plan regressions
Runtime statistics over time
Compare before and after changes
Plan forcing (when appropriate)
QUERY STORE
FOR TUNING
REGRESSED QUERIES
Plans that have gotten WORSE over a time period
OVERALL RESOURCE CONSUMPTION
OVERALL RESOURCE CONSUMPTION
OVERALL RESOURCE CONSUMPTION
EXECUTION
PLANS
Execution Plans: Reading the Evidence
Execution Plans: Reading the Evidence
Estimated vs Actual plans
Large memory grants
Key lookups & scans
Sorts & hash matches
Spills and warnings
INDEXES
LAST SEEN: NEVER ….IMPACT COULD BE
SUBSTANTIAL
HAVE YOU SEEN ME?
Impact: 99.2157
Table: Users
Operator Cost: 100%
Name: Names Matter
CAUTION! CONSIDER THEM ARMED & DANGEROUS – DON’T JUST ADD THEM
WAIT
STATISTICS:
Understanding
the Motive
WHO’S HOGGING THE HALLWAY?
Wait Statistics: Understanding the Motive
CPU pressure (SOS_SCHEDULER_YIELD)
IO pressure (PAGEIOLATCH_)
Locking (LCK_M_)
Memory pressure (RESOURCE_SEMAPHORE)
TempDB contention (PAGELATCH_)
HOW LONG HAVE
THEY BEEN
STUCK?
WAIT QUERIES
TOP 10 WAITS
GLENN BERRY QUERY #38
BLOCKING
HEY!
WHO’S HOLDING UP THE
LINE?
BLOCKING
Identify Blocking
sp_WhoIsActive @find_block_leaders =
Lock Concurrency
sp_who2
Sys.dm_exec_requests
Sys.dm_os_waiting_task
SSMS Activity Monitor
SSMS Reports
SQL Profiler
xEvents
One SPID holds a lock on resources
while another SPID attempts to
acquire the same resources
KILL SPID 58
1;
Great Reference by Andy Mallon
https://am2.co/2017/10/finding-leader-blocker/
WHAT NOT TO
DO
What NOT To Do
Rebuild all indexes blindly
Update all stats daily without reason
Change MAXDOP without evidence
Throw hardware at bad query design
SQL
UNMASKED
Building a Drama-Free SQL Environment
Enable and monitor Query Store
Baseline performance
Monitor trends, not single incidents
Current Compatibility level
Automate health checks where possible
Performance isn’t magic
It’s measurable
It’s diagnosable
It’s solvable
Turning Chaos into Clarity
How was
the session?
Complete Session Surveys in
for your chance to WIN
PRIZES!
Monica Rathbun
MRathbun@sqlespresso.com
@SQLEspresso
sqlespresso.com
/in/sqlespresso