SQL Server 2008香港釋出會

昨天在香港會展中心參加了SQL Server 2008的釋出會，EMC Global Service作為微軟在香港2008 Launch Wave的白金贊助商在最大的601室拿到了一個45分鐘的Session，所以就成全了我在香港會展中心的首次演講。

我的演講主題是SQL Server 2008對企業級資料倉儲的商業價值，內容涉及了SQL Server 2008對ETL的改善、對查詢分析的改善以及對資料管理能力的改善。結果發現聽眾反映不是很熱烈，可能是英文太濫了，倒是有一個老外結束了之後問我們EMC有沒有興趣加入他們的全球服務供應商名錄。

不過為了這次釋出會，我倒是準備了一段新的Data Compression的演示程式碼，這段演示程式碼也在國內MSDN的Webcast中用過。這裡和大家分享一下。

程式碼一：對非分割槽表壓縮的演示，通過演示可以相當清楚的發現壓縮後物理IO明顯減少。

--Step 1: Create demo environment
CREATE DATABASE CompressionDemo
GO
USE CompressionDemo
GO

CREATE TABLE dbo.Customer_UnCompress(
 CustomerKey int NOT NULL,
 GeographyKey int NULL,
 CustomerAlternateKey nvarchar(15) NOT NULL,
 Title nvarchar(8) NULL,
 FirstName nvarchar(50) NULL,
 MiddleName nvarchar(50) NULL,
 LastName nvarchar(50) NULL,
 NameStyle. bit NULL,
 BirthDate datetime NULL,
 MaritalStatus nchar(1) NULL,
 Suffix nvarchar(10) NULL,
 Gender nvarchar(1) NULL,
 EmailAddress nvarchar(50) NULL,
 YearlyIncome money NULL,
 TotalChildren tinyint NULL,
 NumberChildrenAtHome tinyint NULL,
 EnglishEducation nvarchar(40) NULL,
 SpanishEducation nvarchar(40) NULL,
 FrenchEducation nvarchar(40) NULL,
 EnglishOccupation nvarchar(100) NULL,
 SpanishOccupation nvarchar(100) NULL,
 FrenchOccupation nvarchar(100) NULL,
 HouseOwnerFlag nchar(1) NULL,
 NumberCarsOwned tinyint NULL,
 AddressLine1 nvarchar(120) NULL,
 AddressLine2 nvarchar(120) NULL,
 Phone nvarchar(20) NULL,
 DateFirstPurchase datetime NULL,
 CommuteDistance nvarchar(15) NULL,
 CONSTRAINT PK_Customer_UnCompress PRIMARY KEY CLUSTERED (CustomerKey ASC),
 CONSTRAINT IX_Customer_UnCompress_CustomerAlternateKey UNIQUE NONCLUSTERED (CustomerAlternateKey ASC)
)
GO

CREATE TABLE dbo.Customer_RowCompress(
 CustomerKey int NOT NULL,
 GeographyKey int NULL,
 CustomerAlternateKey nvarchar(15) NOT NULL,
 Title nvarchar(8) NULL,
 FirstName nvarchar(50) NULL,
 MiddleName nvarchar(50) NULL,
 LastName nvarchar(50) NULL,
 NameStyle. bit NULL,
 BirthDate datetime NULL,
 MaritalStatus nchar(1) NULL,
 Suffix nvarchar(10) NULL,
 Gender nvarchar(1) NULL,
 EmailAddress nvarchar(50) NULL,
 YearlyIncome money NULL,
 TotalChildren tinyint NULL,
 NumberChildrenAtHome tinyint NULL,
 EnglishEducation nvarchar(40) NULL,
 SpanishEducation nvarchar(40) NULL,
 FrenchEducation nvarchar(40) NULL,
 EnglishOccupation nvarchar(100) NULL,
 SpanishOccupation nvarchar(100) NULL,
 FrenchOccupation nvarchar(100) NULL,
 HouseOwnerFlag nchar(1) NULL,
 NumberCarsOwned tinyint NULL,
 AddressLine1 nvarchar(120) NULL,
 AddressLine2 nvarchar(120) NULL,
 Phone nvarchar(20) NULL,
 DateFirstPurchase datetime NULL,
 CommuteDistance nvarchar(15) NULL,
 CONSTRAINT PK_Customer_RowCompress PRIMARY KEY CLUSTERED (CustomerKey ASC),
 CONSTRAINT IX_Customer_RowCompress_CustomerAlternateKey UNIQUE NONCLUSTERED (CustomerAlternateKey ASC)
) WITH (DATA_COMPRESSION = ROW)
GO

CREATE TABLE dbo.Customer_PageCompress(
 CustomerKey int NOT NULL,
 GeographyKey int NULL,
 CustomerAlternateKey nvarchar(15) NOT NULL,
 Title nvarchar(8) NULL,
 FirstName nvarchar(50) NULL,
 MiddleName nvarchar(50) NULL,
 LastName nvarchar(50) NULL,
 NameStyle. bit NULL,
 BirthDate datetime NULL,
 MaritalStatus nchar(1) NULL,
 Suffix nvarchar(10) NULL,
 Gender nvarchar(1) NULL,
 EmailAddress nvarchar(50) NULL,
 YearlyIncome money NULL,
 TotalChildren tinyint NULL,
 NumberChildrenAtHome tinyint NULL,
 EnglishEducation nvarchar(40) NULL,
 SpanishEducation nvarchar(40) NULL,
 FrenchEducation nvarchar(40) NULL,
 EnglishOccupation nvarchar(100) NULL,
 SpanishOccupation nvarchar(100) NULL,
 FrenchOccupation nvarchar(100) NULL,
 HouseOwnerFlag nchar(1) NULL,
 NumberCarsOwned tinyint NULL,
 AddressLine1 nvarchar(120) NULL,
 AddressLine2 nvarchar(120) NULL,
 Phone nvarchar(20) NULL,
 DateFirstPurchase datetime NULL,
 CommuteDistance nvarchar(15) NULL,
 CONSTRAINT PK_Customer_PageCompress PRIMARY KEY CLUSTERED (CustomerKey ASC),
 CONSTRAINT IX_Customer_PageCompress_CustomerAlternateKey UNIQUE NONCLUSTERED (CustomerAlternateKey ASC)
) WITH (DATA_COMPRESSION = PAGE)
GO

--Step 2: Load data into demo tables
INSERT INTO Customer_UnCompress SELECT * FROM AdventureWorksDW.dbo.DimCustomer;
INSERT INTO Customer_RowCompress SELECT * FROM AdventureWorksDW.dbo.DimCustomer;
INSERT INTO Customer_PageCompress SELECT * FROM AdventureWorksDW.dbo.DimCustomer;

--Step 3: Compare the storage cost for each compression setting
----------The storage size here are estimated value, you may check the SSMS report
----------"Disk Usage by Table" for more details
SELECT * FROM (
SELECT OBJECT_NAME(object_id) AS TableName,
  CASE index_type_desc WHEN 'CLUSTERED INDEX' THEN 'Data' ELSE 'Index' END AS Type,
  page_count * 8 AS Size
 FROM sys.dm_db_index_physical_stats(DB_ID('CompressionDemo'), OBJECT_ID('dbo.Customer_UnCompress'), NULL, NULL, DEFAULT)
 --WHERE index_type_desc = 'CLUSTERED INDEX'
UNION
SELECT OBJECT_NAME(object_id) AS TableName,
  CASE index_type_desc WHEN 'CLUSTERED INDEX' THEN 'Data' ELSE 'Index' END AS Type,
  page_count * 8 AS Size
 FROM sys.dm_db_index_physical_stats(DB_ID('CompressionDemo'), OBJECT_ID('dbo.Customer_RowCompress'), NULL, NULL, DEFAULT)
 --WHERE index_type_desc = 'CLUSTERED INDEX'
UNION
SELECT OBJECT_NAME(object_id) AS TableName,
  CASE index_type_desc WHEN 'CLUSTERED INDEX' THEN 'Data' ELSE 'Index' END AS Type,
  page_count * 8 AS Size
 FROM sys.dm_db_index_physical_stats(DB_ID('CompressionDemo'), OBJECT_ID('dbo.Customer_PageCompress'), NULL, NULL, DEFAULT)
 --WHERE index_type_desc = 'CLUSTERED INDEX'
) AS t ORDER BY TableName, Type

--Step 4: Compare the IO statistics for selecting data from three tables
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON

SELECT * FROM Customer_UnCompress

SELECT * FROM Customer_RowCompress

SELECT * FROM Customer_PageCompress

程式碼二：對分割槽表的壓縮，從演示中可以看到SQL Server 2008支援對不同分割槽設定不同的壓縮選項，這個功能對一些載荷混合型的資料倉儲有極大的幫助。

USE CompressionDemo
GO

--Step 1: Create the demo table and fill it up
CREATE PARTITION FUNCTION CustomerKeyRangePF (int)
AS RANGE RIGHT FOR VALUES (15000, 20000, 25000);
GO

CREATE PARTITION SCHEME CustomerPS
AS PARTITION CustomerKeyRangePF
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

CREATE TABLE dbo.Customer_Partitioned(
 CustomerKey int NOT NULL,
 GeographyKey int NULL,
 CustomerAlternateKey nvarchar(15) NOT NULL,
 Title nvarchar(8) NULL,
 FirstName nvarchar(50) NULL,
 MiddleName nvarchar(50) NULL,
 LastName nvarchar(50) NULL,
 NameStyle. bit NULL,
 BirthDate datetime NULL,
 MaritalStatus nchar(1) NULL,
 Suffix nvarchar(10) NULL,
 Gender nvarchar(1) NULL,
 EmailAddress nvarchar(50) NULL,
 YearlyIncome money NULL,
 TotalChildren tinyint NULL,
 NumberChildrenAtHome tinyint NULL,
 EnglishEducation nvarchar(40) NULL,
 SpanishEducation nvarchar(40) NULL,
 FrenchEducation nvarchar(40) NULL,
 EnglishOccupation nvarchar(100) NULL,
 SpanishOccupation nvarchar(100) NULL,
 FrenchOccupation nvarchar(100) NULL,
 HouseOwnerFlag nchar(1) NULL,
 NumberCarsOwned tinyint NULL,
 AddressLine1 nvarchar(120) NULL,
 AddressLine2 nvarchar(120) NULL,
 Phone nvarchar(20) NULL,
 DateFirstPurchase datetime NULL,
 CommuteDistance nvarchar(15) NULL,
 CONSTRAINT PK_Customer_Partitioned PRIMARY KEY CLUSTERED (CustomerKey ASC)
) ON CustomerPS(CustomerKey)
GO

INSERT INTO Customer_Partitioned SELECT * FROM AdventureWorksDW.dbo.DimCustomer;
GO

--Step 2: Record the initial size for each partition
SELECT partition_number, in_row_reserved_page_count * 8 AS ReservedSize, row_count AS [RowCount]
 INTO Before_Compressed
 FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('Customer_Partitioned');
GO

--Step 3: Change the partition compress option
ALTER TABLE Customer_Partitioned REBUILD PARTITION = ALL WITH
(
 DATA_COMPRESSION = NONE ON PARTITIONS(1),
 DATA_COMPRESSION = ROW ON PARTITIONS(2),
 DATA_COMPRESSION = PAGE ON PARTITIONS(3, 4)
)
GO

ALTER TABLE Customer_Partitioned REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE);
GO

--Step 4: Compare the each partition's compression ratio
SELECT ac.partition_number, bc.ReservedSize AS ReservedSizeBefore,
 in_row_reserved_page_count * 8 AS ReservedSizeAfter,
 bc.ReservedSize - in_row_reserved_page_count * 8 AS CompressedSize,
 row_count AS [RowCount]
 FROM sys.dm_db_partition_stats ac INNER JOIN Before_Compressed bc
 ON ac.partition_number = bc.partition_number
 WHERE ac.object_id = OBJECT_ID('Customer_Partitioned');
GO

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9079672/viewspace-231289/，如需轉載，請註明出處，否則將追究法律責任。