Indexing

Home
Indexing

ColumnStore Indexes And Recursive CTEs

July 27, 2017

Columnstore Indexes, Indexing, SQL Server

dba, development, performance tuning, 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

dba, development, performance tuning, 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

dba, development, performance tuning, 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

dba, development, performance tuning, sql server

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

dba, development, performance tuning, sql server

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

dba, development, performance tuning, sql server

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

dba, development, performance tuning, 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

dba, development, performance tuning, 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

dba, development, performance tuning, sql server

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

dba, development, performance tuning, 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

In-Memory OLTP (Hekaton), Licensing, Partitioning, SQL Server

A really odd choice I’ve talked a bit about my annoyance with hardware limits for Standard Edition here and here. If you’re a bunch of SQL-savvy developers who understand the Enterprise Edition features that are now available in 2016 SP1, great. You’re a very small minority of shops. I’m talking about places like StackOverflow and…

Top 5 Overlooked Index Features

November 10, 2016

Indexing, SQL Server

dba, development, performance tuning, sql server

I see a lot of indexes Most of them are pretty vanilla. Some are pretty memorable, like one that I saw with 93 INCLUDES. A lot of things that people could do with indexes get overlooked, either because they don’t know about them, or they’re not sure when they’ll help. So, here goes my attempt…

Can Indexes My Query Doesn’t Use Help My Query?

November 9, 2016

Indexing, SQL Server

dba, development, performance tuning, sql server

This came up during Office Hours And I love questions like this, because they reveal some interesting underpinnings of how the cardinality estimator works. It was something along the lines of “I have a slow query, and when I add an index the query goes faster even though it doesn’t use the index I created.”…

Partitioned Views: A How-To Guide

September 22, 2016

Indexing, Partitioning, SQL Server

dba, development, performance tuning

This is not about table partitioning If you want to learn about that, there’s a whole great list of links here, and the Best Blogger Alive has a tremendous post on why table partitioning won’t make your queries any faster over here. With that out of the way, let’s talk about partitioned views, and then…

Bad Idea Jeans Week: Dynamically Generating 999 Indexes on a Table

August 25, 2016

Bad Idea Jeans, Humor, Indexing, SQL Server

Let’s say, just theoretically, you wanted to demo a query that takes SQL Server a long time to compile. And in order to make SQL Server’s job tougher, you wanted to create the maximum number of indexes possible on a table. You might do something like this: Transact-SQL SELECT TOP 999 'CREATE INDEX [IX_' +…

How To Fix Forwarded Records

July 29, 2016

Index Maintenance, SQL Server

Some of our clients have very high forwarded record counts and aren’t aware of it until they run sp_BlitzFirst and get an alert about high Forwarded Records per Second. Some of these clients are using Ola Hallengren‘s IndexOptimize stored procedure to maintain their indexes. This brought up a question of whether or not rebuilding a…

Where Do Missing Index Requests Come From?

July 27, 2016

Indexing, sp_BlitzIndex

Be honest, here You don’t care about most indexing problems. Duplicates, Borderline Duplicates, Unused, Wide, Non-aligned Partitions… All you’re here for are the Missing Indexes. Wham, bam, your query finishes in less than a second, ma’am. Take this quarter, go downtown and have a rat gnaw that thing off your face. And I get it.…

Why Not Just Create Statistics?

July 14, 2016

Indexing, SQL Server, Statistics

dba, performance tuning, sql server

Here at Brent Ozar Unlimited We have a proud tradition of not blaming index fragmentation for everything. There are points you should deal with it, but they’re probably not 5% and 30% and 1000 pages. But that’s not what this blog post is about. I’m hoping to clarify why we’re more interested in up to…

Menu