RiCo技術農場

前幾天朋友問使用Partition Table就一定能改善效能嗎?

我的回答是查詢效能不保證能改善，

但資料維護保證可改善工作效率(參考 [SQL SERVER][Performance]善用Partition Table#2測試)

至於查詢效能我下面做個簡單實驗

 

兩個資料表資訊:

SELECT
sch.name + '.' + so.name AS [Table],
ss.name AS [Statistic],
sp.last_updated AS [Stats Last Updated],
sp.rows AS [Rows],
sp.rows_sampled AS [Rows Sampled],
sp.modification_counter AS [Row Modifications]
FROM sys.stats AS ss
INNER JOIN sys.objects AS so  ON ss.[object_id] = so.[object_id]
INNER JOIN sys.schemas AS sch ON so.[schema_id] = sch.[schema_id]
OUTER APPLY sys.dm_db_stats_properties(so.[object_id], ss.stats_id) AS sp
WHERE so.[object_id] IN (OBJECT_ID(N'NoPT_SalesOrderHeader'), OBJECT_ID(N'PT_SalesOrderHeader'))
AND ss.stats_id = 1;

 

 

partition table info:

SELECT $partition.[PFOrderDateRange]([o].[OrderDate])
         AS [Partition Number]
   , MIN([o].[OrderDate]) AS [Min Order Date]
   , MAX([o].[OrderDate]) AS [Max Order Date]
   , COUNT(*) AS [Rows In Partition]
FROM PT_SalesOrderHeader AS [o]
GROUP BY $partition.[PFOrderDateRange]([o].[OrderDate])
ORDER BY [Partition Number];

 

 

查詢非partition table:

select [SalesPersonID], SUM([TotalDue])
from NoPT_SalesOrderHeader
where [OrderDate] BETWEEN '2012-12-01' AND '2013-01-31'
group by [SalesPersonID];

 

 

成本最高的運算子為clustered index scan ，I/O為54021(因為沒有正確索引)。

 

查詢partition table:

select [SalesPersonID], SUM([TotalDue])
from PT_SalesOrderHeader
where [OrderDate] BETWEEN '2012-12-01' AND '2013-01-31'
group by [SalesPersonID];

透過partition table成本最高為clustered index seek，因為只讀取必要資料分區(該查詢跨2個分區)所以I/O讀取相當低

但須注意如果查詢跨分區越多的話，查詢效能也會降低。

 

現在來建立相關索引並看看查詢效能有什麼變化

CREATE INDEX [idx1]
ON NoPT_SalesOrderHeader ([OrderDate]) INCLUDE ([SalesPersonID], [TotalDue]);

CREATE INDEX idx1
ON PT_SalesOrderHeader([SalesPersonID]) INCLUDE ([TotalDue]);

 

查詢非partition table:

 

如預期有正確索引即可大大降低I/O並採用正確運算子。

 

查詢partition table:

 

建立partition index後，可以看到I/O讀取稍稍高於非partition table，scan次數也多一次

主要是因為該查詢跨了2個分區，如果partition index又沒有對齊的話，

查詢效能會更差(所以索引務必要對齊)。以這案例來看，

查詢跨多分區可能效能會低於非partition table(有正確索引前提下)，

所以針對查詢設計正確索引才是改善查詢效能最重要關鍵。

 

 

 

 

參考

[SQL SERVER][Performance]善用Partition Table#2測試

[SQL SERVER][Performance]善用Partition Table#1簡介

提升查詢效能的資料分割設計