we are using SQL Server 2016 and currently we are facing some performance issues. Solarwinds tells us, that there is sometimes much wait time caused by a Lock "LCK_M_SCH_M".

I was now able to check the activity monitor while we have there problems and I saw that our server dropped an index with the user "Database Engine Tuning Advisor" and that this causes the named lock.

Can someone tell me why the server is doing this and how I can stop it? As I read this is a feature from Azure SQL Database 2017 and not SQL Server 2016.

  • "Database Engine Tuning Advisor" is an application that someone is presumably running manually. There is no built in auto index create and drop in SQL Server 2016 – Martin Smith Jan 16 at 20:04

Talked to some colleagues. One really ran the Tuning Advisor without knowing that this caused the problem

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