Brent & Jeremiah are coming to Pittsburgh on October 2-3, 2015. We’re doing a one-day pre-con, and then sticking around to present at SQLSaturday Pittsburgh too.
Developer’s Guide to SQL Server Performance – Live!
You’re stuck with a database server that’s not going fast enough. You’ve got a hunch that the biggest bottleneck is inside the database server somewhere, but where? In this one-day class, you’ll learn how to use powerful scripts to identify which queries are killing your server, what parts of the database server are holding you back, how to tackle indexing improvements, and how to identify query anti-patterns.
In one day of training with Brent Ozar and Jeremiah Peschka, you’ll learn:
- How wait stats tell you where to focus your tuning
- How the plan cache shows you which queries are the worst
- How to make fast improvements by picking the right indexes
- How to identify and fix the most common query anti-patterns
This is a pre-con before SQLSaturday Pittsburgh. If you haven’t attended a pre-con before, there’s a few things to be aware of. The session will be held at:
Pittsburgh Technical Institute
111 McKee Road
Oakdale, PA 15071
Lunch is included with your admission, but it will not include hands-on lab exercises or copies of the slides. This is how we keep SQLSaturday pre-con costs low – these SQLSaturday pre-cons are the most cost-effective way to get a day of training from us.
Use coupon code EarlyBirdPittsburgh to save $50 for registrations this month (May) – see you there!
You want your SQL Server VM to go as fast as possible, but you don’t have a lot of time or budget money, and you’re not allowed to reinstall it or upgrade. Good news – we’ve got a webcast for you.
In part 1, my demo-focused session will explain the VM-friendly performance features of SQL Server 2012, 2014, and even the next version, 2016.
You’ll learn how to measure a running application’s performance, and then simply by turning a few knobs and switches, you’ll see how performance improves. Microsoft Certified Master Brent Ozar will explain which features are right for your workloads, plus give you resources you can use when you get back to the office to go faster and learn more.
In part 2, Veeam will explain how to get fast transaction-level recovery of SQL databases, including agentless transaction log backup and replay, so you can restore your SQL databases to a precise point in time and achieve low recovery time and point objectives (RTPO™). Learn more at Veeam Explorers™.
This session is for sysadmins and DBAs running production workloads in VMware, Hyper-V, and Xen, whose end users are unhappy with performance.
Yesterday, Microsoft announced availability of Service Pack 1, saying:
As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments via the download links below.
Yeah, about that commitment to software excellence.
This morning, the download is gone:
Notice: The SQL SSIS team has found an issue with SP1 installation if SSIS catalog is present in the SQL Server instance.They are currently investigating this issue including possible workarounds and fixes.
Oof – the term “possible workarounds and fixes” doesn’t sound good for those who jumped in and applied the patch. A commenter on the MS Data Platform Insider blog reported that it
hosed the master database broke the instance in a way suspiciously similar to a similar bug in SQL Server 2012 SP2.
(And jeez, what is it with service packs lately? Remember the SQL 2012 SP1 100% CPU issue? I’m starting to think you’re safer with cumulative updates than with service packs.)
Remember, kids, don’t rush into patching. If your servers are mission critical, test in your staging environment first – staging is the DBA’s development. (No, your development environment isn’t staging – because your developers make their living in the dev environment, and if you broke that with SP1 yesterday, you’ll be slaving away today to get your dev instance back up and running.)
Update: the fix is in. If you applied SP1, follow the instructions in this StackExchange post.
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.