Development

Execution Plans, SQL Server 2022

Until 2022, when a query couldn’t go parallel, all we got was a really cryptic note in the execution plan properties saying NonParallelPlanReason = CouldNotGenerateValidParallelPlan. But starting with SQL Server 2022, even when I’m running under older compatibility levels: ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */ GO DECLARE @TableVariable TABLE(Total BIGINT); INSERT…

The 6-Year-Old T-SQL Book That Never Goes Out of Style

Last Updated May 7, 2022

You’ve been working with Microsoft SQL Server for yeeears, and you’re pretty confident that you know how to write a query. So when I tell you about a book called T-SQL Fundamentals, you’re all, “nah, man, I got this covered.” You are incorrect. Sure, the first 5 chapters of the book are about 1-2 table…

Don’t Treat SQL Like a Programming or Scripting Language.

Last Updated May 10, 2022

In a programming language like C# or Java, you tell the computer what to do, in order. Get the customers from California, then get their invoices, then sum the total. SQL, on the other hand, is a declarative language where you declare the shape of your result set: Get the total sales from Californian customers.…

“Index Seek” Doesn’t Mean Much.

Last Updated May 4, 2022

When you see “index seek” on an execution plan, that doesn’t mean SQL Server is jumping to exactly the row you’re looking for. It only means that SQL Server is seeking on the first column of the index. This is especially misleading on indexes where the first column isn’t very selective. To explain, I’ll take…

What Do the LCK_M_SCH_M and LCK_M_IS Wait Types Indicate?

Last Updated April 9, 2022

Locking, Blocking, and Isolation Levels, Wait Stats

You’re investigating your SQL Server’s top wait stats, and you’re noticing a lot of LCK% waits. Normally, that indicates blocking and deadlocks, but you’re just not getting complaints from your end users, and you’re wondering what’s causing it. It might be overzealous index rebuilds. Let’s demo why by starting a new query in the Stack…

Meme Week: NOLOCK? NOSQL.

Last Updated February 19, 2022

Locking, Blocking, and Isolation Levels

When you find yourself using NOLOCK, consider NOSQL instead. NOLOCK has 4 big problems: Your query can see the same rows twice Your query can skip rows altogether Your query can see data that was never committed Your query can fail altogether If you’re okay with all 4 of those, and you find yourself REGULARLY…

Meme Week: SET DEADLOCK_PRIORITY HIGH

Last Updated February 19, 2022

Locking, Blocking, and Isolation Levels

Hey, this seems harmless. Seriously, don’t SET DEADLOCK_PRIORITY HIGH. Even setting it to low can be a bad idea. I’ve heard people say, “Well, I’ve got a big process that runs nightly, but if it gets in anybody’s way, I would rather just kill my process than slow someone else down.” Keep in mind that…

Error Handling Quiz Week: Where Are You Handling Errors?

Last Updated January 2, 2022

This week’s series on error handling has been an eye opener for many of us. We’ve laughed. We’ve cried. We’ve screamed in horror. We’ve read the documentation. I don’t blame you. This is a really confusing topic, and for many of us, it’s the first time we’ve actually looked at the mechanics of how transactions,…

Error Handling Quiz Week: Making a Turkey Sandwich with XACT_ABORT

Last Updated January 13, 2022

CAN YOU BELIEVE THAT HOT MESS IN YESTERDAY’S POST?!? I know, right? You thought that by combining try/catch with a transaction, you’d get robust error handling. Instead, you ended up with half the tables populated, and a leftover open transaction. You’d already forgotten Tuesday’s post in which I pointed out that TRY/CATCH doesn’t catch low…

Error Handling Quiz Week: Combining Transactions And TRY/CATCH

Last Updated January 13, 2022

In yesterday’s epic cliffhanger, you might have been shocked to discover that a plain ol’ transaction does not solve our problem – and in fact, it makes things worse. With yesterday’s code, we still got rows inserted into the Parent table, no rows in the Child table, and a nasty surprise that our session will…

Error Handling Quiz Week: Will a Transaction Help?

Last Updated January 12, 2022

Locking, Blocking, and Isolation Levels, T-SQL

In yesterday’s post, we tried a TRY/CATCH, and those of us who actually ran the demo scripts were disappointed. We found that a TRY/CATCH alone doesn’t fix it: we end up getting rows in the Parent table, but no rows in the Child table. It turns out there are some gotchas in Books Online’s summary of…

Error Handling Quiz Week: Tryin’ TRY/CATCH

Last Updated January 12, 2022

Let’s say we have two tables, Parent and Child, and we need to guarantee that they both get populated at once. We’ll write a single stored procedure to do both inserts: DROP TABLE IF EXISTS dbo.Parent; DROP TABLE IF EXISTS dbo.Child; CREATE TABLE dbo.Parent (ID INT IDENTITY(1,1), DateAdded DATETIME2); CREATE TABLE dbo.Child (ID INT IDENTITY(1,1),…

NOLOCK Is Bad And You Probably Shouldn’t Use It.

Last Updated November 18, 2021

Locking, Blocking, and Isolation Levels

I’m waiting for the YOLOck option. When you put NOLOCK in your query, SQL Server will: Read rows twice Skip rows altogether Show you changes that never actually got committed Let your query fail with an error This is not a bug. This is by design. To understand what happens, read these blog posts in order:…

5 Ways to Measure a Query

Last Updated November 1, 2021

In my free How to Think Like the Engine class, we start out by measuring query performance using logical reads. That’s the number of 8KB data pages that SQL Server has to read in order to find your query’s results. That’s the measure I use the most because generally speaking, the less data your server…

What Is a Cost-Based Optimizer?

Last Updated September 17, 2021

When you execute a query, the database server has to figure out things like: Which table to process first Which index to use on that table Whether to seek on that index or scan it Which table to process next How to join the data between those two tables When to sort the data For…

Most T-SQL Queries Don’t Even Try to Handle Errors.

Last Updated August 21, 2021

David Tovee asked a great question in yesterday’s Mastering Query Tuning class. He asked his fellow students, “How many of you actually use TRY/CATCH?” I turned it into a Twitter poll because I wanted to check a wider audience: When the developers at my company write new queries, they use TRY/CATCH and exception handling: —…

Why Are Linked Server Queries So Bad?

Last Updated July 15, 2021

Development

No, I don’t want to be your valentine. Remember when you were in school, and you had a crush on someone? You would write a note asking them to be your valentine, and you’d ask a mutual friend to pass the note over to them. The adult equivalent is linked server queries. When your query…

What does SET NOCOUNT ON do?

Last Updated June 4, 2021

When you’re working with T-SQL, you’ll often see SET NOCOUNT ON at the beginning of stored procedures and triggers. What SET NCOUNT ON does is prevent the “1 row affected” messages from being returned for every operation. I’ll demo it by writing a stored procedure in the Stack Overflow database to cast a vote: CREATE…

“I’m getting index seeks. Why are my row estimates still wrong?”

Last Updated June 6, 2021