In this blog post, I would like to share an interesting issue that I encountered during a consultation with one of my customers who extensively uses SQL Server for their operations. This particular issue involved a wait type called MSSQL_XP, which was the most common wait type across their 100+ SQL Server instances.

When I initially ran diagnostic queries like sp_who2, I couldn’t immediately identify the cause of this wait type because the associated query was nowhere to be found. However, after a day of research, I was able to find a simple solution to this problem.

According to the SQL Server Book Online, MSSQL_XP occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS (Multiple Active Result Sets) application deadlocks. The wait stops when the extended stored procedure call ends.

The reason why this wait type can be challenging to understand is that extended stored procedures are executed within SQL Server, but SQL Server has no control over them. Unless you have access to the code and understand what the extended stored procedure is doing, it is difficult to determine why this wait type is occurring.

In order to reduce the MSSQL_XP wait type, it is crucial to identify which extended stored procedure is causing it. In the case of my customer, we discovered that a third-party backup tool they were using was utilizing an extended stored procedure. After contacting the vendor’s technical team, they acknowledged that there were some optimization issues with the code and provided a patch within a day. Once the updated version was installed, the MSSQL_XP wait type disappeared across all the SQL Server instances.

To summarize, if you encounter the MSSQL_XP wait type, it is important to identify the extended stored procedure responsible for it and try to optimize the code. If possible, reach out to the creator of the stored procedure for assistance. Sharing your experiences in managing this wait type can be helpful, but please refrain from mentioning specific vendor names in the comments, as the focus of this blog post is to understand wait types rather than discuss specific vendors.

It is worth noting that the information presented in this blog post is based on my personal experience and may not be universally applicable. For further clarification, I recommend referring to the SQL Server Book Online. Additionally, it is always recommended to test any changes on a development server before implementing them on a production server.

Thank you for reading this blog post. Stay tuned for more articles in the Wait Types and Queue series.