Hey, That’s Not My Sort!

Understand Your Plan Mr. Optimizer does the rock n roll hoochie koo with Mrs. Optimizer, c. 1953. When reading query plans, you may sometimes see a sort when you didn’t explicitly ask for data to be sorted. Sometimes they show up to support order-preserving operators like stream aggregates, merge joins, or segments. Other times, they…
Read More

Concurrency Week: How Entity Framework and NHibernate Can Cause Poison RESOURCE_SEMAPHORE Waits

I’ve already blogged about my dislike for ORMs from a production DBA performance tuning standpoint only. I get that they’re useful to developers. I get it. But I’m focused on performance. A quick recap of what I don’t like about ORMs from that other blog post: Implicit conversions due to nvarchar variables vs varchar columns…
Read More
SQL Server Training by Kalen Delaney

Concurrency Week: Can SELECTs Win Deadlocks?

Yes, especially when they’re part of a larger transaction. In this example, I’m going to use a database in Read Committed Snapshot Isolation (RCSI), my favorite isolation level for building new applications from the ground up. Optimistic concurrency (aka MVCC) helps avoid a lot of blocking issues – but not all of them. In a…
Read More

Column Store Deadlocks: Missing Information

People seem to care about deadlocks That’s why I wrote sp_BlitzLock. Why not sp_BlitzDeadlock? Well, I had this song stuck in my head. But enough about that. While poking around with things recently, I created a pretty typical deadlock on a table, but this one had a clustered column store index on it. Of course,…
Read More

The Many Mysteries of Merge Joins

Not A Single Picture Of A Zipper Image humbly borrowed from There are some interesting things about Merge Joins, and Merge Join plans that I figured I’d blog about. Merge joins have at least one interesting attribute, and may add some weird stuff to your query plans. It’s not that I think they’re bad,…
Read More

Why Multiple Plans for One Query Are Bad

I’m going to demo this using the Stack Overflow public database. We’ll use the Users table – which has exactly what you think it has, everyone who’s asked/answered/commented at I need to search for people by their DisplayName, so I’ve created an index on that: Transact-SQL CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName); 1 CREATE INDEX…
Read More

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance

sp_prepare For Mediocre You may remember me from movies like Optimize for… Mediocre? and Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)! Great posts, Kendra! Following the same theme, we found this issue while looking at queries issued from JDBC. Specifically, the prepared statement class seems to cause queries to hit the density vector…
Read More
Database DevOps Training Class

Announcing 2 New Online Classes: Database DevOps and Practical Real-World Performance Tuning

We’re proud to announce that in addition to our existing summer lineup: Always On Availability Groups: The Senior DBA’s Field Guide with Edwin Sarmiento, 3 days $2,995 Data Science Fundamentals with R with Steph Locke, 2 days, $1,995 PowerShell for DBAs with Drew Furgiuele, 2 days, $1,995 We’ve now got two new online training classes…
Read More

How to Get a Random Row from a Large Table

Method 1, Bad: ORDER BY NEWID() Easy to write, but it performs like hot, hot garbage because it scans the entire clustered index, calculating NEWID() on every row: The plan with the scan That took 6 seconds on my machine, going parallel across multiple threads, using tens of seconds of CPU for all that computing…
Read More