Development

sp_BlitzLock showing deadlocks

How to Create Deadlocks and Troubleshoot Them

Watch in awe and wonder as I create a deadlock, then use sp_BlitzLock after the fact to show you which queries and tables were involved: Here are the scripts to run in the left hand window: Transact-SQL CREATE TABLE dbo.Lefty (Numbers INT PRIMARY KEY CLUSTERED); INSERT INTO dbo.Lefty VALUES (1), (2), (3); CREATE TABLE dbo.Righty…
Read More

When Query Plans Lie Part 2

Getting Weirder In Part 1, we looked at how query plans can tell us little lies. Now we’re going to look at how those little lies can turn into bigger lies. Adding An Index Right now, this is our query: Transact-SQL SELECT u.UpVotes, u.DownVotes FROM dbo.Users AS u WHERE u.Reputation >= 100000; 123 SELECT u.UpVotes,…
Read More

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?

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 https://70srichard.wordpress.com/2014/12/17/beverly-hills-cop/ 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 StackOverflow.com. 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
Menu
{"cart_token":"","hash":"","cart_data":""}