Question :
While analyzing our environment, I’ve noticed a recurrent blocking that usually happens around the same time, our monitoring tool says the blocked process is a “CREATE INDEX”, however, the index maintenance is not in-progress when the issue arises. Looking at the details, the command seems to be running in the background.
Has anyone seen this background task before? I’d like to understand how/why this happens. I’ve looked it up and could not find much information.
I don’t have automatic tuning enabled, also this is a small data mart, the ETL process doesn’t include any index create statement. When I look at the blocking history, I can’t really see what was the complete statement, it doesn’t show plan handle or anything. I’m guessing it’s an internal process as the SPID is below 50.
There is a similar thread here:
https://social.technet.microsoft.com/Forums/windowsserver/en-US/65283481-f4e7-422f-99bc-2677699ba70b/background-process-create-index?forum=sqldatabaseengine
Answer :
If you are seeing the same index(es) being created over and over again, this is most likely being initiated by the application. Some applications, particularly reporting or analysis applications, will drop indexes before importing data, and then create them again after the import is complete.
Another possibility is that Automatic index management is enabled. However, you won’t see this creating the same index over and over again, unless something is dropping an index that it creates.
Can you find that index–what table is it on? I’m wondering now if it might be on a tempdb table that is created, populated, indexed, and dropped repeatedly.
If you can catch it happening with sp_whoisactive that should provide more clues. Set @show_system_spids = 1.