Product has been added to your cart.

Development

And it looks bad even in shades.

PSPO: How SQL Server 2022 Tries to Fix Parameter Sniffing

Last Updated August 24, 2022

Parameter Sniffing, SQL Server 2022

Parameter sniffing is a notorious problem for Microsoft SQL Server because it tries to reuse execution plans, which doesn’t work out well for widely varying parameters. Here’s a primer for the basics about how it happens. SQL Server 2022 introduces a new feature called Parameter Sensitive Plan optimization. I’m not really sure why Microsoft capitalized…

Estimated and Actual Plans Can Have Different Shapes.

Last Updated August 4, 2022

Execution Plans

A reader posted a question for Office Hours: Hi Brent, What is your take on Hugo Kornelis’s explanation of execution plan naming. As her his explanation, estimated exec plan is simply an execution plan whereas actual execution plan = execution plan+run-time stats. Do you agree that the naming is flawed and confusing? – Yourbiggestfan I…

[Video] Fundamentals of Stored Procedures at SQLBits

Last Updated July 27, 2022

Anybody can write a stored procedure with a little help from Google. This session is about how to write stored procedures that have a high likelihood of performing well and are easy to troubleshoot. This fast-paced, all-demo session from SQLBits will NOT cover how to write a query, syntax, or performance tuning. This is about…

SQL Server Pop Quiz: A Key Lookup Without the Index

Last Updated July 17, 2022

Execution Plans

How do we get this to show up in an execution plan: Without having an index seek or index scan operation on the same table? Take any copy of the Stack Overflow database and write a query that will produce a key lookup on the Users, table, but it’s not allowed to have an index…

I Wish SQL Server Warned About Hard-Coded Estimates.

Last Updated June 23, 2022

Execution Plans

I wish we got a yellow bang on execution plans when SQL Server was making up an estimate out of thin air. I’ll give you an example: if you compare two columns on the same table, looking to find rows where they’re equal, SQL Server has a hard-coded estimate that 10% of the rows will…

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

Last Updated June 7, 2022

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

Execution Plans

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 September 2, 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),…

And it looks bad even in shades.

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:…

Mastering Query Tuning

5 Ways to Measure a Query

Last Updated November 1, 2021

Execution Plans

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

Execution Plans

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…