ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL Server 2008香港发布会

SQL Server 2008香港发布会

原创 Linux操作系统 作者:drillchina 时间:2008-04-09 09:27:22 0 删除 编辑

昨天在香港会展中心参加了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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2008-01-24

  • 博文量
    35
  • 访问量
    529391