www.tobuku.com
TABLE & INDEX PARTITIONING Tingkat:
Juni 2010 Oleh : Feri Djuandi
√ Pemula
Menengah
Mahir
Platform : Ms SQL Server 2008
1. PENDAHULUAN Apakah Partitioning Partitioning adalah sebuah teknik untuk menempatkan data-data table atau index yang berbentuk page ke dalam partisi-partisi yang terpisah di dalam sebuah atau beberapa filegroup. Fitur table dan index partitioning mulai dikenal pada SQL Server 2005 dan hanya ada pada edisi Enterprise dan Developer. Fitur ini tidak ada pada edisi Standard dan Workgroup.
Mengapa Partitioning Pada table yang berukuran sangat besar (jumlah barisnya sangat banyak) maka partitioning membantu untuk membagi sebuah data yang besar menjadi beberapa partisi yang lebih kecil sehingga mudah dikelola. Pembuatan partisi-partisi ini memungkinkan performa yang lebih baik melalui operasi parallel. Partitioning yang dimaksud pada uraian ini dikenal secara umum sebagai horizontal partitioning, yang maksudnya adalah pembagian partisi dilakukan pada baris-baris table. Hal ini berbeda dengan vertical partitioning yang pembagiannya dilakukan pada kolom-kolom table. HORIZONTAL PARTITIONING
VERTICAL PARTITIONING
Partition 1
Kolom
Kolom
Kolom
Baris 1…100
1…5
6…10
11...15
Partition 2 Baris 101…200 Partition 3 Baris 201…300 Di masa lalu sebelum SQL Server 2005 dirilis, horizontal partitioning sebetulnya telah diimplementasikan dengan membuat beberapa table yang berfungsi sebagai partisi dimana masing-masing berisi data berdasarkan pemilahan kriteria tertentu misalnya periode, area, produk, dan sebagainya. Kemudian table-table itu digabungkan melalui perintah SELECT dan UNIION untuk membentuk sebuah view. Itulah sebabnya teknik ini dikenal dengan partitioned view. -1-
www.tobuku.com
SalesJan 1 2
01/01/2010 01/02/2010
n
01/31/2010
YearlySales (view)
SalesFeb 1 2
02/01/2010 02/02/2010
n
02/28/2010
SalesDec 12/01/2010 12/02/2010
n
12/31/2010
01/01/2010 01/02/2010
n
12/28/2010
SELECT … FROM SalesJan UNION ALL SELECT … FROM SalesFeb UNION ALL … SELECT … FROM SalesDec
...
1 2
1 2
Cara ini menuntut pemeliharaan table-table secara cermat dan juga meningkatkan kerumitan di dalam pembuatan program aplikasinya karena data-data yang dimasukkan ke dalam masingmasing table harus dipastikan sesuai dengan kriteria penempatannya. Pada umumnya untuk memastikan data-data dimasukan ke dalam table yang tepat, perlu ditambahkan constraint CHECK yang akan memastikan terpenuhinya kriteria untuk setiap partisi. Pada SQL Server 2005 dan 2008, partitioning dapat diaplikasikan dengan jauh lebih mudah pada sebuah table tanpa perlu memperhatikan proses penempatan datanya karena hal itu sudah ditangani oleh SQL Server secara otomatis.
-2-
www.tobuku.com
LAB 1 Tujuan:
Melihat perbedaan antara table yang dipartisi dan yang tidak. Table-table tersebut sengaja dibuat tanpa index agar perbedaan akibat pengaruh full-scan semakin terasa.
Mempersiapkan Table Buat dua buah table, masing-masing sebuah untuk table yang tidak dipartisi dan sebuah table yang dipartisi. Kedua table ini akan diisi dengan baris-baris data yang banyak agar ukurannya cukup besar. 1. Buat table yang pertama. --SCRIPT #1 --CRATE TABLE T01 (NOT PARTITIONED) SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[T01]( [INT_1] [int] IDENTITY(1,1) NOT NULL, [INT_2] [int] NULL, [STRING_1] [varchar](500) NULL, [DATE_1] [datetime] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[T01] ADD DEFAULT ((0)) FOR [INT_2] GO
-3-
www.tobuku.com 2. Isi table tersebut dengan satu juta baris data yang isi kolom-kolomnya acak. --SCRIPT #2 --INSERT RANDOM INTEGER & DATETIME SET NOCOUNT ON DECLARE @i int SET @i=0 WHILE @i < 1000000 BEGIN INSERT dbo.T01(INT_2,STRING_1,DATE_1) SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))/1000000 AS [RandomNumber] ,CAST(NEWID() AS VARCHAR(100)) AS [RandomString] ,DATEADD(hh,ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))/100000,GETDATE()) AS [RandomDate] SET @i=@i+1 END
3. Buat table kedua yang strukturnya identik dengan table pertama namun bentuknya dipartisi. Nantinya performa kedua table tersebut akan diperbandingkan. Langkah-langkah untuk membuat partitioned table adalah sebagai berikut:
Membuat filegroup baru (opsional)
Membuat partition function
Membuat partition scheme
Membuat table
Penjelasannya akan diberikan kemudian.
--SCRIPT #3 --CREATE TABLE T02 (PARTITIONED) SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE PARTITION FUNCTION PF_MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (400,800,1200) GO CREATE PARTITION SCHEME PS_MyPartitionScheme AS PARTITION PF_MyPartitionRange ALL TO ([PRIMARY]) GO CREATE TABLE [dbo].[T02]( [INT_1] [int] IDENTITY(1,1) NOT NULL, [INT_2] [int] NULL, [STRING_1] [varchar](500) NULL, [DATE_1] [datetime] NULL ) ON PS_MyPartitionScheme([INT_2]) GO SET ANSI_PADDING OFF GO
-4-
www.tobuku.com ALTER TABLE [dbo].[T02] ADD GO
DEFAULT ((0)) FOR [INT_2]
4. Salin isi table yang pertama ke dalam table yang kedua agar keduanya betul-betul identik. --SCRIPT #4 --INSERT T02 FROM T01 SET IDENTITY_INSERT [dbo].[T02] ON GO INSERT [dbo].[T02] (INT_1, INT_2, STRING_1, DATE_1) SELECT INT_1, INT_2, STRING_1, DATE_1 FROM [dbo].[T01] GO SET IDENTITY_INSERT [dbo].[T02] OFF
-5-
www.tobuku.com
Melihat Partisi yang Terbentuk Setelah keduanya terbentuk, perhatikan perbedaan partisi kedua table tersebut. --SCRIPT #5 --VIEW PARTITIONS OF T01 & T02 SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('T01') GO SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('T02')
Tampak bahwa table pertama hanya memiliki sebuah partisi, sedangkan table ke-dua memiliki empat buah partisi. Quiz:
Jelaskan pengertian RANGE pada skrip #3
Mengapa terbentuk empat buah partisi?
-6-
www.tobuku.com
Melihat Perbedaan Performa (tanpa index) Jalankan query untuk melihat perbedaan performa kedua table ini. --SCRIPT #6 --COMPARING T01 & T02 SET STATISTICS IO ON GO SELECT COUNT(*) FROM dbo.T01 WHERE INT_2 BETWEEN 100 AND 650 GO SELECT COUNT(*) FROM dbo.T02 WHERE INT_2 BETWEEN 100 AND 650
Tampak bahwa table yang dipartisi memiliki logical reads yang lebih kecil. Quiz:
Apa artinya scan count? Apakah semakin besar nilainya semakin baik?
Apa artinya logical reads? Apakah semakin besar nilainya semakin baik?
Apa artinya physical reads? Apakah semakin besar nilainya semakin baik?
PETUNJUK: Scan count is the number of times that the table was accessed in order to get records Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query. Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache. Buffer hit ratio will be calculated based on these two kinds of read as the following formula: (logical reads – physical reads)/logical read * 100%. The high buffer hit ratio (if possible to near 100%) indicates good database performance on SQL Server level.
-7-
www.tobuku.com
2. MEMILIH TABLE YANG AKAN DIPARTISI Tidak semua table layak dibuat menjadi partitioned table. Kandidat table yang cocok untuk dibuatkan partisi adalah table-table yang berukuran sangat besar. Salah satu cara untuk memperkirakan ukuran table adalah melalui jumlah baris datanya, walaupun cara ini tidak akurat karena ukuran table juga dipengaruhi oleh ukuran setiap kolomnya. Berikut ini adalah skrip SQL yang menampilkan daftar table berikut jumlah barisnya diurutkan dari yang terbesar: --SCRIPT #7 --LIST BIG TABLES SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count FROM sys.dm_db_partition_stats st WHERE index_id < 2 ORDER BY st.row_count DESC
3. MEMBUAT PARTITIONED TABLE Pada bagian sebelumnya telah disebutkan secara singkat langkah-langkah untuk membuat partisi. Berikut ini adalah penjelasannya yang lebih lengkap.
Membuat Filegroup Tujuan langkah ini adalah untuk menentukan penempatan partisi-partisi itu secara fisik. Secara default sebuah database memiliki sebuah filegroup yang bernama PRIMARY. Jika partisipartisi yang akan dibuat ingin dipisahkan dari filegroup yang sudah ada, maka sebuah atau beberapa filegroup baru perlu dibuat. Langkah ini adalah opsional karena partisi-partisi dapat ditempatkan di dalam filegroup yang sudah ada. Partisi-partisi yang ditempatkan pada sebuah filegroup-filegroup yang berbeda Partition 1
Partition 2
Partition 3
Partition 4
FG1
FG2
FG3
FG4
Partisi-partisi yang ditempatkan pada sebuah filegroup X <400
400 <= X < 800
800 <= X < 1200
1200 <= X
PRIMARY
PRIMARY
PRIMARY
PRIMARY
Penempatan partisi-partisi pada filegroup-filegroup yang berbeda atau sama tergantung dari konfigurasi disk dari server database yang bersangkutan. Jika server tersebut memiliki beberapa disk yang secara fisik terpisah dengan masing-masing controller-nya, maka pembuatan beberapa filegroup adalah sebuah langkah yang logis karena hal itu memungkinkan pembacaan data pada disk secara paralel yang dapat meningkatkan performa. Namun jika server itu hanya memiliki sebuah disk controller, maka pembuatan filegroup-filegroup itu tidak akan memberikan perbedaan yang terlalu besar. -8-
www.tobuku.com Berikut ini adalah contoh skrip SQL untuk membuat tiga buah filegroup baru. ALTER DATABASE LAB2010 ADD FILEGROUP FG1 GO ALTER DATABASE LAB2010 ADD FILE ( NAME = 'LAB2010_FG1' , FILENAME = 'E:\SQLData\LAB2010_FG1.ndf' , SIZE = 20MB , FILEGROWTH = 20% ) TO FILEGROUP FG1 GO ALTER DATABASE LAB2010 ADD FILEGROUP FG2 GO ALTER DATABASE LAB2010 ADD FILE ( NAME = 'LAB2010_FG2' , FILENAME = 'E:\SQLData\LAB2010_FG2.ndf' , SIZE = 20MB , FILEGROWTH = 20% ) TO FILEGROUP FG2 GO ALTER DATABASE LAB2010 ADD FILEGROUP FG3 GO ALTER DATABASE LAB2010 ADD FILE ( NAME = 'LAB2010_FG3' , FILENAME = 'E:\SQLData\LAB2010_FG3.ndf' , SIZE = 20MB , FILEGROWTH = 20% ) TO FILEGROUP FG3 GO
-9-
www.tobuku.com
Membuat Partition Function Langkah ini dilakukan untuk menetapkan kriteria penempatan data di dalam masing-masing partisi. Sebagai contoh pada sebuah table transaksi, bisa saja pada partisi ke-1 berisi semua transaksi tahun 2007, partisi ke-2 tahun 2008 dan seterusnya. Kriteria ini nantinya akan diasosiasikan dengan sebuah kolom pada table yang bersangkutan. Sebuah partition function hanya bisa diasosiasikan dengan sebuah kolom pada table, tidak bisa lebih dari satu – oleh sebab itu pembuatan partisi harus disertai dengan pemilihan sebuah kolom secara cermat karena isi kolom ini akan menentukan horizontal partitioning dari table tersebut. Pada contoh perintah pembuatan partition function berikut ini tampak kriteris penempatan data ditentukan melalui range (400, 800, 1200) yang didahului oleh opsi LEFT. CREATE PARTITION FUNCTION PF_MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (400,800,1200)
Perbedaan antara opsi LEFT dan RIGHT dijelaskan dengan gambar di bawah ini.
RANGE LEFT X <=400
400 < X <= 800
800 < X <= 1200
1200 < X
Partition 1
Partition 2
Partition 3
Partition 4
X <400
400 <= X < 800
800 <= X < 1200
1200 <= X
Partition 1
Partition 2
Partition 3
Partition 4
RANGE RIGHT
Membuat Partition Scheme Partition scheme dibuat untuk menghubungkan partition function dengan filegroup. Berikut ini adalah contoh pembuatan partition scheme dengan partisi-partisi yang ditempatkan pada sebuah filegroup yang sudah ada (dalam hal ini adalah PRIMARY). CREATE PARTITION SCHEME PS_MyPartitionScheme AS PARTITION PF_MyPartitionRange ALL TO ([PRIMARY])
Contoh lainnya adalah pembuatan partition scheme dengan partisi-partisi yang ditempatkan pada filegroup-filegroup yang berbeda. CREATE PARTITION SCHEME PS_MyPartitionScheme AS PARTITION PF_MyPartitionRange ALL TO (FG1, FG2, FG3, FG4)
- 10 -
www.tobuku.com
Membuat Table Langkah terakhir adalah membuat partitioned table dengan menggunakan partition scheme yang sudah didefinisikan sebelumnya. Contoh: CREATE TABLE [dbo].[T02]( [INT_1] [int] IDENTITY(1,1) NOT NULL, [INT_2] [int] NULL, [STRING_1] [varchar](500) NULL, [DATE_1] [datetime] NULL ) ON PS_MyPartitionScheme([INT_2])
Nama partition scheme disebutkan pada klausa ON yang diikuti dengan nama kolom yang diasosiasikan dengan kriteria yang sesuai dengan partition function.
Catatan:
Partition function dan partition scheme yang sama dapat digunakan oleh table-table yang berbeda.
Untuk menambahkan atau menghapus partisi, jalankan perintah ALTER PARTITION FUNCTION dengan menyertakan klausa SPLIT atau MERGE.
- 11 -
www.tobuku.com
LAB 2 Tujuan:
Pengaruh pembuatan index pada partitioned table
Melihat Perbedaan Performa (dengan index) Ingat bahwa partisi pada table T02 dibuat berdasarkan kolom INT_2. 1. Buat index pada kolom INT_2 masing-masing pada table T01 dan T02 --SCRIPT #8 --CREATE ALIGNED INDEXES IN T01 & T02 CREATE INDEX IX_T01_INT_2 ON dbo.T01 (INT_2) GO CREATE INDEX IX_T02_INT_2 ON dbo.T02 (INT_2) GO
Jalankan kembali skrip #6 yang sama seperti pada latihan sebelumnya.
Tampak performanya meningkat pada kedua table. Dari hasil tersebut kelihatan tidak ada perbedaan yang signifikan antara table yang dipartisi dan tidak.
- 12 -
www.tobuku.com Ubah query di atas dengan menambahkan criteria lain. --SCRIPT #9 --COMPARINF T01 & T02 (MORE WHERE CRITERIA) SET STATISTICS IO ON GO SELECT COUNT(*) FROM dbo.T01 WHERE INT_2 BETWEEN 100 AND 650 AND MONTH(DATE_1)=10 GO SELECT COUNT(*) FROM dbo.T02 WHERE INT_2 BETWEEN 100 AND 650 AND MONTH(DATE_1)=10
Hasilnya akan tampak berbeda seperti di bawah ini.
Silakan jalankan lagi skrip #5 untuk melihat partisi yang terbentuk setelah pembuatan index.
Quiz:
Coba jelaskan apa arti dari hasil query di atas.
4. MENGENAL PARTITIONED INDEX Secara default sebuah index yang dibuat pada sebuah partitioned table akan menjadi sebuah partitioned index yang penempatan partisi-partisinya mengikuti table-nya. Sebagai contoh pada - 13 -
www.tobuku.com latihan sebelumnya tampak sebuah partitioned table yang menempati empat buah partisi memiliki index yang juga menempati empat buah partisi. Sebuah index pada partitioned table dapat saja dibuat menjadi index yang biasa (tidak terpartisi) walaupun hal tersebut mungkin tidak ada manfaatnya. Sebaliknya sebuah table yang tidak terpartisi dapat memiliki sebuah partitioned index jika dibutuhkan. Pada latihan berikut ini akan diperlihatkan cara membuat partitioned index.
LAB 3 Tujuan:
Melihat perbedan index biasa dan partitioned index dan pengaruhnya pada performa
1. Selain sebuah index yang sudah ada pada table T01, buat sebuah index lain dengan skrip SQL berikut ini. --SCRIPT #10 --CREATE NORMAL INDEX IN T01 CREATE INDEX IX_T01_INT_1A ON dbo.T01(INT_1) ON [PRIMARY]
2. Jalankan skrip SQL berikut ini untuk melihat penempatan index pada partisi. --SCRIPT #11 --VIEW PARTITION IN T01 SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('T01')
Tampak bahwa table ini mengandung dua buah index yang menempati sebuah partisi.
- 14 -
www.tobuku.com 3. Jalankan query berikut ini sebagai basis pengukuran. --SCRIPT #12 --BASE MEASUREMENT OF T01 SELECT COUNT(*) FROM dbo.T01 WHERE INT_2 BETWEEN 100 AND 450 AND INT_1 BETWEEN 50000 AND 200000
4. Hapus index yang terakhir dibuat, dan tambahkan sebuah index yang lain. Kali ini index yang akan dibuat akan menempati beberapa partisi. --SCRIPT #13 --DROP & RECREATE INDEX IN T01 DROP INDEX [IX_T01_INT_1A] ON [dbo].[T01] WITH ( ONLINE = OFF ) GO CREATE INDEX IX_T01_INT_1B ON dbo.T01(INT_1) ON PS_MyPartitionScheme(INT_1)
5. Jalankan kembali skrip #12 sebelumnya untuk membandingkan performa setelah penambahan index yang terakhir ini.
- 15 -
www.tobuku.com 6. Jalankan kembali skrip #11 sebelumnya untuk melihat penempatan index pada partisi. Tampak bahwa index yang terakhir menempati empat buah partisi sekalipun table-nya hanya menempati sebuah partisi.
Quiz:
Jelaskan perbedaan hasil dari index biasa dengan partitioned index pada percobaan ini. Pada kondisi apakah partitioned index akan berpengaruh meningkatkan performa?
- 16 -
