PAGELATCH_EX
由于大量插入而导致的等待问题确实需要一些帮助。我们看到一个简单的插入到表的等待中PAGELATCH_EX
和等待中的随机峰值。_SH
每天只发生几次,但当出现短暂的锁定和等待时间增加时,可能会导致我们的 Web 应用程序排队。服务器超出了规范,SSD 磁盘似乎没有接近 I/O 容量。
我读PAGELATCH_EX
的是由于表中的顺序聚集主键以及最后一页争用,所以我已将聚集索引更改为 GUID,并将原始顺序 ID 更改为非聚集索引,因为我们仍然需要基于此查询ID。
不幸的是,它看起来并没有解决问题。我还尝试在索引上设置 70% 的填充因子。顺序非聚集索引还会导致PAGELATCH_EX
?我们是否也需要将非唯一数字作为该索引的一部分?
这是带有 GUID 和所有索引的新表:
CREATE TABLE [dbo].[data_202210](
[Id] [uniqueidentifier] NOT NULL,
[sequentialnumber] [bigint] NOT NULL,
[w1] [bigint] NOT NULL,
[h1] [bigint] NOT NULL,
[datetime] [datetime] NOT NULL,
[p1] [bigint] NOT NULL,
[memberid] [bigint] NULL,
[datatype] [int] NOT NULL,
[datavalueid] [bigint] NOT NULL,
[categoryid] [bigint] NULL,
[categorytype] [int] NULL,
[orderid] [bigint] NULL,
[ordertotal] [decimal](10, 2) NULL,
[url] [nvarchar](2048) NULL,
[img] [nvarchar](2048) NULL,
CONSTRAINT [PK_data_Id_202210] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[data_202210] ADD DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[data_202210] WITH CHECK ADD CONSTRAINT [CC_data_202210_Id_datetime] CHECK (([datetime]>='2022-10-01' AND [datetime]<'2022-11-01'))
GO
ALTER TABLE [dbo].[data_202210] CHECK CONSTRAINT [CC_data_202210_Id_datetime]
GO
--this is the old clustered index which is sequentially created number in our application
--still used in a queries to join to another table
CREATE NONCLUSTERED INDEX [pigdataid_202210] ON [dbo].[data_202210]
(
[sequentialnumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
GO
-- covering indexes for querying the data
CREATE NONCLUSTERED INDEX [lookup_202210] ON [dbo].[data_202210]
(
[w1] ASC,
[p1] ASC,
[datetime] ASC,
[datatype] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [trending_lookup_202210] ON [dbo].[data_202210]
(
[w1] ASC,
[datatype] ASC,
[datetime] ASC
)
INCLUDE ([datavalueid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
GO
我使用各种查询来查看等待以及 RedGate SQL Monitor。
服务器是 16 个 CPU 和 128GB RAM。磁盘为 10,000 IOPS,500 MB/s 吞吐量。峰值时每秒大约 400 次插入。我不会认为 400 很多,但等待肯定指向PAGELATCH_EX
and PAGELATCH_SH
。插入大约需要 1 毫秒。其他等待还可以。
不幸的是,我们无法迁移到 SQL Server 2019。
您仍然在顺序增加的键上有一个索引,您只是将它从聚集索引更改为非聚集索引。它仍然是一个 B 树。不过,由于新的“递增”索引更窄,争用可能已经减少。
Fillfactor < 100 无济于事,因为最后一页会立即填满。即,填充因子仅在您创建索引时才相关。通过指定一个 < 100 的值,您所做的只是使索引更大。
您要确保您的问题在不断增加的索引上(作为集群或非集群)。这篇文章末尾的文章有一个查询,可让您确定这一点。仅仅看到 PAGELATCH_* 的累积等待还不足以确定这一点。
如果可能,请使用 SQL Server 2019 进行测试。对于这种情况,它有一个索引选项:
OPTIMIZE_FOR_SEQUENTIAL_KEY
. 它将改变调度,以便更有可能在一个量子(4 ms)内完成的线程将在队列中获得优先级,从而减少昂贵的上下文切换。它可能不会减少累积的等待,但可能会增加吞吐量(这最终是您想要的)。见这篇文章。这篇 MS 文章讨论了您的问题情况,并有一个查询以确定这是否是您的实际问题,以及改善情况的可能方法;包括上面提到的指数期权。如果您仍然需要顺序值的索引并且如果该索引是争用的位置,则您无能为力,但上述索引选项除外,但它再次需要 SQL Server 2019。https://learn.microsoft.com /en-us/troubleshoot/sql/performance/resolve-pagelatch-ex-contention
在 SQL Server 中使用 Resolve last-page insert PAGELATCH_EX contention 中的主文件组上的分区实现方法 6将我们的插入总持续时间性能从 1 毫秒提高到 0.1 毫秒。
PAGELATCH_EX
或不再有尖峰_SH
。会推荐给运行 SQL 2016 SP1 或 2017 的任何人。还发现Erik Darling的 Last Page Contention Solutions For SQL Server 的优点和缺点非常有帮助。