Compression Database
Sql
Salah satu dari banyak fitur baru yang diperkenalkan kembali
di SQL Server 2008 adalah Data Compression. Kompresi pada tingkat baris atau
halaman memberikan peluang untuk menghemat ruang disk, dengan pertukaran yang
membutuhkan CPU lebih banyak untuk mengompresi dan mengekompresi data. Ini
sering diperdebatkan bahwa sebagian besar sistem terikat IO, bukan terikat CPU,
jadi trade off sepadan. Tangkapan? Anda harus menggunakan Edisi Perusahaan
untuk menggunakan Kompresi Data. Dengan rilis SQL Server 2016 SP1, itu telah
berubah! Jika Anda menjalankan Edisi Standar SQL Server 2016 SP1 dan yang lebih
tinggi, Anda sekarang dapat menggunakan Kompresi Data. Ada juga fungsi bawaan
baru untuk kompresi, COMPRESS (dan DECOMPRESS lawannya). Kompresi Data tidak
berfungsi pada data offline, jadi jika Anda memiliki kolom seperti NVARCHAR
(MAX) di tabel Anda dengan nilai yang biasanya berukuran lebih dari 8000 byte,
data itu tidak akan dikompresi (terima kasih Adam Machanic telah
menjelaskannya).
Fungsi COMPRESS memecahkan masalah , dan memperkecil ukuran
data hingga berukuran 2GB. Selain itu, sementara saya berpendapat bahwa fungsi
ini seharusnya hanya digunakan untuk data besar, offline, saya pikir
membandingkannya langsung dengan kompresi row and page adalah percobaan yang bermanfaat
untuk dba atau programer yang mempunyai database yang besar.
Setup
Untuk data pengujian,
saya sedang mengerjakan dengan script punya Aaron Bertrand yang telah gunakan sebelumnya, tetapi saya telah
membuat beberapa penyesuaian. Saya membuat database terpisah untuk pengujian
tetapi Anda bisa menggunakan tempdb atau database sampel lain, dan kemudian
saya mulai dengan tabel Customer yang memiliki tiga kolom NVARCHAR. Saya
mempertimbangkan untuk membuat kolom yang lebih besar dan mengisinya dengan
string huruf berulang, tetapi menggunakan teks yang dapat dibaca memberikan
sampel yang lebih realistis dan dengan demikian memberikan akurasi yang lebih tepat.
Catatan: Jika Anda tertarik untuk menerapkan kompresi dan
ingin tahu bagaimana itu akan memengaruhi penyimpanan dan kinerja di lingkungan
Anda, SAYA SANGAT MENYARANKAN BAHWA ANDA MENGUJI ITU Di DATABSE BACK UP DULU.
Saya memberi Anda metodologi dengan data sampel; menerapkan ini seharusnya
tidak melibatkan pekerjaan tambahan tetapi sebagai mana pun kita harus test
dulu dilocal manusia tidak selalu benar(hanya allah yang memiliki kebenaran).
Anda akan perhatikan di bawah bahwa setelah membuat database
kami mengaktifkan Query Store. Mengapa membuat tabel terpisah untuk mencoba dan
melacak metrik kinerja kami saat kami dapat menggunakan fungsionalitas bawaan
untuk SQL Server ?!
USE [master];
GO
CREATE DATABASE [CustomerDB]
CONTAINMENT = NONE
ON PRIMARY
(
NAME =
N'CustomerDB', FILENAME = N'C:\Databases\CustomerDB.mdf' ,
SIZE = 4096MB , MAXSIZE
= UNLIMITED, FILEGROWTH = 65536KB
)
LOG ON
(
NAME =
N'CustomerDB_log', FILENAME = N'C:\Databases\CustomerDB_log.ldf' ,
SIZE = 2048MB ,
MAXSIZE = UNLIMITED , FILEGROWTH = 65536KB
);
GO
ALTER DATABASE [CustomerDB] SET COMPATIBILITY_LEVEL = 130;
GO
ALTER DATABASE [CustomerDB] SET RECOVERY SIMPLE;
GO
ALTER DATABASE [CustomerDB] SET QUERY_STORE = ON;
GO
ALTER DATABASE [CustomerDB] SET QUERY_STORE
(
OPERATION_MODE =
READ_WRITE,
CLEANUP_POLICY =
(STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS
= 60,
INTERVAL_LENGTH_MINUTES = 5,
MAX_STORAGE_SIZE_MB
= 256,
QUERY_CAPTURE_MODE =
ALL,
SIZE_BASED_CLEANUP_MODE = AUTO,
MAX_PLANS_PER_QUERY
= 200
);
GO
Sekarang kita akan mengatur beberapa hal di dalam database
kita
USE [CustomerDB];
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
-- note: I removed the unique index on [Email] that was in
Aaron's version
CREATE TABLE [dbo].[Customers]
(
[CustomerID]
[int] NOT NULL,
[FirstName]
[nvarchar](64) NOT NULL,
[LastName]
[nvarchar](64) NOT NULL,
[EMail]
[nvarchar](320) NOT NULL,
[Active]
[bit] NOT NULL DEFAULT 1,
[Created]
[datetime] NOT NULL DEFAULT SYSDATETIME(),
[Updated]
[datetime] NULL,
CONSTRAINT
[PK_Customers] PRIMARY KEY CLUSTERED ([CustomerID])
);
GO
CREATE NONCLUSTERED INDEX [Active_Customers]
ON [dbo].[Customers]([FirstName],[LastName],[EMail])
WHERE ([Active]=1);
GO
CREATE NONCLUSTERED INDEX [PhoneBook_Customers]
ON [dbo].[Customers]([LastName],[FirstName])
INCLUDE ([EMail]);
Dengan tabel dibuat, kita akan menambahkan beberapa data, kit
tambahkan 5 juta record, bukan 1 juta. Ini membutuhkan waktu sekitar delapan
menit untuk berjalan di laptop saya
INSERT dbo.Customers WITH (TABLOCKX)
(CustomerID,
FirstName, LastName, EMail, [Active])
SELECT rn = ROW_NUMBER() OVER (ORDER BY n),
fn, ln, em, a
FROM
(
SELECT TOP
(5000000) fn, ln, em, a = MAX(a), n = MAX(NEWID())
FROM
(
SELECT fn, ln,
em, a, r = ROW_NUMBER() OVER (PARTITION BY em ORDER BY em)
FROM
(
SELECT TOP (20000000)
fn =
LEFT(o.name, 64),
ln =
LEFT(c.name, 64),
em =
LEFT(o.name, LEN(c.name)%5+1) + '.'
+
LEFT(c.name, LEN(o.name)%5+2) + '@'
+
RIGHT(c.name, LEN(o.name + c.name)%12 + 1)
+
LEFT(RTRIM(CHECKSUM(NEWID())),3) + '.com',
a = CASE WHEN c.name LIKE '%y%' THEN 0 ELSE 1
END
FROM
sys.all_objects AS o CROSS JOIN sys.all_columns AS c
ORDER BY
NEWID()
) AS x
) AS y WHERE r = 1
GROUP BY fn, ln, em
ORDER BY n
) AS z
ORDER BY rn;
GO
Sekarang kita kan buat 3 table lagi:
satu untuk kompresi row,
satu untuk kompresi page, dan satu untuk COMPRESS function. Perhatikan bahwa
dengan fungsi COMPRESS, Anda harus membuat kolom sebagai tipe data VARBINARY.
Akibatnya, tidak ada nonclustered indexes
pada tabel (karena Anda tidak dapat membuat index keys pada kolom varbinary).
CREATE TABLE [dbo].[Customers_Page]
(
[CustomerID]
[int] NOT NULL,
[FirstName]
[nvarchar](64) NOT NULL,
[LastName]
[nvarchar](64) NOT NULL,
[EMail]
[nvarchar](320) NOT NULL,
[Active]
[bit] NOT NULL DEFAULT 1,
[Created]
[datetime] NOT NULL DEFAULT SYSDATETIME(),
[Updated]
[datetime] NULL,
CONSTRAINT
[PK_Customers_Page] PRIMARY KEY CLUSTERED ([CustomerID])
);
GO
CREATE NONCLUSTERED INDEX [Active_Customers_Page]
ON [dbo].[Customers_Page]([FirstName],[LastName],[EMail])
WHERE ([Active]=1);
GO
CREATE NONCLUSTERED INDEX [PhoneBook_Customers_Page]
ON [dbo].[Customers_Page]([LastName],[FirstName])
INCLUDE ([EMail]);
GO
CREATE TABLE [dbo].[Customers_Row]
(
[CustomerID]
[int] NOT NULL,
[FirstName]
[nvarchar](64) NOT NULL,
[LastName]
[nvarchar](64) NOT NULL,
[EMail]
[nvarchar](320) NOT NULL,
[Active]
[bit] NOT NULL DEFAULT 1,
[Created]
[datetime] NOT NULL DEFAULT SYSDATETIME(),
[Updated]
[datetime] NULL,
CONSTRAINT
[PK_Customers_Row] PRIMARY KEY CLUSTERED ([CustomerID])
);
GO
CREATE NONCLUSTERED INDEX [Active_Customers_Row]
ON [dbo].[Customers_Row]([FirstName],[LastName],[EMail])
WHERE ([Active]=1);
GO
CREATE NONCLUSTERED INDEX [PhoneBook_Customers_Row]
ON [dbo].[Customers_Row]([LastName],[FirstName])
INCLUDE ([EMail]);
GO
CREATE TABLE [dbo].[Customers_Compress]
(
[CustomerID]
[int] NOT NULL,
[FirstName]
[varbinary](max) NOT NULL,
[LastName]
[varbinary](max) NOT NULL,
[EMail]
[varbinary](max) NOT NULL,
[Active]
[bit] NOT NULL DEFAULT 1,
[Created]
[datetime] NOT NULL DEFAULT SYSDATETIME(),
[Updated]
[datetime] NULL,
CONSTRAINT
[PK_Customers_Compress] PRIMARY KEY CLUSTERED ([CustomerID])
);
Go
Selanjutnya kita akan menyalin data dari [dbo]. [Customers]
ke tiga tabel lainnya. Ini adalah INSERT langsung untuk halaman dan baris tabel
kami dan membutuhkan sekitar dua hingga tiga menit untuk setiap INSERT, tetapi
ada masalah skalabilitas dengan fungsi COMPRESS: mencoba memasukkan 5 juta
baris dalam sekali gerakan hanya tidak masuk akal. Skrip di bawah ini
menyisipkan baris dalam batch 50.000, dan hanya menyisipkan 1 juta baris, bukan
5 juta. Saya tahu, itu berarti kita tidak benar-benar membandingkan apples-to-apples
di sini, tapi saya setuju dengan itu. Memasukkan 1 juta baris membutuhkan waktu
10 menit pada mesin saya; merasa bebas untuk mengubah skrip dan memasukkan 5
juta baris untuk pengujian Anda sendiri.
INSERT dbo.Customers_Page WITH (TABLOCKX)
(CustomerID,
FirstName, LastName, EMail, [Active])
SELECT CustomerID, FirstName, LastName, EMail, [Active]
FROM dbo.Liat oke;
GO
INSERT dbo.Customers_Row WITH (TABLOCKX)
(CustomerID,
FirstName, LastName, EMail, [Active])
SELECT CustomerID, FirstName, LastName, EMail, [Active]
FROM dbo.Customers;
GO
SET NOCOUNT ON
DECLARE @StartID INT = 1
DECLARE @EndID INT = 50000
DECLARE @Increment INT = 50000
DECLARE @IDMax INT = 1000000
WHILE @StartID < @IDMax
BEGIN
INSERT
dbo.Customers_Compress WITH (TABLOCKX) (CustomerID, FirstName, LastName, EMail,
[Active])
SELECT
top 100000 CustomerID, COMPRESS(FirstName), COMPRESS(LastName),
COMPRESS(EMail), [Active]
FROM
dbo.Customers
WHERE
[CustomerID] BETWEEN @StartID AND @EndID;
SET
@StartID = @StartID + @Increment;
SET
@EndID = @EndID + @Increment;
END
Dengan semua tabel kami yang terisi, kami dapat melakukan
pengecekan ukuran. Pada titik ini, kami belum menerapkan kompresi ROW atau
PAGE, tetapi fungsi COMPRESS telah digunakan:
SELECT [o].[name], [i].[index_id], [i].[name],
[p].[rows],
(8*SUM([au].[used_pages]))/1024 AS [IndexSize(MB)],
[p].[data_compression_desc]
FROM [sys].[allocation_units] [au]
JOIN [sys].[partitions] [p]
ON
[au].[container_id] = [p].[partition_id]
JOIN [sys].[objects] [o]
ON
[p].[object_id] = [o].[object_id]
JOIN [sys].[indexes] [i]
ON
[p].[object_id] = [i].[object_id] AND [p].[index_id] = [i].[index_id]
WHERE [o].[is_ms_shipped] = 0
GROUP BY [o].[name], [i].[index_id], [i].[name], [p].[rows],
[p].[data_compression_desc]
ORDER BY [o].[name], [i].[index_id];
Seperti yang diharapkan, semua tabel kecuali Customers_Compress
berukuran hampir sama. Sekarang kita
akan membangun kembali indeks pada semua tabel, menerapkan kompresi baris dan
halaman pada Customers_Row dan Customers_Page, masing-masing
ALTER INDEX ALL ON dbo.Customers REBUILD;
GO
ALTER INDEX ALL ON dbo.Customers_Page REBUILD WITH
(DATA_COMPRESSION = PAGE);
GO
ALTER INDEX ALL ON dbo.Customers_Row REBUILD WITH
(DATA_COMPRESSION = ROW);
GO
ALTER INDEX ALL ON dbo.Customers_Compress REBUILD;
Go
Setelah compres data
kita cek size table, sekarang kita bisa lihat disk spacenya:
SELECT [o].[name], [i].[index_id], [i].[name],
[p].[rows],
(8*SUM([au].[used_pages]))/1024 AS [IndexSize(MB)],
[p].[data_compression_desc]
FROM [sys].[allocation_units] [au]
JOIN [sys].[partitions] [p]
ON
[au].[container_id] = [p].[partition_id]
JOIN [sys].[objects] [o]
ON
[p].[object_id] = [o].[object_id]
JOIN [sys].[indexes] [i]
ON
[p].[object_id] = [i].[object_id] AND [p].[index_id] = [i].[index_id]
WHERE [o].[is_ms_shipped] = 0
GROUP BY [o].[name], [i].[index_id], [i].[name], [p].[rows],
[p].[data_compression_desc]
ORDER BY [i].[index_id], [IndexSize(MB)] DESC;
Seperti yang diharapkan, kompresi row dan page secara
signifikan mengurangi ukuran tabel dan indeksnya. Fungsi COMPRESS menghemat
ruang paling banyak bagi kami - indeks berkerumun adalah seperempat ukuran
tabel asli. MEMERIKSA KINERJA QUERY Sebelum kami menguji kinerja kueri,
perhatikan bahwa kami dapat menggunakan Query Store untuk melihat kinerja
INSERT dan REBUILD:
SELECT [q].[query_id], [qt].[query_sql_text],
SUM([rs].[count_executions]) [ExecutionCount],
AVG([rs].[avg_duration])/1000 [AvgDuration_ms],
AVG([rs].[avg_cpu_time]) [AvgCPU],
AVG([rs].[avg_logical_io_reads]) [AvgLogicalReads],
AVG([rs].[avg_physical_io_reads]) [AvgPhysicalReads]
FROM [sys].[query_store_query] [q]
JOIN [sys].[query_store_query_text] [qt]
ON
[q].[query_text_id] = [qt].[query_text_id]
LEFT OUTER JOIN [sys].[objects] [o]
ON
[q].[object_id] = [o].[object_id]
JOIN [sys].[query_store_plan] [p]
ON
[q].[query_id] = [p].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs]
ON
[p].[plan_id] = [rs].[plan_id]
WHERE [qt].[query_sql_text] LIKE '%INSERT%'
OR
[qt].[query_sql_text] LIKE '%ALTER%'
GROUP BY [q].[query_id], [q].[object_id], [o].[name],
[qt].[query_sql_text], [rs].[plan_id]
ORDER BY [q].[query_id];
INSERT dan REBUILD metrik kinerja Meskipun data ini menarik,
saya lebih penasaran tentang bagaimana kompresi mempengaruhi kueri SELECT
harian saya. Saya memiliki satu set tiga prosedur tersimpan yang masing-masing
memiliki satu permintaan SELECT, sehingga setiap indeks digunakan. Saya membuat
prosedur ini untuk setiap tabel, dan kemudian menulis skrip untuk menarik nilai
untuk nama depan dan belakang yang akan digunakan untuk pengujian. Setelah kami memiliki prosedur tersimpan yang
dibuat, kami dapat menjalankan skrip di bawah ini untuk memanggil mereka.
Mulailah ini dan kemudian tunggu beberapa menit ...
SET NOCOUNT ON;
GO
DECLARE @RowNum INT = 1;
DECLARE @Round INT = 1;
DECLARE @ID INT = 1;
DECLARE @FN NVARCHAR(64);
DECLARE @LN NVARCHAR(64);
DECLARE @SQLstring NVARCHAR(MAX);
DROP TABLE IF EXISTS #FirstNames, #LastNames;
SELECT DISTINCT
[FirstName],
DENSE_RANK()
OVER (ORDER BY [FirstName]) AS RowNum
INTO #FirstNames
FROM [dbo].[Customers]
SELECT DISTINCT
[LastName],
DENSE_RANK()
OVER (ORDER BY [LastName]) AS RowNum
INTO #LastNames
FROM [dbo].[Customers]
WHILE 1=1
BEGIN
SELECT
@FN = (
SELECT
[FirstName]
FROM
#FirstNames
WHERE
RowNum = @RowNum)
SELECT
@LN = (
SELECT
[LastName]
FROM
#LastNames
WHERE
RowNum = @RowNum)
SET @FN
= SUBSTRING(@FN, 1, 5) + '%'
SET @LN
= SUBSTRING(@LN, 1, 5) + '%'
EXEC
[dbo].[usp_FindActiveCustomer_C] @FN;
EXEC
[dbo].[usp_FindAnyCustomer_C] @LN;
EXEC
[dbo].[usp_FindSpecificCustomer_C] @ID;
EXEC
[dbo].[usp_FindActiveCustomer_P] @FN;
EXEC
[dbo].[usp_FindAnyCustomer_P] @LN;
EXEC
[dbo].[usp_FindSpecificCustomer_P] @ID;
EXEC
[dbo].[usp_FindActiveCustomer_R] @FN;
EXEC
[dbo].[usp_FindAnyCustomer_R] @LN;
EXEC
[dbo].[usp_FindSpecificCustomer_R] @ID;
EXEC
[dbo].[usp_FindActiveCustomer_CS] @FN;
EXEC
[dbo].[usp_FindAnyCustomer_CS] @LN;
EXEC
[dbo].[usp_FindSpecificCustomer_CS] @ID;
IF @ID
< 5000000
BEGIN
SET
@ID = @ID + @Round
END
ELSE
BEGIN
SET @ID = 2
END
IF
@Round < 26
BEGIN
SET
@Round = @Round + 1
END
ELSE
BEGIN
IF
@RowNum < 2260
BEGIN
SET
@RowNum = @RowNum + 1
SET
@Round = 1
END
ELSE
BEGIN
SET
@RowNum = 1
SET
@Round = 1
END
END
END
GO
Setelah beberapa menit, intip apa yang ada di Query Store:
SELECT [q].[query_id], [q].[object_id], [o].[name],
[qt].[query_sql_text],
SUM([rs].[count_executions]) [ExecutionCount],
CAST(AVG([rs].[avg_duration])/1000 AS DECIMAL(10,2)) [AvgDuration_ms],
CAST(AVG([rs].[avg_cpu_time]) AS DECIMAL(10,2)) [AvgCPU],
CAST(AVG([rs].[avg_logical_io_reads]) AS DECIMAL(10,2)) [AvgLogicalReads],
CAST(AVG([rs].[avg_physical_io_reads]) AS DECIMAL(10,2))
[AvgPhysicalReads]
FROM [sys].[query_store_query] [q]
JOIN [sys].[query_store_query_text] [qt]
ON
[q].[query_text_id] = [qt].[query_text_id]
JOIN [sys].[objects] [o]
ON
[q].[object_id] = [o].[object_id]
JOIN [sys].[query_store_plan] [p]
ON
[q].[query_id] = [p].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs]
ON
[p].[plan_id] = [rs].[plan_id]
WHERE [q].[object_id] <> 0
GROUP BY [q].[query_id], [q].[object_id], [o].[name],
[qt].[query_sql_text], [rs].[plan_id]
ORDER BY [o].[name];
Pengingat: Semua prosedur tersimpan yang diakhiri dengan _C
berasal dari tabel yang tidak dikompresi. Prosedur yang diakhiri dengan _R
adalah tabel terkompresi baris, yang diakhiri dengan _P dikompresi halaman,
dan prosedur dengan _CS menggunakan fungsi COMPRESS (saya menghapus hasil untuk
tabel tersebut untuk usp_FindAnyCustomer_CS dan usp_FindActiveCustomer_CS saat
mereka kehilangan grafik sehingga kita kehilangan grafik sehingga kita
kehilangan grafik sehingga kita kehilangan banyak grafik sehingga kita
kehilangan grafik). perbedaan dalam sisa data). Prosedur usp_FindAnyCustomer_ *
dan usp_FindActiveCustomer_ * menggunakan indeks yang tidak dikelompokkan dan
mengembalikan ribuan baris untuk setiap eksekusi. Saya berharap durasinya lebih
tinggi untuk prosedur usp_FindAnyCustomer_ * dan usp_FindActiveCustomer_ *
terhadap tabel terkompresi baris dan halaman, dibandingkan dengan tabel yang
tidak dikompresi, karena overhead dari dekompresi data. Data Query Store tidak
mendukung harapan saya - durasi untuk kedua prosedur tersimpan kira-kira sama
(atau kurang dalam satu kasus!) Di ketiga tabel tersebut. IO logis untuk kueri
hampir sama di seluruh tabel terkompresi tanpa halaman dan baris. Dalam hal
CPU, dalam prosedur tersimpan usp_FindActiveCustomer dan usp_FindAnyCustomer
selalu lebih tinggi untuk tabel terkompresi. CPU sebanding dengan prosedur
usp_FindSpecificCustomer, yang selalu merupakan pencarian tunggal terhadap
indeks berkerumun. Perhatikan CPU tinggi (tetapi durasinya relatif rendah)
untuk prosedur usp_FindSpecificCustomer terhadap tabel [dbo].
[Pelanggan_Kompresi], yang mengharuskan fungsi DECOMPRESS untuk menampilkan
data dalam format yang dapat dibaca. RINGKASAN CPU tambahan yang diperlukan
untuk mengambil data terkompresi ada dan dapat diukur menggunakan Query Store
atau metode pangkalan data tradisional. Berdasarkan pengujian awal ini, CPU
dapat dibandingkan untuk pencarian tunggal, tetapi meningkat dengan lebih
banyak data. Saya ingin memaksa SQL Server untuk mendekompresi lebih dari 10
halaman - setidaknya saya ingin 100 halaman. Saya mengeksekusi variasi skrip
ini, tempat puluhan ribu baris dikembalikan, dan temuannya konsisten dengan
yang Anda lihat di sini. Harapan saya adalah bahwa untuk melihat perbedaan yang
signifikan dalam durasi karena waktu untuk mendekompres data, permintaan akan
mengembalikan ratusan ribu, atau jutaan baris. Jika Anda menggunakan sistem
OLTP, Anda tidak ingin mengembalikan banyak baris, jadi tes di sini akan
memberi Anda gambaran tentang bagaimana kompresi dapat memengaruhi kinerja.
Jika Anda berada di gudang data, maka Anda mungkin akan melihat durasi yang
lebih tinggi bersama dengan CPU yang lebih tinggi ketika mengembalikan set data
yang besar. Sementara fungsi COMPRESS memberikan penghematan ruang yang
signifikan dibandingkan dengan kompresi halaman dan baris, kinerjanya lebih
baik dalam hal CPU, dan ketidakmampuan untuk mengindeks kolom terkompresi
karena tipe datanya, menjadikannya layak hanya untuk volume besar data yang
tidak akan dicari.
Source from
https://sqlperformance.com/2017/01/sql-performance/compression-effect-on-performance
