Production Database Administration

DBA Training Plan 12: What Query Plans Are In Cache?

August 15, 2019

DBA Training Plan, Execution Plans, sp_BlitzCache

In the last episode, while talking about the basics of executing queries, I said that SQL Server caches execution plans in memory, reusing them whenever the same query gets executed again. I’ve also talked about the dynamic management views (DMVs) that let you query what’s in SQL Server’s memory. Let’s put those two things together…

Plan Properties

DBA Training Plan 11: The Basics of Executing a Query

August 14, 2019

DBA Training Plan, Execution Plans

Up til now in the DBA Training Plan, we’ve been taking inventory of our servers, making sure the data’s well-protected, and understanding the basics of how the data’s stored in indexes inside those data files. Now, let’s start looking at performance, and let’s start by zooming really far out to think about how SQL Server…

Defrag

DBA Training Plan 10: Managing Index Fragmentation

August 13, 2019

DBA Training Plan, Index Maintenance

In the last episode, we looked at your index designs with the output of sp_BlitzIndex. You might have gotten a little overwhelmed what with all the different warnings (and all the learning resources!) You might have thought, “Is there an easier way?” “Is there a way I can just run a job, and have something…

DBA Training Plan 9: My 3 Index Guidelines

August 12, 2019

DBA Training Plan, Indexing

In our last episode, we used sp_BlitzIndex @Mode = 2 to get to know the contents of our database, sorted our indexes from biggest to smallest, and started asking questions about leftover backup tables that we probably didn’t need to keep around anymore. Now that you’ve taken out the trash, let’s have a closer look…

DBA Training Plan 8: What’s Inside Your Own Databases

August 6, 2019

DBA Training Plan, Indexing

In the last episode of the DBA Training Plan, we talked about what’s inside the MDF file: a series of pages that store a database’s tables. Now, it’s time to check out the contents of YOUR database to understand what’s using the most space, what’s getting used the most often, and what you might be…

Brent Ozar reading

DBA Training Plan 7: How SQL Server Stores Stuff in MDF Files

July 31, 2019

DBA Training Plan, Indexing

So far in the DBA Training Plan, we’ve been working hard to make sure the contents of our MDF files are backed up, corruption-free, and secured. Now, uh…what’s actually inside them? Inside each of your database data files (MDFs, although technically you can use any extension you want, even PDF or MP3), SQL Server stores…

DBA Training Plan 6: How to Monitor Drive Space

July 30, 2019

DBA Training Plan, Monitoring

Don’t. It’s not your job. No, seriously, hang on a second. I’m not saying that you should ever say things like “It’s not my job,” but I do want you to understand when you should avoid reinventing the wheel. Your Windows administrators should be monitoring every Windows server you have, making sure it doesn’t run…

DBA Training Plan 5: Knowing Who Has Access

July 24, 2019

DBA Training Plan

Who can get you fired? Run our free sp_Blitz stored procedure on one of your production servers and pay particular attention to the Security section of the results. It lists the logins who have been granted the sysadmin or security admin roles. Don’t think of them as logins. Think of them as people who can get…

Brent Ozar reading

DBA Training Plan 4: Checking for Corruption

July 23, 2019

CHECKDB and Corruption, DBA Training Plan

You would think that when SQL Server writes your data to disk, it’s totally safe. Forever. You would be wrong. It’s almost like there are gremlins out to get you, purposely trying to trash your data. In the really old days, it was problems with magnetic hard drives. Later, it was buggy shared storage arrays.…

DBA Training Plan 3: Treat Your Servers Like Cattle, Not Like Pets

July 22, 2019

Backup and Recovery, DBA Training Plan

In our last post in this series, I talked about why you should be using Ola Hallengren’s maintenance script for backups and sp_DatabaseRestore for faster restores. These two tools have implications for how you build, back up, recover, and fail over your SQL Server. There’s a saying in the cloud: treat your servers like cattle,…

Nines don't matter if users aren't happy

DBA Training Plan 2: Backups (And More Importantly, Restores)

July 17, 2019

Backup and Recovery, DBA Training Plan

When I first started out as a SQL Server DBA, I thought things were going well as long as the backup jobs ran successfully. I’d go into SQL Server Agent every now and then, make sure they were still running, and … that was the end of it. I figured if disaster ever struck, I’d…

DBA Training Plan 1: Build a Server Inventory

July 16, 2019

DBA Training Plan

At your company, walk into the VP of Sales’s office and ask them how many salespeople they have. NO, I mean, don’t actually DO that, because he’s going to ask you why the sales app is so slow. But I mean, imagine if you COULD walk into his office and ask him that. I bet…

Welcome to the DBA Training Plan.

July 15, 2019

DBA Training Plan

Foreword: 8 years ago, I launched an email series with a 6-Month DBA Training Plan. I sent one email per week, bringing people up to speed on the most important topics that nobody taught ’em along the way. It’s hard to believe it’s been 8 years! This month, I’m revisiting the emails, updating their content,…

Big tequila

Things to Think About When Your Databases Grow Quickly

June 25, 2019

Production Database Administration

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…

SQL Saturday Dallas

What Happens to DBAs When We Move to the Cloud?

June 10, 2019

Production Database Administration

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…

Things SQL Server Setup Doesn’t Do – And Should

May 30, 2019

Production Database Administration

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.…

My kind of upgrade

5 Questions to Ask When You Upgrade SQL Server

May 27, 2019

Production Database Administration

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…

Countdown clock at SQLServer2008.com

What You Said You’re Going to Do About SQL Server 2008

April 3, 2019

Production Database Administration

Last week, I asked you what you were going to do about SQL Server 2008’s fast-approaching end-of-support date. Countdown clock at SQLServer2008.com Here were some of my favorite responses from blog comments and Twitter. Terry’s been doing a good diligent job, but struggling to keep up: Like Andrew we’ve been working on retiring/migrating off 2008…

sys.dm_server_memory_dumps

What should you do about memory dumps?

February 22, 2019

Production Database Administration

A SQL Server monitoring tool is warning you that you’re getting memory dumps, and you’re wondering what that means. This isn’t normal: you’re encountering a situation where SQL Server is so freaked out that it’s unable to proceed normally with a query. In order to help you (and really, Microsoft) debug the problem, it’s calling…

[Video] How to Capture Baselines with sp_BlitzFirst

February 15, 2019

Monitoring, sp_BlitzFirst

Everybody tells you to capture baselines – but what exactly are you supposed to capture? Which Perfmon counters matter? How do you track which data files are growing, and which ones are slow? How can you track which queries are using the most resources? All you have to do is install the free, open source…

Menu