Development

When someone says, “Find all the rows that have been deleted,” it’s a lot easier when the table has an Id/Identity column. Let’s take the Stack Overflow Users table: It has Ids -1, 1, 2, 3, 4, 5 … but no 6 or 7. (Or 0.) If someone asks you to find all the Ids…

Should You Use SQL Server 2022’s STRING_SPLIT?

Last Updated December 16, 2022

SQL Server 2022 improved the STRING_SPLIT function so that it can now return lists that are guaranteed to be in order. However, that’s the only thing they improved – there’s still a critical performance problem with it. Let’s take the Stack Overflow database, Users table, put in an index on Location, and then test a…

Should You Use SQL Server 2022’s DATETRUNC?

Last Updated December 16, 2022

SQL Server 2022 introduced a new T-SQL element, DATETRUNC, that truncates parts of dates. For example: SELECT DATETRUNC(year, '2017-06-01'); 1 SELECT DATETRUNC(year, '2017-06-01'); Truncates everything in that date other than the year, so it returns just 2017-01-01 00:00: You might ask, “Well, why not just use YEAR()?” That’s a good question – there are times…

Slow “Having” Query? Try Pre-Filtering.

Last Updated November 21, 2022

I was helping a client with a query, and I’m going to rework the example to use the Stack Overflow database for easier storytelling. Say we need to: Find all the locations where users have logged in since a certain date, then Return the total count of people who live in those locations One way…

#tsql2sday: Start Your Dynamic SQL with a Comment.

Last Updated October 7, 2022

When you write dynamic SQL, start like this: CREATE OR ALTER PROC dbo.MyProc AS BEGIN DECLARE @StringToExecute NVARCHAR(4000); SET @StringToExecute = N'SELECT /* MyProc */ '; ... 12345 CREATE OR ALTER PROC dbo.MyProc ASBEGIN DECLARE @StringToExecute NVARCHAR(4000); SET @StringToExecute = N'SELECT /* MyProc */ '; ... Right after the SELECT (or INSERT or UPDATE or whatever),…

This Week, Fundamentals of Query Tuning Classes are Free!

Last Updated October 9, 2022

Every weekday during the month of October, one of the modules from my Fundamentals classes will be absolutely free to watch. But you gotta watch it that day – you snooze, you lose. This week is Fundamentals of Query Tuning. Last week’s class had hands-on labs you had to follow, but this week’s doesn’t. You’re welcome…

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

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

T-SQL, Videos

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

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

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

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