Ugly Bug: SQL Server Online Index Rebuild Sometimes Happens Offline Without Warning

I found a nasty bug in SQL Server and Azure SQL Managed Instance recently: sometimes an ‘online’ index rebuild of a disk-based rowstore clustered index (basically a normal, everyday table) isn’t actually ‘online". In fact, it’s very OFFLINE, and it blocks both read and write queries against the table for long periods.

If you manage to make it through a rebuild successfully, the problem goes away for future rebuilds of that clustered index – likely leaving you bruised and bewildered.

Continue reading

Should We Do Index Maintenance on an Azure SQL Managed Instance or Azure SQL Database?

Have you ever received advice that was technically correct, but it was too hard to understand?

I think of this as “accidental bad advice,” because it can lead to confusion and bad outcomes. There’s a LOT of accidental bad advice out there on index maintenance for SQL Server and cloud versions like Azure SQL, even in the official documentation.

In this post I’m answering a common index maintenance question, and we’re going to keep it simple.

Continue reading

Online, Resumable, and WAIT_AT_LOW_PRIORITY Operations in SQL Server

ONLINE operations in SQL Server were simple to understand for years – we got ONLINE index rebuilds in SQL Server 2005. That was it for a while.

Then, things got more complicated: we got more types of indexes. We got ONLINE options for schema changes that don’t involve indexes. We got more options for managing things like blocking, because online operations are really only mostly online: generally there’s going to be at least a short period where an exclusive lock is needed to update metadata. We now have some RESUMABLE operations coming in, too, for those big operations that are tough to handle.

Continue reading

Code: Fixing a Filtered Index Which is Causing Data Modifications to Fail

This question came up in a webcast recently: if a filtered index is causing data modifications to fail, does disabling the filtered index fix the problem?

Continue reading