Sql-server – Background Index Creation on SQL Server

azure-vmsql serversql-server-2017

I have no maintenance plans yet I see indexes being created in the background (low spid). I have no idea what indexes are being created or why. Using Standard SQL Server 2017 – a VM on Azure with SQL Installed.

Snippet of sp_who

I checked and did not see anywhere with automatic index management. You can configure backups, etc. but I cannot find a spot for indexing.

I tried EXEC sp_WhoIsActive @show_system_spids = 1; and got this result:

(542701ms)RESOURCE_SEMAPHORE

I think what is happening is I have a "messed up index" and the system is trying to fix it. I cannot drop the index whatsoever or rebuild it. At least that is my guess.

Best Answer

This sounds a lot like the Automatic tuning features of Azure SQL Database. In particular, one of the options is:

CREATE INDEX - Identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.

You can view these settings here:

HI LAMAK

You mentioned that you are on SQL Server 2017, though - so this doesn't apply to you per se.

Is this a Managed Instance perhaps? The docs currently state that automatic index management is not available in MI, but that may have changed from what I hear.

Since you are on an Azure VM, this won't be automatic index tuning. You're best bet is to try and capture more information about this process by running the following sp_WhoIsActive command while this is occurring (something that was suggested in a similar Q&A from several months ago):

EXEC sp_WhoIsActive @show_system_spids = 1;

In your particular case, it seems index creation was being rolled forward by the database startup/crash recovery process. You would have seen the database in a non-online state, and recovery progress messages in the error log.

Related Question