When I first got started working with SQL Server, I assumed it was flawless because it came in a sealed box from a ginormous corporation. I figured they'd tested every edge case, and that there was no way things were gonna go sideways. These days, even though I read CU KB articles and giggle about…
In the last episode, while talking about the basics of executing queries, I said that SQL Server caches execution plans in memory, reusing them whenever the same query gets executed again. I've also talked about the dynamic management views (DMVs) that let you query what's in SQL Server's memory. Let's put those two things together and find out what queries have been running on the server lately.
You've got one SQL Server hosting a bunch of databases, and performance is bad.
Which database should you focus on first?
Everybody tells you to capture baselines - but what exactly are you supposed to capture? Which Perfmon counters matter? How do you track which data files are growing, and which ones are slow? How can you track which queries are using the most resources? All you have to do is install the free, open source…
If you're a graduate of my free How to Think Like the SQL Server Engine course - and you'd better be, dear reader - then you're vaguely familiar with DBCC SHOW_STATISTICS. It's a command that shows you the contents of a statistics histogram. When I'm doing the first two parts of the D.E.A.T.H. Method - (D)eduplicating identical…
When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon? During a live emergency, start with sp_WhoIsActive. Adam Machanic's excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it's way more powerful. The documentation is extensive -…
sp_BlitzIndex gives your indexes a psychiatrist-style evaluation. It's a lot like a real psychiatrist: it's just flagging behaviors, and there's not necessarily anything wrong with being a hoarder, or a workaholic, or a furry. They're just behavioral traits. Let your freak flag fly. One of the warnings is "Aggressive Indexes," which means sys.dm_db_operational_stats reports minutes of…
Clientele
Most of out customers are on slightly (ahem) older (AHEM) hardware, and they have big questions:
How do we know what kind of hardware we need?
How do we load test it?
How do we know what to change to make it better?
Deadlocks are hard - especially when there are a lot of them. When there are dozens or more, looking at each graph is a pain.
sp_BlitzLock gives you a slice-and-dice analysis of which tables, indexes, and queries are involved in most of your deadlocks so you can quickly get to the root cause.
In the First Responder Kit's Power BI Dashboard, there's a Quick Diagnosis results area where sp_BlitzFirst takes a guess at why your SQL Server was slow - or in this case, why it suddenly got faster:
In this case, wait times were bad, and then all of a sudden the wait times just disappeared, and the line graph for Batch Requests/sec takes off. We're doing way more queries, but waiting less times. Why?
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.
When you're analyzing SQL Server performance, one of the best places to start is wait stats. What's your SQL Server waiting on?
sp_BlitzFirst makes it really easy to trend this data over time for free with all currently supported versions of SQL Server (2008+). Just set up an Agent job to run this every 15 minutes:
[crayon-6aabb332e59a8680411509/]
Your client or boss just said, "Find out why this SQL Server is slow." You've already done a free SQL Server health check, and now you wanna investigate deeper.
Step 1: Download & run sp_BlitzFirst.
Start with wait stats, which tell you what the SQL Server has been waiting on since the service was last started, or since someone cleared wait stats with DBCC SQLPERF, whichever is more recent.
Your boss or client came to you and said, "Give me a quick health check on this SQL Server." Step 1: Download & run sp_Blitz. Go to our download page and get our First Responder Kit. There's a bunch of scripts and white papers in there, but the one to start with is sp_Blitz.sql. Open…
StackExchange users often ask, "Why is my server slow?" Here's a few tips to get better, faster answers. Server information You may have already included server information in your question, but it may not be sufficient. Run sp_Blitz to output a lot of server information fast. [crayon-6aabb332e77ba004631767/] Click on the XML mumbo jumbo and post the…
In our Faux PaaS project, we need a backup plan - or rather, a restore plan.
On each SQL Server instance, clients can create as many databases as they want, anytime they want, with no human intervention. We need those databases covered by disaster recovery as quickly as practical.
If one of your SQL Servers went down at some point over the last week:
What's the most data you could have lost?
What's the longest you would have been down for?
You use Ola Hallengren's maintenance scripts to do your database backups on SQL Server 2016 or newer, and you want to do things like:
Restore the most recent database backup
Test it for corruption
Initialize restores to get ready for log shipping or replication
When you're tuning queries
It's sort of a relief when the first time you get your hands on it, you get the plan and there's a missing index request. Even if it's not a super high-value one, something in there is crying for help. Where there's smoke, there's a bingo parlor.
But does adding missing indexes from requests always make things better?
sp_Blitz is our open source free SQL Server health check script.
From a really high level, here's what it does:
