Posted by Erland Sommarskog on 09/02/07 17:39

Dimitri Furman (dfurman@cloud99.net) writes:
> 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.

If you have to restart the server to resolve the situation, it certainly
sounds that the prospects for completion are utterly bleak.

> 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.

If you run the function alone, you should see its plan I think.

But I was mainly interested in whether the main query had any parallelism.
In that case it could be one thread blocking another. Hm, then again,
if the UDF causes parallelism, I guess that could also be an issue.
But I don't think this is likely, since if you insert into a table
variable, there cannot be parallelism. And it's difficult to anything
in a UDF without modifying a table variable.

Anyway, you can easily examine this next time it happens by running

SELECT * FROM sys.dm_os_tasks WHERE session_id = <trouble spid>

If there are rows with non-zero exec_context_id, there are parallel
threads.

The output from sys.dm_os_waiting_tasks would also be interesting.

> 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.

At least it is a clue for anyone who is trying to produce a repro.
Given that you say it's intermittent, I am not going to try.

>> 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...

It's also available in sys.dm_exec_text_query_plan. A way to get the
plan, sys.os_waiting_tasks and more packaged into one result set, is
to use my beta_lockinfo, available at
http://www.sommarskog.se/sqlutil/beta_lockinfo.html


There have been some bugs around temp-table caching, I don't if they
could be related to what you see. There is a Cumultative Update, including
these two bugfixes at http://support.microsoft.com/kb/939537.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]