0

Got an interesting one that I've struggled to resolve for several years now and is starting to become a bigger issue. We have a UDF that is used thoroughly throughout our system and retrieves a single value back from a journal table. It usually runs instantly, however periodically something appears to happen to its query plan causing it to run for several minutes without returning.

We have found that if we recompile the UDF, it works again.

If I pull back the execution plan I don't really get any information back indicating a problem with an index.

EDIT: Copy of execution plan: https://filebin.net/jlxf0ejqeyz9j9b9

EDIT 2: Screenshots of exec plan: screenshot 1: https://filebin.net/eula7cir72lp95iv/exec-screenshot1.png?t=0i8i2kyj

screenshot 2: https://filebin.net/yr2d84x5fftqjvnm/exec-screenshot2.png?t=um9ldpdk

EDIT 3: screenshot 3 hovering over the function call: https://filebin.net/9azjdvb1mpmsi3am/exec-screenshot3.png?t=qjsemw6d

We currently have a job which loops through and recompiles the UDF every 30 minutes which I know is just a bandaid solution. If anyone has some recommendations on how to debug this further I would greatly appreciate your insight!

Unfortunately it's a problem that doesn't always show itself so quite often we don't know about it until someone points it out.

Sami.C
  • 561
  • 1
  • 11
  • 24
  • sounds like parameter sniffing. you need to supply the execution plans . – Martin Smith Jan 14 '20 at 23:39
  • It could be a parameter sniffing issue, at some point a parameter value causes it to change to a query plan which works for that parameter but not for any others. – Dale K Jan 14 '20 at 23:40
  • Scalar, In-line TVF, or Multi-Statement TVF? – David Browne - Microsoft Jan 14 '20 at 23:44
  • @DavidBrowne-Microsoft its a scalar value function – Sami.C Jan 14 '20 at 23:48
  • Edited my post to incude the execution plan exported. Or would you rather see a screenshot? Might just be a bit big! – Sami.C Jan 14 '20 at 23:54
  • I'm looking at it in an ipad which is not the best environment to look at it. Which particular statement has the issue? I can see possible parameter sniffing issues with `@WorkflowID` in that the compile time value and runtime values are different – Martin Smith Jan 14 '20 at 23:58
  • the function which causes the issues is: [dbo].[udfWF_ObjectStateID]( @WorkflowID, [O].[PKID] ) – Sami.C Jan 15 '20 at 00:00
  • Trying to debug it through reading the raw XML is not particularly easy, hopefully someone will have a look at it in SSMS or similar. Is screenshot 1 or 2 the good one? Either way in the bad plan have a look at the nested loops and see if the estimated number of executions of the inside are much lower than actually happens in reality. – Martin Smith Jan 15 '20 at 00:13
  • @MartinSmith I just attached a 3rd screenshot, hovering over what I think is the problematic line of code. It says the estimated number of executions is 200 which is in line with the SQL query which is returning a top 200 rows in total. I'm joining to a table with the value returned from the UDF. – Sami.C Jan 15 '20 at 01:10
  • I'm now back at a computer with SSMS and had a look as no one else has taken this up. The only reference to that function I can see is in NodeId 25 of statement 2 and that executed in 105 miliseconds so doesn't seem to represent the issue. In any event it is the execution plan **of the UDF** that is needed. Not of the code that calls it – Martin Smith Jan 20 '20 at 19:58
  • @MartinSmith - is it crucial to see the query plan on the UDF when I run into the slow response? Unfortunately it is so hit and miss, I haven't been able to successfully trigger it on demand. I'm assuming if it is a parameter sniffing issue like you suggested that it probably occurs when data is inserted/updated in the underlying table but nevertheless I haven't been able to replicate yet. You mentioned the compile time value and runtime values of workflowID were different, should I be looking at this closer? – Sami.C Jan 21 '20 at 00:17
  • hi guys, without completely re-inventing the wheel of our system which heavily relies on this scalar udf, is there a way i can force a recompilation when joining on a table using my scalar udf? It doesn't appear possible to add recompile options into the scalar UDF itself. – Sami.C Feb 14 '20 at 00:14

0 Answers0