Ever wonder how somebody else does it? Watch over my shoulder for this beautifully recorded one-hour session at SQLRally Nordic in Copenhagen from a couple weeks ago:
The resources, scripts, and my Be Creepy process are all free too. Enjoy, and check out all of the session recordings for even more free learnin’.
Sometimes we pile just a few too many duties onto our servers. They start to become a clown car – the classic joke where dozens of clowns come pouring out of an impossibly small car. (The physics behind it are actually really interesting.)
So how can you tell if your SQL Server has crossed the line into clown car territory? It’s easy:
10. You have both production and development databases in it.
9. Everyone runs SSMS and Visual Studio by remote desktopping into the server.
8. You’ve enabled remote desktop services for more people to log in simultaneously.
7. It has the engine, SSRS, SSAS, SSIS all installed and running, but less than 32GB RAM.
6. It has multiple instances of each of those services running. (Bonus points for different versions.)
5. Application servers point to a file share hosted by this database server.
4. You’re monitoring it, and the monitoring software’s repository also lives on the same server you’re monitoring.
3. You built a spreadsheet to track your affinity masking, Resource Governor, and max memory settings.
2. When you say “the server,” you don’t have to clarify because everyone knows it’s your only one.
1. You’re reading this blog on the server right now.
Kendra says: If you care about performance, you’ve got to start pulling clowns out of that tiny car.
When I ask you how fast your car is, there’s a two common metrics for answers:
- How fast it can go
- How much horsepower or torque its engine can deliver
If I ask you how hard your car is working right now, you’d probably answer with:
- Its current speed (in MPH or KPH)
- Its current engine workload (in RPM)
Conveniently, these two numbers are shown front and center on most car dashboards:
For SQL Server, those numbers are:
Current speed: Batch Requests per Second – the number of queries your server is currently handling. It’s available via Perfmon counter, and it’s on the dashboard of most monitoring software.
Wait Time per Core per Second – wait stats is the technique of measuring how much SQL Server is waiting on. You can’t tune wait stats using percentages – instead, you have to look at the total amount of time involved.
Compare these two five-second moments in the life of a SQL Server:
It’s clear that in Workload A, nothing is going on. Sure, 100% of our waits are on storage, but we don’t really have a storage problem. If Workload A was a car, it would be rolling along at 1mph, waiting for the driver to hit the gas pedal.
In workload B, there’s much more going on – but do we have a performance problem yet? That’s not quite as clear. To get the real picture, I have to tell you how many cores the server has.
Let’s Use Wait Time Per Core Per Second
Waits over time don’t really give you a clear picture – you have to divide the workload by the number of cores you have. Let’s say they’re both running on a VM with 8 virtual cores:
The VM on the left isn’t doing any work. It’s coasting.
On the VM on the right, for every second on the clock, each of its cores is spending .4 seconds (400 milliseconds) waiting on something (disk, memory, locks, etc.) While that might sound significant at first, it’s still really not doing any hard work. It’s not unusual for each core to spend several seconds per second waiting on different resources – and that’s fine, because SQL Server’s schedulers are good at switching over and running other queries that don’t need to wait.
For example, say you fire off a big SELECT query that scans a huge table, and we have to go get a lot of data from disk to accomplish it. That query’s tasks can wait for dozens (or hundreds or thousands) of milliseconds while other queries get their work done.
That’s why to measure SQL Server workloads, I’m proposing a new metric: Wait Time per Core per Second. That one metric, by itself, is a lot like the tachometer on a car’s dashboard. It’s not a measurement of how powerful your car is, but it does tell you how hard your car is working at this moment in time.
sp_AskBrent® now gives you this metric.
We’ve added new key metrics to the default output, including Batch Requests per Second, and Wait Time per Core per Second:
Note the new output down around priority 250-251 – it helps you get a fast idea of whether the SQL Server is working hard right now, or hardly working.
You can download our First Responder Kit with sp_AskBrent® now, and in our training classes, I show you more details on how I use it to triage real-world performance emergencies.
When your SQL Server is having performance problems, sp_AskBrent® checks a bunch of common trouble spots. In this week’s new version, it also checks sys.dm_os_ring_buffers for the most recent CPU utilization report from SQL Server and returns it.
Here’s what it looks like when the server isn’t under load – note that “No Problems Found” means the rest of the alerts are just information about the SQL Server:
And here’s a heavily loaded server:
I haven’t been blogging about this tool much, but I’ve become quite proud of it over the last few months.
@Seconds = 5 – you can run it for longer periods like 60 seconds to see the server’s health during that time range. It’s really useful when you’re tuning a query or doing a presentation demo – fire it off on a 60-second span, go run your query or workload in another window, and then come back to sp_AskBrent to see what the effects were. Especially useful when combined with…
@ExpertMode = 1 – returns more result tables including wait stats, Perfmon counters, and my personal favorite, file stats – which shows you how much your workload read and wrote from your data/log files and TempDB:
@OutputDatabaseName, @OutputSchemaName, @OutputTableName – you can log results to tables. Some folks are running sp_AskBrent® every 5 minutes in a SQL Agent job, dumping the data to a table, so that they can go back in time and trend what happened on the server. This got better in last month’s v13, which also added the ability to output the file/Perfmon/wait stats details to individual tables too.
To get started, download our First Responder Kit with our latest scripts, posters, and e-books.
Hey SQL Server DBAs — we’re hiring!
Here’s a quick five question quiz. Give yourself one point for every ‘yes':
- Have you been a database administrator for a few years?
- Does planning how to keep an application reliable and safe from disasters sound like fun?
- Do you love helping people make SQL Server go faster?
- Would you like to have a cartoon character of yourself?
- Can you see yourself dropping into our Office Hours sessions occasionally to help people for free?
If you got five points, you just might be the next Brent Ozar Unlimited employee!
Here’s what to do next:
Read more about the job here to find out what we’re looking for and what benefits we offer. Then tell us about you! We plan to accept applications through Friday, March 6, but don’t wait too long to submit — life moves fast sometimes.
Update March 7 – applications are closed, and we’re interviewing candidates. Thanks!
To figure it out, we have to define what the words senior and DBA even mean. I explain in this video.
To follow along, print this image out:
Kendra says: The part of this that resonated with me most was how to work with your existing management to show that you’ve been growing your skills – I wish I’d heard that back when I was struggling to figure out how to be a Senior DBA!
Jeremiah says: The best part of this was the reminder that I don’t need to be a specialist everywhere. If I had kept this advice in mind when I was struggling to become a Senior DBA.
Jes’s thoughts: my favorite advice from this video is to focus on 1-2 things to learn in the next 6-12 months, and make a plan for that. When I started, I assumed I’d be able to learn everything at the same rate – I know now that isn’t true. In 2015, I plan to learn more about Extended Events and virtualizing with VMware.
Doug says: Love it. It’s all true in my experience too — you can’t become a specialist without focus, you can’t focus without a plan, and you won’t get noticed for a promotion (or a new job) if you aren’t really good at a few things. No one in the SQL Server world ever made for themselves a reputation for being good at everything.
And the next step after finishing the video and building your learning plan: learn to say no.
Over the years, I’ve come up with some rather “interesting” answers to SQL Server problems including:
- Warming up SQL Server’s buffer pool by selecting all the data from all the tables
- Creating indexes with triggers
- Adding nonclustered indexes to clustered columnstore indexes
- Compressing backups the hard way
- Putting databases on a RAM drive
In this 20-minute video, I’ll explain what drove me to these crazy solutions, and it’ll be up to you to decide whether they’re awesome or awful.
For questions & answers about these, tune in to our Tuesday webcast.
SQL Server 2012 introduced AlwaysOn Availability Groups, a way to achieve high availability, disaster recovery, and scale-out reads. SQL 2014 brought some improvements around higher uptime and more scale-out, and all signs point to continued improvements in the next version of SQL Server, too. (I love it when Microsoft brings out features like this and continues to invest in them over time.)
A lot of the emails I get start with, “I’d like you to help me implement AlwaysOn AGs,” but it’s funny – most of the projects don’t end up actually deploying AGs. There’s a few barriers to adoption, and even when you’ve built an Availability Group, management can be a little tricky. Don’t get me wrong – I love the feature – but it comes with some surprises.
Rather than me prejudicing you, I’ll just put it out there as a question:
How would you change AlwaysOn Availability Groups?
Leave your answer in the comments. (And yes, Microsoft is watching.) Bonus points if you link to your Connect request.
We give away a lot of stuff – scripts, setup checklists, e-books, posters, you name it.
But we kept hearing a theme from folks: “Wow, I’ve seen one of your tools before, but I had no idea there were so many others!” In order to get everything, they had to go all over the place in our site.
To fix that, we’ve got a new easy button: our free SQL Server download pack. Now when you get anything, you’ll get everything in a single zip file, plus get email notifications whenever there’s a new version.
Enjoy, and hope we make your job suck just a little less.