SQL Server

CHECKDB and Corruption, SQL Server

It’s easy to get caught up in daily incidents, tickets, and special projects. Like a good scout, though, a core task in a DBA’s job is to be prepared. You have to set time aside in your schedule to: 1. Practice responding to corruption. Gail Shaw’s “Help, my database is corrupt, now what?” post should…

No but really, how big should my log file be?

Last Updated July 13, 2018

Most of you are going to hate this And TL;DR, there’s a script at the end of the post. But like The Monster At The End Of This Book, it’s worth it not to skip the middle. There are about a billion but-what-ifs that could come into play. I can’t possibly answer all of those…

Stored Procedure Cached Time vs SQL Statement Cached Time

Last Updated January 29, 2016

Tara Kizer

I recently ran into a performance problem that had me scratching my head. How could a stored procedure’s plan have changed, and yet it not be reflected in sys.dm_exec_procedure_stats? So here’s what happened and what I knew: 7:45am: Users were complaining that a page was timing out 8:00am: Developer emailed the offending stored procedure and input parameter…

New York City: The Data That Never Sleeps

Last Updated January 29, 2016

I love living in the city Blog posts about people’s favorite data sets seem to be popular these days, so I’m throwing my hat in the ring. NYC has been collecting all sorts of data from all sorts of sources. There’s some really interesting stuff in here. Another personal favorite of mine is MTA turnstile…

When Shrinking Tempdb Just Won’t Shrink

Last Updated February 13, 2017

Tara Kizer

SQL Server, TempDB

I am not a proponent of shrinking databases, but sometimes you have to because your momma said to. Sometimes that database is tempdb. It used to be that we were warned against shrinking tempdb because it could cause corruption, so your only recourse was to restart the SQL Server service. Paul Randal let us know…

A funny thing happened on my way to set up Mirroring…

Last Updated February 9, 2017

Database Mirroring, SQL Server

I’ve set up Mirroring about a billion times I’m not bragging about that. I’d rather say that I set up a billion AGs, and not one of them ever failed. But then I’d be lying to you; those things fail like government programs. One thing I’d never done, though, is set up Mirroring with a…

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

Last Updated February 12, 2017

Index Maintenance, SQL Server

Company News, SQL Server

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…

Should I Worry About Index Fragmentation?

Last Updated February 13, 2017

Doug Lane

Here’s a handy flowchart for whenever you find yourself worrying about fragmentation: *Talk to your doctor about whether fragmentation is a problem for you. Seek help for index maintenance lasting longer than four hours. Serious side effects may occur if all index maintenance is ignored completely. Brent says: pop quiz: what are you doing more…

SQL Server Agent is an application server.

Last Updated January 27, 2016

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 Now Easier to Query sp_BlitzFirst’s Historical Tables

Last Updated January 7, 2016

When you want to know why the server’s slow, you can ask Brent. sp_BlitzFirst® 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…

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

Last Updated December 9, 2018

Architecture, SQL Server

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…

How to Get Your Very First Full Time DBA Job

Last Updated February 12, 2017

Professional Development, SQL Server

It’s about time. Step 1: get a job as a developer or a sysadmin. Step 2: stand near the database. Step 3: wait. Almost every DBA I know got their start this way. Sooner or later, something went wrong with the database, and management grabbed the nearest person and yelled, “You! You look smart and…

Cheat Sheet: How to Configure TempDB for Microsoft SQL Server

Last Updated November 4, 2020

SQL Server, TempDB

111

The short version: configure one volume/drive for TempDB. Divide the total space by 9, and that’s your size number. Create 8 equally sized data files and one log file, each that size. Presto, the drive is full and your TempDB is configured for easy performance. The long version is a little more complicated. If you’re on…

How to Talk People Out of the SA Account, Option 2

Last Updated January 13, 2016

In my last post, I explained the kind, gentle way to talk people out of the SA account. Today, I’ll describe a time when Option 1 didn’t work, and I had to get ugly. Power User: “You can’t change the SA password. It’s embedded everywhere. Everything we have relies on it, and we all use…

How to Talk People Out of the SA Account, Option 1

Last Updated May 23, 2016

So you’ve got a bunch of people using the SA account all over the place. It’s hard-coded into connection strings, embedded in linked servers, and the password is on post-it notes throughout the data center. Step 1: Create an alternative SA account. In a perfect world, you’d create a minimally-privileged AD login that only has…

sp_Blitz® v44: Reorganized and Reprioritized Results.

Last Updated January 7, 2016

Angie Walker, our new Triage Specialist, had the fun experience of sitting in and watching several of our SQL Critical Care® engagements. When I asked her if she noticed any patterns or ways we could improve, she said, “The order of sp_Blitz’s output needs to be reworked for 2016. There’s some stuff at the top that isn’t really high-priority…

Still Serial After All These Years

Last Updated March 29, 2018

With each new version of SQL comes a slew of new stuff While some changes are cosmetic, others bewildering, and the rest falling somewhere between “who cares about JSON?” and “OH MY GOD TAKE MY MONEY!”, but not really my money, because I only buy developer edition. Aaron Bertrand has done a better job finding,…

Announcing the Office Hours Podcast (And 78% Off Videos)

Last Updated December 29, 2015

Every Wednesday, we get together on a live webcast to take your questions. (We don’t actually give answers, we just admire the questions.) Hundreds of folks attend every week to hear us flail about. People kept emailing in saying, “I really wanna attend Office Hours, but I’m working/asleep/drunk. Can you turn it into a podcast so…

Does index fill factor affect fragmentation?

Last Updated March 12, 2018