MSQL_XP

Extended stored procedures within SQL Server allow you to make a T-SQL external call to a compiled DLL, so you can run code that can’t be run from within SQL Server itself. There are built-in SQL Server extended procedures, and it’s a common method used by third-party software. This wait indicates that a session is waiting on one of these external calls.

You’ll also see this wait if you’re using Multiple Active Result Sets (MARS), which is a special feature that allows the execution of multiple batches on a single connection. The MSQL_XP wait statistic is used by SQL Server to monitor deadlocks within MARS.

If your T-SQL is using an extended stored procedure, you will see this wait until the DLL finishes whatever it’s doing and the external call ends. There is nothing you can do from within your T-SQL or SQL Server to alleviate these calls; the wait is completely dependent on the external process.

Investigating queries

  • Check the affected queries and the Top queries table. Look for queries with high execution times for calls to extended stored procedures and see if there is an alternative way to perform the action.
  • Check sys.dm_exec_connections for connections using MARS. These will have net_transport = ‘Shared’. See: sys.dm_exec_connections (MSDN).

Investigating external code

  • If the extended stored procedure is developed locally, go through a debug process to determine why the DLL code is running long. See: How to debug an extended stored procedure (TechNet).
  • Look into alternatives to using extended stored procedures such as a Common Language Runtime (CLR) function. See: CLR Integration overview (TechNet).

Investigating vendor code

  • If the extended stored procedures in use are from a third-party vendor, contact the vendor to see if there are updates to the DLL.
  • If the extended stored procedures are from Microsoft, determine if there are service packs or updates that fix bad behavior.
  • There are updates available for MARS connections to reduce instances of deadlocks. Ensure you have those updates from Microsoft installed on your systems.


 


Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?