In my last post, I talked about how you can get 43%-67% faster backups. Today, I'm using that same SQL Server setup to discuss how throwing more CPU cores might help you check for corruption faster if you've already got modern fast storage. I don't expect everyone to go to this level of detail by any means, but it's the kind of tuning you can do when you're facing multiple terabytes of production data per server and shrinking maintenance windows.
You want to check for corruption, but you don't want to slow down your primary production server.
In this post, I'm specifically talking about offloading the corruption checking process. I'm not talking about doing corruption checking on both the primary and other servers - that's wonderful, and if you're doing that, you should hug yourself. You're doing a good job. Who's a good dog? You are! Good dog.
You would think that when SQL Server writes your data to disk, it's totally safe.
Forever.
Hard Enough At this point, we've all probably got a database that's a terabyte or more in size. If you're anything like I was, you've got a ton of multi-terabyte databases, and the SAN admin has a special folder for your emails. When you've got a lot of large databases, normal maintenance is out the…
Microsoft has been resting on Paul Randal's laurels for far too long.
From 1999 to 2007, Paul poured his heart and soul into rewriting SQL Server's code to check for and repair database corruption. (For more about his illustrious career, read his bio and enjoy the infectious enthusiasm in his bio photo.)
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?
You've been such a good database administrator.
You followed the setup checklist in our First Responder Kit. You ran sp_Blitz. You set up email alerts for common issues. You run CHECKDB as frequently as practical - weekly, or maybe even daily.
Cash Rules
Most people, when they get through paying for Azure, and SQL Server Enterprise Licensing, are left with a hole in their wallet that could only be filled with something that says "Bugatti", and has a speedometer with an infinity sign at the end.
So far in this series, I've shown you how to corrupt a clustered index, then how nonclustered indexes can be corrupted independently. If you haven't read those, you should start there first.
Let's start again with our 50Ways database, but this time we're going to be good and make sure that we've got checksums enabled, and that we're in full recovery model:
We see a lot of SQL Servers.
During our sales process, prospective customers run sp_Blitz on their SQL Server, and we talk through the results. I see a lot of horrifying stuff.
We got an email!!! It was weird. It was from an Australian, and it was right side up. I could hardly believe it. Normally we encourage people to ask us questions during Office Hours, or over on Stack Exchange. That way lots of people can benefit from the answer. But since this reader is on…
In part 1, I showed you how to corrupt a database - not because you'd need to do it, or because SQL Server has some kind of vulnerability. I just wanted to show you how SQL Server reacts when it finds corruption in a data file, why you should check your databases for CHECKSUM settings, and set up alerts so you can react fast.
Hold my beer. [crayon-6a3ee30c32c36078184786/] Now, let's corrupt it. Open it with a hex editor - my personal favorite is the free xvi32 because it doesn't require installation. Just download it, fire it up (you'll want to run it as administrator), and open the database's MDF file: Next thing you know, you're looking at the contents…
Companies call us for performance or high availability issues, but over and over, the very first two things we find are:
They're not taking backups to match the business's RPO and RTO
They're not doing CHECKDB weekly, or at all, and don't understand why that's an issue
Seriously
DBCC CHECKDB (YourDatabaseHere) WITH NO_INFOMSGS, ALL_ERRORMSGS;
Brent says: normally, we don't post client screenshots on the blog because it's hard to sanitize the sensitive data. This time, not so much. Storage took care of this for us.
So you're hosting your SQL Server in the cloud - say Amazon EC2, Azure VM, or Google Compute Engine - and you've noticed that when you're running a backup or a DBCC CHECKDB, you suffer from extreme performance problems.
Queries run slow, and even worse, applications report timeout errors even just trying to connect to SQL Server. More symptoms can include database mirroring and cluster failovers. What's going on?
At first glance, if you set up log shipping the way most folks do, it looks like you can't run CHECKDB on a subscriber:
On SQL Server Enterprise Edition, you can try working around it by creating a database snapshot - but that won't work either:
Contrary to popular belief, we spend a lot of time with clients when we’re not blogging, answering questions in Office Hours, or working on new features for the download pack. Something we hear a lot is, “How do we compare to other clients?” or “Is this the worst/best setup you’ve seen?”. This got me thinking,…
Let's just get one thing out of the way first.
Yes, I understand that you, dear reader, probably hate maintenance plans. You associate them with everything dumb that you did early on in your career, like setting up a single plan that reorganized indexes, then rebuilt them, then updated their statistics. You are older and wiser now, and you swear by tools like Ola Hallengren's maintenance scripts or Minion Reindex.
The instant you encounter corruption in a production SQL Server database, stop. Read this entire article first to understand the big picture, and then come back here to take action. First, understand that you shouldn't start by trying to repair the corruption. You may be facing a storage subsystem problem where your storage is corrupting…
