SQL Server 简单插入语句超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/447201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server simple Insert statement times out
提问by Meidan Alon
I have a simple table with 6 columns. Most of the time any insert statements to it works just fine, but once in a while I'm getting a DB Timeout exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.
我有一个有 6 列的简单表格。大多数情况下，对它的任何插入语句都可以正常工作，但偶尔我会收到 DB Timeout 异常：超时已过期。操作完成前超时时间已过或服务器未响应。该语句已终止。
Timeout is set to 10 seconds.
超时设置为 10 秒。
I should mention that I'm using NHibernate and that the statement also include a "select SCOPE_IDENTITY()" right after the insert itself.
我应该提到我正在使用 NHibernate 并且该语句在插入本身之后还包括一个“select SCOPE_IDENTITY()”。
My thought was that the table was locked or something, but there were no other statements running on that table at that time.
我的想法是该表被锁定或什么的，但当时该表上没有其他语句在运行。
All the inserts are very simple, everything looks normal in sql profiler, the table has no indices but the PK (Page fullness: 98.57 %).
所有的插入都非常简单，在 sql profiler 中一切看起来都很正常，表没有索引，只有 PK（页面完整度：98.57 %）。
Any ideas on what should I look for?
关于我应该寻找什么的任何想法？
Thanks.
谢谢。
采纳答案by Meidan Alon
Our QA had some Excel connections that returned big result sets, those queries got suspended with WaitType of ASYNC_NETWORK_IO for some time. During this time all other queries timed out, so that specific insert had nothing to do with it.
我们的 QA 有一些 Excel 连接返回了大结果集，这些查询因 ASYNC_NETWORK_IO 的 WaitType 暂停了一段时间。在此期间，所有其他查询都超时，因此特定的插入与它无关。
回答by SqlRyan
I think your most likely culprit is a blocking lock from another transaction (or maybe from a trigger or something else behind the scenes).
我认为你最有可能的罪魁祸首是来自另一个事务的阻塞锁（或者可能来自触发器或幕后的其他东西）。
The easiest way to tell is to kick off the INSERT, and while it's hung, run EXEC SP_WHO2in another window on the same server. This will list all of the current database activity, and has a column called BLKthat will show you if any processes are currently blocked. Check the SPIDof your hung connection to see if it has anything in the BLKcolumn, and if it does, that's the process that's blocking you.
最简单的判断方法是启动INSERT，并在挂起EXEC SP_WHO2时在同一服务器上的另一个窗口中运行。这将列出所有当前的数据库活动，并有一个名为的列BLK，该列将显示当前是否有任何进程被阻止。检查SPID挂起的连接，看看它在BLK列中是否有任何内容，如果有，那就是阻止您的进程。
Even if you don't think there are any other statements running, the only way to know for sure is to list the current transactions using an SP like that one.
即使您认为没有任何其他语句在运行，唯一确定的方法是使用类似的 SP 列出当前事务。
回答by andreister
This question seems like a good place for a code snippet which I used to see the actual SQL text of the blocked and blocking queries.
这个问题似乎是我用来查看阻塞和阻塞查询的实际 SQL 文本的代码片段的好地方。
The snippet below employs the convention that SP_WHO2returns " ." text for BlockedByfor the non-blocked queries, and so it filters them out and returns the SQL text of the remaining queries (both "victim" and "culprit" ones):
下面的代码片段采用了SP_WHO2返回“.”的约定。用于BlockedBy非阻塞查询的文本，因此它将它们过滤掉并返回剩余查询的 SQL 文本（“受害者”和“罪魁祸首”）：
--prepare a table so that we can filter out sp_who2 results
DECLARE @who TABLE(BlockedId INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlockedById VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT)
INSERT INTO @who EXEC sp_who2
--select the blocked and blocking queries (if any) as SQL text
SELECT
(
SELECT TEXT
FROM sys.dm_exec_sql_text(
(SELECT handle
FROM (
SELECT CAST(sql_handle AS VARBINARY(128)) AS handle
FROM sys.sysprocesses WHERE spid = BlockedId
) query)
)
) AS 'Blocked Query (Victim)',
(
SELECT TEXT
FROM sys.dm_exec_sql_text(
(SELECT handle
FROM (
SELECT CAST(sql_handle AS VARBINARY(128)) AS handle
FROM sys.sysprocesses WHERE spid = BlockedById
) query)
)
) AS 'Blocking Query (Culprit)'
FROM @who
WHERE BlockedById != ' .'
回答by Meidan Alon
Could be that the table is taking a long time to grow.
可能是该表需要很长时间才能增长。
If you have the table set to grow by a large amount, and don't have instant file initialization enabled, then the query could certainly timeout every once in a while.
如果您将表设置为大量增长，并且没有启用即时文件初始化，那么查询肯定会每隔一段时间超时。
Check this mess out: MSDN
看看这个烂摊子：MSDN
回答by SqlACID
no other statements running on that table at that time.
当时没有其他语句在该表上运行。
What about statements running against other tables as part of a transaction? That could leave locks on the problem table.
作为事务的一部分针对其他表运行的语句怎么样？这可能会在问题表上留下锁。
Also check for log file or datafile growth happening at the time, if you're running SQL2005 it would show in the SQL error logs.
还要检查当时发生的日志文件或数据文件的增长，如果您运行的是 SQL2005，它会显示在 SQL 错误日志中。
回答by SQLMenace
look at fragmentation of the table, you could be getting page splits because of that
看看表格的碎片，你可能会因此而获得页面拆分
