Capturing active work and short-term runtime evidence during a problem.
25 associated posts23 primary posts
In this live stream recording, I'll show you how to use the latest features of sp_BlitzCache to rapidly improve performance on an existing server. I'll show you how to find your server's top bottleneck, find the queries causing that bottleneck, and then demonstrate how to get advice from AI on how to tune them in seconds.
When you turn on last actual plans in SQL Server 2019 and newer:
[crayon-6a70633408237835355775/]
I took a break from unpacking my SQL Saturday San Diego bags and re-packing for a Boston wedding long enough to take your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/Z0GSjyK2bps?si=5u3bMm8MYFkHViBD&t=29
Post your questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me answer.
https://youtu.be/V5illj_KOJg
Post your questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me cover. In today's episode, I sit down on a quiet hotel balcony and talk through a bunch of interesting questions:
https://www.youtube.com/watch?v=VJAwgkhq_Z0
I drove up the volcano Hekla in central Iceland - also known as the Gateway to Hell - and parked the Defender here:
While my travel companions hiked up higher (and took that photo), I set up the tripod and camera, then went through your highly-upvoted questions from https://pollgab.com/room/brento.
Not the one you like the least, or the one that requires the most work.
What's the one that you can't use because it's just flat out busted?
You've got a stored procedure that calls other stored procedures that build dynamic SQL that call other functions, and there's so much calling going on that you would like to phone a friend.
Your query plans look like piles of spaghetti, and when you zoom in, you just see more tiny piles of tiny spaghetti.
When you have a stored procedure or application query that has multiple queries inside of it, how can you tell which query to focus on?
For starters, do NOT believe the percentages you see in execution plans.
For years, I hated troubleshooting servers with high CPU usage caused by queries constantly asking for new execution plans. Hated it. SQL Server just doesn't make it easy to find queries with recompile hints.
Then Erik Darling's sp_HumanEvents came along.
I happened to see this DBA.StackExchange.com question about killing a big delete, and I really felt for the admin involved: Four days ago a user ran the command below on a table with 400,000,000 rows. It's still running and the log file is increasing in size. Hoo boy. When you kill a query, it's hard…
Queries are running when you're not around. They're wreaking havoc - maybe they're filling up your TempDB, or causing blocking, or flushing your buffer pool out.
When I wanna see what queries are running, I run sp_BlitzWho:
Way Back When I blogged about the new USE HINT that logs query plans that use it to a new XE session. The use hint and session both share the same name -- query_plan_profile. There are currently some rather unfortunate limitations to the event in the GUI. Right now, there's no query plan tab so you…
Recent Updates
To SQL Server 2016 and 2017 introduced a new USE HINT that lets you direct an actual execution plan to a new Extended Event, called query_plan_profile.
The hint by itself doesn't do anything, and the XE by itself doesn't do anything. You need to have both.
This week, Brent, Erik, Tara, and Richie discuss index maintenance, stats updates, the acquisition of GitHub, using linked servers to move tables across, talking to developers as a DBA, the best cloud option, setting the number of tempdb files, and their favorite SQL Server features.
Here’s the video on YouTube:
Where have you been all my life?
Whenever I'm teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills.
They're not mutually exclusive -- some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill to tempdb.
sp_BlitzWho is our open source replacement for sp_who and sp_who2. It has all kinds of really neat-o outputs like how many degrees of parallelism the query is using, how much memory it's been granted, how long it's been waiting for memory grants, and much more.
If you're on SQL Server 2016 SP1 or newer, it can show you a query's live execution plan from sys.dm_exec_query_statistics_xml.
You're a data professional working with (or at least applying to work with) a company using the StackOverflow database (I'm using the March 2016 version today). Your users are complaining that this stored procedure is slow:
They didn't give you parameter 26837 - I'm just giving you that so you can see an execution plan.
SQL Server 2016 is adding some really awesome stuff Not the least of which is a ton of super cool information in execution plans, and an Extended Events session to expose the same information for all queries. The details are detailed in detail over on this blog post from MS. For those of you with…
I've written a terrible query. It might be the worst query in the world. Sure, there are other queries that may be slower, but this query is terrible and elegantly short.
The query is gloriously bad for a two reasons:
