development

Computed Columns: Reversing Data For Easier Searching

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…
Read More
Pocket Square

sp_BlitzQueryStore: A Gentle Introduction

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.…
Read More

Partition Level Locking: Explanations From Outer Space

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…
Read More

Optional Parameters and Missing Index Requests

That’s when it all gets blown away At one point or another in everyone’s SQL-querying career, they end up writing a query that goes something like this: Transact-SQL SELECT something FROM stuff WHERE (@thing1 is NULL or whatever = @thing1) AND ... 1234 SELECT somethingFROM stuffWHERE (@thing1 is NULL or whatever = @thing1)AND ... These…
Read More

How Much Can One Column Change A Query Plan? Part 2

What happened in Part 1? Join Elimination, naturally. Until the end. My copy of the Stack Overflow database doesn’t have a single foreign key in it, anywhere. If we go down the rabbit hole a couple steps, we end up at a very quotable place, with Rob Farley. 2. Duplicated rows Not necessarily duplicated completely,…
Read More

How Much Can One Column Change A Query Plan? Part 1

Beyond Key Lookups I’m going to show you two queries, and two query plans. The two queries are only different by one selected column, but the two query plans are wildly different. Unless I’ve talked to you in the last few days, these may surprise you as well. Here’s the first one. Transact-SQL DECLARE @DisplayName…
Read More

Five Mistakes Performance Tuners Make

There’s no Top in the title And that’s because a TOP without an ORDER BY is non-deterministic, and you’ll get yelled at on the internet for doing that. This is just a short collection of things that I’ve done in the past, and still find people doing today when troubleshooting performance. Sure, this list could…
Read More

Locking When There’s Nothing To Lock

Demo Day We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it’s pretty awesome. Where things get tricky is with locking demos. Sometimes the modifications can take a long time. This may be by design if you need to show long-held locks by sessions that aren’t…
Read More

ColumnStore Indexes And Recursive CTEs

Alone Together When I think about SQL Server features, I often picture a high school cafeteria. AGs are making fun of Mirroring, index rebuilds are walking around taking everyone’s lunch, dta is making a glue sandwich, and no one knows who Service Broker is even though they’ve been going to school together since Kindergarten. At…
Read More

sp_BlitzCache: Eventual Compatibility With Azure

Better than nothing Yesterday it was announced that global temp tables were available in public preview for Azure. That means that unmodified versions of sp_BlitzCache (which uses a global temp table to hold a whole bunch of information) will be compatible up there. This is a nice addition for Azure, and an even nicer addition…
Read More

There’s Something Funny About Variable Assignment

Guess who I’m going to give you three queries, and you have to guess what the output will be before you run them. Here they are: Transact-SQL DECLARE @DatabaseName NVARCHAR(256) = N'' SELECT @DatabaseName = d.name FROM sys.databases d WHERE d.name = 'master' AND compatibility_level = 130 ORDER BY d.database_id PRINT @DatabaseName SELECT @DatabaseName =…
Read More
Menu
{"cart_token":"","hash":"","cart_data":""}