Press "Enter" to skip to content

Category: Indexing

Nonclustered Index Leaf Records and Null Bitmaps

Published 2021-07-29 by Kevin Feasel

Alex Stuart lays out a finding:

While testing a script that involved calculating index record size recently I was getting some confusing results depending on server version, and after some digging it appears there was a somewhat undocumented change to nonclustered index leaf page structure in SQL Server 2012.

Prior to 2012, as dicussed by Paul Randal in this 2010 blog post (which is still the top result for searching for ‘nonclustered index null bitmap’, hence this post) the null bitmap – that is, a >= 3 byte structure representing null fields in a record – was essentially present in all data pages but not the leaf pages of a nonclustered index that had no nulls in either the index key or any clustering key columns.

Read on for a demonstration using SQL Server 2008 R2 as well as SQL Server 2012.

Leave a Comment

The Downside Risk of Index Hints

Published 2021-07-19 by Kevin Feasel

Chad Callihan explains why you should be careful before deploying code which uses index hints:

This might be good enough…for now. The potential issues with index hints can be more about the future than the present. You might come along later on and think “why not use an index to cover the whole query?” We can add the index:

But if our query is still written to include the index hint (in a stored procedure for example) the new index is not going to matter. The old index is still forced to be used. Even if something better comes along, you’re going to need to modify the query in addition to adding the better index. If an index was added for a completely separate query but would also be an improvement for the query in question, it’s also not going to get by the index hint.

Click through for additional problems which can crop up as you use index hints. This isn’t a big argument against using them at all, but rather understanding (and remembering!) where you do use them and making sure that’s communicated well to the entire team, including future you.

Leave a Comment

Aggregation and Indexed Views

Published 2021-07-01 by Kevin Feasel

Randolph West dives into the archives:

Ten years of hindsight (and being able to read the wrap-up post with all the responses) gives me an advantage in this retrospective, I admit, but I didn’t find the thing I was going to write about anyway even though one or two people had a similar idea. And that, dear reader, means that I can write about one of my favourite performance secret weapons: the indexed view. It’s essentially a regular view with an index (or indexes) attached to it. Oracle calls them materialized views. Unlike a regular view which is simply a query definition, the indexed view persists the results, making it a lot more efficient to query that data:

Read on for more information.

Comments closed

Just One Well-Placed Index

Published 2021-07-01 by Kevin Feasel

Eric Cobb has a success story:

This image is an actual screen shot of one of our production SQL Server’s CPU usage, taken from our SQL Sentry monitoring system. Obviously this server was under some strain. Users were complaining that queries were taking 30+ seconds to run when they normally returned data in milliseconds. It eventually reached a point to where applications were timing out because queries were taking so long. After doing some analysis in SQL Sentry, we were able to determine what was causing the CPU spike, and came up with 3 nonclustered indexes to add to help alleviate the pressure. The screen shot you see here is what happened after I added the first index.

Click through to see the image as well as the major difference in CPU utilization from a single change.

Comments closed

Bug with Filtered Index on Computed Column

Published 2021-06-21 by Kevin Feasel

Erik Darling points out a weird bug:

At some point in the past, I blogged about a silent bug with computed columns and clustered column store indexes.

In this post, I’m going to take a quick look at a very loud bug.

Normally, you can’t add a filtered index to a computed column. I’ve always hated that limitation. How nice would that be for so many currently difficult tasks?

Click through to see how you can create a filtered index against a computed column, as well as all of the pain it provides.

Comments closed

Persistent Computed Columns and Columnstore Indexes

Published 2021-06-14 by Kevin Feasel

Erik Darling found a way to do something interesting:

If you read the documentation for column store indexes, it says that column store indexes can’t be created on persisted computed columns.

And that’s true. If we step through this script, creating the column store index will fail.

But it turns out that if there’s a will, there’s a way. Even if this is something you shouldn’t wish to do because who knows what it will mess up.

Comments closed

Setting up a Full-Text Index in SQL Server

Published 2021-06-10 by Kevin Feasel

Steve Jones walks us through setup for a new full-text index in SQL Server:

A full text index allows you to search a little more freely than standard T-SQL with a LIKE or wildcards. It’s useful for going through large amounts of text, mainly hundreds or thousands of words.

To get started, you need to know a few things. First, this system in modern SQL Server (2008+) is set up on all instances. You don’t enabled FTS like you would for In-Memory OLTP tables or FILESTREAM.

Next, you need a catalog for the FTS indexes, which is a logical container.

Next, a table with data.

Finally, you create the index. In this post, I’ll look at SSMS and the GUI. In another one, I’ll look at the T-SQL itself.

With all that in mind, click through to read Steve’s post and set up your own full-text search process.

Comments closed

Handling Unused Indexes

Published 2021-06-07 by Kevin Feasel

Welcome to the Chad Callihan Home for Unloved Indexes:

What do you do if you see that an index is being updated but is low on seeks and scans? Should you drop it because the table is never queried or a better index is being chosen? If an index doesn’t have updates, seeks, or scans then is the next step to drop?

The answer to these questions is a resounding “not yet!” Getting rid of an index that isn’t getting much use sounds simple but there are careful considerations to make.

Click through for those considerations.

Comments closed

Dealing with Key Lookups

Published 2021-06-01 by Kevin Feasel

Jared Poche shows a good technique for removing key lookups:

A key lookup is an operation that occurs when a query has used a nonclustered index on a given table, but needs to access more columns to complete the query. It may need to check columns not in that index for additional filters, or it may just need to return that column as part of its result set.

In the simple query above, we’re retrieving 100 rows from the seek against a nonclustered index, then performing a key lookup against the clustered index. There is a nested loops operator between the two and understanding how that operates is important; for each row we receive from the first table, we perform the second operation once. So, in this query we are seeking 100 rows from the nonclustered index, then performing the key lookup 100 times. We go through the index once for each row we return, and you can see the cost of the key lookup operator is 99% of the query.

Read on for more information, including how to eliminate key lookups.

Comments closed