Production Database Administration Archives

How to Check Performance on a New SQL Server

August 3, 2018

Processes and Practices, Production Database Administration

So you just built a brand new SQL Server, and before you go live, you wanna check to see if it’s going to perform as well as your existing server. The easy way: test your maintenance jobs I’m just asking for the chance to test it, that’s all It’s as easy as 1, 2, 3:…

[Video] What to Do When SQL Server is Slow

June 29, 2018

Brent Ozar

Monitoring, Videos

Someone comes running in and asks, “Are you doing something on the SQL Server right now? Can you take a look at it? It seems really slow.” It’s one of the most common complaints we get about our databases – and yet, most of us don’t have a written plan with what steps to take,…

Job Duties for Database Developers, Development DBAs, and Production DBAs

June 22, 2018

Brent Ozar

Production Database Administration

At the start of my classes and calls, I ask folks to introduce themselves – not by what it says on their email signature, but by the kind of tasks they spend most of their time on. Here’s what I show onscreen to help illustrate it: DBA job duties Down the left hand side of…

How to Tell If Your SQL Server Has Too Much Memory

May 28, 2018

Brent Ozar

Production Database Administration

Sounds impossible, right? The saying goes that you can never be too rich or too thin or have too much memory. However, there is one good indication that your SQL Server is probably overprovisioned, and to explain it, I need to cover 3 metrics. 1. Max Server Memory is set at the instance level: right-click…

Availability Groups Bug with Indexed Views

May 7, 2018

Brent Ozar

Breaking News, SQL ConstantCare

The short story: if you drop an index on an indexed view, queries on the Always On Availability Groups replicas that point to that indexed view will cause memory dumps. The long story: to reproduce – AND DO NOT DO THIS IN PRODUCTION – on the primary, create a couple of tables, an indexed view,…

“Full Stack” Means “Part Time” – or Why DBAs Might Wanna Learn DevOps

March 14, 2018

Brent Ozar

Processes and Practices, Production Database Administration

Long before the “full stack developer” phrase became fashionable, us early developers did exactly that. We didn’t do any of these full time, mind you – full stack doesn’t mean full time. Here’s a grid I use to explain the work involved in building and troubleshooting database apps: Full Stack, Part Time Developers start from the…

Pop Quiz: Can You Use Your Monitoring Tool?

March 1, 2018

Brent Ozar

Monitoring

In the free 30-minute intro calls I do about our SQL Critical Care®, one of the questions I ask is, “Do you have a monitoring tool? And if so, what has it told you about the root cause of the problems you’re facing?” I either hear one of two things: They don’t have a tool,…

New SQL Server Management Studio 17.5: It’s Classified

February 15, 2018

Brent Ozar

Breaking News, SQL Server 2017

SQL Server Management Studio 17.5 is out, and new in this release is a SQL Data Discovery & Classification feature. The idea is that it’ll scan your database, identify columns containing potentially sensitive data, and help you become compliant with regulations like PCI, HIPAA, and GDPR. Let’s see how it works on the Stack Overflow…

How to Suck at Database Administration

February 14, 2018

Tara Kizer

Production Database Administration

155

This isn’t about having a sucky job, this is about you sucking at your job. There’s a big difference. You could be sucking at your job because you hate your job and just don’t care. I get that. In that case though, you know better but choose not to do better. I’ve encountered some people…

What’s So Bad About Shrinking Databases with DBCC SHRINKDATABASE?

December 29, 2017

Brent Ozar

Production Database Administration

You read a lot of advice that says you shouldn’t shrink databases, but…why? To demonstrate, I’m going to: Create a database Create a ~1GB table with 5M rows in it Put a clustered index on it, and check its fragmentation Look at my database’s empty space Shrink the database to get rid of the empty…

Book Review: Database Reliability Engineering by Campbell & Majors

November 27, 2017

Brent Ozar

Production Database Administration

Database Reliability Engineering – good buy When you see the cover of Database Reliability Engineering, the first question you’re probably gonna ask is, “Wait – how is this different from database administration?” And I’ve got good news: that’s the very first thing @LaineVCampbell and Charity Majors (@MipsyTipsy) cover in the preface. “…for a long long…

How to Move TempDB to Another Drive & Folder

November 24, 2017

Brent Ozar

TempDB

Every time I have to do this, I Google for a script. I might as well write my own and put it here so at least I find myself in the Google results: Transact-SQL SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],' + ' FILENAME = ''Z:\MSSQL\DATA\' + f.name +…

No More SQL Server Service Packs: Is CU12 the New SP1?

October 3, 2017

Brent Ozar

Production Database Administration, SQL Server 2017

Starting with yesterday’s release of SQL Server 2017, Microsoft has a new servicing model: they’re only delivering Cumulative Updates, and not doing Service Packs. Pedro Lopes writes: SPs will no longer be made available. Only CUs, and GDRs when needed. CUs will be delivered more often at first and then less frequently. Every month for…

Breaking News: Using TDE on 2016? Doing Backups? Time to Patch.

September 21, 2017

Brent Ozar

Backup and Recovery, Breaking News

backups, compression, tde

Normally, when you use Transparent Data Encryption (TDE) to secure your database at rest, you can’t get backup compression. Encrypted data basically looks like random data, and random data doesn’t compress well. SQL Server 2016 introduced the ability to compress your TDE databases. Yay! Unfortunately, it has bugs that can leave you unable to restore…

Chaining Agent Tasks For Alerting The Right Way

August 4, 2017

Erik Darling

Processes and Practices, Production Database Administration, SQL Server

dba, sql server

Step By Step When you think about setting up maintenance, a persistent fear is that tasks may step on each other. You probably don’t want backups and CHECKDB and statistics maintenance running at the same time as statistics maintenance (notice I didn’t say index maintenance, here, because you’re smart and you skip it?). You may…

New Online Course: The Fundamentals of Database Administration

July 12, 2017

Brent Ozar

Production Database Administration

Remember that very first day when you officially became a Database Administrator? Remember how the Senior DBA sat you down and gave you clear, specific instructions on how to handle SQL Server? No? You don’t remember that? That’s because it rarely happens. You just happened to be the one standing closest to the database server…

Trainable SQL Servers

June 23, 2017

Erik Darling

Production Database Administration

dba, development, performance tuning, sql server

Funny thing The human body is pretty good at adaptation. When you get sick or injured, your body responds and (most of the time) heals itself up. When you stress your body in a certain way, over time, your body will feel less stressed by the same level of that activity. If you do 10…

What Do You Want to See in a Free Query Store Tool?

May 19, 2017

Erik Darling

Execution Plans, Monitoring, Query Store (QDS), SQL Server

I love Query Plans No seriously, I love digging into query plans. Even writing XQuery to dig into them in sp_BlitzCache. I was both honored and horrified to take over development of it from Jeremiah. It’s a beast, but it’s my kind of beast. At around 4500 lines of code, when you want to make…

Group Post: If I Took Another DBA Job, My First Question Would Be…

May 10, 2017

Brent Ozar

Production Database Administration, Professional Development

“Question 1: how do you predict how long a rollback will take?” Brent says: “How many people are in the on-call rotation with me, and can I give them each a technical test?” Once you get out of the on-call rotation, it’s really hard to go back to the electronic tether. I’d be willing to…

Checking for Strange Client Settings with sys.dm_exec_sessions

March 31, 2017

Brent Ozar

Configuration Settings, Indexing

In my performance training class, people get really excited when I cover filtered indexes, computed columns, and indexed views. Then they get a really strange expression on their face when I talk about one of the biggest drawbacks: if your connection options aren’t set correctly, your delete/update/insert (DUI) operations will fail. That gave me a really…