Sql-Server

索引上的高 IO，有沒有更高性能的解決方案？

  • June 29, 2018

我有這個查詢，過去需要幾分鐘才能執行，現在需要 6 秒，但它每天執行數千次，所以我想讓它更快。

https://www.brentozar.com/pastetheplan/?id=SJMLjJOWm

似乎此查詢期間超過 99% 的 I/O 發生在一次聚集索引掃描上。

這是正常的嗎？這個查詢足以證明為它添加任何額外的索引是合理的，所以我想知道我是否在這裡遺漏了一些明顯的東西。

dbo.GROUP_CONCAT 函式來自這個 github 組裝項目 https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr

SpecsProd 表定義：

CREATE TABLE [dbo].[SpecsProd](
   [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [specsID_1] [int] NULL,
   [specsID_2] [int] NULL,
   [specID] [int] NOT NULL,
   [productID] [int] NOT NULL,
   [SpecValue_1] [varchar](1000) NULL,
   [SpecValue_2] [varchar](1000) NULL,
   [Flock] [bit] NULL,
   [SpecValue_1a] [varchar](2000) NULL,
   [SpecValue_2a] [varchar](2000) NULL,
CONSTRAINT [PK_SpecsProd] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SpecsProd] ADD  CONSTRAINT [DF_SpecsProd_Flock]  DEFAULT ((0)) FOR [Flock]
GO

ALTER TABLE [dbo].[SpecsProd]  WITH NOCHECK ADD  CONSTRAINT [FK_SpecsProd_Products] FOREIGN KEY([productID])
REFERENCES [dbo].[Products] ([Id_product])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[SpecsProd] CHECK CONSTRAINT [FK_SpecsProd_Products]
GO

使用 99% I/O 的聚集索引是

$$ PK_SpecsProd $$（第一個）。其他指標也在那裡。

   ALTER TABLE [dbo].[SpecsProd] ADD  CONSTRAINT [PK_SpecsProd] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ProdID_SpecID] ON [dbo].[SpecsProd]
(
   [productID] ASC,
   [specID] ASC
)
INCLUDE (   [ID],
   [SpecValue_1],
   [SpecValue_2]) 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 = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SpecID_ProductID] ON [dbo].[SpecsProd]
(
   [specID] ASC,
   [productID] ASC
)
INCLUDE (   [SpecValue_1],
   [SpecValue_2]) 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 = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [product] ON [dbo].[SpecsProd]
(
   [productID] ASC
)
INCLUDE (   [specID],
   [SpecValue_1],
   [SpecValue_2]) 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 = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [specId_inc_prodID_specvalue1] ON [dbo].[SpecsProd]
(
   [specID] ASC
)
INCLUDE (   [productID],
   [SpecValue_1]) 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 = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [specsID] ON [dbo].[SpecsProd]
(
   [specsID_1] 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 = 90) ON [PRIMARY]
GO

列最多的表（規格表）的吉尼斯記錄競爭者之一

CREATE TABLE [dbo].[Specs](
   [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [Id_spec] [int] NOT NULL,
   [CatId] [int] NOT NULL,
   [sect] [varchar](50) NULL,
   [spec] [varchar](75) NOT NULL,
   [format] [varchar](50) NULL,
   [unit] [varchar](20) NULL,
   [definition] [ntext] NULL,
   [ordre] [int] NOT NULL,
   [Id_langue] [int] NOT NULL,
   [FormField] [varchar](50) NULL,
   [List] [varchar](max) NULL,
   [filterField] [bit] NOT NULL,
   [isFilter] [bit] NOT NULL,
   [isCollectionFilter] [bit] NOT NULL,
   [quickViewSubcats] [varchar](250) NULL,
   [width] [bit] NOT NULL,
   [height] [bit] NOT NULL,
   [depth] [bit] NOT NULL,
   [weight] [bit] NOT NULL,
   [DateCreation] [datetime] NOT NULL,
   [DateModification] [datetime] NOT NULL,
   [quickView] [bit] NOT NULL,
   [Visible] [bit] NULL,
   [compare] [bit] NOT NULL,
   [priceTag] [bit] NOT NULL,
   [ConvertionRate] [varchar](20) NULL,
   [ConvertionUnit] [varchar](20) NULL,
   [searchableLabel] [bit] NULL,
   [searchableValue] [bit] NULL,
CONSTRAINT [PK_Specs] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_filter]  DEFAULT ((0)) FOR [filterField]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_isFilter]  DEFAULT ((0)) FOR [isFilter]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_isCollectionFilter]  DEFAULT ((0)) FOR [isCollectionFilter]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_width]  DEFAULT ((0)) FOR [width]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_height]  DEFAULT ((0)) FOR [height]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_depth]  DEFAULT ((0)) FOR [depth]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_weight]  DEFAULT ((0)) FOR [weight]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_DateCreation]  DEFAULT (getdate()) FOR [DateCreation]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_DateModification]  DEFAULT (getdate()) FOR [DateModification]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_quickView]  DEFAULT ((0)) FOR [quickView]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_compare]  DEFAULT ((0)) FOR [compare]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_priceTag]  DEFAULT ((0)) FOR [priceTag]
GO
  1. 等待統計表明ASYNC_NETWORK_IO是瓶頸。

在您的實際計劃中，大約 87% 的已用時間用於將結果發送給客戶。您可能需要更改應用程式碼或減少發回的數據量。

<WaitStats>
 <Wait WaitType="CMEMTHREAD" WaitTimeMs="33" WaitCount="86" />
 <Wait WaitType="SESSION_WAIT_STATS_CHILDREN" WaitTimeMs="1029" WaitCount="55" />
 <Wait WaitType="LATCH_EX" WaitTimeMs="2796" WaitCount="308" />
 <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="3083" WaitCount="144" />
</WaitStats>
<QueryTimeStats ElapsedTime="3542" CpuTime="7247" />
  1. MAXDOP 22``SELECT對於查詢來說是相當高的。

您是否使用不同的MAXDOP值測試了此查詢以驗證 22 確實是最佳選擇？如果我不得不猜測我會說你有一個兩個插槽伺服器，每個插槽有 12 個核心。出於某種原因，可能MAXDOP在實例級別設置為 22。我基於計劃中的執行緒資訊：

<ThreadStat Branches="4" UsedThreads="88">
 <ThreadReservation NodeId="0" ReservedThreads="24" />
 <ThreadReservation NodeId="1" ReservedThreads="24" />
 <ThreadReservation NodeId="2" ReservedThreads="16" />
 <ThreadReservation NodeId="3" ReservedThreads="24" />
</ThreadStat>

NUMA 節點 2 的調度程序比其他節點少，伺服器有 2 個插槽，每個插槽有 12 個調度程序，伺服器有 4 個插槽，每個插槽有 6 個調度程序，或者設置了手動軟 NUMA。MAXDOP 的一個常見建議是使用少於每個硬 NUMA 節點的物理核心數的東西。

綜上所述，您無需了解所有技術細節即可進行測試。嘗試使用不同的 MAXDOP 進行測試（確保多次執行測試），看看是否有幫助。

  1. 如果您願意，您可以減少計劃中所有三個表的 IO。

請注意，我只是查看了查詢計劃，並沒有查看您已經擁有的索引定義。SpecsProd看起來是你最大的桌子。您可以僅在所需的列上定義覆蓋索引。您無法對其進行搜尋，但您將執行更少的 IO，因為僅包含查詢中使用的三列的非聚集索引將小於聚集索引。

該Products表的 IO 成本次之。您已經按聚集索引排序，這很好，但聚集索引的大小是表中所有數據的大小。您可以僅在聚集鍵列上創建非聚集索引以減少 IO。您甚至可以創建它DESC以使掃描符合併行性，但我不知道這是否會由於TOP表達式而在實踐中產生影響。

您已經有一個覆蓋索引 on Specs，但過濾器是在謂詞而不是搜尋謂詞中評估的。如果您更改索引或創建具有正確順序的鍵列的新索引，您應該能夠進行查找而不是掃描。

只看程式碼，我推薦以下兩件事

第一：創建一個臨時表，其中包含以下子查詢

WHERE sp.productID IN (
           SELECT TOP 25000 id_product
           FROM products
           ORDER BY 1 DESC
           )

即我會做

create table #t (id_product int primary key);
insert into #t (id_product)
SELECT TOP 25000 id_product
FROM products
ORDER BY 1 DESC;

然後將原來的 WHERE 子句更改為

WHERE sp.productID IN (
               SELECT id_product
               FROM #t )

第二：我會刪除 where 子句中的 lower() 函式，如果您的數據庫排序規則不區分大小寫 (CS)，則不需要 lower() 函式。如果你的數據庫是 CS，那麼你可以重寫條件。

例如，

lower(sp.SpecValue_1) = ('y')

可以重寫為

sp.SpecValue_1 in ('y', 'Y')

您可以從這種方法開始，看看這是否會提高您的性能。不要忘記使用全掃描更新您的統計資訊。

如果它仍然不符合您的要求，請考慮其他方法，例如 @Joe Obbish 提到的。

引用自：https://dba.stackexchange.com/questions/210144

相關問答

Sql-Server

SQL Server 昂貴的嵌套循環連接和惰性表假離線

  • August 2, 2019
檢視問答
Sql-Server

使用不同的區分大小寫排序規則時的 SQL Server PATINDEX 問題/錯誤

  • June 28, 2022
檢視問答
Sql-Server

如果數據類型為浮點數，如何獲取具有 IE 的數字？

  • June 5, 2022
檢視問答
Sql-Server

where 子句中的 dateadd - 在這種特殊情況下如何避免完全掃描？

  • March 30, 2022
檢視問答
Sql-Server

SQL 連接的性能

  • March 23, 2022
檢視問答
Sql-Server

為什麼 sys.dm_exec_query_stats 中缺少大多數記憶體計劃？

  • March 22, 2022
檢視問答