RiCo技術農場

Partition Table如果應用恰當，其實不難發現為什麼當初微軟會大力宣傳該技術(小弟認為還是有很大進步空間拉!)

這篇小弟將簡單測試Partition Table兩個應用方向。

 

改善select效能

1.先建立Partition function

這裡我簡單切partition function

create partition function PF_Nabk_FromDate(datetime2(0))

as

range right for values (

'1990-01-01',

'1991-01-01',

'1992-01-01',

'1993-01-01',

'1994-01-01',

'1995-01-01',

'1996-01-01',

'1997-01-01',

'1998-01-01')

Go

2.建立partition schema

(當然你不一定都要放在primary group，存放不同filegroup效能會更好)

create partition scheme PS_NABK

as

partition PF_Nabk_FromDate

all to ([primary])

go

3.建立partition table

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[PTNABK](

.....)

 

insert data

查看partition table中每個partition資料筆數及邊界值(因為測試所以沒詳細切割)

Select partition table

執行計畫

Select none partition table

執行計畫

結果一覽表

Table Name	筆數	CPU時間(ms)	查詢時間(ms)	實體IO
Dbo.nabk	5111058	4036	121670	3218
Dbo.ptnabk	5111058	2579	11349	478

 

改善資料維護作業效能

這是小弟朋友遇到的問題，SQL2008環境有個Table快4千萬筆(1~12月分資料)

同時因為一些必要的SQL該Table總共有6個Index(還好沒破10~XD)，雖然Index可以增加查詢效能

但也相對的影響Insert 、update和 delete效能(所以Oracle發展reverse index來改善，但SQLSERVER似乎沒有)

由於每個月底都要維護該Table資料，要先insert當月新資料就得先Delete Table舊資料

而每個月新資料平均約5百萬筆左右(舊資料也破5百萬筆以上)

所以整個時間約3~4小時完成(算他很有耐心這樣搞~XD)

當小弟建議他使用Partition Table後，從此以後在也看不到他MSN上掛者月底加班鬱卒表情和字樣了(30分鐘內搞定)

小弟就來大概模擬當時資料維護情況

一般作法:

先刪除舊資料(假設條件from_date >='1998-01-01';)

新增資料(這裡應該塞原本Table，不過模擬所以我先塞到中繼Table，方便後續測試~我太懶了)

Partition table做法:

先清空分區(partition 10)

Switch the data from Partition table to non partition table (不到一秒時間就清空資料)

ALTER TABLE dbo.PTNABK SWITCH PARTITION 10 TO TEMPNABK ;

確認分區10筆數

將資料先新增至中繼Table(之前我們已執行過，約花307550ms)

insert完後將中繼TABLE資料轉換至partition table

Switch the data to Partition table(不到一秒時間完成資料轉換)

確認最後筆數

結果一覽表

類型	CPU時間(ms)	執行時間(ms)	實體IO
一般作法	38844+59094	527813+307550	133584+124
Partition table作法	秒殺	秒殺	無

當然有時隨者資料的更新和累積，可能就要再繼續切Partition或合併Partition

或者變更partition_scheme和filegroup...等

微軟也幫DBA想到了這些實用的功能，更多的資訊可以參考

ALTER PARTITION FUNCTION

ALTER PARTITION SCHEME

 

SQLSERVER Partition Table就告一段落了，也在這裡預祝大家中秋節快樂~^^

也希望颱風不要來(88水災夠讓人傷心了)