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 environmentITPUB个人空间p~%R$]0wo
ps
CREATE DATABASE CompressionDemo
6@|4v+dtSl0GOITPUB个人空间1kp2AV_0@8`
USE CompressionDemo
5A#ShfO7N*t0GO

CREATE TABLE dbo.Customer_UnCompress(ITPUB个人空间/C WE
x~ m
 CustomerKey int NOT NULL,
l3A ^#D*d6D3O0 GeographyKey int NULL,ITPUB个人空间Qn-lc;G+B-t
 CustomerAlternateKey nvarchar(15) NOT NULL,ITPUB个人空间%Pm/zy-A-O8An:Y5U
 Title nvarchar(8) NULL,ITPUB个人空间T`c1f9c^ }?O{
 FirstName nvarchar(50) NULL,ITPUB个人空间aD&^!`qm&i{G
 MiddleName nvarchar(50) NULL,
Y2UYDQ3l0 LastName nvarchar(50) NULL,ITPUB个人空间axn9Sx%zc
 NameStyle. bit NULL,ITPUB个人空间 ^%t }.VE
 BirthDate datetime NULL,
9H6c
c4vdY9c0 MaritalStatus nchar(1) NULL,
$yf*ewOi3]0 Suffix nvarchar(10) NULL,ITPUB个人空间KPT[*Dq7q
 Gender nvarchar(1) NULL,ITPUB个人空间#~H DWH^
 EmailAddress nvarchar(50) NULL,ITPUB个人空间8UKk|2KD{7}
 YearlyIncome money NULL,ITPUB个人空间 }5YJ$lJ+]1Y
 TotalChildren tinyint NULL,ITPUB个人空间p g.r/_4k*h;s#s L
 NumberChildrenAtHome tinyint NULL,ITPUB个人空间V QQ1N5YmRkB1?5\
 EnglishEducation nvarchar(40) NULL,ITPUB个人空间.YC;a`F
 SpanishEducation nvarchar(40) NULL,
6} ug;z-b"n3Hm0 FrenchEducation nvarchar(40) NULL,
iW+Hkg,j"vp+r`]0 EnglishOccupation nvarchar(100) NULL,ITPUB个人空间^+~3j sm
 SpanishOccupation nvarchar(100) NULL,
*Jg&ycR(`0 FrenchOccupation nvarchar(100) NULL,ITPUB个人空间l r6XeA(W@6R$f.P
 HouseOwnerFlag nchar(1) NULL,
f_DQ:_7s0 NumberCarsOwned tinyint NULL,ITPUB个人空间4ITJ/xJ2O M,n
 AddressLine1 nvarchar(120) NULL,
\v3Az5i9L-v-{~0 AddressLine2 nvarchar(120) NULL,
f
m!FaX[0 Phone nvarchar(20) NULL,ITPUB个人空间VoI/]6P
 DateFirstPurchase datetime NULL,
oak x#P"P0 CommuteDistance nvarchar(15) NULL,
C*u1I2lZY0 CONSTRAINT PK_Customer_UnCompress PRIMARY KEY CLUSTERED (CustomerKey ASC),
kJY'y8yL0 CONSTRAINT IX_Customer_UnCompress_CustomerAlternateKey UNIQUE NONCLUSTERED (CustomerAlternateKey ASC)ITPUB个人空间(\Yq9I(_ v
)ITPUB个人空间#d*kFU:h%n
GO

CREATE TABLE dbo.Customer_RowCompress(ITPUB个人空间f%@8oZjEH
 CustomerKey int NOT NULL,
#ZG E)Q,] @-c0 GeographyKey int NULL,ITPUB个人空间$^h#L5`8o1u$c
 CustomerAlternateKey nvarchar(15) NOT NULL,
8{!ne(z]h0 Title nvarchar(8) NULL,ITPUB个人空间:G
w _ p-@H y!E)f
 FirstName nvarchar(50) NULL,ITPUB个人空间ihWlNH2j9D'P
 MiddleName nvarchar(50) NULL,ITPUB个人空间(BM0f0x;H%Sr7Ai D+NU"G
 LastName nvarchar(50) NULL,ITPUB个人空间d*A@:x$g$Y
 NameStyle. bit NULL,ITPUB个人空间V|8d&?4H4GiixD
 BirthDate datetime NULL,
O(u3?w)]&h5L0 MaritalStatus nchar(1) NULL,ITPUB个人空间gF)^_:_|,m{Bcu
 Suffix nvarchar(10) NULL,
] `d~l*_0 Gender nvarchar(1) NULL,ITPUB个人空间hF'Y{z9K6Xa)t;Mf+D9A
 EmailAddress nvarchar(50) NULL,
+JxkG
tq'D+O0 YearlyIncome money NULL,
JC xNB^e g9P0 TotalChildren tinyint NULL,ITPUB个人空间!gP;~!c0}+@x
 NumberChildrenAtHome tinyint NULL,
n~1X/q|+}(h,DgT0 EnglishEducation nvarchar(40) NULL,ITPUB个人空间+nTyej9[
 SpanishEducation nvarchar(40) NULL,ITPUB个人空间)DbK!}yqU
 FrenchEducation nvarchar(40) NULL,
Ot(Gzmp$z0 EnglishOccupation nvarchar(100) NULL,
|HT2S3ii5dUb0 SpanishOccupation nvarchar(100) NULL,
s_zO\ \R0 FrenchOccupation nvarchar(100) NULL,
}Q.|$wH$eb6Q$w;rC0 HouseOwnerFlag nchar(1) NULL,ITPUB个人空间x/S,B V_d`#p
 NumberCarsOwned tinyint NULL,
Ly/F2K|0 AddressLine1 nvarchar(120) NULL,
-z0ro+D@:SZ0 AddressLine2 nvarchar(120) NULL,
$Bx|9AD$M'D;AZB0 Phone nvarchar(20) NULL,
3c:vV:Acq3O4s0 DateFirstPurchase datetime NULL,
#Pvc8FWZ4vP0 CommuteDistance nvarchar(15) NULL,ITPUB个人空间g_ oKj:m+e7QV
 CONSTRAINT PK_Customer_RowCompress PRIMARY KEY CLUSTERED (CustomerKey ASC),ITPUB个人空间 q$I,|v*@
 CONSTRAINT IX_Customer_RowCompress_CustomerAlternateKey UNIQUE NONCLUSTERED (CustomerAlternateKey ASC)ITPUB个人空间-CC(hewR Qg
) WITH (DATA_COMPRESSION = ROW)
#H'QT3f.OZ3JH%y0GO

CREATE TABLE dbo.Customer_PageCompress(
)Jl&g9l+GRV0 CustomerKey int NOT NULL,
u^.Oi
}1D%uC%]0 GeographyKey int NULL,ITPUB个人空间7H_ w$f/f
 CustomerAlternateKey nvarchar(15) NOT NULL,ITPUB个人空间X+]$EW(Iy"S'c
 Title nvarchar(8) NULL,
^9BrO~rc2i0 FirstName nvarchar(50) NULL,ITPUB个人空间`;D0?.q
uAq
 MiddleName nvarchar(50) NULL,ITPUB个人空间Sc?O]9O1?e
 LastName nvarchar(50) NULL,ITPUB个人空间0I3p%m#j-gU}q
 NameStyle. bit NULL,
#E*ca0X.~(V0 BirthDate datetime NULL,
(MWU1J [g7m%r}0 MaritalStatus nchar(1) NULL,
X8T9W d
[ \]/J0 Suffix nvarchar(10) NULL,
+u"LEzc1N7A0 Gender nvarchar(1) NULL,
m%z&Q"[RO^Fz0 EmailAddress nvarchar(50) NULL,ITPUB个人空间P7kZzg imY}
 YearlyIncome money NULL,ITPUB个人空间dR Fvy/v T
 TotalChildren tinyint NULL,
0Rf;L+[U&X\x0 NumberChildrenAtHome tinyint NULL,ITPUB个人空间K*e-AE
MOwQP"[2X
 EnglishEducation nvarchar(40) NULL,
@/l}O7Ap0 SpanishEducation nvarchar(40) NULL,ITPUB个人空间G6@tbUFVh
 FrenchEducation nvarchar(40) NULL,ITPUB个人空间+E^
k1rDV l
 EnglishOccupation nvarchar(100) NULL,
'` kR @_Z/Wr0 SpanishOccupation nvarchar(100) NULL,ITPUB个人空间.d.i\(]H+m(r;z
 FrenchOccupation nvarchar(100) NULL,
0T@H@!J2X#ot&y0 HouseOwnerFlag nchar(1) NULL,ITPUB个人空间Z{i0~E4jD'a
 NumberCarsOwned tinyint NULL,
(z4Zc-xn Y0 AddressLine1 nvarchar(120) NULL,ITPUB个人空间n:t6W-WZ
 AddressLine2 nvarchar(120) NULL,
,h?V!v2Ikz3U0 Phone nvarchar(20) NULL,
-rCGbD8O0 DateFirstPurchase datetime NULL,
(Rn-F.ta!D!O){HK0 CommuteDistance nvarchar(15) NULL,ITPUB个人空间Te}HMw
 CONSTRAINT PK_Customer_PageCompress PRIMARY KEY CLUSTERED (CustomerKey ASC),
p1Y/T }{ B0 CONSTRAINT IX_Customer_PageCompress_CustomerAlternateKey UNIQUE NONCLUSTERED (CustomerAlternateKey ASC)ITPUB个人空间]d3Q)x3|Kb
) WITH (DATA_COMPRESSION = PAGE)
qp'c%w G nV![0GO

--Step 2: Load data into demo tables
M_.B"\
E Si0INSERT INTO Customer_UnCompress SELECT * FROM AdventureWorksDW.dbo.DimCustomer;
"Lk&el!whl4[ZY0INSERT INTO Customer_RowCompress SELECT * FROM AdventureWorksDW.dbo.DimCustomer;
|?3?)jQNd0INSERT INTO Customer_PageCompress SELECT * FROM AdventureWorksDW.dbo.DimCustomer;

--Step 3: Compare the storage cost for each compression settingITPUB个人空间ws+?KcJ7J,h7@
----------The storage size here are estimated value, you may check the SSMS reportITPUB个人空间MU,~?8E"Dg
----------"Disk Usage by Table" for more detailsITPUB个人空间)`J6zTw
SELECT * FROM (
*q3P
vR KV0SELECT OBJECT_NAME(object_id) AS TableName,ITPUB个人空间A"E4_/]6Z6O
  CASE index_type_desc WHEN 'CLUSTERED INDEX' THEN 'Data' ELSE 'Index' END AS Type,ITPUB个人空间oJYTj^ RE
  page_count * 8 AS Size
+Z&D |k|7ft0 FROM sys.dm_db_index_physical_stats(DB_ID('CompressionDemo'), OBJECT_ID('dbo.Customer_UnCompress'), NULL, NULL, DEFAULT)
7^j hVE;K,z0 --WHERE index_type_desc = 'CLUSTERED INDEX'
K}v~7j[)~g7Y0UNIONITPUB个人空间W/~#|K5oHxc`/vR+V
SELECT OBJECT_NAME(object_id) AS TableName,
)x R!I#j~#^I4ewYN0  CASE index_type_desc WHEN 'CLUSTERED INDEX' THEN 'Data' ELSE 'Index' END AS Type,ITPUB个人空间 j"Q:nONdgX
  page_count * 8 AS SizeITPUB个人空间3OXKn0s)x$M&G&r3Ci
 FROM sys.dm_db_index_physical_stats(DB_ID('CompressionDemo'), OBJECT_ID('dbo.Customer_RowCompress'), NULL, NULL, DEFAULT)

P,{q OZ2E)FS0 --WHERE index_type_desc = 'CLUSTERED INDEX'ITPUB个人空间3xG i&s-G#a'uY5B/o4k6C
UNION
_3h*uc&[+V0Q H0SELECT OBJECT_NAME(object_id) AS TableName,
(Z!L^M#i0k-Yf0  CASE index_type_desc WHEN 'CLUSTERED INDEX' THEN 'Data' ELSE 'Index' END AS Type,ITPUB个人空间l)o"TMF7v9n
  page_count * 8 AS SizeITPUB个人空间n Mfrs*u7@
 FROM sys.dm_db_index_physical_stats(DB_ID('CompressionDemo'), OBJECT_ID('dbo.Customer_PageCompress'), NULL, NULL, DEFAULT)ITPUB个人空间Q So:dlB-v
 --WHERE index_type_desc = 'CLUSTERED INDEX'
0E!I
{m]I%W0) AS t ORDER BY TableName, Type

ITPUB个人空间4Gso/|4l$Mb!M
--Step 4: Compare the IO statistics for selecting data from three tables
J,S"j'A)`8N8Ww0DBCC DROPCLEANBUFFERSITPUB个人空间acyB#P)d0NTh
SET STATISTICS IO ON

SELECT * FROM Customer_UnCompress

SELECT * FROM Customer_RowCompress

SELECT * FROM Customer_PageCompress

代码二：对分区表的压缩，从演示中可以看到SQL Server 2008支持对不同分区设置不同的压缩选项，这个功能对一些载荷混合型的数据仓库有极大的帮助。

USE CompressionDemoITPUB个人空间@C u%{#HCg
GO

--Step 1: Create the demo table and fill it up
;w V-ck }0CREATE PARTITION FUNCTION CustomerKeyRangePF (int)
/^hv%CM Fj/W:dU*z0AS RANGE RIGHT FOR VALUES (15000, 20000, 25000);

p zSO |E0GO

CREATE PARTITION SCHEME CustomerPSITPUB个人空间 nG9y!n4Kz/R
AS PARTITION CustomerKeyRangePFITPUB个人空间vmL GWOC
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

CREATE TABLE dbo.Customer_Partitioned(ITPUB个人空间d~1Qy/vxU
 CustomerKey int NOT NULL,ITPUB个人空间/hF/yBf1y$^
 GeographyKey int NULL,
%nzGa;zlSi-J0 CustomerAlternateKey nvarchar(15) NOT NULL,ITPUB个人空间;K4N1`/Fzg
 Title nvarchar(8) NULL,
o%ID@x y.H0 FirstName nvarchar(50) NULL,ITPUB个人空间6a3u6{'Dywrd4Te
 MiddleName nvarchar(50) NULL,ITPUB个人空间1lA9`0`,po R[2r
 LastName nvarchar(50) NULL,ITPUB个人空间[*cRi$nX'C;B|
 NameStyle. bit NULL,
:WP {"a fe&`(R0 BirthDate datetime NULL,
+HiQ5h4|U/Q0 MaritalStatus nchar(1) NULL,ITPUB个人空间(e2V$I`8g"Nhp-O
 Suffix nvarchar(10) NULL,ITPUB个人空间cU:\]3VR"?6|
 Gender nvarchar(1) NULL,ITPUB个人空间6UiGYiv,Yx`y
 EmailAddress nvarchar(50) NULL,
"w{bQVbK%f0 YearlyIncome money NULL,
3b_&PvPW0 TotalChildren tinyint NULL,ITPUB个人空间]"@WD+l*y"g
 NumberChildrenAtHome tinyint NULL,ITPUB个人空间p%jY&T;~2q
 EnglishEducation nvarchar(40) NULL,
K&Z?4W1rS/V.K
U0 SpanishEducation nvarchar(40) NULL,ITPUB个人空间(G.O9qr/az.AG|
 FrenchEducation nvarchar(40) NULL,
2y_W Oi5f4`0 EnglishOccupation nvarchar(100) NULL,
#Y|U4qB9D0 SpanishOccupation nvarchar(100) NULL,ITPUB个人空间 c+Ki R/l `!i
 FrenchOccupation nvarchar(100) NULL,
+]tR3L0?0 HouseOwnerFlag nchar(1) NULL,
?qN,d@7o8Z0 NumberCarsOwned tinyint NULL,
K@'O0q:k%D^ b}0 AddressLine1 nvarchar(120) NULL,
%oz8E/{sn0 AddressLine2 nvarchar(120) NULL,ITPUB个人空间7i~y!`-hi2s
 Phone nvarchar(20) NULL,ITPUB个人空间ku jn/s`-x/D"I
 DateFirstPurchase datetime NULL,ITPUB个人空间(h"uU8BiC3f~&G
 CommuteDistance nvarchar(15) NULL,
aV(`!c.X1}0 CONSTRAINT PK_Customer_Partitioned PRIMARY KEY CLUSTERED (CustomerKey ASC)ITPUB个人空间,n.e$Uerz&w;H'j
) ON CustomerPS(CustomerKey)
dQ0I&w}7G0GO

INSERT INTO Customer_Partitioned SELECT * FROM AdventureWorksDW.dbo.DimCustomer;ITPUB个人空间#ERrE:RL9ij
GO

--Step 2: Record the initial size for each partition
h-c!xE%HTNa0SELECT partition_number, in_row_reserved_page_count * 8 AS ReservedSize, row_count AS [RowCount]ITPUB个人空间cmg{ rf4H9u
 INTO Before_CompressedITPUB个人空间 ]#f3W&RRZ)t
y
 FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('Customer_Partitioned');
Z&Io"s orjUV0GO

--Step 3: Change the partition compress optionITPUB个人空间){lH6X&CAs Uq
ALTER TABLE Customer_Partitioned REBUILD PARTITION = ALL WITH
9@U3O_2K0(
-kC!ehqG0 DATA_COMPRESSION = NONE ON PARTITIONS(1),ITPUB个人空间7{8XY2^_a'^%RXf
 DATA_COMPRESSION = ROW ON PARTITIONS(2),ITPUB个人空间4C%|o2DKMKtXh
 DATA_COMPRESSION = PAGE ON PARTITIONS(3, 4)
}$fZ _$b_q0)ITPUB个人空间$P(V)co"X(tBNd
GO

ALTER TABLE Customer_Partitioned REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE);ITPUB个人空间hB7dM(WxZ~
GO

--Step 4: Compare the each partition's compression ratioITPUB个人空间xr0s8?l(Ba M!w0y
SELECT ac.partition_number, bc.ReservedSize AS ReservedSizeBefore,ITPUB个人空间lj#@!]b%e
 in_row_reserved_page_count * 8 AS ReservedSizeAfter,
&h?~#P0D0 bc.ReservedSize - in_row_reserved_page_count * 8 AS CompressedSize,ITPUB个人空间:Vv5D(V^S*Q
 row_count AS [RowCount]
y x-b/E8Srqn?@0 FROM sys.dm_db_partition_stats ac INNER JOIN Before_Compressed bcITPUB个人空间W l%Z#CGmqQe
 ON ac.partition_number = bc.partition_number
r*R3r&i%c)]iy0VFM0 WHERE ac.object_id = OBJECT_ID('Customer_Partitioned');
i }`_zy~/d0GO