Hung condition on a query to SQLCLR stored procedure doing P/Invoke on a native dll

  • Article

If you run into a scenario where you see your managed stored procedure from SQLCLR hung perpetually while calling a native dll method using P/Invoke, You may have to check following things.

Run following query from another connection

select os.task_address, os.state, os.last_wait_type, clr.state, clr.forced_yield_count from sys.dm_os_workers os join sys.dm_clr_tasks clr on (os.task_address = clr.sos_task_address) where clr.type = 'E_TYPE_USER'

In last_wait_type column If you see anything except from “CLR_.....” or “SQLCLR_.....” e.g. MISCELLENOUS .

Stop your SQL Server service from Configuration manager or command prompt e.g.:

C:\>Net Stop MSSQL$SQLExpess

Start SQL Server in console mode with following command (this is just for testing, you cannot use SQL in console mode forever)

sqlservr.exe -c –iSQLExpress

Execute your SQLCLR stored procedure again. This time you might get additional interactive information from your native dll (for e.g: some error message box or popup). You would notice that as soon as you click on Ok or Cancel buttons for the message box, the query execution would be successful.

In this case the dll may have encountered a runtime exception and have tried to create UI handle for the message box control. Since SQL Service runs in windows service mode it’s not possible for SQL to create a UI handle hence the user never see the message box window. This happens because deploying native dlls in SQLCLR involves marking these dlls as unsafe. Unsafe dll are not verified by SQLCLR for all code paths at the time of deployment and its security, stability and testing is expected to be performed by user before throwing it up on SQL Server.

Although P/Invoke is not a bad thing but native assembly has to be tested before being called from SQL process. If the managed code calls unmanaged code such as P/Invoke, the calling thread will switch to pre-emptive. This acts like an extended stored procedure. Any UI created from SQL process is unsupported and could lead for unforeseen behaviour like this.

Only supported assemblies from SQLCLR are mentioned in this KB https://support.microsoft.com/kb/922672

 

Author : Runeet Vashisht (MSFT) , SQL Developer Engineer , Microsoft