優化 2,135,044,521 行表的索引

我有一個大表的 I/O 問題。

一般統計

該表具有以下主要特點：

• 環境：Azure SQL 數據庫（層級為 P4 高級版（500 個 DTU））
• 行數：2,135,044,521
• 1,275 個使用的分區
• 聚集和分區索引

模型

這是表實現：

CREATE TABLE [data].[DemoUnitData](
   [UnitID] [bigint] NOT NULL,
   [Timestamp] [datetime] NOT NULL,
   [Value1] [decimal](18, 2) NULL,
   [Value2] [decimal](18, 2) NULL,
   [Value3] [decimal](18, 2) NULL,
   CONSTRAINT [PK_DemoUnitData] PRIMARY KEY CLUSTERED 
   (
       [UnitID] ASC,
       [Timestamp] ASC
   )
)
GO

ALTER TABLE [data].[DemoUnitData] WITH NOCHECK ADD CONSTRAINT [FK_DemoUnitData_Unit] FOREIGN KEY([UnitID])
REFERENCES [model].[Unit] ([ID])
GO

ALTER TABLE [data].[DemoUnitData] CHECK CONSTRAINT [FK_DemoUnitData_Unit]
GO

分區與此有關：

CREATE PARTITION SCHEME [DailyPartitionSchema] AS PARTITION [DailyPartitionFunction] ALL TO ([PRIMARY])

CREATE PARTITION FUNCTION [DailyPartitionFunction] (datetime) AS RANGE RIGHT
FOR VALUES (N'2017-07-25T00:00:00.000', N'2017-07-26T00:00:00.000', N'2017-07-27T00:00:00.000', ... )

服務質量

我認為通過增量重建/重組/更新，每晚都可以很好地維護索引和統計資訊。

這些是使用最頻繁的索引分區的目前索引統計資訊：

這些是使用最頻繁的分區的目前統計屬性：

問題

我對錶進行高頻簡單查詢。

SELECT [UnitID]
   ,[Timestamp]
   ,[Value1]
   ,[Value2]
   ,[Value3]
FROM [data].[DemoUnitData]
WHERE [UnitID] = 8877 AND [Timestamp] >= '2018-03-01' AND [Timestamp] < '2018-03-13'
OPTION (MAXDOP 1)

執行計劃如下所示：https ://www.brentozar.com/pastetheplan/?id=rJvI_4TtG

我的問題是這些查詢會產生大量的 I/O 操作，從而導致PAGEIOLATCH_SH等待瓶頸。

問題

我讀過PAGEIOLATCH_SH等待通常與未優化的索引有關。您對我如何減少 I/O 操作有什麼建議嗎？也許通過添加更好的索引？

---

答案 1 - 與來自 @S4V1N 的評論有關

發布的查詢計劃來自我在 SSMS 中執行的查詢。在您發表評論後，我對伺服器歷史進行了一些研究。從服務執行的準確查詢看起來有點不同（與實體框架相關）。

(@p__linq__0 bigint,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7)) 

SELECT 1 AS [C1], [Extent1] 
  .[Timestamp] AS [Timestamp], [Extent1] 
  .[Value1] AS [Value1], [Extent1] 
  .[Value2] AS [Value2], [Extent1] 
  .[Value3] AS [Value3]  
FROM [data].[DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] >= @p__linq__1)  
AND ([Extent1].[Timestamp] < @p__linq__2) OPTION (MAXDOP 1) 

此外，該計劃看起來不同：

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

或者

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

正如您在此處看到的，我們的數據庫性能幾乎不受此查詢的影響。

答案 2 - 與@Joe Obbish 的答案相關

為了測試解決方案，我用一個簡單的 SqlCommand 替換了 Entity Framework。結果是驚人的性能提升！

查詢計劃現在與 SSMS 中的相同，並且每次執行的邏輯讀寫下降到約 8 次。

整體 I/O 負載幾乎降至 0！

它還解釋了為什麼在將分區範圍從每月更改為每天后性能會大幅下降。分區消除的缺失導致要掃描的分區更多。

如果您能夠更改 ORM 生成的數據類型，您可能能夠減少PAGEIOLATCH_SH對此查詢的等待。表中的Timestamp列的數據類型為DATETIME但參數@p__linq__1和@p__linq__2數據類型為DATETIME2(7)。這種差異就是 ORM 查詢的查詢計劃比您發布的第一個具有硬編碼搜尋過濾器的查詢計劃複雜得多的原因。您也可以在 XML 中得到提示：

<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@p__linq__1],NULL,(22))">

照原樣，使用 ORM 查詢您無法消除任何分區。對於分區函式中定義的每個分區，您將至少獲得一些邏輯讀取，即使您只是在搜尋一天的數據。在每個分區中，您都會獲得一個索引搜尋，因此 SQL Server 不需要很長時間就可以移動到下一個分區，但也許所有的 IO 都在加起來。

我做了一個簡單的複制來確定。分區函式中定義了 11 個分區。對於此查詢：

DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime2(7) = '20180103';
DECLARE @p__linq__2 datetime2(7) = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] >= @p__linq__1)  
AND ([Extent1].[Timestamp] < @p__linq__2)
OPTION (MAXDOP 1) ;

這是 IO 的樣子：

表’DemoUnitData’。掃描計數 11，邏輯讀取 40

當我修復數據類型時：

DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime = '20180103';
DECLARE @p__linq__2 datetime = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] >= @p__linq__1)  
AND ([Extent1].[Timestamp] < @p__linq__2)
OPTION (MAXDOP 1) ;

IO 因分區消除而減少：

表’DemoUnitData’。掃描計數 2，邏輯讀取 8

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