Production Database Administration

Home
Production Database Administration

SQL Server installation wizard

Should you run SSAS/SSIS/SSRS on the SQL Server?

February 14, 2019

Architecture, Configuration Settings

When you’re building a new SQL Server, you’re going to see a few intriguing checkboxes during setup. It’s all free, right? Check everything! The services are all free, right? You can just check the boxes for Machine Learning Services, R, Python, Data Quality Services, PolyBase, Integration Services, Analysis Services, and Reporting Services – it’s not…

Brent Ozar in a tin foil hat

What Queries Does Microsoft’s Telemetry Service Run On Your SQL Server?

February 7, 2019

I was working in my lab and I was having some odd blocking waits that I wasn’t expecting. I started firing up sp_WhoIsActive to see what queries were running, and I got a rather odd surprise: sp_WhoIsActivelyRunningTelemetryQueries (click to see full size) Ah-ha, the telemetry service, also known as SQLCEIP! Starting with SQL Server 2016,…

Free Downloads for Powerful SQL Server Management

January 22, 2019

Production Database Administration, Videos

You’re responsible for managing and troubleshooting SQL Server. Good news: you’re not alone. The SQL Server community has banded together and built a bunch of cool stuff to help you do your job easier. In a one-hour session today for the PASS DBA Fundamentals online user group (free to join, of course), I showed some off.…

SQL Server Perfmon counters that are still interesting in 2019

January 16, 2019

“Page Life Expectancy? That belongs in a museum!“ (Cross-posted from the PASS Blog.) Years ago, my first go-to tool for performance tuning was Performance Monitor: it was available everywhere, worked in all versions, and gave me a lot of useful information without paying for a monitoring tool. Since then, I’ve moved on – wait stats…

How to Log Active Queries with sp_BlitzWho

December 3, 2018

Queries are running when you’re not around. They’re wreaking havoc – maybe they’re filling up your TempDB, or causing blocking, or flushing your buffer pool out. When I wanna see what queries are running, I run sp_BlitzWho: That shows me who’s running the query, what it’s been waiting on over time, memory grants, blocking, and…

Brent getting caffeinated

How to Troubleshoot Blocking and Deadlocking with Scripts and Tools

November 28, 2018

Deadlocks, Monitoring, sp_BlitzLock

When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon? During a live emergency, start with sp_WhoIsActive. Adam Machanic’s excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it’s way more powerful. The documentation is extensive –…

Brent Ozar reading

A Lot of Your SQL Servers Are Unsupported.

November 21, 2018

Production Database Administration, SQL ConstantCare

I’m almost afraid to look into the SQL ConstantCare® client base to see the answer to this, but let’s put on a pair of rubber gloves and go find out. SQL Server 2016: <1% out of support. I LOVE YOU PEOPLE. However, you’ve had the luxury of time here: SP1 came out in November 2016,…

Brent Ozar reading

6 DBA Lessons I Wish Someone Would Have Taught Me Earlier

September 21, 2018

Production Database Administration

I was talking to a DBA friend of mine, reminiscing about some of the hard lessons we learned early on in our career. The more we talked, the more we realized that there should probably be a one-page cheat-sheet that you’re required to read before you open SQL Server Management Studio. 1. The name of…

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

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

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

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

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

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…

Brent Ozar reading

Pop Quiz: Can You Use Your Monitoring Tool?

March 1, 2018

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

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

Production Database Administration

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

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

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

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

Menu