It's a third party app on SQL Server 2008R2. In Test, they are trying to run some reporting, and they have to use the SPs with many built in CLR functions. Running the script in SSMS on a system with no other activity, occasionally their sessions will just spin for hours. Normally the process takes 30 seconds. When it occurs, checking their session shows "Sleeping". No locks. Just dead process, but SSMS never returns. Due to the intermittent nature, I haven't been able to catch it in Extended Events and see what portion ran right before it went idle. My only thought here would be it's dropping off in a CLR package but not reporting back to SSMS. This is a theory. Anyone run into this?

  • That does not sound like anything inherently SQLCLR-related. How are you checking the session activity? Are you using sys.dm_exec_requests? What was the last SQL statement to be executed by that session? What version of SSMS are you using? Also, what do you mean by "built in" CLR functions? – Solomon Rutzky Jul 23 '18 at 22:02
  • @SolomonRutzky SSMS is v17.7 (was my 1st question too). Yes, dm_exec_requests. "built in" was poor wording. Just CLR packages compiled in the DB (from vendor). The query is a long sp call that calls several views that call several views with many CLR calls. It's so unpredictable, that I haven't been able to watch the process. Was a bit of a shot in the dark about the CLR, because I haven't seen this behavior before, and that was the only piece for which I'm not as familiar with tracking. If you have any suggestions for testing/tracking – – Dbot Jul 24 '18 at 16:02
  • So, did sys.dm_exec_requests even return a row or nothing? If it returns a row, then something is running. If sys.dm_exec_sessions returns a row for a SPID that does not have a corresponding row in sys.dm_exec_requests, then nothing is executing at that moment. You can also look at DBCC INPUTBUFFER(spid) to see the last command executed, but it might just be the exec of the proc and not what's inside of the proc. If sys.dm_exec_requests returns a row, you can use SELECT * FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) WHERE req.[session_id] = spid. – Solomon Rutzky Jul 27 '18 at 22:14
  • @SolomonRutzky Yeah, it was returning nothing in dm_exec_requests, but the spid was in dm_exec_sessions. Unfortunately last cmd executed just shows the proc execution. At this point, likely not much else I can do without being able to reproduce it, I don't think. I can see most of the source code (not the CLRs), so I can run the sp in parts, but since it only happens on occasion, I'm not sure this is going to produce any useful results – Dbot Jul 30 '18 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.