I bet you think you suck at databases.
You're sitting there on your computer, reading all kinds of blogs, watching presentations online, and you're thinking, "Everybody knows more about databases than I do."
Whew. We've worked through a couple dozen posts together in this DBA Training Plan, and you're still just getting warmed up.
First, pick your specialty. Here's some of the more popular specializations in the SQL Server business:
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.
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 the last server.
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.
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…
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 table into smaller ones on the same server?
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 with your top wait type, and...
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 perfmon counters or CPU graphs.
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:
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 use parameters. Maybe they're stored procedures, or maybe they're parameterized SQL.
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 and find out what queries have been running on the server lately.
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 runs a query.
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?"
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 at what's left. This time, run sp_BlitzIndex without a Mode parameter:
[crayon-6a649614e32a7531228983/]
You get back a prioritized list of index design issues like:
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…
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 your data in 8KB pages. That's kilobytes - not megabytes, not gigabytes, but just mere kilobytes.
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.
You would think that when SQL Server writes your data to disk, it's totally safe.
Forever.
