为什么在我的测试用例中，顺序GUID键的执行速度比顺序INT键的执行速度快？

NEWSEQUENTIALID()  1977
IDENTITY()         2223

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000


WHILE (@BatchCounter <= 20)
BEGIN 
BEGIN TRAN

DECLARE @LocalCounter INT = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @LocalCounter = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestInt (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @BatchCounter +=1
COMMIT 
END

DBCC showcontig ('TestGuid2')  WITH tableresults
DBCC showcontig ('TestInt')  WITH tableresults

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [NEWSEQUENTIALID()]
FROM TestGuid2
GROUP BY batchNumber

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [IDENTITY()]
FROM TestInt
GROUP BY batchNumber

DROP TABLE TestGuid2
DROP TABLE TestInt

#1 楼

我修改了@Phil Sandler的代码，以消除调用GETDATE（）的影响（可能涉及硬件影响/中断？），并使行的长度相同。

[自SQL Server 2000以来，有几篇文章涉及计时问题和高分辨率计时器，因此我希望将其影响降至最低。]

在具有数据和日志文件的大小均按所需大小进行调整，以下是计时（以秒为单位）：（根据下面的确切代码，使用新结果进行了更新）

       Identity(s)  Guid(s)
       ---------    -----
       2.876        4.060    
       2.570        4.116    
       2.513        3.786   
       2.517        4.173    
       2.410        3.610    
       2.566        3.726
       2.376        3.740
       2.333        3.833
       2.416        3.700
       2.413        3.603
       2.910        4.126
       2.403        3.973
       2.423        3.653
    -----------------------
Avg    2.650        3.857
StdDev 0.227        0.204

使用的代码：

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(88))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @Numrows INT = 1000000

CREATE TABLE #temp (Id int NOT NULL Identity(1,1) PRIMARY KEY, rowNum int, adate datetime)

DECLARE @LocalCounter INT = 0

--put rows into temp table
WHILE (@LocalCounter < @NumRows)
BEGIN
    INSERT INTO #temp(rowNum, adate) VALUES (@LocalCounter, GETDATE())
    SET @LocalCounter += 1
END

--Do inserts using GUIDs
DECLARE @GUIDTimeStart DateTime = GETDATE()
INSERT INTO TestGuid2 (SomeDate, batchNumber) 
SELECT adate, rowNum FROM #temp
DECLARE @GUIDTimeEnd  DateTime = GETDATE()

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber) 
SELECT adate, rowNum FROM #temp
DECLARE @IdTimeEnd DateTime = GETDATE()

SELECT DATEDIFF(ms, @IdTimeStart, @IdTimeEnd) AS IdTime, DATEDIFF(ms, @GUIDTimeStart, @GUIDTimeEnd) AS GuidTime

DROP TABLE TestGuid2
DROP TABLE TestInt
DROP TABLE #temp
GO

阅读@Martin的调查后，我在两种情况下都使用建议的TOP（@num）重新运行，即

...
--Do inserts using GUIDs
DECLARE @num INT = 2147483647; 
DECLARE @GUIDTimeStart DATETIME = GETDATE(); 
INSERT INTO TestGuid2 (SomeDate, batchNumber) 
SELECT TOP(@num) adate, rowNum FROM #temp; 
DECLARE @GUIDTimeEnd DATETIME = GETDATE();

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber) 
SELECT TOP(@num) adate, rowNum FROM #temp;
DECLARE @IdTimeEnd DateTime = GETDATE()
...

，这是计时结果：

       Identity(s)  Guid(s)
       ---------    -----
       2.436        2.656
       2.940        2.716
       2.506        2.633
       2.380        2.643
       2.476        2.656
       2.846        2.670
       2.940        2.913
       2.453        2.653
       2.446        2.616
       2.986        2.683
       2.406        2.640
       2.460        2.650
       2.416        2.720

    -----------------------
Avg    2.426        2.688
StdDev 0.010        0.032

我无法获得实际的执行计划，因为查询从未回！似乎有可能存在错误。 （运行Microsoft SQL Server 2008 R2（RTM）-10.50.1600.1（X64））

#2 楼

在简单恢复模式下的新数据库中，数据文件大小为1GB，日志文件大小为3GB（便携式计算机，两个文件都在同一驱动器上），恢复间隔设置为100分钟（以避免检查点使结果倾斜）单行inserts与您的结果类似。

我测试了三种情况：对于每种情况，我做了20批，分别将100,000行插入下表。完整脚本可以在该答案的修订历史记录中找到。

CREATE TABLE TestGuid
  (
     Id          UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
     SomeDate    DATETIME, batchNumber BIGINT, FILLER CHAR(100)
  )

CREATE TABLE TestId
  (
     Id          Int NOT NULL identity(1, 1) PRIMARY KEY,
     SomeDate    DATETIME, batchNumber BIGINT, FILLER CHAR(100)
  )

CREATE TABLE TestInt
  (
     Id          Int NOT NULL PRIMARY KEY,
     SomeDate    DATETIME, batchNumber BIGINT, FILLER  CHAR(100)
  )  

对于第三张表，测试插入的行带有递增的Id值，但这是通过递增循环中变量的值。

对20个批次的平均时间得出以下结果。

NEWSEQUENTIALID() IDENTITY()  INT
----------------- ----------- -----------
1999              2633        1878

结论

所以肯定似乎是负责结果的identity创建过程的开销。对于自行计算的递增整数，则结果与仅考虑IO成本时所期望的结果更加一致。

将上述插入代码放入存储过程中并进行检查时， sys.dm_exec_procedure_stats给出以下结果

proc_name      execution_count      total_worker_time    last_worker_time     min_worker_time      max_worker_time      total_elapsed_time   last_elapsed_time    min_elapsed_time     max_elapsed_time     total_physical_reads last_physical_reads  min_physical_reads   max_physical_reads   total_logical_writes last_logical_writes  min_logical_writes   max_logical_writes   total_logical_reads  last_logical_reads   min_logical_reads    max_logical_reads
-------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
IdentityInsert 20                   45060360             2231067              2094063              2645079              45119362             2234067              2094063              2660080              0                    0                    0                    0                    32505                1626                 1621                 1626                 6268917              315377               276833               315381
GuidInsert     20                   34829052             1742052              1696051              1833055              34900053             1744052              1698051              1838055              0                    0                    0                    0                    35408                1771                 1768                 1772                 6316837              316766               298386               316774

因此，在这些结果中，total_worker_time大约高30％。这表示


自编译以来，此存储过程的执行
所消耗的CPU时间总量（以
微秒为单位）。 br />

因此，看起来好像生成IDENTITY值的代码比生成NEWSEQUENTIALID()的代码要占用更多的CPU资源（两个数字之间的差为10231308，平均每个插入大约5µs。），对于此表定义，此问题已修复CPU成本足够高，足以抵消由于键的宽度较大而导致的额外逻辑读取和写入。 （注意：Itzik Ben Gan在此处进行了类似的测试，发现每个插入片段的惩罚为2µs）

为什么IDENTITY的CPU占用量比UuidCreateSequential的CPU占用更多？本文。对于生成的每十个identity值，SQL Server必须将更改写入磁盘上的系统表中。

多行插入怎么办？

一次插入100,000行时，声明中，我发现差异消失了，但对GUID案仍然可能有轻微的好处，但没有明显的结果。我的测试中20批次的平均值为

NEWSEQUENTIALID() IDENTITY()
----------------- -----------
1016              1088

在Phil的代码和Mitch的第一组结果中没有明显的损失的原因是因为碰巧我用来做多行插入的代码使用了SELECT TOP (@NumRows)。这使优化器无法正确估计将要插入的行数。

这似乎是有好处的，因为有一个临界点，它将在GUID上添加附加的排序操作（假定是顺序的！）。



从BOL中的解释性文本中不需要此排序操作。


创建大于<自Windows启动以来，此
函数以前在指定的计算机上由此
函数生成的任何GUID。重新启动Windows后，GUID可以从较低范围重新启动，但仍然是全局的
。


因此，在我看来，SQL Server无法识别计算标量的输出将已经像对identity列所做的那样已经进行了预排序，似乎是一个错误或缺少优化。 （编辑，我报告了此问题，现在不必要的排序问题已在Denali中修复）

#3 楼

非常简单：使用GUID，在行中生成下一个数字比使用IDENTITY便宜（GUID的当前值不必存储，IDENTITY必须存储）。即使对于NEWSEQUENTIALGUID，也是如此。

您可以使测试更公平，并使用具有较大CACHE的SEQUENCER-比IDENTITY便宜。 GUID有一些主要优点。事实上，它们比IDENTITY列具有更大的可伸缩性（但前提是它们不是顺序的）。

请参阅：http://blog.kejser.org/2011/10/05/通过生成可伸缩键来提高插入速度/

#4 楼

我对这种类型的问题着迷。您为什么必须在星期五晚上发布它？ :)

我认为，即使您的测试仅旨在测量INSERT性能，您（可能）仍会引入许多可能会引起误解的因素（循环，长时间运行的事务等）。

我不完全相信我的版本可以证明任何内容，但身份确实比其中的GUID更好（家用PC上为3.2秒，而6.8秒）：

#5 楼

我多次运行您的示例脚本，对批处理数量和大小进行了一些调整（非常感谢您提供它）。

首先，我要说的是，您只测量了一次键的性能-INSERT速度。因此，除非您特别关心只将数据尽快放入表中，否则这种动物会有更多的收获。

我的发现总体上与您的发现相似。但是，我要提到的是，使用INSERT时，在GUID和IDENTITY（int）之间在GUID速度上的差异要比使用IDENTITY时稍大-两次运行之间可能+/- 10％。每次使用IDENTITY的批次变化都小于2-3％。

还要注意，我的测试盒显然不如您的测试盒强大，因此我不得不使用较小的行数。

#6 楼

我将再次参考关于同一主题的关于stackoverflow的另一个转换-https://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid

我确实知道的一件事是，具有顺序的GUID是由于很少的叶子移动而使索引的使用更好，因此减少了HD寻道。因此，我认为插入操作也会更快，因为它不必将密钥分布在大量页面上。大型高流量DB，最好使用GUID，因为它使它与其他系统集成时具有更大的可伸缩性。具体来说，这适用于复制，并且适用于int / bigint限制。...不是要用完bigints，而是最终您会循环回来。