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。