在非常大的表上更快地進行查詢和 BCP
這個問題在某種程度上是這個問題的延續： Huge data and performance in SQL Server，但不同之處足以值得提出自己的問題。為了便於閱讀，我將複製並粘貼該問題中的一些文本。
我編寫了一個帶有 SQL Server 後端的應用程序，它收集和儲存大量記錄。我計算過，在高峰期，平均記錄量約為每天 3-40 億條（執行 20 小時）。
我的應用程序接收數據，將其分成約 10 萬條記錄的批次，並創建 BCP 就緒的暫存文件，將它們移動到伺服器，SQL Server 使用 BCP 將數據移動到生產表中。使用該解決方案，我已成功將 52 億行移動到我的生產表中，幾乎沒有 BCP 文件的積壓。我讓 BCP 作為 SQL 代理作業持續執行。
我的生產表具有以下架構：
CREATE TABLE [dbo].[SignalValues]( [Timestamp] [datetime] NOT NULL, [SignalId] [uniqueidentifier] NOT NULL, [SignalValue] [nvarchar](4000) NULL, [SignalValueId] [uniqueidentifier] NOT NULL )由於記錄量大，表
[Timestamp]每半小時從2016-01-20 21:00:00.000到進行一次分區2016-06-15 08:00:00.000。（約 11k 總分區）。我的問題是雙重的：
- 以任何方式查詢表都需要很長時間。
- 在對錶執行查詢時，BCP 處理基本上停止。就像所有處理能力都用於查詢並且我得到了文件積壓。
如何加快查詢性能？使用者大多會基於
[Timestamp]和進行查詢SignalId。我在上面創建了一個非聚集索引，[Timestamp]但它似乎並沒有加快速度。似乎我應用的索引越多，BCP 插入的速度就越慢，這會創建文件積壓。有什麼索引建議嗎？我對分區的假設是它們在內部充當單獨的表，因此如果我要查詢，它應該只鎖定查詢所需的一個分區，而讓其他分區保持解鎖狀態
[Timestamp]。2016-01-25 22:01:00.000我的假設不正確嗎？上面 2) 中的行為似乎如此。如何強制 SQL Server 在SELECT查詢期間不鎖定（WITH (READPAST)並且WITH (NOLOCK)沒有幫助）？我應該注意，此表中的行將永遠不會被更新或刪除。此外，可能會從過去的某個時間插入數據，這取決於應用程序為我的應用程序提供的數據集作為其行的時間戳。不幸的是，這否定了分區切換的任何可能性，除非我要做一些瘋狂的魔法。
每個分區的平均行數約為 7000 萬。
根據我之前的問題（上）的回饋，我能夠大大增強我的伺服器。以下是我的伺服器的系統資訊：
另外，我根據回饋配置了 SQL Server，如下所示：
- 所有與數據庫相關的文件都連接到通過光纖通道連接到系統的 SAN
- 我的數據庫的數據文件單獨在它自己的驅動器上——一個由 4 個旋轉磁碟組成的 RAID 10 陣列
tempdb由 6 個文件（邏輯處理器數量的一半）組成，每個文件大小為 512mb，全部位於一個驅動器上 - 一個 RAID 0 陣列，由 2 個旋轉磁碟組成- 所有事務日誌都存在於它們自己的驅動器上——一個由 2 個旋轉磁碟組成的 RAID 1 陣列。
該表沒有聚集索引。我之前有一個聚集唯一索引（PK），但唯一性約束導致插入需要 10 分鐘才能插入一個有 800m 行的表中的 100k 條記錄。我的理解是，聚集索引會根據標題在內部創建唯一性約束，“你可以在具有重複值的列上創建聚集索引嗎？” 從這個頁面：
不過我可能完全錯了。
讓我知道我是否遺漏了什麼。
這是一個很長的鏡頭，但是當您執行 SQL 2014 Enterprise 時，我將對此表使用聚集列儲存索引進行測試，看看這是否會提高您的性能。
特別是考慮到您僅將表用於選擇和批量插入 - 沒有更新和刪除。
您將獲得額外的優勢，即減輕旋轉磁碟的一些負載，並將其更多地移向您似乎擁有相當數量的 CPU / 記憶體。
很難找到在數據入口（每秒接受 +50k 行）和臨時查詢任意 EAV 時間序列（
timestmap,signal_id,signal_value）方面都表現出色的東西。我會嘗試聚集列儲存。聚集列儲存將利用段消除，timestamp並且聚集列儲存還具有更好的批量插入並發特性。確保讀取SQL Server 集群列儲存 Tuple Mover並獲得足夠高的 BCP 速率來創建直接壓縮的段。如果沒有，staging, bcp+rebuild in staging 然後分區切換是必須的。使用您擁有的佈局，我更希望在
timestamp. 就像現在一樣，您最好的希望是利用分區消除。有很多方法可能會失敗（為什麼分區消除不起作用？），即使它起作用，它仍然會導致並發問題。特別是因為大多數時候使用者查詢關注最近的數據，這會導致對 BCP 插入的阻塞。我會確保我的 BPC 不會升級到表 X 鎖。數據載入性能指南是強制性閱讀，但請注意，當涉及列儲存時，白皮書已經過時，因此僅適用於目前結構。請特別注意“批量載入分區表”一章。
我會考慮更粗的分區粒度，可能每天 1 個分區。分區過多會帶來巨大的執行時成本。
並且，與任何性能問題一樣，測量.
在考慮激進的不同方法時，我不會害羞。例如， Cassandra可以以非常高的速率輸入數據，並具有“在問題上投入更多硬體”橫向擴展解決方案的優勢。可查詢性不是 Cassandra 的強項，但是一旦您購買了該生態系統，就會有 Spark+Hive+ORC/Parquet，它們可以非常快速地查詢數據。