Product has been added to your cart.

Production Database Administration

DBA Training Plan 21: Building a New SQL Server

Last Updated September 18, 2019

DBA Training Plan, High Availability

I have a secret setup checklist. Oh sure, our free First Responder Kit has a SQL Server setup checklist that walks you through a few things you need to do before you install, plus a few things to do immediately afterwards. That’s good. It’s actually pretty darned good. But it’s not the secret checklist. Gather round and cover your…

My new hardware

DBA Training Plan 20: Planning Your Next Server

Last Updated September 9, 2019

Architecture, DBA Training Plan

When the business decides it’s time to give up fixing the old server because it’s not fast enough or reliable enough, stop. The worst thing you can do is jump directly to the hardware vendor’s site, check a few boxes, and order a new shiny server that ends up with exactly the same problems as…

SQL Server Licensing Guide

DBA Training Plan 19: The L-Word, Licensing

Last Updated September 6, 2019

DBA Training Plan, Licensing

You’ve gotten through a lot of this training. You’ve tried fixing the problem with indexes, query tuning, and judicious application of various configuration switches. You’ve been watching webcasts and using free scripts, but… Things just aren’t getting better. Before you start browsing your cloud vendor’s sizing price list or your favorite server vendor’s build-and-price site,…

Mitch Bottell is preparing to take an exclusive lock on some BBQ.

DBA Training Plan 18: Managing Concurrency

Last Updated September 3, 2019

DBA Training Plan, Locking, Blocking, and Isolation Levels

Locking isn’t a problem – queries are gonna lock data. As long as nobody else wants the lock, you can take your sweet time locking it, and it won’t show up as a problem. (That’s one of the many reasons queries work so well in development, and then hit performance issues in production.) Blocking, now…

Time to partition my wine collection

DBA Training Plan 17: Should You Partition Your Tables?

Last Updated September 2, 2019

DBA Training Plan, Partitioning

In the last episode, I talked about knowing when it’s time to scale out: identifying when our data is getting to be so large that we have to split it across multiple servers, and I explained why that is so challenging. But what about table partitioning – SQL Server’s ability to break up a single…

I would like to scale out my Porsche collection.

DBA Training Plan 16: Is It Time to Scale Out?

Last Updated August 27, 2019

Architecture, DBA Training Plan

In our last episode, we found out what your server was waiting on. In the closing remarks, I mentioned that you should find out which queries are causing the problem, and focus your mitigation efforts there. You might have circled back to the episode where we covered sp_BlitzCache, used a @SortOrder parameter that lines up…

DBA Training Plan 15: What’s Your SQL Server Waiting On?

Last Updated August 30, 2019

DBA Training Plan, Wait Stats

You’re here, dear reader, because you weren’t “classically trained” as a database administrator. You didn’t graduate from the University of Sciencing Computerses with a Master’s of Transactional Processing. You probably got your start as a developer or sysadmin, and gradually fumbled your way here. You’re used to monitoring stuff from the OUTSIDE using things like…

It's a trick question.

DBA Training Plan 14: POP QUIZ!

Last Updated August 28, 2019

DBA Training Plan

AH-HA! You didn’t know there would be one of these, did you, hotshot? You’ve been reading along in the series, just smiling and nodding, pretending you’re keeping up, but not really doing the homework. BUSTED! Put your books away and get your pencils out. Backup & Recovery Questions: How many production SQL Servers do you…

Brent getting caffeinated

DBA Training Plan 13: Why Do Cached Plans Go Bad?

Last Updated September 5, 2019

DBA Training Plan, Execution Plans

It’s a little bit of a trick question: what could make a good query plan turn bad? In the last episode, we used sp_BlitzCache to spot the most resource-intensive, longest-running query plans in your server’s cache. Go run those queries again, right now, and I bet you’re going to see plans in the cache that…

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

Last Updated August 11, 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

Last Updated August 15, 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

Last Updated May 7, 2020

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

Last Updated August 15, 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

Last Updated August 13, 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

Last Updated August 13, 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

Last Updated August 13, 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

Last Updated August 13, 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

Last Updated August 13, 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

Last Updated August 13, 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)

Last Updated August 13, 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…