在聚簇索引上使用顺序GUID键的INSERT不会明显加快 [英] INSERTs with sequential GUID key on clustered index not significantly faster
问题描述
在SQL Server 2008中，我尝试重现在此处看到的顺序与非顺序GUID键上的聚簇索引实验的结果
http://sqlblog.com/blogs/denis_gobo/archive/2009/02/05/11743.aspx
但我没有经历过我期望的插入（以及作者体验）的显着加速。使用顺序GUID可以明显改善页面利用率，但由于某些原因，插入10,000行的速度只有100毫秒（超过10,300毫秒）。
In SQL Server 2008 I have tried to reproduce the results from the experiments on clustered index on sequential vs. non-sequential GUID keys seen here http://sqlblog.com/blogs/denis_gobo/archive/2009/02/05/11743.aspx but I do not experience the significant speedup for insertions that I would expect (and the author experiences). The page utilization is clearly improved with the sequential GUID, but for some reasons, inserting 10,000 rows is only around 100 ms faster (out of 10,300 ms).
我使用的是以下代码：
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上没有经历更显着的加速加速？
Can anyone explain why I do not experience a more significant speedup for insertions on TestGuid2?
跟进：
根据以下主题中的要求，我扩展了测试：测试结果往往会有显着差异随着时间的推移，现在实验重复N次，并报告总时间和平均时间。我还添加了第三个测试，即连续整数列上的主键。这应该是所有三种方法中最快和最紧凑的，因为整数类型较小且IDENTITY（1,1）是（或至少应该）快。至少通过我的直觉。
平均执行时间现在是顺序GUID的好处，但令人惊讶的是，第三个实验中的插入（使用顺序整数键）比顺序GUID 更慢 。我没有解释这个。
以下是新实验的代码：
Follow-up: As requested in the thread below, I have expanded the test: the test results tend to vary significantly over time, so now the experiments are repeated N times, and the total and average time usage reported. I have also added a third test, namely for primary keys on sequential integer columns. This should be the fastest and most compact of all three methods as the integer type is smaller and IDENTITY(1,1) is (or at least should be) fast. At least by my intuition. The average execution time is now to the benefit of the sequential GUID, but surprisingly insertions in the third experiment (with sequential integer keys) is slower than sequential GUIDs. I have no explanation of this. Here is the code for the new experiments:
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
页面用法如下： / p>
The page usage is as follows:
Table Pages AveragePageDensity
----------------------------------------
TestGuid1 50871 68,4
TestGuid2 35089 99,2
TestInt 32259 98,7
我没看到，为什么这些页面统计数据（最适合TestInt）并不意味着实验三是最快。
I fail to see, why these page statistics (which are best for TestInt) doesn't imply that experiment three is fastest.
推荐答案
您可以尝试这个修改过的脚本并发布结果吗？
Can you try this modified script and post your results?
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
我看到各个运行之间的结果差异很大（在我的笔记本电脑上不是服务器！）但顺序更快的明确趋势。
I see quite wildly varying results between individual runs (on my laptop not a server!) but a definite trend for sequential to be faster.
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
NEWSEQUENTIALID() Average 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
这篇关于在聚簇索引上使用顺序GUID键的INSERT不会明显加快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
