Indexing

New Fundamentals of Index Tuning Course

Last Updated January 22, 2019

You’re a developer who needs to make your application go faster, and you don’t want to (or can’t!) change the queries. You’ve never been to an official training class, and your manager isn’t about to spring for travel. In my new Fundamentals of Index Tuning course, you’ll learn: How to design indexes for a query…

sp_BlitzIndex's new statistics histograms at the bottom

How Should We Show Statistics Histograms in sp_BlitzIndex?

Last Updated January 4, 2019

Brent Ozar

Indexing, sp_BlitzIndex

If you’re a graduate of my free How to Think Like the SQL Server Engine course – and you’d better be, dear reader – then you’re vaguely familiar with DBCC SHOW_STATISTICS. It’s a command that shows you the contents of a statistics histogram. When I’m doing the first two parts of the D.E.A.T.H. Method – (D)eduplicating identical…

The Pleasure Of Presenting With Joe Obbish

Last Updated November 26, 2020

Erik Darling

Execution Plans, GroupBy Conference, Indexing, Videos

Grouple On At the December 2018 GroupBy I had the honor of co-presenting with one of the smartest guys around, Joe Obbish. Here’s the video of our session, “The Problems With Optional Operators”, and a link to the resources. Enjoy! Resources: Slides and Demos

Why Do Some Indexes Create Faster Than Others?

Last Updated January 2, 2019

Erik Darling

Execution Plans, Indexing

To warn you ahead of time, this post is an exploration without an answer (yet). There’s some interesting stuff in here, but no conclusions. If that’s not your kind of post, feel free to skip it. If it is, well, here goes nothin’… Wild, Wild Life Creating indexes is kind of a funny thing. By…

When Does SARGability Matter Most?

Last Updated August 9, 2019

Erik Darling

Execution Plans, Indexing

!erehT iH I know what you’re thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster. Just watch the video. For more reading, check out these posts: Sargability: Why %string% Is Slow Optional Parameters and Missing Index Requests Computed Columns: Reversing Data…

Do Functions Stop You From Using Indexes?

Last Updated December 5, 2018

Brent Ozar

Indexing, T-SQL

Say I’ve got a function in my WHERE clause: Transact-SQL SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; 123 SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; If I have an index on DisplayName, will SQL Server use it? Sure: Function in the WHERE clause Even though SQL Server can’t seek to “Brent Ozar,” it will…

Indexed View Matching With GROUP BY And DISTINCT

Last Updated November 29, 2018

Erik Darling

Execution Plans, Indexing

Bit Of A Kick I’ve been playing with indexed views a little bit lately for some demos in my Bits Precon. There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT. Fair enough, but you can do GROUP BY. And what’s pretty…

Indexed View Creation And Underlying Indexes

Last Updated November 29, 2018

Erik Darling

Indexing

Accidental Haha While working on some demos, I came across sort of funny behavior during indexed view creation and how the indexes you have on the base tables can impact how long it takes to create the index on the view. Starting off with no indexes, this query runs in about six seconds. Transact-SQL DECLARE…

Tales From Overindexing: Too Many One Column Indexes

Last Updated November 23, 2018

Erik Darling

Execution Plans, Indexing, SQL Server

Master Plan Sometimes you see a query, and it’s hitting one table, and then the query plan looks like a vase full of spaghetti. Usually, there’s a view involved. Usually. Sometimes, there’s just really weird indexing. Here’s A Thing, Look At It Promises This is a query against one table. No views, no functions, no…