SQL Server

It’s Friday afternoon. You, the DBA, are looking forward to a long weekend. You’re going to walk out the door in 30 minutes and enjoy some family time. The phone rings and you pick it up. On the other end, Joe Developer says, “There’s a query that’s been running for a long time. Can you…

When Do You Fire the DBA?

Last Updated March 6, 2014

Locking, Blocking, and Isolation Levels, SQL Server

I know a lot of DBAs, but it’s really, really rare that I’ve seen one get fired. I don’t think it’s ever happened during one of my consulting engagements, and I’ve seen some incredibly horrific database disasters (not to mention a whole lotta ugly near-misses). So I asked Twitter: Thinking about a blog post. When…

Finding Blocked Processes and Deadlocks using SQL Server Extended Events

Last Updated March 14, 2019

Jeremiah Peschka

A lot of folks would have you think that Extended Events need to be complicated and involve copious amounts of XML shredding and throwing things across the office. I’m here to tell you that it doesn’t have to be so bad. Collecting Blocked Process Reports and Deadlocks Using Extended Events When you want to find…

T-SQL Tuesday: Stop Tuning with Wait Stats Percentages

Last Updated July 3, 2018

SQL Server, Wait Stats

For this month’s T-SQL Tuesday topic, Michael J. Swart told us to argue against a popular opinion, and man, is that right up my alley. I’ve told you to stop worrying about index fragmentation, stop backing up your logs once an hour, and to stop running $14k of licensing on $3k of hardware. You’re probably checking…

The Worst Database User

Last Updated February 28, 2014

“Can you tell me a little about this code?” I asked, keeping my best poker face on. “I’m really proud of it. It gathers a whole bunch of stuff from accounting, human resources, manufacturing, you name it. I want to be able to track how we’re doing over time.” He nearly hovered in his chair…

Back Up Transaction Logs Every Minute. Yes, Really.

Last Updated February 9, 2017

Backup and Recovery, SQL Server

127

Right now, your transaction log backups are set up to run every 15 minutes, every hour, or maybe every few hours. It’s time for a reality check. Figure out what internal customer owns the data inside the database, and walk into their office. Ask them how much data they’re comfortable losing. You’re probably going to…

Extended Events for Tracking Code Improvements (video)

Last Updated February 21, 2014

Ever wanted to prove that your code or index changes had a positive impact? In this 30 minute video, Kendra shows you how to use Extended Events to measure a production workload– and how you can aggregate the results to prove your changes made a difference. Want to skip straight to the demos? Here’s a…

The Evolution of SQL Server Instrumentation

Last Updated February 19, 2014

One of my favorite things about SQL Server is the instrumentation. It’s incredibly good at giving you information about what’s going on in the database engine and query optimizer. Want to know why your server is slow? Need to know what made it go offline? You have a lot of tools available. This hasn’t always…

San Diego Class Recap

Last Updated August 15, 2024

We had a lot of fun this month teaching SQL Server classes at the San Diego Harbor Hilton. (We wrote about how we picked the cities earlier.) Even when we teach, we learn, and then we share what we learned, so here we are. San Diego Harbor Hilton We like to try new things with our…

How to count the number of rows in a table in SQL Server

Last Updated January 28, 2021

Jes Schultz

“How many rows exist in a table?” It seems like such an innocent request. It isn’t too hard to get this information out of SQL Server. But before you open SSMS and whip out a quick query, understand that there are multiple methods to get this information out of SQL Server – and none of…

Why Your Slow SQL Server Doesn’t Need a SQL Consultant (or Does It?)

Last Updated February 13, 2014

Your SQL Server is slow – but should you call us in? Jeremiah and Brent had a throw-down the other day, and we figured we’d capture it here on the blog. Brent Says You Do, and Here’s Why 5. You’ve been blindly throwing money at the problem without results. You’re on a first name basis…

PREEMPTIVE_OS_WRITEFILEGATHER and Instant File Initialization (IFI)

Last Updated February 13, 2014

The other day I was running a script I’d written to expand out some tables in the AdventureWorks sample database for some testing. The script was taking a long time, and at first I thought I was just spoiled because I usually run my tests on my laptop, whose storage is backed by SSDs. In…

Using SQL Server’s Table Valued Parameters

Last Updated March 7, 2018

Jeremiah Peschka

Table valued parameters (TVPs) are nothing new – they were introduced in SQL Server 2008. TVPs are a great way to get data into SQL Server, and yet very few developers use them. Getting Started with TVPs In order to get started, we need to create a user defined type. This type is necessary so…

Statistics Matter on Temp Tables, Too

Last Updated February 13, 2017

SQL Server, TempDB

Temp tables are like real tables, just a little tricker. When you’re starting out writing TSQL, it’s easy to want to do all your work in a single query. You learn about derived sub-queries, CROSS APPLY statements, and common table expressions. Suddenly, each of your queries is so complex that you hardly know what you’re…

What If You Knew The Server Would Crash Today?

Last Updated January 26, 2014

This morning you woke up with a terrible premonition – you were absolutely sure your most important production database server was going to crash today. What would you do? Step 0: stop for coffee, because it’s gonna be a long day. Here’s a quick list of places to start: Identify where the most recent backups…

Satya Slips a SQL Server Standard Secret?

Last Updated February 4, 2014

Everybody wants to know the next SQL Server release date, pricing, and feature lists, but anybody who knows those facts is bound by non-disclosure agreement and can’t say it publicly. Every now and then, though, we get lucky and someone from Microsoft slips up. That’s why I pay particular attention to Microsoft interviews as we…

What Do You Ask the Leaving DBA?

Last Updated January 22, 2014

The last DBA had an unfortunate accident in Alaska. When the database administrator turns in her notice, what questions should you ask her in her last couple of weeks? I’m assuming, of course, that it was a friendly departure and you’ve got the full two weeks to have good conversations. Or maybe the DBA is…

UPDATE STATISTICS: the Secret IO Explosion

Last Updated February 13, 2017

Index Maintenance, SQL Server, Statistics

I first knew something was up when I looked at the job history for a simple maintenance plan. It had two steps: Rebuild all the indexes in the database – this took 10 minutes each night. Update statistics – this took 2-3 hours each night. What was going on? Statistics in SQL Server are small,…

Reporting in Production: SQL Server (video)

Last Updated January 28, 2014

Jeremiah Peschka