Development

Then Why Doesn’t SQL Always Seek?

Last Updated February 6, 2018

Scan The Man There seems to be a perpetual battle waged against the Index Scan. At some point it was declared that scans were inferior to seeks, and all energy should be dedicated to eradicating them. Much like asking why the whole plane isn’t made out of the stuff the black box is made out…

Memory Grants: SQL Server’s Other Public Toilet

Last Updated May 26, 2020

Erik Darling

Execution Plans, Indexing, Memory Grants, Parameter Sniffing

Sharing Is Caring When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants. The problem becomes itself when queries start to over and under estimate their practical needs. Second Hand Emotion Queries ask for memory to do stuff. Memory is a shared resource. What kind of stuff? Well, usually…

The Annals of Hilariously Bad Code, Part 2

Last Updated February 9, 2018

Erik Darling

Development

You’re Crazy In Part 1, I showed you code that I think has some anti-patterns in it. In case you didn’t recognize it, it’s actually code that Microsoft wrote. It’s from sp_delete_backuphistory, and it is plum awful. I have a lot of personal dislike for this one, because after inheriting a server with a 25GB msdb,…

Creating Insert Triggers to Silently Ignore Data You Don’t Want

Last Updated February 1, 2018

Brent Ozar

Bad Idea Jeans, T-SQL

Say you’ve got an application that insists on inserting data into the database, and…you don’t want the data. You want the application to THINK it inserted the data – you don’t want to roll it back or return an error to the end user. You just don’t want the data, and you don’t want the…

Using LIKE on Integers Gets You Implicit Conversion

Last Updated February 1, 2018

Brent Ozar

T-SQL

Using the Stack Overflow public export, take these two queries looking for a particular user by Id (the clustering key): Transact-SQL SELECT * FROM dbo.Users WHERE Id = 26837; SELECT * FROM dbo.Users WHERE Id LIKE 26837; 12 SELECT * FROM dbo.Users WHERE Id = 26837;SELECT * FROM dbo.Users WHERE Id LIKE 26837; The first…

The Annals of Hilariously Bad Code, Part 1: Critique the Code

Last Updated February 6, 2018

Erik Darling

Development

Hey Beavis That’s the first time I’ve used “annals” correctly. I will be 40 sooner than later. Sometimes when we blog, we get inspiration from clients, students, other bloggers, questions on Stack Exchange, nightmares, or just drinking heavily. I’m going to anonymize some code, and I want you to guess where it came from. I’d…

Functions Can Still Use Indexes, Kinda.

Last Updated May 1, 2021

Erik Darling

Execution Plans, Indexing, SQL Server

This sentence gets repeated a lot. You know the one: “Functions prevent the use of indexes.” Well, knowing you, it’s probably just your indexes. I’ve seen your indexes. Functions can use indexes, but even so, that doesn’t mean that they’re going to perform as well as queries without functions. Which Indexes? In the SUPERUSER database (yeah, I know, I’m…

Two Code Patterns That Don’t Virtualize Well

Last Updated January 19, 2018

Brent Ozar

T-SQL

Virtualization used to be a really Big Deal™ for database admins: we had to do a lot of careful planning to get a virtualization project done right. These days, virtualization is more and more of a no-brainer: most apps make the transition just fine. Every now and then, though, an exception pops up – usually…

Computed Columns: Reversing Data For Easier Searching

Last Updated January 19, 2018

Erik Darling

Computed columns, Development, SQL Server

During Training We were talking about computed columns, and one of our students mentioned that he uses computed columns that run the REVERSE() function on a column for easier back-searching. What’s back-searching? It’s a word I just made up. The easiest example to think about and demo is Social Security Numbers. One security requirement is…

How Much Can One Row Change A Plan, Part 4

Last Updated January 17, 2018

Erik Darling

Execution Plans, SQL Server

Monte Carlo Or Bust In Part 3, I showed you how two queries with TOP clauses can produce wildly different plans. To figure some stuff out about why the plans changed, let’s focus on the Nested Loops Join plan. Why? Because when I force the Hash Join plan, the results are no different. I get…

How Much Can One Row Change A Plan, Part 3

Last Updated January 17, 2018

Erik Darling

Execution Plans, SQL Server

All The Rage We’ve asked how much one column and even one row can change a plan before. The doctor says I’m not allowed to read blog comments anymore on account of my blood pressure, but the AI I wrote in PowerShell assures me they were well-received. So here we go again, ONCE MORE INTO THE FRAY!…

“But It Worked in Development!” – 3 Hard Performance Problems

Last Updated January 8, 2019

Brent Ozar

Development, Memory Grants

You’ve been performance tuning queries and indexes for a few years, but lately, you’ve been running into problems you can’t explain. Could it be RESOURCE_SEMAPHORE, THREADPOOL, or lock escalation? These problems only pop up under heavy load or concurrency, so they’re very hard to detect in a development environment. In a very fast-paced session, I’ll…

SQL Server 2017 CU3 adds optimizer row goal information in query plans

Last Updated March 22, 2018

Erik Darling

Execution Plans, SQL Server 2017

Row Whats? Like it or not, the optimizer has row goals. We’re not going to go terribly in-depth here, because the point is just to make you aware that this is in executions plans, and I’ll be adding it to the FRK in an upcoming release. I am going to give you a round up…

SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

Last Updated May 26, 2020

Erik Darling

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2017

Where have you been all my life? Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills. They’re not mutually exclusive — some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill…

SQL Server 2017 CU3 add a new CXCONSUMER wait type

Last Updated August 9, 2020

Erik Darling

Execution Plans, SQL Server 2017, Wait Stats

SQL Server 2017’s new CXCONSUMER wait type was originally announced by Microsoft’s Pedro Lopes, and now it’s out. Here’s what it means for performance tuners. According to Pedro, this wait is the “safe” type of parallelism wait, as opposed to the CXPACKET wait type, which means work isn’t evenly balanced across all of our cores. Pedro blogged…

SQL Server 2017 CU3 adds execution statistics for scalar-valued, user-defined functions

Last Updated December 9, 2018

Erik Darling

Execution Plans, SQL Server 2017

Update This seems to finally be fixed as of CU12 for SQL Server 2017. If you’re not already patched up, head over to the link to get there. Estimated Plans Need Not Apply This is, of course, only available in actual plans. All together now: If a query uses one or more user-defined scalar functions…

Introducing sp_BlitzLock: For Troubleshooting SQL Server Deadlocks

Last Updated September 26, 2023

Erik Darling

Deadlocks, First Responder Kit, sp_BlitzLock

Deadlocks are hard – especially when there are a lot of them. When there are dozens or more, looking at each graph is a pain. sp_BlitzLock gives you a slice-and-dice analysis of which tables, indexes, and queries are involved in most of your deadlocks so you can quickly get to the root cause. sp_BlitzLock works…

Query Plan Oddities: Two Identical Missing Indexes

Last Updated December 9, 2017

Brent Ozar

Execution Plans

As I’ve been building labs for my Mastering Query Tuning class, I’ve been spelunking through data.stackexchange.com. It’s where anybody can write queries against the Stack Overflow databases, and share them with friends. For example, Daniel Vandersluis wrote a query to see how many edits he has: Transact-SQL -- How many edits do I have? --…

sp_BlitzQueryStore: A Gentle Introduction

Last Updated May 26, 2020

Erik Darling

Execution Plans, First Responder Kit, Parameter Sniffing, Query Store (QDS), sp_BlitzQueryStore, SQL Server 2016, SQL Server 2017

Odds and ends During the precon, we asked how many people were on 2016, how many people were aware of query store, how many people were using it, and how many people were using sp_BlitzQueryStore. About 1/3 of the hands went up at first, then for each successive question, fewer and fewer hands stayed up.…

Partition Level Locking: Explanations From Outer Space

Last Updated November 22, 2017

Erik Darling

Development, Locking, Blocking, and Isolation Levels, Partitioning

It’s not that I don’t like partitioning It’s just that most of my time talking about it is convincing people not to use it. They always wanna use it for the wrong reasons, and I can sort of understand why. Microsoft says you can partition for performance. Partitioning large tables or indexes can have the…