Indexing

  1. Home
  2. Indexing
  3. (Page 4)

Index Tuning Week: How Many Indexes Are Too Many?

Indexing
28 Comments
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…
Read More

Should Index Changes Require Change Control?

Indexing
9 Comments
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…
Read More

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

Indexing
9 Comments
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…
Read More

A Visual Guide to Choosing an Index Type

Indexing
4 Comments
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…
Read More

[Video] Interpreting Missing Index Recommendations

Indexing, Videos
0
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…
Read More

Management Studio Hides Missing Indexes From You.

Indexing
1 Comment
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…
Read More

Does It Matter Which Field Goes First in an Index?

Indexing
9 Comments
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 =…
Read More

Can Non-SARGable Predicates Ever Seek?

Development, Indexing
4 Comments
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),…
Read More

Mysterious Forwarded Records

Indexing
5 Comments
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…
Read More
Menu