Blog

In SQL ConstantCare®, we’re adding a notification for when your data size grows >10% in a single week. When this happens, you want to stop for a second to ask yourself a few questions:

Did someone just create a backup table? Folks often do a SELECT INTO to back up an important table right before they make a dangerous change. Unfortunately, they rarely remember to go back and delete that table. Run sp_BlitzIndex @Mode = 2, which gives you an inventory of objects in the database. Copy/paste that into Excel, and sort it by size descending (either number of rows, or space allocated), and just make sure you don’t have a bunch of backup tables using up space. If you’re not sure if they’re still used, try renaming them, like rename SalesBckup to SalesBckup_ToBeDeleted_20191231. That way, if someone comes running back in saying they need that table, you can rename it back and you’re good to go. Otherwise, delete it on the specified date.

Did someone just create a bunch of indexes? Someone might have gone a little overboard with the Database Tuning Advisor creating covering indexes for a whole bunch of queries.

Is Instant File Initialization properly configured? Will we be able to grow out data files instantly? This is a massive help for restores, too: when you’re under the gun to get back online quickly, you need the ability to instantly grow out the data files before a restore begins.

Do I need to reconfigure the database’s log file? Instant File Initialization only works on the data files, not the log files, so large file growths can be slow. Make sure you don’t have the log file configured with a large percentage growth, like 10%, which might take a long time on a 100GB log file. It’s also a good time to make sure you don’t have shrink jobs – if someone’s really growing the data by 10% per week, you’re going to need some log space while these events happen.

Am I backing up the log often enough? If we’re only doing log backups once per hour, the log file might be growing to a large size, and log backup times might be getting ugly. Consider backing up the log more frequently to nibble off the changes over time.

Do I have enough space for TempDB? My general rule of thumb is that you want TempDB to be able to handle 25% of the server’s total data size, or the size of your largest table, whichever is larger. Sooner or later, somebody’s gonna dump your largest table into TempDB as part of a reporting query, and you’re going to need to be able to handle that workload without taking the server down.

Do I have enough space in DR? It’s not enough to monitor space on your primary replicas: you also have to check your disaster recovery environments to make sure you’ll be able to fail over. Don’t just think about data & log space – think about TempDB size and backup capacity over there, too.

I’m finishing up a bit of a summer tour – Techorama Belgium, SQL Saturday Dallas, SQL Saturday South Florida, and this week, SQL Intersection in Orlando – and I’ve gotten the same question from several attendees. It goes something like this:

“My company hasn’t moved to the cloud yet, but at every conference session, I keep hearing Microsoft talk about the cloud. Is everybody else really doing it? And if they do, what happens to my job?”

To answer it, I usually start with a show of hands – asking the other attendees how many of them run their production databases in the cloud. I don’t have exact numbers here, since I do show-of-hands, but I’ll give you a rough rundown:

• Most attendees aren’t running production databases in the cloud yet
• Of the ones who are, most of them are using infrastructure-as-a-service (VMs)
• Relatively few (5-10%) are hosting their production databases in platform-as-a-service (Azure SQL DB, Amazon RDS)

Microsoft sessions are about the future.

The 5-10% Azure SQL DB ratio is very much the opposite of the content ratios that you see from Microsoft presenters at conferences: those can sometimes seem like 90-95% Azure services. However, it makes sense: there’s already an army of community presenters who can teach you about on-premises servers or VMs – those are the same as they’ve always been. Microsoft needs to tell you information you can’t get anywhere else, and that’s Azure. They need to tell you about the future, not the present.

It’s no different than SQL Server versions. You don’t see a lot of Microsoft presenters talking about how to get the most performance out of SQL Server 2012 and 2014. To them, that’s old hat. They’re focused on telling you about what’s new in  SQL Server 2019, a version we don’t even have a release date for yet – and again, that makes sense too. Microsoft already gave presentations about how to manage SQL Server 2012 and 2014 – they did it back in 2012 and 2014, hahaha. They’re moving on – and that’s fair.

That can seem odd to attendees who are only just now adopting 2014 or 2016, but have no fear: there are still plenty of other conference sessions for you. They’re just not taught by Microsoft employees, and that’s totally okay.

Even in Microsoft’s vision of the future,
there are still production DBAs.

Let’s run with the Microsoft presentations about SQL Server 2019 for a minute. Even in 2019 – the version you don’t have yet, and you lust after with hungry eyes – it’s still a plain ol’ boxed product that you download and install. In the cloud, that means infrastructure-as-a-service – but it’s just plain ol’ VMs, only more complicated. Microsoft’s talking a lot about running it on Linux and in Kubernetes clusters – again, things you have to set up yourself, things not exactly known for their ease of use.

You still have to figure out patching.

You still have to figure out high availability and disaster recovery.

You still have to figure out troubleshooting on that Big Data Cluster.

You still have to manage permissions. And Agent jobs. And corruption checking. And restores. I could go on and on, but you get the point: there is still a lot of administration for that database.

If your app fits into a few profiles, you can outsource some of that production database administration work to Microsoft if you move to Azure SQL DB. Today, Azure SQL DB, Hyperscale, and Managed Instances are great fits for a lot of databases that, frankly, never had production database administrators to begin with. I love that – I’d rather have someone taking care of a database than no one taking care of it.

And in platform-as-a-service,
development DBAs are hella valuable.

There’s a big difference between development DBAs and production DBAs. I know, you think you do all of those things, but you only kinda sorta halfway do all of ’em. As you gain seniority, it’s time to specialize in one of those two:

The development DBA job role is incredibly valuable in the cloud. See, on premises, when I tune a query, the company doesn’t get a refund for those unused CPU cycles. They can’t instantly downgrade from Enterprise Edition to Standard Edition and get their licensing money back.

In the cloud, they can.

One of my favorite recent examples was a company who came to me saying, “We’re spending about $2M per year in the cloud just on our databases alone. Can you help us reduce those costs?” Absolutely: with just a couple of days spent query & index tuning, we chopped their biggest database expenses in half while increasing performance.

At the end of that engagement, the CTO told me, “I thought I’d save money in the cloud by not having a DBA, but what I’m learning is that in the cloud, I actually get a return on my DBA investments.”

Bingo.

In the cloud, a good development DBA pays for herself.

It’s up to you to communicate that.

If you’re a DBA and your company uses the cloud – whether it’s infrastructure-as-a-service or platform-as-a-service – it’s time for you to start learning more about:

• How much your company is spending on your databases
• What your workloads look like on each of those
• How you could do performance tuning to cut the costs
• How to make sure to reduce your instance sizes after your tuning work completes
• How to follow up with the end users to make sure they’re still happy after the cost cuts
• How to take the credit for your hard work, and prove that you’re paying for yourself

This is how you’ll get your raise this year, and how you’ll be the obvious no-brainer candidate next time you’re looking for work. The cloud isn’t killing your job – the cloud is making you the hero of the executive suite.

During the setup wizard, SQL Server should ask for your email address. It should suggest that you use a distribution list for people on your team, so that someone gets the alerts when you’re out on vacation. Then, it should send these common failure alerts to you rather than making you set them up manually.

It should ask where you want your backups written to. It should suggest that you back up to a UNC path, not store the backups locally, lest the box crash and you lose everything.

It should ask if this is a new server, or replacing an existing server. Your phone probably does this – shouldn’t your expensive database server? I’m not saying it has to migrate the existing data and settings over – that’s genuinely hard, I get it. (However, I’ll say that the fact that Apple phones & laptops do this means that I upgrade more often. Upgrading is painless – go buy the new one, your stuff transfers over, boom, done. The pain of moving to a new SQL Server means we don’t do upgrades as often, and I know Microsoft wants to spur adoption. This kind of enhancement would make that happen.) I get that we’re not going to get automated moves, but the very least, go connect to that SQL Server to get a rough idea of how many databases and how much size is involved for this next step…

It should ask how much backup history you want to keep in that backup folder. As you move a slider back and forth between, say, 1 day and 1 year, it should show you how much space will be required on your backup target, and whether you have enough space for that now. Sure, you probably have something to sweep those backups off to a more long-term destination, but this is a good start.

It should ask how much data you’re willing to lose. As you move a slider back and forth between 1 minute and 1 week, it should show you the full, differential, and log backup schedule it’s going to use in order to meet your goal, plus corruption checking jobs. If you’re replacing an existing server, it should also show you roughly how long your restores are going to take. At the very least, it could open the maintenance plan wizard with suggestions already filled out. (I know, you prefer Ola Hallengren’s maintenance scripts, and I do too, but…baby steps, right?)

Shouldn’t we start users off by helping them protect the data?

Whether you’re looking to go cutting-edge and hop on SQL Server 2019 as soon as it’s available, or whether you’re just trying to evacuate from SQL Server 2008 to anything that’s going to be supported after this summer, you’re probably doing upgrade projects. This is a good time to ask yourself 5 questions:

1. Do we really need the higher compatibility level right away? When you flip the switch at the database level to enable a newer compat level, you’re taking a risk that your performance will be better across the board for every single query. The reality is that sometimes, it’s not. You probably tested your slow queries and found they went faster with the new compat level, but you didn’t test your fast queries to see if they regressed. After migrating to a newer version, just leave the compat level at the same one you had before for a week or two. Later, flip the switch on the newer compat level, watch what queries perform better or worse, and be ready to pop back to the older compat level temporarily if necessary. This just buys you and your team time to do performance mitigations on queries that regressed.

2. Will we enable Query Store? This built-in tool helps you do a better job of identifying which queries are regressing, and why. It’s not intuitive, and the default settings are a really bad idea, but you can work around those if you spend some time learning it ahead of time. It’s just a question of whether you’re willing to invest that time. (The tool isn’t going away, and I think learning how to configure and use it is a good investment that would pay you dividends in the long term.)

3. What’s our back-out plan? I’m not a fan of upgrading in place because it leaves you without a safety net. However, even if you build new VMs and migrate over to them, you still need to do a round of user acceptance testing to make sure everything’s okay on the new environment. Once you go live on a newer version, and you start doing deletes/updates/inserts (DUIs) on that new version, your data just flat out now lives in the new version. You can’t restore a database to an earlier SQL Server version. So before you let the users in, designate a team to do a round of quick performance tests, and have everyone agree on the success criteria. If they don’t sign off on go-live data, scratch it, and stay on the older infrastructure for another week or two (or more) until you iron out the performance issues.

4. What sp_configure settings and trace flags are we using, and why? Over the years, we might have implemented different tweaks in order to fix performance or reliability issues. When you upgrade, that’s a good time to stop and revisit those settings to see if they’re still relevant. Plus, their behavior can change – here’s a problem I’m running into right now with trace flag 834 and CTP 3.0:

Microsoft just doesn’t have the bandwidth to test every possible combination of trace flags and sp_configure settings. The more you venture away from the defaults, the more you’re taking risks that your particular combination won’t behave the way it has in the past.

Update May 29: with the help of the brilliant @sqL_handLe, we traced down that it’s a bug in the Hybrid Buffer Pool feature that’s trying to use conventional memory as a PMEM device. Boy, talk about a good example of it being difficult to test every combination of feature – I never would have thought to investigate Hybrid Buffer Pool as a culprit, and I’m still not sure how to do good root cause analysis on that feature given the limited instrumentation that I found during my investigation.

5. How often will we patch? SQL Server 2017 doesn’t have Service Packs anymore, only Cumulative Updates. Going to 2017 or 2019 is a good time to stop and talk with management about their expectations for patch outages. If they’re used to only taking annual outages for Service Packs, they’re going to be in for a big surprise with the frequency of Cumulative Updates: every 30 days for the first year of a product, and then every 60 days after that. When managers tell me they want less frequent maintenance windows, I walk them through the list of things fixed in recent updates. Take SQL Server 2017 Cumulative Update 15, for example: 2017’s been out for almost two years now, and yet CU15 still found & fixed bugs involving incorrect query results, Dynamic Data Masking security leaks, incorrect query results, stack overflows during backups, slight memory issues, and incorrect query results. Bugs are real, y’all.

What, you’re upset that SQL Server has bugs?

Well, it could be worse. The bugs could be there, and not getting fixed. Like, uh, the SQL Server 2008 that you’re supposed to be binning this summer.