Posted by Dan Guzman on 09/01/07 13:10

Regarding Erland's comment about a parallel plan, try running the query with
an OPTION (MAXDOP 1) hint if you see parallelism. That might provide an
easier workaround and/or provide additional info for the Connect bug report.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dimitri Furman" <dfurman@cloud99.net> wrote in message
news:Xns999DC1CC07A1Fdfurmancloud99@127.0.0.1...
> SQL Server 2005 SP2 (build 3054)
>
> Consider the following scenario:
>
> - A complex multi-statement table valued function is created. Let's call
> it dbo.tfFunc(@Param1, @Param2)
> - A SELECT statement is executed, that calls the above function twice,
> each time with a different set of parameters. In pseudocode:
>
> SELECT <column list>
> FROM dbo.tfFunc(1, 2) AS f1
> <some JOIN operator> dbo.tfFunc(3, 4) AS f2
> ON f1.col = f2.col
> INNER JOIN dbo.Table1 AS t1
> ON ...
> etc.
>
> The exact statement is probably irrelevant, as long as the same table-
> valued function is called twice (I have observed the issue in two very
> different statements calling the same function). The statement is
> executed in a SNAPSHOT isolation level transaction, although this may
> also be irrelevant.
>
> - 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
> (only relevant columns are shown):
>
> 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.
>
> 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).
>
> I do not know why this does not cause a deadlock error.
>
> 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.
>
> --
> remove a 9 to reply by email

[Back to original message]