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)
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.
Thanks
raj
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?
Thanks
genhua
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.
Thanx.
Hany
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
‘false’
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.