Indexing

Does an Index on Just The Clustering Key Ever Make Sense?

Last Updated April 20, 2018

Say you’ve got a table – in this case, I’m using the Stack Overflow Posts table, which holds all of the questions and answers: StackOverflow.dbo.Posts See the clustered index at the bottom? It’s on the Id field, which is an identity – starts at one and goes up to a bajillion. So it would be…

Index Key Column Order And Supporting Sorts

Last Updated April 12, 2018

Erik Darling

Execution Plans, Indexing

Whatever Man When tuning queries that need to sort large amounts of data, sometimes it makes sense to stick the ordering elements as the leading key column(s) in your index. This allows SQL Server to easily sort your data by that column, and then access other key and included columns to satisfy other parts of…

Parking Garages And Predicates

Last Updated April 12, 2018

Erik Darling

Execution Plans, Indexing, SQL Server

Hotels on Wheels Parking garages and predicates have a “lot” in common. GET IT? No no but seriously. They do. If you’ve ever lost your ticket, forgotten where you’ve parked, or just driven around hopelessly waiting to see an empty spot, you probably wish there were a better way to do things. Transact-SQL CREATE TABLE…

The Many Mysteries of Merge Joins

Last Updated April 8, 2018

Erik Darling

Execution Plans, Indexing

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,…

Missing Index Impact and Join Type

Last Updated March 22, 2018

Erik Darling

Indexing

Just Another Way No matter how you delve into missing index requests — whether it’s the plan level, DMV analysis, or (forgive me for saying it), DTA, the requests will generally be the same. They’ll prioritize equality predicates, the columns may or not may be in the right order, the columns may or may not…

If You Can’t Index It, It’s Probably Not SARGable

Last Updated March 13, 2018

Erik Darling

Execution Plans, Indexing

Thumbs Rule OK? When writing queries, it’s really common to want to take shortcuts to express some logic. I know, it “works fine”, and the results are “right”, and you have “more important” things to do. But take a minute here for future you. Just think of reading this post as a continuation of the…

Then Why Doesn’t SQL Always Seek?

Last Updated February 6, 2018

Erik Darling

Execution Plans, Indexing, SQL Server

Scan The Man There seems to be a perpetual battle waged against the Index Scan. At some point it was declared that scans were inferior to seeks, and all energy should be dedicated to eradicating them. Much like asking why the whole plane isn’t made out of the stuff the black box is made out…

Memory Grants: SQL Server’s Other Public Toilet

Last Updated May 26, 2020

Erik Darling

Execution Plans, Indexing, Memory Grants, Parameter Sniffing

Sharing Is Caring When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants. The problem becomes itself when queries start to over and under estimate their practical needs. Second Hand Emotion Queries ask for memory to do stuff. Memory is a shared resource. What kind of stuff? Well, usually…

Functions Can Still Use Indexes, Kinda.

Last Updated May 1, 2021

Erik Darling

Execution Plans, Indexing, SQL Server

This sentence gets repeated a lot. You know the one: “Functions prevent the use of indexes.” Well, knowing you, it’s probably just your indexes. I’ve seen your indexes. Functions can use indexes, but even so, that doesn’t mean that they’re going to perform as well as queries without functions. Which Indexes? In the SUPERUSER database (yeah, I know, I’m…

Computed Columns: Reversing Data For Easier Searching

Last Updated January 19, 2018

Erik Darling

Computed columns, Development, SQL Server

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…

When Does Index Size Change Index Choice?

Last Updated January 13, 2018

Erik Darling

Indexing, SQL Server

Size Matters? One thing I hear a lot is that the optimizer will take the size of an index into account when choosing which one to use. The tl;dr here is that it may make more of a difference when it comes to index width (think the number of columns in an index) than it…

Heaps, Deletes, and Optimistic Isolation Levels

Last Updated March 12, 2018

Erik Darling

Indexing, SQL Server

The Humble Heap If you don’t know this by now, I’m going to shovel it at you: If you have a table with no clustered index (a Heap), and you delete rows from it, the resulting empty pages may not be deallocated. You’ll have a table with a bunch of empty pages in it, and…

Do Disabled Indexes Affect Missing Index Recommendations?

Last Updated December 16, 2017

Brent Ozar

Indexing

I’m so glad you asked! Let’s take a look. Open up the Stack Overflow database, turn on actual execution plans, and run a query that will cause SQL Server to beg and plead for an index: Transact-SQL SELECT * FROM dbo.Users WHERE DisplayName = 'Hovercraft Full Of Eels'; 123 SELECT *FROM dbo.UsersWHERE DisplayName = 'Hovercraft…

Rebuilding Indexes Can Slow Queries Down

Last Updated December 18, 2017

Brent Ozar

Index Maintenance

Today, Jonathan Kehayias wrote about how fragmentation impacts execution plans. I’m really into performance tuning, so I read the post carefully, really interested to see how it impacted query runtime. You should read it too – it’s a great post with a solid demo. But oddly, he didn’t mention the query speeds. I thought that was…

Partition Level Locking: Explanations From Outer Space

Last Updated November 22, 2017

Erik Darling

Development, Locking, Blocking, and Isolation Levels, Partitioning

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…

Good Reasons to Rebuild or Reorganize Indexes

Last Updated November 29, 2017

Erik Darling

Index Maintenance

No, not that It’s not the F word. Not like this embarrassing page that was somehow updated in 2017, which still tells you to reorg at 5% and rebuild at 30% fragmentation, like your data is still on a drive that looks like a record player and might still start making a clicking noise when…

New System Stored Procedures in SQL Server 2017

Last Updated October 3, 2017

Brent Ozar

Columnstore Indexes, SQL Server 2017

Got a few new stored procs for columnstore indexes: sp_add_columnstore_column_dictionary – input params for @table_id, @column_id sp_is_columnstore_column_dictionary_enabled – as above, plus output param for @is_enabled bit sp_remove_columnstore_column_dictionary – input params for @table_id, @column_id And a couple for snapshot views (which appear to be related to the new snapshot messages in sys.messages) sp_refresh_single_snapshot_view – input param for @view_name…

Live Query Plans and Blocking

Last Updated September 21, 2017

Erik Darling

Execution Plans, Indexing, Locking, Blocking, and Isolation Levels, SQL Server

You’d think this would be cool After all, you’ve got a query plan being executed LIVE ON NATIONAL TELEVISION! But it’s not. It’s actually one of those weird reasons why you still need other ways of looking at DMVs for issues. Demo Library Let’s take a closer look! First, we need an index. Indexes are…

Why Columnstore Indexes May Still Do Key Lookups

Last Updated June 25, 2021

Erik Darling

Columnstore Indexes, Execution Plans, Indexing, SQL Server

I was a bit surprised that key lookups were a possibility with ColumnStore indexes, since “keys” aren’t really their strong point, but since we’re now able to have both clustered ColumnStore indexes alongside row store nonclustered indexes AND nonclustered ColumnStore indexes on tables with row store clustered indexes, this kind of stuff should get a…

Optional Parameters and Missing Index Requests

Last Updated September 15, 2017

Erik Darling

Execution Plans, Indexing, SQL Server

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…