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

  • 问题

  • 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)


    助人等于自助。3w@live.cn



    2012年4月19日 3:05

答案

  • 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 


    助人等于自助。3w@live.cn


    2012年4月27日 15:41

全部回复

  • 我的电脑上测试,都差不多

    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)

    2012年4月19日 3:42
  • 多谢邹老大,我的是32位,11.0.2100.60

    助人等于自助。3w@live.cn

    2012年4月19日 4:01
  • 从表现上来看,SET NOCOUNT ON有影响,这个是控制影响记录数到客户端的,所以怀疑是不是与客户端的难忘有影响

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

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

    2012年4月19日 5:51
  • 从表现上来看,SET NOCOUNT ON有影响,这个是控制影响记录数到客户端的,所以怀疑是不是与客户端的难忘有影响

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

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


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


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

    助人等于自助。3w@live.cn

    2012年4月19日 6:41
  • 我测试了一下,结果跟你的差不多,不过我是在不同的机器上测试的(2008一个机器,2012是另外一个机器)。我不知道你是不是在同一个机器上测试的?
    2012年4月19日 11:25
  • 我测试了一下,结果跟你的差不多,不过我是在不同的机器上测试的(2008一个机器,2012是另外一个机器)。我不知道你是不是在同一个机器上测试的?
    我是在同一台PC上的不同实例,测试一个时关闭另一个实例的服务,应该是互不影响的。两个实例的MDF和LDF文件都位于同一个分区。

    助人等于自助。3w@live.cn

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

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


    2012年4月19日 12:15
  • 这个就是我上面的截图所显示的那样,有一个突然的100-200毫秒的Duration,我也不知道发生了什么。

    助人等于自助。3w@live.cn

    2012年4月19日 12:34
  • 你好,

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

    DBCC USEROPTIONS

    Stephanie Lv

    TechNet Community Support

    2012年4月23日 9:51
  • 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 


    助人等于自助。3w@live.cn


    2012年4月27日 15:41