We start facing some uncommon waits after the last release.
XE_SERVICES_RWLOCK waits starts to appear in our top waits. Investigation shows that this waits happen during insert to our main OLTP tables. The only thing we change in this part is that we change the insert into identity column by inserting with sequence. Actually we have not removed identity option from the column. Rather we are just doing
SET IDENTITY_INSERT ON INSERT STATEMENT HERE SET IDENTITY INSERT OFF
The reason that we have not removed the identity property was that it requires to rebuild the table, but our table is huge and it will require hours of downtime. The question is what are
XE_SERVICES_RWLOCK waits, can they appear in case of massive
SET IDENTITY_INSERT ON|OFF statements or can the root case be using
sequence instead of
sp_WhoIsActive every 30 sec and save result into tables to be able to investigate problems later. You can see results from that table attached.
We are using SQL Server 2016 SP2.