1

I have a stored procedure that call a CLR function. They are both on the same database. The CLR function calls a web service. The problem here is that the SQL timestamp is 40s earlier than the webservice timestamp. This means that either SQL or webservice cannot handle many calls in parallel.

I am able to see through the logs I keep when the SP was called. How can I see when the CLR function was called?

user3170
  • 61
  • 2
  • 8
  • are you using a Scalar UDF (or several)? – Mitch Wheat Oct 16 '18 at 12:01
  • https://stackoverflow.com/questions/24722708/sql-server-clr-udf-parallelism-redux – Mitch Wheat Oct 16 '18 at 12:01
  • *How* does your stored procedure call the function, and how is the stored procedure called? Parallelism is something that only happens automatically when processing queries that involve aggregates. Your situation sounds like just the sort of scenario where that's not what's going on. (Nor should it -- you don't want a parallel aggregate that's supposed to be free of side effects firing off service calls!) – Jeroen Mostert Oct 16 '18 at 12:12
  • Do you see for each call a 40 seconds delay? Have you compared the system time on the web service box vs. the SQL box? – Niels Berglund Oct 16 '18 at 12:24

1 Answers1

1

Is there anything in the stored procedure that executes before calling the SQLCLR function?

In general, if you want to see what is happening in real-time, you have three options:

  1. SQL Server Profiler
  2. Extended Events
  3. Database Audits

The first two will let you watch events as they happen, and you can see what the start times and duration of each statement are.

The third option, Audits, let you easily capture events to a log file that you can view via a system TVF. You can trap SCHEMA OBJECT ACCESS and then filter at the Server Audit level (required for either Database Audit Specifications or Server Audit Specifications) for objects having the name of either the stored procedure or the function. You won't get to see the level of detail that you get with the first two options, nor can you see a breakdown of individual statements within a stored procedure, but it is a fairly light-weight mechanism for capturing these events if they are happening when you aren't there to watch via the first two options.

That said, if you are seeing delays with a web service call, and if that web service call is being executed by more than 2 sessions concurrently, and if those concurrent web service calls are to the same URI, then you are running into the default max connections per URI limit, which is 2. When this limit is reached, all additional calls to that URI are on hold until one of the 2 calls completes. You can increase this max connections per URI by setting the ConnectionLimit property of the ServicePoint class, which itself is a property of HttpWebRequest (and any other *WebRequest):

_MyHttpWebRequest.ServicePoint.ConnectionLimit = 10;
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171