|
|
Posted by Dimitri Furman on 09/02/07 15:55
On Sep 01 2007, 08:39 am, Erland Sommarskog <esquel@sommarskog.se> wrote
in news:Xns999E95B819D01Yazorman@127.0.0.1:
> Dimitri Furman (dfurman@cloud99.net) writes:
>> - The statement continues executing for a long time.
>
> Long time? But does it ever complete?
The longest time I let it run for is 40 minutes. Considering that it
usually runs in less than 10 seconds, the likely answer is no.
> Is this a parallel plan?
Hard to tell. I forgot to mention that the problem is intermittent. When
the statement completes successfully, there is no indication of parallelism
in the actual plan. When it does not, there is obviously no plan to look at
(in fact, the only way to kill the connection in that case is to restart
the server). The estimated plan doesn't show any parallelism either. I am
talking here about the plan for the statement, not the plan for the called
function, which I apparently cannot see.
I did try OPTION (MAXDOP 1) in both the statement and the function, and
have not been able to reproduce the issue so far. But this is inconclusive,
sometimes it works for days without a problem.
> The table in question is likely to be the return table for the UDF.
> You should be able to find out more about this table by looking in
> sys.objects and sys.columns.
I did, and this is where it gets a bit interesting. The UDF in question
includes a table variable, and it turns out that the mentioned schema locks
are placed on the table in tempdb corresponding to that table variable, not
the return table for the UDF. I am not sure if this makes any substantive
difference though.
> 4) The XML showplan. (You can save this from the graphical plan in
> Mgmt
> Studio.)
I'm not sure how I could save the plan if the statement never completes...
--
remove a 9 to reply by email
Navigation:
[Reply to this message]
|