sql - 在聚簇索引上使用顺序 GUID 键的 INSERT 并没有显着加快

Question

在 SQL Server 2008 中，我尝试重现在此处看到的顺序 GUID 键和非顺序 GUID 键的聚簇索引实验的结果 http://sqlblog.com/blogs/denis_gobo/archive/2009/02/05/11743.aspx但我并没有体验到我期望的插入速度的显着提升(以及作者的体验)。顺序 GUID 明显提高了页面利用率，但由于某些原因，插入 10,000 行仅快了大约 100 毫秒(超过 10,300 毫秒)。

我使用以下代码:

CREATE TABLE TestGuid1 (Id UNIQUEIDENTIFIER not null DEFAULT newid(),
SomeDate DATETIME, batchNumber BIGINT)

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER not null DEFAULT newsequentialid(),
SomeDate DATETIME, batchNumber BIGINT)

CREATE CLUSTERED INDEX ix_id1 ON TestGuid1(id)
CREATE CLUSTERED INDEX ix_id2 ON TestGuid2(id)

go

SET NOCOUNT ON
INSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),3)
go 10000

SET NOCOUNT ON
INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),3)
go 10000

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

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))
FROM TestGuid1
GROUP BY batchNumber

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))
FROM TestGuid2
GROUP BY batchNumber

谁能解释为什么我在 TestGuid2 上没有体验到更显着的插入加速？

跟进:根据下面线程中的要求，我扩展了测试:测试结果往往会随时间发生显着变化，因此现在重复实验 N 次，并报告总时间和平均时间使用情况。我还添加了第三个测试，即顺序整数列上的主键。这应该是所有三种方法中最快和最紧凑的，因为整数类型更小并且 IDENTITY(1,1) 是(或至少应该)快。至少根据我的直觉。平均 执行时间现在有利于顺序 GUID，但令人惊讶的是，第三个实验(使用顺序整数键)中的插入比顺序 GUID 慢。我对此没有任何解释。这是新实验的代码:

SET NOCOUNT ON

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

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 TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @LocalCounter = 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 ('TestGuid1') WITH tableresults
DBCC showcontig ('TestGuid2')  WITH tableresults
DBCC showcontig ('TestInt')  WITH tableresults

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

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 TestGuid1
DROP TABLE TestGuid2
DROP TABLE TestInt

平均执行时间:

NEWID()            3064
NEWSEQUENTIALID()  1977
IDENTITY()         2223

页面使用情况如下:

Table          Pages  AveragePageDensity
----------------------------------------
TestGuid1      50871  68,4
TestGuid2      35089  99,2
TestInt        32259  98,7

我不明白，为什么这些页面统计信息(最适合 TestInt)并不意味着实验三是最快的。

Answer 1

你能试试这个修改后的脚本并发布你的结果吗？

    SET NOCOUNT ON

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

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

    DECLARE @BatchCounter INT = 1

    WHILE (@BatchCounter <= 20)
    BEGIN 
    BEGIN TRAN

    DECLARE @LocalCounter INT = 0

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

    SET @LocalCounter = 0

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

    SET @BatchCounter +=1
    COMMIT 
    END

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

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

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

DROP TABLE TestGuid1
DROP TABLE TestGuid2

我看到各个运行之间的结果差异很大(在我的笔记本电脑上，而不是在服务器上!)但是顺序运行的一个明确趋势是更快。

NEWID() 平均 5168.9

batchNumber          NEWID()
-------------------- -----------
1                    4270
2                    2480
3                    2706
4                    3333
5                    7480
6                    5346
7                    4306
8                    7713
9                    7313
10                   4760
11                   4680
12                   4113
13                   3433
14                   2686
15                   4963
16                   8040
17                   5313
18                   8160
19                   9533
20                   2750

NEWSEQUENTIALID() 平均 3000.85

batchNumber          NEWSEQUENTIALID()
-------------------- -----------------
1                    2016
2                    1820
3                    1886
4                    1870
5                    4873
6                    3473
7                    3730
8                    3690
9                    1983
10                   2020
11                   1906
12                   5596
13                   2100
14                   1950
15                   2096
16                   1876
17                   5196
18                   2110
19                   2113
20                   7713