So, I started to tune a piece of sql code that had a high number of waits - a stored procedure that takes 6 parameters and inserts their values into a table. I am running SQL Server 2016 Enterprise CU5, DB Compatibility Mode is SQL Server 2016(130).
This is where it gets interesting - I captured an actual command from a trace and plugged those values into the stored procedure and attempted to get an estimated execution plan. I get this error in SSMS:
Msg 2739, Level 16, State 1, Line 20
The text, ntext, and image data types are invalid for local variables.
My question is, why would the stored procedure continue to execute without issue VS a manual run by SSMS using the same values? Further inspection does indeed show that there is an NTEXT datatype defined in the table involved in the INSERT...
I believe that there is something going on with this scenario that is causing excessive waits...