SQL Server 2005中的表观数据库引擎错误 [英] Apparent DB engine bug in SQL Server 2005

问题描述

SQL Server 2005 SP2（内部版本3054）


考虑以下场景：


- 一个复杂的多语句表值函数是创建。我们打电话给

dbo.tfFunc（@ Param1，@ Param2）

- 执行SELECT语句，调用上述函数两次，

每次使用不同的参数集。在伪代码中：


SELECT<列列表>

来自dbo.tfFunc（1,2）AS f1

<一些JOIN operatordbo.tfFunc（3,4）AS f2

ON f1.col = f2.col

INNER JOIN dbo.Table1 AS t1

ON ...

等


确切的陈述可能无关紧要，只要相同的表格 -

有价值函数被调用两次（我已经在两个调用相同函数的非常不同的语句中观察到了这个问题）。声明是在SNAPSHOT隔离级别事务中执行的
，尽管这可能与
无关。


- 声明继续执行很长一段时间。如果sp_who2当时在

运行，则语句连接返回以下行

（仅显示相关列）：


SPID状态BlkBy命令CPUTime DiskIO LastBatch

63暂停63选择29282 683 08/31 18:17:37


语句似乎被阻止通过它自己。如果在

时间运行sp_lock，则会返回以下行：


spid dbid ObjId IndId类型资源模式状态

63 2 1316624641 0 TAB Sch-S GRANT

63 2 1316624641 0 TAB Sch-M WAIT


似乎SQL Server无限期地等待尝试获取模式 -

修改锁定已经具有架构稳定性锁定的资源

通过相同的连接放置它。


以下是纯粹的推测，但似乎有理由假设服务器已经使用tempdb中的临时表实现了对函数

的第一次调用的结果。试图使用相同的临时表（

sp_lock结果中的相同ObjId）实现第二次调用的结果

。


我不知道为什么这不会导致死锁错误。


不幸的是，我没有一个简单的repro脚本。实际的

代码相当复杂。虽然我可以设计一个解决方法，但确实看起来好像是一个bug。我在提交一个关于Connect的错误之前在这里发布它，在

的情况下，任何人都可以解决这个问题。谢谢。


-

删除9回复电子邮件

解决方案

< blockquote> Dimitri Furman（df*****@cloud99.net）写道：

- 语句继续执行很长时间。如果sp_who2当时在

运行，则为语句连接返回以下行

长时间？但是它有没有完成？

SPID状态BlkBy命令CPUTime DiskIO LastBatch

63 SUSPENDED 63 SELECT 29282 683 08/31 18:17： 37

该声明似乎被自己封锁了。如果在

时间运行sp_lock，则返回以下行：


spid dbid ObjId IndId类型资源模式

状态

2 2 1316624641 0 TAB Sch-S GRANT

63 2 1316624641 0 TAB Sch-M等待


似乎是SQL服务器无限期地等待尝试获取模式 -

修改锁定已经具有模式稳定性锁定的资源

由同一连接放置在它上面。

这是一个平行计划吗？在这种情况下，不同的线程可能会相互阻塞。

以下是纯粹的推测，但似乎有理由认为

服务器使用tempdb中的临时表实现了对函数

的第一次调用的结果，并且试图实现

的结果第二次调用使用相同的临时表（

sp_lock结果中的相同ObjId）。

有问题的表可能是UDF的返回表。

你应该能够找到关于这个表的更多信息通过查看

sys.objects和sys.columns。

不幸的是，我没有一个简单的repro脚本。实际的

代码相当复杂。虽然我可以设计一个解决方法，但确实看起来好像是一个bug。我在提交一个关于Connect的错误之前在这里发布它，在

的情况下，任何人都可以解决这个问题。谢谢。

如果没有repro，当然很难解决这个问题。

我建议你提交包含的bug时：


1）查询。

2）UDF的代码。

3）如果可能还有表定义。

4）XML showplan。 （您可以从Mgmt

Studio中的图形计划中保存它。）

5）sys.dm_os_waiting_tasks和sys.tran_locks的输出。

-

Erland Sommarskog，SQL Server MVP， es****@sommarskog.se


SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx

SQL Server联机丛书2000年在
http://www.microsoft。 com / sql / prodinf ... ons / books.mspx

关于Erland关于并行计划的评论，请尝试使用一个OPTION（MAXDOP 1）提示。这可能会提供一个更简单的解决方法和/或提供Connect错误报告的其他信息。


-

希望这个帮助。


Dan Guzman

SQL Server MVP


" Dimitri Furman" < df ***** @ cloud99.net写信息

新闻：Xn ************************* *** @ 127.0.0.1 ...

SQL Server 2005 SP2（版本3054）


考虑以下场景：


- 创建一个复杂的多语句表值函数。我们打电话给

dbo.tfFunc（@ Param1，@ Param2）

- 执行SELECT语句，调用上述函数两次，

每次使用不同的参数集。在伪代码中：


SELECT<列列表>

来自dbo.tfFunc（1,2）AS f1

<一些JOIN operatordbo.tfFunc（3,4）AS f2

ON f1.col = f2.col

INNER JOIN dbo.Table1 AS t1

ON ...

等


确切的陈述可能无关紧要，只要相同的表格 -

有价值函数被调用两次（我已经在两个调用相同函数的非常不同的语句中观察到了这个问题）。声明是在SNAPSHOT隔离级别事务中执行的
，尽管这可能与
无关。


- 声明继续执行很长一段时间。如果sp_who2当时在

运行，则语句连接返回以下行

（仅显示相关列）：


SPID状态BlkBy命令CPUTime DiskIO LastBatch

63暂停63选择29282 683 08/31 18:17:37


语句似乎被阻止通过它自己。如果在

时间运行sp_lock，则会返回以下行：


spid dbid ObjId IndId类型资源模式状态

63 2 1316624641 0 TAB Sch-S GRANT

63 2 1316624641 0 TAB Sch-M WAIT


似乎SQL Server无限期地等待尝试获取模式 -

修改锁定已经具有架构稳定性锁定的资源

通过相同的连接放置它。


以下是纯粹的推测，但似乎有理由假设服务器已经使用tempdb中的临时表实现了对函数

的第一次调用的结果。试图使用相同的临时表（

sp_lock结果中的相同ObjId）实现第二次调用的结果

。


我不知道为什么这不会导致死锁错误。


不幸的是，我没有一个简单的repro脚本。实际的

代码相当复杂。虽然我可以设计一个解决方法，但确实看起来好像是一个bug。我在提交一个关于Connect的错误之前在这里发布它，在

的情况下，任何人都可以解决这个问题。谢谢。


-

删除9回复电子邮件

2007年9月1日上午8：39，Erland Sommarskog< es **** @ sommarskog.sewrote

新闻：Xn *************** *******@127.0.0.1:

Dimitri Furman（df*****@cloud99.net）写道：

> - 语句继续执行很长时间。

好​​久不见？但它有没有完成？

我让它运行的最长时间是40分钟。考虑到它
通常在不到10秒的时间内运行，可能的答案是否定的。

这是一个并行计划吗？

很难说。我忘了提到问题是间歇性的。当

语句成功完成时，实际计划中并没有表示并行性

。如果没有，显然没有计划查看

（事实上，在这种情况下杀死连接的唯一方法是重启

服务器）。估计的计划也没有表现出任何并行性。我在这里谈论有关该声明的计划，而不是被称为

功能的计划，我显然无法看到。


我在语句和函数中都尝试了OPTION（MAXDOP 1），并且

到目前为止还没有能够重现这个问题。但这是不确定的，

有时它可以工作几天没有问题。

有问题的表可能是返回表UDF。

您应该能够通过查看

sys.objects和sys.columns来了解有关此表的更多信息。

我做了，这就是它有点有趣的地方。有问题的UDF

包含一个表变量，事实证明所提到的模式锁定

放在tempdb中与该表变量对应的表上，而不是

UDF的返回表。我不确定这是否会产生任何实质性的

差异。

4）XML showplan。 （您可以从图形计划中保存这个图表

Mgmt

Studio。）

我是如果声明永远不会完成，我不确定如何保存计划...


-

删除9以通过电子邮件回复

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

解决方案

Dimitri Furman (df*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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" <df*****@cloud99.netwrote in message
news:Xn****************************@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 operatordbo.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

On Sep 01 2007, 08:39 am, Erland Sommarskog <es****@sommarskog.sewrote
in news:Xn**********************@127.0.0.1:

Dimitri Furman (df*****@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

这篇关于SQL Server 2005中的表观数据库引擎错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！