Blog

Fake moustaches, barbecue, and SQL Server.

Must be Dell DBA Days:

Join us live as we performance tune – and break – SQL Servers:

  • Thursday Morning: Finding Your Slow SQL Server’s Bottlenecks in Record Time with Wait Stats
  • Thursday Afternoon: How to Prove Hardware is the Problem
  • Friday Morning: SQL Server High Availability, Disaster Recovery, and Licensing Explained
  • Friday Afternoon: Watch SQL Server Break and Explode

Register now and join in.

New and Improved: Relaunching Our Online Training – With a Daily Deals Sale

About two years ago, we launched our online training classes, and you loved ’em. We sold thousands and it became a pretty big part of our business.

The New Hotness

The New Hotness

We’ve launched our all-new online training video store with features like:

  • Comments – so you can ask questions on each video module
  • More powerful e-commerce – VAT, group discounts, Paypal, PDF receipts
  • HTML-based modules – so we can include code samples, images, and posts
  • Better analytics – to understand which modules you like, and which ones to tweak
  • Course reviews – we’ve had thousands of buyers, and it’s time to let them speak
  • Better coupons – including getting a discount for liking us or tweeting (and boy, Black Friday is gonna be fun this year)

To celebrate the launch, we’re running a set of daily deals:

  • Today (Monday 8/24) – The Everything Bundle: $599 – a bundle of all of our online video courses available for $1,553 just $599. Only 50 available. Gone!
  • Tues (8/25) – Chicago SQL Server Performance Troubleshooting class seats for $3,195 just $995. Only 3 available. Sold out!
  • Weds (8/26) – $99 SQLSaturday Pittsburgh Pre-Con seats – normally $199, this one-day Developer’s Guide to SQL Server Performance is half off. 25 available. Gone!
  • Thurs (8/27) – Mystery Pocket Bundles – we’ll post a handful of $29-$99 deals with secret unknown contents. On sale now. Sold out!
  • Fri (8/28) – all $299 courses 50% off – for the first 50 buyers of each course.

Go check it out!

Team Photos from the Company Retreat

For this year’s company retreat at the Oregon coast, we asked awesome photographer Justin Bailie to shoot updated team pictures for the site and this year’s Christmas cards. Enjoy:

Brent_Ozar_Unlimited_Team

Erik_Darling_Jessica_Connors Erik_Darling_Jessica_Connors_Doug_Lane

Brent_Ozar_Kendra_Little Kendra_Little_and_Mister_Little

Jeremiah_Peschka_Kendra_Little Jeremiah_Peschka_Brent_Ozar

Doug_Lane_Kendra_Little Erik_Darling

Brent_Ozar_Unlimited_Team_Goofy

Back up every database on the production server. No exceptions.

Before our consulting engagements start, we send clients a couple of queries to run. One of them outputs a list of databases, and the last backup date for each.

You wouldn’t believe how often this query produces surprises like:

  • “Wait – your script must be wrong. I swear we’re backing things up. We’ll fix that.”
  • “Whoa – it turns out we only run full backups monthly, and differentials weekly, but if we add a new database in the middle of the month, it doesn’t get backed up for weeks. We’ll fix that.”
  • “It’s okay – we don’t need to back up that database because it doesn’t have anything useful.”

The last one is actually the scariest one because often we discover real production data in databases that aren’t supposed to be used.

For example, at one client, there were two databases: AppV3 and AppV2. AppV2 hadn’t been backed up in months, and the architect assured me it was a really old version of the app that was just kept around for checking data every now and then.

However, as we started the engagement, we discovered that the login queries were still hitting AppV2 and storing data in there about user profiles. If they’d have lost that database, their users would have been destroyed, and their app would be down.

If the database is important enough to live on the production database server, then it needs to be considered a production-quality database, and it needs to get backed up. Full stop. Don’t gamble that no one is putting data in it.

How Do You Manage DBAs? Part 2: Training Budgets

Your DBA wants to get better at what they do, but … they don’t know exactly what it is they do, or how to get the business to pay for it. Here’s how.

My I Need to Learn List - man, where do I even begin - by Luis Llerena

My I Need to Learn List – man, where do I even begin – by Luis Llerena

Encourage them to start an “I Need to Learn” List – maybe as a text file, in a notebook, or in a shared spreadsheet.

When they’re working on help desk tickets, server outages, or architectures, and they want to learn something, they open up the list and add:

  • The problem they’re trying to solve (and be specific here)
  • Who needs them to solve it (departments, VPs, staff names)
  • How much time or money they spent working on the solution

That last one is important because your team is probably always going to find a solution even if they don’t understand the problem well. They’re going to reboot the box to make the problem go away, or apply some script from an Internet stranger, and they’re not going to feel terribly confident about what they just did. As far as the business is concerned, Mission Accomplished for now – and that’s fine. The “I Need to Learn” List is about knowing where these gaps were and what they cost at the time.

The DBA is never allowed to add a line “because I’m curious” or “because it sounds awesome” or “because all the cool kids are doing it.” This is about real business pains, because that’s where money comes from.

How to Turn the “I Need to Learn” List Into Money

Once a quarter, meet with your DBA and go through the list.

Look for a common theme. There’s probably one area of trouble that pops up over and over, like clusters or query tuning.

This forms your business case – you can say things like:

“This quarter, we’ve spent 35 hours of outage troubleshooting on the the sales team’s SharePoint cluster and the BI team’s data warehouse. We need to cut that troubleshooting time down, so I need $5k to send Ann to DBA training for that topic.”

“This quarter, Andy has spent 80 hours tuning slow queries for the developers. He’s run out of easy buttons, and he doesn’t feel confident with the advanced problems they’re hitting now. We need to invest $5k in training him to make that tuning time more effective going forward.

This suddenly makes it easier for management to open wallets, and you can even go to these other departments to get them to fork out training money as well. (Hey, look, I gotta be optimistic here.)

Microsoft SQL Server DBA Training Options

We’re lucky in the SQL Server community because we’ve got so many options. Here are the most popular ones – note that the price estimates include travel, but not human hourly time:

Blogs – free, but unorganized – you can learn a lot by regularly reading blogs, but they don’t help you start from zero and go to hero in a short amount of time. The articles aren’t focused on a clear progression. This option is best for DBAs who have a long time to learn and no urgent requirements.

Books – $50, but slow and not interactive – for some reason, people seem to think that buying a book will make them an expert. I’ve tried keeping them on my bookshelf for months, but that just doesn’t do the trick. You have to allocate a lot of time to actually read the book, and most human beings can’t read a technical book start to finish in one week while actually absorbing the material. You read some, get up, do other things, come back, read some more, absorb some of it, and so forth. Technical books often take weeks to fully absorb.

Training videos – $200-$300, ready when you are, but require focus – these cover a technical topic in a logical order, start to finish, broken up in easy-to-digest modules. You can spend 30-60 minutes a day absorbing a topic, tackle a few demos, and take a quiz to see how they’re progressing. The material is ready whenever the DBA is ready to start learning the topic, and you can learn it as fast as you can dedicate the time.

SQLSaturdays – $250-$500, but not often, and topics aren’t organized – these awesome one-day events take place all over the world, once a year in major cities. They’re run by volunteers, and often you can see great sessions – all free. Don’t just wait for them to occur in your city though – at this price, it can make sense to drive to a regional event. Thing is, the sessions are relatively short, and you can’t learn a topic start-to-finish at these events. Again, best for DBAs who have a long time to learn and no urgent requirements.

Pre-Conference Sessions – $500-$1,000 – not often, but fantastic deal – on the days before & after major conferences, speakers put on one-day classes at a bargain price (typically $200-$500). This is a good way to learn a topic in an organized way, although you can only go so deep in one day. You don’t have to attend the entire conference to buy a pre-con seat, which can sometimes make this a heck of a bargain. Conferences with pre-con and post-con sessions include the PASS Summit, SQL Intersection, SQLbits in the UK, and SQL Server Live 360.

Full Conferences – $2,000-$4,000, but topics aren’t organized – these are 2-4 days of material, broken up into 60-75 minute sessions, but the sessions are all over the map. You shouldn’t expect to learn a single subject start to finish here, but rather learn a variety of things about the current and next version of SQL Server. That list of conferences above is a good starting point.

Training classes – $3,000-$5,000, in-depth coverage of a topic – if you need to make a serious skills investment, set aside a week of your calendar, put someone else on the on-call duty, and go to a training class to focus for a week. This gives you the most interactive learning time because you can ask plenty of questions – to both the instructors and your fellow attendees – and maximizes your chance of fixing a serious gap on your “I Need to Learn” list.

Make the DBA Prove the Money was Well-Spent

No matter which training method you and the DBA choose together, give them a homework assignment.

They need to write a Book Report – summarize what they learned, in their own words, and how it relates to the pain points they wrote down in their “I Need to Learn” List. Have them talk about how they would have handled each of those past situations differently, knowing what they know now.

Telling them about this homework ahead of time is critical.

It keeps them focused on why you’re sending them to training – not to buddy around or get drunk with strangers, but to learn things to relieve real business pains.

And that’s how you get the training budget renewed.

Consulting Lines: “I have a hard stop at…”

This has grown to become one of my favorite consulting lines. I don’t use it often, but used judiciously, it’s a lifesaver. I use it a few different ways:

Using This Line at the Start of the Meeting

If you know in advance that the organizer (or some of the attendees) tend to ramble on or miss the agenda entirely, use this line right at the beginning, but do it quietly to the meeting organizer.

“Just to give you a heads up – I’ve got a hard stop today at 9:45. I need to leave early to prep for my next meeting. I’ll leave quietly so I don’t disrupt the meeting.”

The secret is the word “prep” – it tells the organizer you have work that you have to do to prepare, and that’s not negotiable. If you just say you need to make it to the next meeting, they’re gonna hold you as long as they possibly can.

It also doesn’t mean that your next meeting starts at 10:00 – you’re saying that you have prep work to do. Maybe the meeting actually starts at 10:30, or 11:00, or 11:30 – but you have work to do to prepare for it, and that’s why you have to bail.

However, this also means that you’re prepped for this meeting you’re attending now – and you’ve gotta be when you use this line. You need to have all your homework done and give prompt, accurate answers to any questions that come up. You want to show that you respected everyone else’s time too.

This line turns the organizer into a guardian looking out for your schedule. If the organizer is the problem, then it gives them fair warning that you’re going to bail. (But if the organizer is the problem, AND he’s your manager, then you’re kinda screwed.)

Using This Line During the Meeting

If the meeting’s going way off topic and it’s not relevant to you, this line gets a little harder to use:

“I hate to interrupt, but it’s 9:25, and I’ve got a hard stop today at 9:45. Are there things on the agenda that you need me for, before I leave at 9:45?”

Be warned: this is kind of a jerk move.

Scratch that – it’s totally a jerk move.

It’s like going nuclear. You only want to use this when things are way, way off topic, the meeting isn’t making progress, and there are other people in the room whose time is being wasted. (If you’re the only one whose time is being wasted, don’t even bother saying the line because you’ll get a bad reputation amongst your peers – just suck it up, wait until 9:55, and then quietly duck out. If anyone asks where you’re going, you can mumble the hard stop line, but that’s it.)

The Best Part: Your Family is a Hard Stop Too

I have a hard stop at 4:30PM for squeaky time.

I have a hard stop at 4:30PM for squeaky time.

If your kid’s got a baseball game after work, and you need to leave promptly at 4PM to make it, then your hard stop is 3:45PM. You just need to say, “I have a hard stop at HH:MM.”

The first time I used that line for personal reasons, I felt so dirty. I felt like I was lying, abusing the line, but it’s honestly true – I do have a hard stop at that time for commitments. I thought somebody would ask what was so urgent that I had to leave for.

Nobody does.

And it turns out, most of the time people are actually appreciative of you giving that heads-up warning when you need to leave, and they respect your time more.

Check out more of my consulting lines posts.

When Did My Azure SQL Database Server Restart?

In Azure SQL Database, no one can hear you scream query common system objects that tell you when a restart happened. You don’t get the access you need to sys.dm_os_sys_info, sys.dm_exec_sessions, sys.traces, or sys.databases.

The closest I’ve been able to get is to query sys.dm_os_wait_stats for several common wait types that seem to correspond with clock time – meaning, for every minute on the clock, we get about one minute of these waits.

If we subtract those waits from today’s date, we can get a rough idea of when the server restarted:

SELECT DATEADD(ms, AVG(-wait_time_ms), GETDATE()) AS approximate_restart_date
FROM sys.dm_os_wait_stats w
WHERE wait_type IN ('DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION','LAZYWRITER_SLEEP','LOGMGR_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_DISPATCHER_WAIT','XE_TIMER_EVENT')

I’m using an average here because each of those waits actually reports slightly different numbers as shown here:

How to get the Azure SQL Database restart date/time

How to get the Azure SQL Database restart date/time

Best I can tell, this SQL Server restarted on July 14th at around 3:09-3:16AM, and the average reports 2015-07-14 03:11:51.570.

Who cares when the server restarted? sp_AskBrent® cares – because folks have started asking me why their Azure SQL Database is slow, so I made it Azure-friendly. Now when you use the @Seconds = 0 parameter, you can see overall wait stats since server restart – just like your on-premise SQL Servers. (ASD also has sys.dm_db_wait_stats, but I like seeing what the server’s overall bottleneck is too.)

Consulting Lines: “Write this down, because I’m not going to write it.”

Before I became a consultant, I was on the other side of the conference room table – we would bring in consultants, and I’d be amazed at all the cool lines they whipped out. Now that I’m the one giving advice, I like sharing some of my favorite consulting lines that you can use in your own day-to-day work.

The Conversation

Let’s set the stage: I’m walking a client through the sp_Blitz® results on their production server. They called me in because they were having problems recovering from outages. We’ve found a few nasty surprises.

Me: “Alright, so everyone understands why Dr. Honeydew’s backup script was skipping all the user databases on Tuesdays and Fridays, and what we need to do to fix it, right?”

Beaker: “Yes, I’ll get Ola Hallengren’s maintenance scripts installed tonight.”

Me: “OK, great. Now, while we’re looking at the Agent jobs, what’s this Hourly FREEPROCCACHE one here?”

Beaker: “Oh no. Oh no. Ohhhh, no. I thought I’d gotten rid of that.”

Me: “So it sounds like you already understand why that’s a bad thing?”

Beaker: “Yes! I am so sorry – that was another one of Dr. Honeydew’s experiments, and I thought I’d removed it from every server.”

Me: “Alright, you write that task down now, because I’m not going to include it in your written findings.”

What That Line Does

This line establishes that you’re on their side – you’re not trying to document every tiny little detail of what someone is doing wrong. Nobody needs all of their dirty laundry aired out in public, or in a written document that’s often escalated up the chain or added into someone’s permanent personnel file.

Not everything needs to be on the itemized receipt.

Not everything needs to be on the itemized receipt.

When you’re delivering written findings, you need to focus on just the top relevant issues for their problem. You’re going to find other stuff – but stay focused on what they’re paying you for.

This technique works with code reviews, infrastructure audits, or anything where you’re doing a peer review of someone else’s work.

What Happens Next

Sometimes, they pick up a pen and start writing immediately because they’re ashamed of what you’ve found.

Other times – especially when there’s a group in the room – they need a little encouragement. Everyone just nods and expects you to go on. You have to stop, wait, and look around the room for who’s going to write. Sometimes you even have to ask: “OK, who’s going to write that down and take action on it?”

If you enjoyed that, I’ve got more of my favorite consulting lines.

Why Nobody Ever Patches Their SQL Servers

I saw a DBA.StackExchange.com question and had to laugh out loud:

“Is there a cool way of performing CU updates for SQL Server on hundreds of machines?”

Patching got me like

Patching got me like

No, and it has nothing to do with technology.

  • Which servers are mission-critical 24/7 and can’t go down?
  • Which servers can only be taken down in specific time windows?
  • Which servers have dependencies between each other, like database mirroring and AlwaysOn Availability Group replicas?
  • Which servers have automatic failover mechanisms (like clusters) where you can patch the standby node first, then fail over once, and patch the primary without having to fail back?
  • Which servers have vendor apps that required a specific hotfix that may not be included in the cumulative update you’re about to apply?
  • Which servers are running long batch jobs like data warehouse loads or backups that would take a long time to restart if you took it down in the middle of its operations?
  • Which servers have pending sp_configure changes that will take effect and surprise you when you restart?

Patching is hard work. Seriously.

Consulting Lines: “I’d be a bad consultant if I didn’t put that in writing.”

I have to deliver a lot of bad news. It’s pretty much my full time job. Nobody calls us when things are going well. (Well, they tweet us, because that’s free.)

Sometimes, that bad news is very dangerous – especially for the company as a whole, or its customers.

In today’s consulting line, I’m working with a group of developers on a performance issue. We’re using sp_BlitzCache™ to check out the top resource-intensive queries on their system, and figuring out how to make them go faster.

The Conversation

Me: “It looks like this query returns the customer’s email address, password, birth date, and address.”

Larry: “Yeah, it’s the profile page. We show them their account.”

Me: “So this is stored in the database, and it’s not encrypted.”

Larry: “Yeah.”

Me: “And we’re on the development server that everybody in the company has access to, right?”

Larry: “Everybody.”

Me: “Okay, let’s stop for a second. I have to cover my own butt. Gimme a few minutes to document this, and I’ll include it as part of your written findings. I know you didn’t call me in for a security review, but I’d be a bad consultant if I didn’t put that in writing.”

"What do you mean the whole world knows that my password is p0ll0?"

“What do you mean the whole world knows that my password is p0ll0?”

What That Line Does

Putting a big security risk in writing is a career-limiting move.

This line helps you defuse that bomb.

Nobody wants to have something like this in writing – especially a written document that gets forwarded up the management chain. They’re going to want you to stop writing and just ignore it, but this line establishes that it’s not really your call to make. Nobody wants to be bad at their job – but ignoring huge, dangerous risks would make you bad at your job.

What Happens Next: The Easy Way

Larry: “OK, cool.”

Sometimes the rest of the team isn’t happy about the risk either, and they’re dying to have someone else champion the cause.

And you know what’s funny? The developers and sysadmins are often quietly high-fiving each other while you’re writing it down. But if there’s a manager in the room…

What Happens Next: The Hard Way

Moe the Manager: “Wait, that’s not why we brought you here. Let’s focus on the problem at hand.”

Me: “I totally understand. This will only take a few moments for me to document, and we can work a few extra minutes at the end of the day. I won’t bill you at all for this. I just have to write this down to cover my butt.”

Moe the Manager: “I can’t really have something like this in writing.”

Me: “Believe me, I totally understand why you’d say that. If I was in your shoes, I’d say the exact same thing. But I have to protect myself – say for a second that another Target, Anthem, AshleyMadison, or whatever happens here, and it gets out that I was the database consultant, and that the personally identifiable data was out in the wide open, and I didn’t tell you about the risk.”

Moe the Manager: “We won’t tell anybody.”

Me: “You say that now, but if you got hacked and the personal data got out, Curly the CEO would tell your lawyers to point the blame at me. They’d say I was a bad consultant because I didn’t alert you about this risk. I have to include this in my written findings to cover my butt. You can feel totally free to ignore it if everybody else in the company is comfortable with that risk, but I have to put it in writing or I’m a bad consultant.”

I’ve actually lost a couple of clients when this line went downhill, but you have to be careful with your own reputation. Nobody wants to hire the DBA who was on duty when one of these incidents went down.

For more fun, read more of my favorite consulting lines.

 

css.php