0

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 identity?

We run 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.

enter image description here

We are using SQL Server 2016 SP2.

Improve this question
3
  • Rebuild table means, that you need to create new table with same structure but without identity, insert all the data into your new table, drop existing table and rename newly created table. Also you must consider that you can have foreign key which must also be re-created.
    – Artashes Khachatryan
    Dec 9, 2021 at 6:55
  • 2
    Can you share what active Extended Events sessions are running in your environment? I would focus on those that are frequent or collect too much data via actions.
    – Zikato
    Dec 9, 2021 at 7:50
  • 1
    @Zikato I disable ALL my Extended Events but this waits still appear.
    – Artashes Khachatryan
    Dec 9, 2021 at 12:54

1 Answer 1

Reset to default
5

waits

Since this wait has XE in the name, it's presumably attached to Extended Events. It may be completely ignorable, but it's hard to tell from what you've described here. I doubt it's related to the code change you've posted.

I'd check to see if there are:

  • Any Extended Events running that are collecting data aggressively
  • Any queries actually waiting on it during execution

I've confirmed that the wait you're hitting is tied to XE buffering data in and out. If you've disabled all of your active XE sessions and you're still hitting this wait, you need a support case with Microsoft. They should only fire when one is active, and they definitely shouldn't be hampering query performance like this.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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