對大表進行分區並沒有提高性能，為什麼？

在 SQL Server 2014 中，我每週對我的一個大表進行分區，並定義了一個滑動視窗方案，將最早一周的數據切換到存檔數據庫並為下一周創建一個新分區。

這是結果：

這適用於 AVL 系統（車輛跟踪）。我在PositionDate ( datetime ) 上進行了分區。我們所有的查詢在 WHERE 子句中都有PositionDate，在許多情況下，我們在 WHERE 子句中也有VehicleId。所以我在VehicleId ( int ) 上創建了兩個對齊的索引：

• 一個*(PositionDate,VehicleId)* ;
• 一個就在*(VehicleId)*上。

但是在 WHERE 子句中包含VehicleId的每個查詢中，都沒有使用這兩個非聚集索引（根據查詢計劃）。

我現在有性能問題。

我比較了分區表和非分區表之間的查詢計劃，如下所示：

Select * from MyNonPart_Table Where PositionDate between '2016-05-01' AND '2016-06-01'

Select * from PartitinedTable Where PositionDate between '2016-05-01' AND '2016-06-01'

令人驚嘆的是，我看到第一個查詢花費了 30%，而第二個查詢花費了 70%。

我有一個文件組，其中有兩個文件用於分區表。

我的問題：

1. 每個分區中的行數是否超過分區的最佳行數？如果我按天分區並保持最近 60 天的實時數據，這會幫助我提高性能嗎？
2. 我的非聚集索引是否定義良好或者我應該刪除它們？我們在所有查詢的 WHERE 子句中都有PositionDate ，在許多查詢中都有**VehicleId。
3. 我是否在這種情況下濫用了分區？如果我在我的非分區表上定義良好的索引並將最舊的數據（超過 2 個月大）移動到存檔表，這對我的情況是否有效？

我的索引的 DDL：

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_Primary] PRIMARY KEY CLUSTERED 
(
   [PositionDate] ASC,
   [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)
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-VehicleId] ON [dbo].[MyTable]
(
   [VehicleId] 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)
GO

CREATE NONCLUSTERED INDEX [NCIX_VehicleId_PositionDate] ON [dbo].[MyTable]
(
   [VehicleId] ASC,
   [PositionDate] 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)
GO

這是一個範例，我的查詢位於接收日期時間類型參數的 SP 中。

您通常會產生一種錯覺——與標準索引相比，分區會給您的查詢帶來顯著的性能提升。

不是這種情況。使用索引過濾和使用分區過濾幾乎沒有區別。

分區不是為了讓您的查詢更快，而是為了允許快速刪除 - 通過用空表版本換出一個分區。這允許“截斷”樣式的性能來刪除表格的某些部分——這很重要。非常重要的是，如果您曾經經歷過刪除數十 GB 數據可能需要很長時間。

也有分區可以提供幫助的插入場景。

但是對於查詢 - 不，分區不會比正確的索引更好。事實上，它會更慢 - 因為那裡的工作更複雜（管理要訪問的分區）。

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