SQL SERVER – MSQL_XP – Wait Type – Day 20 of 28

In this blog post, I am going to discuss something from my field experience. While consultation, I have seen various wait typed, but one of my customers who has been using SQL Server for all his operations had an interesting issue with a particular wait type. Our customer had more than 100+ SQL Server instances running and the whole server had MSSQL_XP wait type as the most number of wait types. While running sp_who2 and other diagnosis queries, I could not immediately figure out what the issue was because the query with that kind of wait type was nowhere to be found. After a day of research, I was relieved that the solution was very easy to figure out. Let us continue discussing this wait type.

From Book On-Line:

MSQL_XP occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends.

MSQL_XP Explanation:

This wait type is created because of the extended stored procedure. Extended Stored Procedures are executed within SQL Server; however, SQL Server has no control over them. Unless you know what the code for the extended stored procedure is and what it is doing, it is impossible to understand why this wait type is coming up.

Reducing MSQL_XP wait:

As discussed, it is hard to understand the Extended Stored Procedure if the code for it is not available. In the scenario described at the beginning of this post, our client was using third-party backup tool. The third-party backup tool was using Extended Stored Procedure. After we learned that this wait type was coming from the extended stored procedure of the backup tool they were using, we contacted the tech team of its vendor. The vendor admitted that the code was not optimal at some places, and within that day they had provided the patch. Once the updated version was installed, the issue on this wait type disappeared. As viewed in the wait statistics of all the 100+ SQL Server, there was no more MSSQL_XP wait type found.

In simpler terms, you must first identify which Extended Stored Procedure is creating the wait type of MSSQL_XP and see if you can get in touch with the creator of the SP so you can help them optimize the code.

If you have encountered this MSSQL_XP wait type, I encourage all of you to write how you managed it. Please do not mention the name of the vendor in your comment as I will not approve it. The focus of this blog post is to understand the wait types; not talk about others.

Read all the post in the Wait Types and Queue series.

Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading Book OnLine for further clarification. All the discussion of Wait Stats in this blog is generic and varies from system to system. It is recommended that you test this on a development server before implementing it to a production server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Wait Stats
Previous Post
SQL SERVER – PREEMPTIVE and Non-PREEMPTIVE – Wait Type – Day 19 of 28
Next Post
SQL SERVER – Guest Posts – Feodor Georgiev – The Context of Our Database Environment – Going Beyond the Internal SQL Server Waits – Wait Type – Day 21 of 28

Related Posts

5 Comments. Leave new

  • Hi Pinal,

    May be my question is little Stupid but , how could you find out that the wait type MSQL_XP was because of the third-party backup tool.


  • Nakul Vachhrajani
    December 10, 2011 10:58 pm

    Pardon my ignorance here, but my question is around the following line in the post: “SQL Server uses this wait state to detect potential MARS application deadlocks”.

    How does MARS get involved with extended stored procedures?

  • Hi Pinal,
    I have to questions:
    First ,how did you find that the issue was caused by extended stored procedure and what’s the name(s) of the extended stored procedure(s)?
    Second,why the extended stored procedure(s) can cause the issue?


  • Hello all,

    Thanx 4 the article, I had faced the same situation with the same wait type while using a database monitor tool, I managed to release the hanging sessions by restarting the SQL Server service instead of rebooting the whole server.

    Contacted the vendor of course but the provided solution to change some configuration, disable some metrics collection, and restart the Application service did not solve the problem.


  • Sumankar Mitra
    June 24, 2017 10:35 am

    Ver: Sql Server 2014
    We were using the below code snippet to send sms to customers directly from database. The code was written inside a trigger on a transaction table. Random locks were generating during transaction approval. We found out the wait type was MSSQL_XP

    Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);

    Exec sp_OACreate ‘MSXML2.XMLHTTP’, @Object OUT;
    Exec sp_OAMethod @Object, ‘open’, NULL, ‘get’,
    @URL, –Your Web Service Url of sms provider
    Exec sp_OAMethod @Object, ‘send’
    Exec sp_OAMethod @Object, ‘responseText’, @ResponseText OUTPUT

    Exec sp_OADestroy @Object

    Later we found out that sp_OACreate and sp_OAMethod are extended stored procedures using the below query:

    SELECT SystemObject.name AS [Extended storedProcedure]
    FROM master.dbo.sysobjects AS SystemObject
    JOIN master.dbo.syspermissions AS SystemPermissionObject
    ON SystemObject.id = SystemPermissionObject.id
    WHERE (SystemObject.type = ‘X’)
    ORDER BY SystemObject.name;

    We changed our code and were able to resolve the locking issue.


Leave a Reply