在没有设置SET NOCOUNT ON的情况下SQL Server 2012的批量Insert为什么很慢？

Question

IF OBJECT_ID('Customers','U') IS NOT NULL
DROP TABLE Customers

CREATE TABLE Customers ( CustomerID INT primary key identity(1,1),
CustomerNumber CHAR(4),
CustomerName VARCHAR(50),
CustomerCity VARCHAR(20) )
GO

 TRUNCATE table Customers
GO

----清除干扰查询
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DECLARE @d Datetime
SET @d=getdate();

declare @i int=1
while @i<=10000
begin
INSERT INTO Customers (CustomerNumber, CustomerName,
CustomerCity)
SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
CHAR(65 + (@i % 26)) + '-City'
set @i=@i+1
end

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO

以上Insert语句在SQL Server 2012　的默认配置中执行，居然耗时达到5分多钟，而SQL Server 2008R2版，只需要大约6秒钟。更令人费解的是：查询的I/O统计和elapsed time，在这两个版本中几乎一样。对此异象，我只能理解为每次Insert时的毫秒级精度可能不足以度量该次操作带来的细小差距，然而累积起来就非常可观了。

另外将两个数据库放在相同的分区、设置初始文件MDF和LDF均为500M，结论还是一样。
请问SQL Server 2012和SQL Server 2008r2的默认查询选项是相同的，真不知道为何不设置Set NoCount ON时有此巨大差异？？

详情请见：http://www.cnblogs.com/downmoon/archive/2012/04/18/2455631.html

附：我的测试环境为：
SQL Server 2012，命名实例

Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Feb 10 2012 19:13:17
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

---

助人等于自助。[e-mail]

Answer 1

我的电脑上测试，都差不多

2008R2为默认实例，2012为命名实例

Version信息

Microsoft SQL Server 2012 RC0 - 11.0.1750.32 (X64)
 Nov  4 2011 17:54:22
 Copyright (c) Microsoft Corporation
 Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Answer 2

多谢邹老大，我的是32位，11.0.2100.60。

---

助人等于自助。[e-mail]

Answer 3

从表现上来看，SET NOCOUNT ON有影响，这个是控制影响记录数到客户端的，所以怀疑是不是与客户端的难忘有影响

可以试试直接在服务器上执行（如果测试不是在服务器上的话）

另外，用 Profile trace 一下，事件选择T-SQL中的SQL:BatchCompleted 和 SQL:StmtCompleted 两个事件，看看具体每条语句的响应时间，看看有没有异常的

Answer 4

从表现上来看，SET NOCOUNT ON有影响，这个是控制影响记录数到客户端的，所以怀疑是不是与客户端的难忘有影响

可以试试直接在服务器上执行（如果测试不是在服务器上的话）

另外，用 Profile trace 一下，事件选择T-SQL中的SQL:BatchCompleted 和 SQL:StmtCompleted 两个事件，看看具体每条语句的响应时间，看看有没有异常的

您好！感谢回复！
我是在一台Windows Server 2008标准版的PC上测试，按照您的提示，我进行了跟踪，结果如下图：


大多数的Insert行是0或1毫秒，但有些行是100-250毫秒，请问这是为什么？

---

助人等于自助。[e-mail]

Answer 5

我测试了一下，结果跟你的差不多，不过我是在不同的机器上测试的（2008一个机器，2012是另外一个机器）。我不知道你是不是在同一个机器上测试的？

Answer 6

我测试了一下，结果跟你的差不多，不过我是在不同的机器上测试的（2008一个机器，2012是另外一个机器）。我不知道你是不是在同一个机器上测试的？

我是在同一台PC上的不同实例，测试一个时关闭另一个实例的服务，应该是互不影响的。两个实例的MDF和LDF文件都位于同一个分区。

---

助人等于自助。[e-mail]

Answer 7

这个问题我好久以前也碰到过，但是是2个不同的机器，我当时以为是硬件性能造成的，所以没有怎么关注。而且有个现象，我数了一下，大概每插入10条数据后就会出现一条duration在100毫秒左右的记录，其他的都为1，2毫秒。我觉得那个时候肯定发生了一个什么事件，但是不知道那个是什么事件，所以抓不出来。

另外，如实在得不到满意的答案，可以mail给SQL SERVER亚太支持组：
http://blogs.msdn.com/b/apgcdsd/contact.aspx
留下你的email,一般都会有人回复，等待你的结果：-）

Answer 8

这个就是我上面的截图所显示的那样，有一个突然的100-200毫秒的Duration,我也不知道发生了什么。

---

助人等于自助。[e-mail]

Answer 9

你好，

请问在这台服务器上，是否同时有其他的任务在执行，需要消CPU和磁盘I/O? 同时，请检查一下两个数据库是否使用相同的还原模式，并且在执行操作的过程中，数据库文件没有自动增长。另外，不同的SET设置也可能导致执行计划不一样，请使用下面的语句比较两个实例的对应数据库的输出情况：

DBCC USEROPTIONS

---

Stephanie Lv

TechNet Community Support

Answer 10

Hi,everyone

    Thanks for your reply!

    about this topic,I had posted a email to Microsoft-Asia-Pacific official blog:http://blogs.msdn.com/b/apgcdsd/contact.aspx

    and the technical support staff of MS send me a conclusion:

    this huge difference maybe be caused by SSMS 2012 UI,and nothing to do with SQL engineer.

   

         thanks for all repies again!

                               Best wishes to you 

---

助人等于自助。[e-mail]