Press "Enter" to skip to content

Category: Indexing

Halloween Protection and Non-Clustered Indexes

Published 2023-02-21 by Kevin Feasel

Jared Poche digs further into Halloween protection:

I find myself talking about the Halloween Problem a lot and wanted to fill in some more details on the subject. In short, the Halloween Problem is a case where an INSERT\UPDATE\DELETE\MERGE operates on a row more than once, or tries to and fails. In the first recorded case, an UPDATE changed multiple rows in the table more than once.

So let’s take a look at an example using a publicly available database, WideWorldImporters.

Read on for a case of Jared starting from the known and moving into the unknown.

Comments closed

Configuring Ola’s Scripts

Published 2023-02-01 by Kevin Feasel

Ben Miller begins a series on Ola Hallengren’s maintenance solution:

I recommended creating a database to use for this solution or even installing it into an existing DBA function database. I usually create a DBA database and use it for this purpose and others as well. With this new database, you configure the Database in the header of the maintenance solution SQL file, whether to create jobs, retention time and backup directory for the jobs.

This first post acts as a primer for those who might have the solution but haven’t investigated it in any detail.

Comments closed

Defending (Certain) Bad Practices

Published 2023-01-13 by Kevin Feasel

Aaron Bertrand considers the trade-offs:

For the first T-SQL Tuesday in 2023, Raul Gonzalez invites us to talk about cases where we have knowingly implemented worst practices.

Well, I have done it a lot. Most of the posts in my bad habits series are cautionary tales based on my own “learning the hard way.” There are always trade-offs with doing something correctly – maybe proper design is less efficient, or takes longer to write, or has to pass more checks. Over time, though, you start getting a feel for where it makes sense to cut these corners, and where it doesn’t.

Read on for some practical examples around Stack Overflow.

Comments closed

Indexing in Postgres

Published 2023-01-11 by Kevin Feasel

Grant Fritchey provides an overview:

In this article I’m going to go over the different types of indexes and some index behaviors. We’ll get into what the indexes are, how they work, and how best you can apply them within your databases. I’m hoping you’ll develop an understanding of which indexes are likely to work better in each situation.

However, I won’t be covering the indexes in detail. Further, I won’t be going very far into how these indexes affects performance. I’m not yet up on performance tuning within PostgreSQL. That article is quite a way down the track from here. When you know which index to use where, you can then drill down to better understand that index, and how best to measure the performance impacts.

This set of indexes is quite different from what we’re used to in SQL Server and much closer to what you get in Oracle.

Comments closed

Against the Grain: Heaps, Clustered Indexes, and Fragmentation

Published 2023-01-10 by Kevin Feasel

Rob Farley cautions against rash decisions on three topics:

We’ve already mentioned how clustered indexes can help avoid some of the problems with heaps, but I’m rarely in favour of the standard clustered index key choice – that of an ever-increasing identity column. The arguments people make for this are typically around avoiding page-splits, and the smallness of the data type. But I see tables where the clustered index is on a unique ID column and nothing ever refers to it.

Read on for a dose of caution on three popular beliefs.

Comments closed

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