I was working on an SQL Server instance wherein I found the instance running slow for some reason. While checking the background waits, I got to find the MSQL_XP wait type having higher values. MSQL_XP wait type should not be confused with the “preemptive wait type” PREEMPTIVE_GETPROCADDRESS. Though you might have worked on the PREEMPTIVE_GETPROCADDRESS wait type, it does not record the execution time of an extended stored procedure.
What is the MSQL_XP Wait Type?
MSQL_XP
wait type records the execution time of an extended stored procedure in your
SQL Server instance. The wait type can come in handy in detecting deadlock situations
when using Multiple Active Result Sets (MARS).
Note: MARS is a feature in SQL Server that helps the execution of multiple batches through a single
SQL Server connection.
Additionally, there is an article that showcases the MARS example here.
You can
check the statistics of the MSQL_XP wait type in sys.dm_os_wait_stats.
select * from sys.dm_os_wait_stats
where wait_type='MSQL_XP'
How to lower MSQL_XP Waits?
If you notice a high value of wait for the MSQL_XP in sys.dm_os_wait_stats, then the first thing is to identify the exact extended stored procedure event that is causing the slowdown. As the MARS is known to execute multiple batch commands from a single SQL connection, there might be chances that some Windows sessions are slowing down. Due to the same reason, there could be reasons for possible deadlock events in your Windows machine. So your target should be to identify the extended stored procedure slowing things down. Microsoft has been giving a lot of patch updates every month or so. It makes a good practice to patch your SQL Server immediately once the patches are released by them.
Bottomline:
You will need to draw attention toward the MSQL_XP wait type when there is an extended stored procedure that is slowing down and taking longer than normal to complete. In such cases, the wait_time_ms value in the sys.dm_os_wait_stats will be higher. Identify the right extended stored procedure to fix them before they cause any performance issues.
0 comments:
Post a Comment