Press "Enter" to skip to content

Category: Indexing

Stopping a Resumable Index

Andrea Allred doesn’t want that index to resume:

It had been building for about 10 minutes, when I realized I had the fields in the wrong order. No big deal, I just canceled it, corrected it and tried to start it again. “Tried” was the keyword here. I got an error because I had set RESUMABLE = ON but hadn’t properly cleared it out of the system. I started to furiously dig through articles about how to clear out an index that was resumable but had been canceled.

Read on to see what Andrea found.

Comments closed

Indexes and Stats on Tables with Always Encrypted

Matthew McGiffen gives us the low-down:

In a previous post we looked at executing queries against columns encrypted using Always Encrypted. In this short post we look at the ability to be able to have (or not) indexes on those columns. This information is relevant to the case where you are using Always Encrypted without enclaves, we’ll look at working with enclaves later on.

Click through to see how this all works.

Comments closed

Missing Index Hints and Index Rebuilds

Forrest McDaniel abuses that poor server:

The prod version of this issue manifested as 1-3 minutes of SCH-M blocking for index ops, yet only on a single server; I couldn’t replicate it anywhere else. Of course, bad blocking meant nasty timeouts, and that in turn meant I was reluctant to run live diagnostics. Better to just stop index rebuilds. One does not take down production in order to test weird SQL behavior.

An important clue came from a tiny table with a rebuild, and is visible in the whoisactive screenshot above. Why would such a small rebuild need so much CPU? That worker was definitely doing something – the question was what.

Read on to find out the answer, a repro script (that you should not run in your own production environment!), and what you can do about it.

Comments closed

Bugs in SQL Server 2022 CU4

Brent Ozar facepalms:

<sigh> Okay, so, the last few Cumulative Updates have had known issues around broken remote queries using the generic ODBC connector and errors with contained availability groups, but I couldn’t really care less about those. If you use those features, I give you bombastic side eye anyway.

However, in the last few days, two more known issues have surfaced.

And those two issues are definitely bad. Click through to see what they are and what the workarounds are until fixes come in.

Comments closed

Troubleshooting Issues with Full-Text Indexing

Jose Manuel Jurado Diaz digs into a customer problem:

Today, we got a new service request where our customer asks about the time spent populating the full-text. Following, I would like to share with you some lessons learned during this process, specifically working with Azure SQL Database.

For this example, we are going to use a General Purpose database with 8 vCores. Let’s get started with the creation of the table and fulltext.

Read on for a walkthrough of setting up full-text indexing and figuring out what the indexing engine is doing at any point in time.

Comments closed

Ordered Columnstore Indexes in SQL Server 2022

Ed Pollack gives us the scoop on ordered columnstore indexes:

One of the more challenging technical details of columnstore indexes that regularly gets attention is the need for data to be ordered to allow for segment elimination. In a non-clustered columnstore index, data order is automatically applied based on the order of the underlying rowstore data. In a clustered columnstore index, though, data order is not enforced by any SQL Server process. This leaves managing data order to us, which may or may not be an easy task.

To assist with this challenge, SQL Server 2022 has added the ability to specify an ORDER clause when creating or rebuilding an index. This feature allows data to be automatically sorted by SQL Server as part of those insert or rebuild processes. This article dives into this feature, exploring both its usage and its limitations.

I’ve seen a couple places where ordered columnstore indexes make enough sense to use, though not as many as I had first anticipated. That might change over time, as we see additional columnstore development.

Comments closed

Listing Filtered Indexes

Tom Collins has a filter for filtered indexes:

Question: How can I find SQL Server Filtered Indexes  ?    We are woking on some migrations  from SQL Server to other engine platform and part of the process includes locating a range of database objects . The identified objects will be recoded into the target engine plaform 

Click through for a query which provides this answer. Tom is even kind enough to include the filter definition.

Comments closed

Scripting Drop Statements for Redundant Indexes

Eitan Blumin deals with a clone problem:

This article published by Brent Ozar is very informative about redundant/duplicate indexes, what they mean, why they’re bad, and what should be done with them.

Also, a few years ago, Guy Glantser published a post about dropping redundant indexes. It’s very useful for finding all redundant indexes within all tables in a specific database.

But what both of these articles are missing – is the ability to easily generate Drop/Disable commands for these redundant indexes.

Additionally, what if there are “similar” indexes that are only “partially” redundant, and therefore it’s not enough to simply drop one of them? Otherwise, some queries may suffer a negative performance impact.

Click through for the article and be sure to pay close attention to the important note, which I’ll summarize: “kind of redundant” doesn’t always mean redundant.

Comments closed

Halloween Protection and Non-Clustered Indexes

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

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