0

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.

5

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.

0

Got it resolved. My gut was right. It was trying to recreate an index that already existed so it would eventually get a lock timeout (go figure). I ended up doing a restore from last night but it was still in the same situation. So what finally resolved it was having the code ready to drop the index then restarted the SQL Service and mashed on the control E until it dropped the index :-) The crazy infinite recreation of an existing ncci finally quit. Craziest thing I have seen in a long time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.