Indexing

This week I’m running a series of posts introducing the fundamentals of index tuning. First up, let’s talk about understanding how many indexes make sense on a table. The more nonclustered indexes you have on a table, the slower your inserts and deletes will go. It’s that simple. If you have a table with 10…

Unused Indexes – Are they really unused or have they just not been used YET?

October 2, 2018

Tara Kizer

During our Critical Care® sessions with clients, we often see unused indexes (reads=0) with high writes. Sometimes these unused indexes have very high writes. If an index is never being used to help with performance but SQL Server is having to maintain it for INSERT, UPDATE and DELETE operations, then why keep the index around?…

Should Index Changes Require Change Control?

September 14, 2018

We got a phenomenal series of questions from a client, and I wanted to encapsulate the answers into a blog post to help more folks out: Should all index changes require testing in a staging environment, no matter how big or small? What would be a reasonable timeline duration from index identification to deployment? What…

Forwarded Fetches and Bookmark Lookups

September 7, 2018

Base Table When you choose to forgo putting a clustered index on your table, you may find your queries utilizing forwarded fetches — SQL Server’s little change of address form for rows that don’t fit on the page anymore. This typically isn’t a good thing, though. All that jumping around means extra reads and CPU…

It’s Okay If You Don’t Create Statistics.

September 6, 2018

Along with the ability to create indexes (which you most definitely should be doing), SQL Server gives you the ability to create statistics. This helps SQL Server guess how many rows will come back for your searches, which can help it make better decisions on seeks vs scans, which tables to process first, and how…

Single-Column-Key Missing Index Recommendations are Usually Wrong.

September 4, 2018

When you’re looking at an index recommendation – whether it’s in an execution plan or the missing index DMVs – it helps to understand Clippy’s blind spots. Let’s start with the small StackOverflow2010 database so you can follow along. (It’s just a 1GB direct download, and it expands to a 10GB database that reflects StackOverflow.com…

Tall Tales From Table Variables

August 23, 2018

Secret Squirrel When you modify a table with multiple indexes, SQL Server may choose either a narrow plan, if it doesn’t think all that many rows are going to change, or a wide plan if it thinks many will. In narrow plans, the work SQL Server has to do to modify many indexes is hidden…

A Visual Guide to Choosing an Index Type

August 7, 2018

Warning: I’m about to overly simplify a whole lot of topics to make things easy. Armchair architects, warm up your flamethrowers. Your table has rows and columns kinda like a spreadsheet: In most applications, your users care about all of the rows, and all of the columns. However, they put certain columns in the where…

[Video] Interpreting Missing Index Recommendations

July 11, 2018

Indexing, Videos

This week, we’re sharing Instant Replay videos from our training classes. Next up is Pinal Dave talking about missing index requests from the dynamic management views. It doesn’t matter whose script you use – they’re all built atop SQL Server’s missing-index DMVs, and they all have the same limitations. Treat the missing index requests the…

Management Studio Hides Missing Indexes From You.

July 3, 2018

SQL Server Management Studio only shows you the first missing index recommendation in a plan. Not the best one. Not all of them. Just whichever one happens to show up first. Using the public Stack Overflow database, I’ll run a simple query: Transact-SQL SELECT c.CreationDate, c.Score, c.Text, p.Title, p.PostTypeId FROM dbo.Users me INNER JOIN dbo.Comments c…

Does It Matter Which Field Goes First in an Index?

June 28, 2018

Let’s take the dbo.Users table from the Stack Overflow database, which holds exactly what you think it holds – the list of users: StackOverflow.dbo.Users Say I want to count up all of the people with 1 reputation point: Transact-SQL SELECT COUNT(*) FROM dbo.Users WHERE Reputation = 1; 1 SELECT COUNT(*) FROM dbo.Users WHERE Reputation =…

Can Non-SARGable Predicates Ever Seek?

June 15, 2018

Development, Indexing

Cheating At Candy Crush The short answer is that yes, they can. But only with a little extra preparation. Before I show you what I mean, we should probably define what’s not SARGable in general. Wrapping columns in functions: ISNULL, COALESCE, LEFT, RIGHT, YEAR, etc. Evaluating predicates against things indexes don’t track: DATEDIFF(YEAR, a_col, b_col),…

Indexing Strategies For Cross Apply

June 12, 2018

Slicked Brad The apply operator remains one of my favorite tools, but much like CTEs and windowing functions, they’re not totally magical unless you look at your indexes. Starting with a dead simple query on the Users table, it’d be easy to come up with an ideal index for this one thing. Transact-SQL SELECT u.Id,…

Mysterious Forwarded Records

June 1, 2018

Thinking Of You When people think about Heaps and the problems they can cause, they don’t often think of temp tables and table variables as Heaps. Of course, without a clustered index, any table is a Heap. This isn’t an argument for or against indexing temp tables, but while working with a client we came…

Behind Every Trivial Plan Is A Good Demo

May 29, 2018

A While Back I started this Q&A on Stack Exchange about ISNULL. It’s one of the most common client issues I see, and putting stuff like that out in a different community to get more opinions on it is valuable. Also, I’m saving up points for a Stack Overflow Swiss Army Knife. That’s what you…

Trivial Plans, Simple Parameterization and Check Constraints

May 22, 2018

Trivial Plans Are Weird They’re the optimizer’s “Yes, Dear”. The thing is, sometimes “Yes, Dear” can miss some important details. Cost based decisions about operators Potential index improvements Contradiction detection for check constraints Simple parameterization, according to… uh… Official Microsoft documentation? Can only occur in a Trivial Plan. Which makes sense. Simple parameterization is considered…

Where Clustered Index Keys Dare

May 11, 2018

Indexing, SQL Server

Colonel Clustered We’ve blogged a couple times about how clustered index key columns get stored in your nonclustered indexes: here and here. But where they get stored is a matter of weird SQL trivia. You see, it depends on how you define your nonclustered index. “It Depends” We all scream for dependencies! Hooray! If you…

When Query Plans Lie Part 2

May 9, 2018

Execution Plans, Indexing, SQL Server

Getting Weirder In Part 1, we looked at how query plans can tell us little lies. Now we’re going to look at how those little lies can turn into bigger lies. Adding An Index Right now, this is our query: Transact-SQL SELECT u.UpVotes, u.DownVotes FROM dbo.Users AS u WHERE u.Reputation >= 100000; 123 SELECT u.UpVotes,…

When Query Plans Lie Part 1

May 7, 2018