Press "Enter" to skip to content

Category: Indexing

Finding Unused Indexes on an Instance

Published 2023-01-05 by Kevin Feasel

Matthew McGiffen goes searching:

The script uses the undocumented sp_MSforeachdb internal stored procedure to iterate through all the databases (excluding the system ones) and store the results of a standard unused indexes script into a temp table. The table can then be queried to analyse the results.

This script comes with a caveat, that the sp_MSforeachdb procedure can be unreliable and on occasion skip databases so you should check that the results include all the databases you are interested in. A simple count of distinct database names, and comparing that against the number of databases on your instance should be sufficient.

That caveat aside (and if you are concerned about it, check out Aaron Bertrand’s sp_foreachdb), click through for the script. I do like that this limits you to just zero-read indexes, as even write-heavy indexes may still be useful.

Comments closed

Avoid Unnecessary Indexes: Postgres Edition

Published 2023-01-03 by Kevin Feasel

Laetitia Avrot has some advice:

This is why, when I’m called for a performance problem (or for an audit), my first take is to look at the size of the data compared to the size of the indexes. If you store more indexes than data for a transactional workload, that’s bad. The worst I’ve seen was a database with 12 times more indexes stored on disk than data! Of course, it was a transactional workload… Would you buy a cooking book with 10 pages of recipes and 120 pages of indexes at the end of the book?

The problem with indexes is that each time you write (insert, update, delete), you will have to write to the indexes too! That can become very costly in resources and time.

Click through for some Postgres-specific guidance and links to some useful scripts along the way.

Comments closed

Bringing Order to a Columnstore Index

Published 2022-12-15 by Kevin Feasel

Tibor Karaszi puts columnstore ducks in a row:

Data for a columnstore index is divded in groups of approximate 1 million rows, rowgroups. Each rowgroup has a set of pages for each column. The set of pages for a column in a rowgroup is called a segment. SQL Server has meta-data for the lowest and highest value for a segment. There are no SEEKs in a columnstore index. But, SQL Server can use this meta-data to skip reading segments, with the knowledge that “this segment cannot contain any data that I need based on my predicates in my WHERE clause”.

Also, you might want to do these operations using MAXDOP 1, so we don’t have several threads muddling our neat segment alignment.

I’m not sure I actually set the ORDER BY clause on columnstore indexes all that often—a quick mental survey says maybe once, though that could be my own failing rather than a statement on the utility of ordered columnstore indexes.

Comments closed

Lessons Learned from Index Tuning

Published 2022-11-02 by Kevin Feasel

Lee Markum has seven lessons for us:

SQL Server indexing basics are critical to query and server performance. Resources, like CPU and disk, are affected by the indexes that you have, or the ones you’re missing.

In the StackOverflow2013 database we’re going to look at Badges and users. Specifically, I want to start by seeing what badges a user has and when that user received them. Some badges, because of the type of badge it is, can be awarded more than once.

Click through for a demonstration.

Comments closed

An Analysis of Resumable Online Index Operations

Published 2022-10-20 by Kevin Feasel

Chris Taylor sums it up:

This is more of a heads up for me / reminder regarding some of the caveats to using ONLINE / RESUMABLE index operations with SQL Server.

Don’t get me wrong, there’s a ton of advantages to using ONLINE and RESUMABLE which I will highlight below but here is the link to the Microsoft Learn page if you want more in depth information

Read on for the pros and cons. I do like the idea, though I personally haven’t used the feature.

Comments closed

Specifying Multiple Indexes in a Table Hint

Published 2022-10-14 by Kevin Feasel

Michael J. Swart is not satisfied with just one index:

My team wondered if this could be used as to help with a concurrency problem. We recently considered using it to resolve a particular deadlock but we had little success.

It’s useful to think that SQL Server takes locks on index rows instead of table rows. And so the idea we had was that perhaps taking key locks on multiple indexes can help control the order that locks are taken. But after some effort, it didn’t work at avoiding deadlocks. For me, I’ve had better luck using the simpler sp_getapplock.

I now would be curious what the maximum number of such index hints would be so we could apply Swart’s 10% Rule. Though I suppose, no matter the number, this would be a degenerate case of the rule, always leading you down to “one or zero, and stop messing with my database!”

Comments closed

Careful Batching

Published 2022-09-22 by Kevin Feasel

Michael J. Swart follows up on an older post:

When I wrote Take Care When Scripting Batches, I wanted to guard against a common pitfall when implementing a batching solution (n-squared performance). I suggested a way to be careful. But I knew that my solution was not going to be universally applicable to everyone else’s situation. So I wrote that post with a focus on how to evaluate candidate solutions.

But we developers love recipes for problem solving. I wish it was the case that for whatever kind of problem you got, you just stick the right formula in and problem solved. But unfortunately everyone’s situation is different and the majority of questions I get are of the form “What about my situation?” I’m afraid that without extra details, the best advice remains to do the work to set up the tests and find out for yourself.

Definitely read the original article first. My normal approach is the naive + index method, so I’ll have to try out Michael’s method as well next time I need to delete a chunk of records.

Comments closed

Eager Index Spooling

Published 2022-09-20 by Kevin Feasel

Chad Callihan has a great analogy for eager spools:

Think of a time when you went to someone for help on a problem. Rather than walk you through how to solve the problem, that someone just groans, speeds through solving it for you, and sends you on your way without any explanation.

Did you learn anything? No.

Will you need help again next time that same problem comes up? You bet.

I also like Erik Darling’s explanation that an eager spool is SQL Server’s passive-aggressive way of telling you that you need an index but no, you’re just too busy to create one so I’ll just keep working here all day and do you even call your poor mother anymore?

Comments closed

Blocking during Creation of Indexed Views

Published 2022-09-07 by Kevin Feasel

Tom Zika runs into a problem:

There was a need to make changes to a table with an Indexed View. Since Indexed Views must be created with SCHEMABINDING, the View must be dropped and recreated.

From past experience, I knew that this operation blocked all queries (Read/Write) that referenced any table from the View’s definition for the duration of the Clustered index creation, even under the RCSI level.

Because the index might be large and the maintenance window small, I want to do that as fast as possible.

Tom does a good job of taking us through the problem as well as the solution. Definitely worth a read if you’re creating indexed views with Enterprise Edition.

Comments closed