Blog

[Video] Free Training of the Week: 70 DBA Technical Interview Questions

This year, we’re trying something new: every Monday, we’re going to show you one module out of our online training classes.

It’s the whole module, not a sneak peek, and it’s available the whole week. But you’d better not put it off – this post will disappear the following week, and get replaced by a new post.

In this week’s free video training sample, Kendra Little grills you with 25 core DBA skills questions, 22 infrastructure/platform questions, and 23 performance tuning questions:

(If you don’t see the video above, you’re reading this somewhere that doesn’t support video embedding. You’ll need to read the post on our blog.)

The full course covers resume-building for DBAs, 5 things you should always do in a DBA interview, how to get in front of hiring managers, and much more. Go check it out, and if you’re an Everything Bundle subscriber, you can start watching the training course now.

[Video] Office Hours 2016/02/03

In this week’s Office Hours Q&A, Erik, Tara, Doug, and the new guy Richie Rump answer fast-paced questions about logging Perfmon counters to a table, Standard Edition’s 4-socket licensing limit, whether odd or even MAXDOP numbers make a difference, and – one of our favorite questions in a long time – is it a good practice to cut your SQL Server’s max server memory in half every two hours just to, uh, clean things up?

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Introducing Our Latest Employee, Richie Rump (aka @Jorriss)

Richie Rump, aka @Jorriss

Richie Rump, aka @Jorriss

During our very first training class, we showed the students how we use SET STATISTICS IO ON to get the number of logical reads performed on each table in a query, and then sum ’em up to see the query’s overall impact. It’s kind of a painful, manual process.

Sitting in the back row of the class (because that’s how he rolls), Richie Rump saw that process and thought to himself, “I bet I could make that easier.”

The bad news is that he probably didn’t learn much the rest of that day in class, because he immediately started building StatisticsParser.com.

The good news is that you got StatisticsParser, hahaha.

That kind of mentality is what we’re all about. We look for SQL Server tasks that are complex, and figure out how to make them easier for free. Whether it’s our blog posts, our community presentations, or our tools, we’re always looking for new ways to make your job suck less.

So let’s talk to Richie and figure out what makes him tick.

Brent: I think of each person as a toolbox. Over time, as we experience situations and solve problems, they become tools in our toolbox. I think back to my time in hotels, my work in accounting, and even my love of cars as various tools in my toolbox that I can use to get things done. What are some of the tools in your toolbox that make up who you are?

Richie: This surprises some but I spent almost five years as a project manager. I even earned the dreaded Project Management Professional (PMP) certification. I also started my career as a Access database programmer (way back in Access 2.0). Most of my career I spent using Microsoft tooling like Visual Basic, ASP, C#, and the .NET framework. I also spent a fair amount of time as a software architect. After my time as a project manager I pivoted my career towards the data side and absorbed all of the SQL Sever knowledge that I could. I spent almost twelve years in the supply-chain management vertical and have spent time in the accounting, payment processing, and legal areas as well. After reading all of that I feel old.

Brent: Today, you’re a serial (or maybe parallel) community activist – starting dotNet Miami, helping put on the South Florida Code Camp, co-founded the Away From the Keyboard podcast, built StatisticsParser and sp_DataProfile, give community presentations, etc. Take me back to the first time you said to yourself, “I should create something that gives back.”

Richie: That probably would be starting dotNet Miami. For years I followed the SQL Server community and the WordPress communities. I was always impressed by their generosity in not only their sharing of technical knowledge but in the way they care for each other as human beings. I looked around the developer community in Miami and wondered “Why can’t we have a .NET community like that in Miami?” So I grabbed a few people and met at a sports bar and we talked about starting a group. Six months later dotNet Miami was born. We’re still going strong and are committed to being a place where we can grow as technologists and as people. My favorite description of dotNet Miami is “We are not competitors, we are comrades.”

Brent: For years, you’ve been making our classes and webcasts more fun by poking fun at us. Now, you’re going to be on the other side. How do we help raise the next generation of the peanut gallery?

Richie: It takes commitment that’s for sure. I think the whole peanut gallery thing came from getting to know the team personally. Twitter conversations, in-person conference discussions, and training classes all lead to not me to poke fun at the Brent Ozar Unlimited team, but have a friendly conversation with friends. So you want to join the peanut gallery? Get to know us better over Twitter, chat with us at a conference, or join us for in-person training. (See what I did there?)

Brent: One last thing everybody’s gonna ask: where’s @Jorriss come from?

Richie: Ha! Back in the early days of the Internet we had these things called “handles”. For a while I was going by richier but that never sat well with me. So one night in college, a bunch of us geeky types were creating personas that would live in the Star Wars universe (don’t judge). There was a character in Timothy Zhan’s Heir to the Empire trilogy called Joruus C’baoth that I dug so I changed it a bit and out came Jorriss Orroz. From there the handle just stuck. Bonus points if you can figure out where the Orroz comes from.

[Video] Free Training of the Week: How to Configure Quorum for SQL Server

This year, we’re trying something new: every Monday, we’re going to show you one module out of our online training classes.

It’s the whole module, not a sneak peek, and it’s available the whole week. But you’d better not put it off – this post will disappear the following week, and get replaced by a new post.

This week, it’s Kendra Little explaining what quorum is, how to configure it, and what dynamic quorum and dynamic witness mean:

UPDATE 2/7 – Expired!

(If you don’t see the video above, you’re reading this somewhere that doesn’t support video embedding. You’ll need to read the post on our blog.)

Wanna see more? Check out our DBA’s Guide to High Availability and Disaster Recovery course. We cover log shipping, mirroring, quorum, failover clustering, AlwaysOn Availability Groups, and more. For $299, you get 18 months of access to stream it on your desktop, laptop, tablet, or phone.

[Video] Office Hours 2016/01/27

In this week’s Office Hours Q&A, we talk about the pains of patching lots of SQL Servers, how to move databases from one drive to another with minimal downtime, the difference between NOLOCK and READ UNCOMMITTED, and assign Doug all future calls for Dynamics tuning.

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

SQL Server Agent is an application server.

Application server.

Application server.

SQL Server Agent is a job scheduler.

When we first get started managing SQL Server, we usually use it for backups, index maintenance, DBCC CHECKDB, and other common maintenance tasks.

And then one day, we say, “Hey, I need this T-SQL script to run once a day.” We add an Agent job for it. It’s a rare occasion, we think. Not really a big deal.

The next thing you know, the entire business’s lifeline depends on dozens – or heaven forbid, hundreds or thousands – of intricately interwoven and ill-documented Agent jobs. Nobody knows what they do – they just have to run.

Except many of them don’t. They fail for one reason or another, but then they work the next time, and we just hope and pray everybody ignores it.

Eventually, some of them start failing permanently. When we try to figure out what’s going on with them, we ask around about who wrote the script, and it always turns out it was some guy who left the company months ago. Nobody knows how to debug his stuff, or if it even matters. We’ll just leave it in place and maybe it will start working again.

Don’t let yourself get to this place.

To make your life easier, keep application-logic Agent jobs out of your production SQL Servers.

If someone needs application logic, and it has to be an Agent job, it doesn’t actually have to be on the production database server. Give them their own Standard Edition VM where they can go to town, creating any Agent jobs they want. However, those users are 100% responsible for managing the success and failure of their Agent jobs because these are applications, not databases. There are no databases on this server – or if they are, they ain’t backed up or production-quality. This server is for jobs that can point at any SQL Server.

In addition, give them a VM in the DR environment where they can sync their jobs. Source control and deployment is totally up to them.

This way, it lets you focus: if there’s a failing job on the production SQL Server, you own it.

To help you pull it off, consider using my SQL Server support matrix. It sets clear expectations about what you’ll allow in dev, QA, production, and mission-critical production boxes.

[Video] Free Training of the Week: Joins in Execution Plans

This year, we’re trying something new: every Monday, we’re going to show you one module out of our online training classes.

It’s the whole module, not a sneak peek, and it’s available the whole week. But you’d better not put it off – this post will disappear the following week, and get replaced by a new post.

This week, it’s Jeremiah Peschka explaining the different kinds of joins in SQL Server execution plans:

UPDATE 1/30 – Expired!

(If you don’t see the video above, you’re reading this somewhere that doesn’t support video embedding. You’ll need to read the post on our blog.)

Wanna see more? Check out our How to Read Execution Plans course. We cover what an execution plan is, how to read them, predicates, aggregates, grouping, windowing, parallelism, the plan cache, and common problems in execution plans For $299, you get 18 months of access to stream it on your desktop, laptop, tablet, or phone.

[Video] Office Hours 2016/01/20

In this week’s Office Hours Q&A, the entire team avoids spinlocks, then discusses the new Cardinality Estimator, the virtual inserted/deleted tables, monitoring tools, restoring single tables, DBA Viagra, and more.

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go. (Rate our podcast, and you get 78% off our video training this month only!)

It’s Now Easier to Query sp_AskBrent’s Historical Tables

When you want to know why the server’s slow, you can ask Brent. sp_AskBrent® checks a lot of DMVs to find common causes of server slowness.

When you turn on @ExpertMode = 1, you can see additional information like file stats, wait stats, and Perfmon counters.

To log those to a table permanently, check out the @Output parameters. In this example, I’m writing all of the working tables to the DBAtools database:

EXEC dbo.sp_AskBrent
    @OutputDatabaseName = 'DBAtools'
  , @OutputSchemaName = 'dbo'
  , @OutputTableName = 'AskBrentResults'
  , @OutputTableNameFileStats = 'AskBrentResults_FileStats'
  , @OutputTableNamePerfmonStats = 'AskBrentResults_PerfmonStats'
  , @OutputTableNameWaitStats = 'AskBrentResults_WaitStats'

That creates the tables if they don’t already exist, and then adds the result sets each time it runs so you can track performance over time.

Why was the server slow yesterday afternoon?

When it’s time to query the data back out, you can add the @AsOf parameter with a date & time to see the main result set of alert results as of that moment in time:

The query goes 88mph, and then goes back in time

The query goes 88mph, and then goes back in time

That returns results within 15 minutes of either side of your time so you can see if there were any problems leading up to (or after) that moment. It’s really useful when someone says the server was slow last night.

However, that only shows the basic result set of sp_AskBrent® – not the detailed file/Perfmon/wait stats results. For those, you’re best off querying the tables directly for trending purposes.

v20: You Can Query Your File/Perfmon/Wait Statistics Over Time, Too

When you use the @parameter (or PerfmonStats or WaitStats), the latest version of sp_AskBrent® automatically creates a matching view, too, with a suffix of _Deltas. For example, if your results are in DBAtools.dbo.WaitStats, then sp_AskBrent automatically creates a view called WaitStats_Deltas. Querying that view will give you trending data over time because it automatically matches up samples for you to get running averages.

This way, if you run sp_AskBrent® in an Agent job every 5 minutes, you can track your SQL Server’s statistics over time. You can query it back out with SSMS, or your favorite reporting tool.

You can grab sp_AskBrent® along with all our other scripts in our handy download pack. Enjoy!

What Is a Staging Environment and How Do You Build One?

If you’re a full time production database administrator, you need a staging environment.

This is where you test infrastructure tasks like failing over mirroring, doing a disaster recovery role swap, cluster quorum changes, log shipping configuration, and patching. You need to test this stuff repeatedly so that when you’re under pressure in the production environment, you feel comfortable and confident. Bonus points if you use this environment to build checklists and screenshots for rarely-done tasks like recovering from corruption.

You can’t use the development or QA environment because many of these tasks will take the entire environment down – sometimes by design, and sometimes by accident. If you’re in the middle of testing a patch at 9:30AM, and something breaks, and you’ve got a meeting starting at 10AM, you don’t want to have the entire developer team sitting idle because their SQL Server is down. You want to be able to leave this environment in a broken state for hours or days without feeling clock pressure.

Staging is the DBA’s development environment.

You’re not going to use it for load testing or performance tuning, just rehearsing and testing infrastructure changes you might make in production. As such, the hardware quality and quantity doesn’t have to matter, but it needs to be as logically similar as possible.

For example, if your production environment consists of a 3-node production failover cluster and a 2-node cluster in DR, with log shipping across the two, then you’re going to want at least one cluster, plus a separate server to log ship to.

Remind me again why you're using rusty spinning frisbees?

Remind me again why you’re using rusty spinning frisbees?

The individual nodes can be low-powered boxes – think single-CPU desktops with 16GB RAM and a couple of $250 1TB SSDs. (You want SSDs because you’re going to be doing a lot of backup/restore on these.) In theory, they can be VMs too, but often sysadmins freak out when you start asking for several terabytes of shared storage space for a staging environment. For $250, I can just avoid that argument altogether.

Use the $50 SQL Server Developer Edition, and this whole thing costs you less than a grand.

css.php