SQL Server

Breaking News, SQL Server

Just announced on the Microsoft Release Services blog, if you run a SELECT query with the NOLOCK hint and your query goes parallel, it can block other queries. This is a bug, and it will be fixed soon, but it is a very big deal for people who think NOLOCK means, uh, NOLOCK. More technical details: While one…

Spring Cleaning Your Databases

Last Updated February 13, 2017

Tara Kizer

Index Maintenance, SQL Server

Even with lots of monitoring in place, we should perform periodic checks of our SQL Servers. Think of this like “Spring Cleaning”, except I would recommend that it be more frequently than just once a year. Doing it monthly might be a bit ambitious due to our busy schedules, but quarterly could be achievable. Below are…

Temporal Tables, Partitioning, and ColumnStore Indexes

Last Updated February 13, 2017

Indexing, Partitioning, SQL Server

This post is mostly a thought experiment The thought was something along the lines of: I have a table I want to keep temporal history of. I don’t plan on keeping a lot of data in that table, but the history table can accumulate quite a bit of data. I want to partition the history…

SQL Interview Question: “Tell me what you see in this screenshot.”

Last Updated May 28, 2016

Brent Ozar Unlimited Team

You’re a data professional working with (or at least applying to work with) a company using the StackOverflow database (I’m using the March 2016 version today). Your users are complaining that this stored procedure is slow: usp_GetPostsByOwnerUserId They didn’t give you parameter 26837 – I’m just giving you that so you can see an execution plan.…

[Video] Office Hours 2016 2016/05/18 (With Transcriptions)

Last Updated May 21, 2016

This week, Brent, Angie, Erik, Jessica, and Richie discuss sp_Blitz® updates, merge replication, detecting index fragmentation, how to punish your DBAs, and Brent’s new chroma key backdrop. 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. Jessica Connors: Let’s talk about some merge…

SQL Server 2016 Maintenance Plan Improvements

Last Updated February 13, 2017

CHECKDB and Corruption, SQL Server

Let’s just get one thing out of the way first. Yes, I understand that you, dear reader, probably hate maintenance plans. You associate them with everything dumb that you did early on in your career, like setting up a single plan that reorganized indexes, then rebuilt them, then updated their statistics. You are older and…

What to Do When DBCC CHECKDB Reports Corruption

Last Updated January 23, 2025

Backup and Recovery, CHECKDB and Corruption, Processes and Practices, SQL Server

The instant you encounter corruption in a production SQL Server database, stop. Read this entire article first to understand the big picture, and then come back here to take action. First, understand that you shouldn’t start by trying to repair the corruption. You may be facing a storage subsystem problem where your storage is corrupting…

Extended Events: Where They Hide The Good Stuff

Last Updated May 14, 2016

You can do a lot with Extended Events I’m really looking forward to the stuff in 2016 becoming mainstream in 2020 or so. Raise your hand if you’re using SQL Server 2014 in production. Raise your hand if your vendor supports SQL Server 2014. Okay then. When you open up the New Session dialog to…

What TRY/CATCH Doesn’t Handle

Last Updated May 20, 2018

Tara Kizer

SQL Server, T-SQL

We were once asked in class what TRY/CATCH doesn’t handle besides object existence errors. It’s well documented in Books Online (BOL). If you’re like me, then tl;dr. Are we even calling it Books Online these days? I still say “bookmark lookup” instead of “key lookup”. I suppose I’ll be saying Books Online for quite some…

SQL Interview Question: “Talk me through this screenshot.”

Last Updated May 20, 2016

Brent Ozar Unlimited Team

After writing about “For Technical Interviews, Don’t Ask Questions, Show Screenshots”, lots of folks asked what kinds of screenshots I’d show. Here’s this week’s example. I show each screenshot on a projector (or shared desktop) to the candidate and say: What’s this screen from? What does the screen mean? If it was a server you inherited from…

[Video] Office Hours 2016 2016/05/11

Last Updated May 14, 2016

SQL Server, Videos

This week, Brent, Angie, Erik, Jessica, Richie, and Tara discuss backups, failover events, tempdb errors, errors, other errors… oh, did we mention errors? 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. How do you feel about third party backup software? Jessica…

What I Look For When I’m Hiring Database Professionals

Last Updated April 7, 2016

Matan, Guy, and I recording the podcast On today’s episode of the SQL Server Radio podcast, I talk with Guy Glantser and Matan Yungman about what we look for when we’re hiring. In the broadest sense, don’t think junior or senior: I’m hiring someone for what they already know, or I’m hiring someone for their…

Implicit vs. Explicit Conversion

Last Updated March 12, 2018

Brent Ozar Unlimited Team

Everyone knows Implicit Conversion is bad It can ruin SARGability, defeat index usage, and burn up your CPU like it needs some Valtrex. But what about explicit conversion? Is there any overhead? Turns out, SQL is just as happy with explicit conversion as it is with passing in the correct datatype in the first place.…

[Video] Office Hours 2016 2016/05/04

Last Updated May 8, 2016

SQL Server, Videos

This week, Richie, Erik, Angie, and Tara discuss deadlocks, replication, SQL Server 2016 features, 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. Transcript: Does NVARCHAR(255) Cost More Than NVARCHAR(30)? Angie Walker: All right. So Steve has a nvarchar(255) column that…

RAM and Sympathy

Last Updated February 13, 2017

In-Memory OLTP (Hekaton), Licensing, SQL Server

With the release date for 2016 finally announced Everyone can start gearing up to gaze upon its far shores from the 2008R2 instance they can’t or won’t upgrade for various reasons. I’m excited for a lot of the improvements and enhancements coming along, and generally hope I’m wrong about customer adoption. One annoyance with the…

Creating Tables and Stored Procedures in TempDB – Permanently

Last Updated November 4, 2020

Bad Idea Jeans, Humor, Parameter Sniffing, SQL Server, TempDB

No, not #tables – actual tables. Here’s how: Transact-SQL USE tempdb; GO /* This one is only available during my session: */ CREATE TABLE #myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* This one is global, meaning it's available to other sessions: */ CREATE TABLE ##myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* You can…

Where Clauses and Empty Tables

Last Updated April 27, 2016

Sometimes SQL is the presentation layer And when it is, you end up doing a lot of concatenating. This isn’t about performance, or trying to talk you out of SQL as the presentation layer, this is just something you should keep in mind. SQL is a confusing language when you’re just starting out. Heck, sometimes…

SQL Interview Question: “Talk me through this query.”

Last Updated April 24, 2016

Last month’s post “For Technical Interviews, Don’t Ask Questions, Show Screenshots” was a surprise hit, and lots of folks asked for more details about the types of screenshots I’d show. Over the next few weeks, I’ll share a few more. Normally I’d show this query as a screenshot, but for easier copy/pasting into comments, I’m…

SQL Server 2016 Release Date: June 1, 2016

Last Updated May 2, 2016