Indexing

This week I’m running a series of posts introducing the fundamentals of index tuning. Yesterday, I used Brent’s 5 and 5 Rule to explain why you want to aim for around 5 indexes per table, and 5 columns (or less) per index. Now, how do we get from our current database situation to a better…

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…

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

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…

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…

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…

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…

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…

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

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…