Indexing

It’s one of those fierce religious wars that divides families and tears loved ones apart. First, if two tables are related, should we tell SQL Server about it? If SQL Server knows that there’s a relationship, then it can make better decisions when building execution plans. The problem is that SQL Server has to be…

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

August 23, 2017

Columnstore Indexes, Development, Execution Plans, Indexing, SQL Server, SQL Server 2017

Yazoo Over on his blog, fellow Query Plan aficionado Joe Obbish has a Great Post, Brent® about query patterns that qualify for Rowgroup Elimination. This is really important to performance! It allows scans to skip over stuff it doesn’t need, like skipping over the dialog in, uh… movies with really good fight scenes. Car chases?…

How to Drop All Your Indexes – Fast

August 11, 2017

Bad Idea Jeans, Indexing

Sometimes I need to reset stuff during performance training classes. I know some of you teach classes, too, and some of you just like doing crazy stuff. So here you go, a stored procedure to lose weight fast: Transact-SQL CREATE OR ALTER PROCEDURE [dbo].[DropIndexes] @SchemaName NVARCHAR(255) = 'dbo', @TableName NVARCHAR(255) = NULL, @WhatToDrop VARCHAR(10) =…

Five Mistakes Performance Tuners Make

August 8, 2017

Development, Execution Plans, Indexing, SQL Server

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…

Locking When There’s Nothing To Lock

August 3, 2017

Development, Execution Plans, Indexing, Locking, Blocking, and Isolation Levels

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…

Why Missing Index Recommendations Aren’t Perfect

August 2, 2017

Using the good ol’ Stack Overflow public database, get the execution plan for this query – either estimated or actual, doesn’t matter: Transact-SQL SELECT Id FROM dbo.Users WHERE LastAccessDate = '2016/11/10'; 123 SELECT IdFROM dbo.UsersWHERE LastAccessDate = '2016/11/10'; In the execution plan, SQL Server asks for a missing index on the LastAccessDate field: Missing index…

ColumnStore Indexes And Recursive CTEs

July 27, 2017

Columnstore Indexes, Indexing, SQL Server

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…

A Better Way To Select Star

July 5, 2017

Development, Execution Plans, Indexing, SQL Server

Mindless Self Promotion I liked writing this blog post so much that I wrote an entire presentation on it. If you’d like to see it at GroupBy, click the link and vote. Update: You can watch the video for this session here. Much has been written about this It’s probably one of the lowest hanging…

Query Plans: Trivial Optimization vs Simple Parameterization

June 16, 2017

Execution Plans, Indexing, SQL Server

Facemaking You know when you think you know something, and the obviousness of it makes you all the more confident that you know it? That’s usually the first sign that there’s a giant gotcha waiting for you. And that’s what happened to me over the weekend. The setup I answered a question. And after I…

Will SQL Server Always Do Index Seeks on Numbers?

May 11, 2017

I often get the question of “Will SQL Server do an index seek with ____?” Another way to phrase it is, “Is this query sargable?” We throw the term “sargable” around to mean that SQL Server can do an index seek to satisfy your search arguments. (We’re specifically talking about index seeks here, not scans – not…

Is it ever worth adding indexes to table variables?

April 20, 2017

Execution Plans, Indexing

Disclaimer I found this totally by accident, and it even surprised me. You can probably guess the TL;DR on this is yes, huh? Dude, where’s my table variable? I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret. Okay, so it’s just embarrassing. Anyway. I had…

Checking for Strange Client Settings with sys.dm_exec_sessions

March 31, 2017

Configuration Settings, Indexing

In my performance training class, people get really excited when I cover filtered indexes, computed columns, and indexed views. Then they get a really strange expression on their face when I talk about one of the biggest drawbacks: if your connection options aren’t set correctly, your delete/update/insert (DUI) operations will fail. That gave me a really…

Indexed Views And Data Modifications

March 16, 2017

Love/Hate When indexed views help, they make aggregation queries super fast. There are some kind of horrible limitations and requirements. When they hurt, your modifications slow to a crawl because SQL has to not only keep your horrible indexes synchronized, it also has to keep your indexed view indexes in sync now, too. But SQL…

Crappy Missing Index Requests

February 21, 2017

Execution Plans, Indexing, sp_BlitzIndex

When you’re tuning queries It’s sort of a relief when the first time you get your hands on it, you get the plan and there’s a missing index request. Even if it’s not a super high-value one, something in there is crying for help. Where there’s smoke, there’s a bingo parlor. But does adding missing…

Indexes For Kids

January 18, 2017

Indexing, SQL Server

Kid Tested When you work with something a lot, you tend to extrapolate concepts from it out to everything you see. That one person collecting tickets is a serial zone. That food truck line is a HEAP. The empty subway car that smells like a toilet is Replication. Stuff like that. If you have one…

Filtered Indexes and Variables: Less Doom and Gloom

January 4, 2017

Indexing, SQL Server

It Is Known That when you use filtered indexes, they get ignored when your queries are parameterized. This is a Plan Caching Thing©, of course. The simplest example is a bit column with a filtered index. If your index is on WHERE Bit = 1, it doesn’t have data for WHERE Bit = 0. That…

Indexing Temp Tables

December 15, 2016

Execution Plans, Indexing, SQL Server, TempDB

People often don’t give this thought Which is a shame, because I see people sticking fairly large amount of data into temp tables. On the rare occurrence that I do see them indexed, it’s a nonclustered index on a column or two. The optimzer promptly ignores this index while you select 10 columns and join…

Selective XML Indexes: Not Bad At All

December 6, 2016

Indexing, SQL Server

Promises, Promises I said I’d follow up on this, and here it is. I’m by no means the world’s foremost authority on these, I just played with a simple example until I figured out what worked the best for my query. So you can all play along at home, I’m using XML we all have…

Initial Thoughts on 2016 SP1

November 21, 2016