Posted by Erland Sommarskog on 09/01/07 12:39

Dimitri Furman (dfurman@cloud99.net) writes:
> - The statement continues executing for a long time. If sp_who2 is run at
> that time, the following row is returned for the statement connection

Long time? But does it ever complete?

> SPID Status BlkBy Command CPUTime DiskIO LastBatch
> 63 SUSPENDED 63 SELECT 29282 683 08/31 18:17:37
>
> The statement appears to be blocked by itself. If sp_lock is run at that
> time, the following rows are returned:
>
> spid dbid ObjId IndId Type Resource Mode
> Status
> 63 2 1316624641 0 TAB Sch-S GRANT
> 63 2 1316624641 0 TAB Sch-M WAIT
>
> It appears that SQL Server waits indefinitely trying to obtain a schema-
> modification lock on a resource which already has a schema-stability lock
> placed on it by the same connection.

Is this a parallel plan? In that case different threads could be
blocking each other.

> The following is pure speculation, but it seems reasonable to assume that
> the server has materialized the result of the first call to the function
> using a temporary table in tempdb, and is trying to materialize the
> result of the second call using the same temporary table (same ObjId in
> sp_lock results).

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.

> Unfortunately, I do not have a simple repro script for this. The actual
> code is rather complex. While I can devise a workaround, this does look
> like a bug. I am posting it here before submitting a bug on Connect, in
> case anyone can shed some light. Thanks.

Without a repro it will of course be difficult to address the issue.
I would suggest that when you file the bug that you include:

1) The query.
2) The code for the UDF.
3) If possible also table definitions.
4) The XML showplan. (You can save this from the graphical plan in Mgmt
Studio.)
5) The output from sys.dm_os_waiting_tasks and sys.tran_locks.


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