Development

SQL Server 2022 Tells You Why A Query Can’t Go Parallel.

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…
Read More

“Index Seek” Doesn’t Mean Much.

Execution Plans
13 Comments
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…
Read More

What Do the LCK_M_SCH_M and LCK_M_IS Wait Types Indicate?

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…
Read More

Error Handling Quiz Week: Tryin’ TRY/CATCH

T-SQL
32 Comments
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),…
Read More
Mastering Query Tuning

5 Ways to Measure a Query

Execution Plans
2 Comments
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…
Read More

What Is a Cost-Based Optimizer?

Execution Plans
0
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…
Read More

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

T-SQL
45 Comments
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: —…
Read More