【IT168 技术文档】
1. 实现分区数据表
作为AdventureWorks2008 的数据库管理员，您发现Orders 数据表在删除旧数据的同时要进行OLTP数据操作，从而严重制约了性能，并且该数据表变得越来越庞大。通常情况下，用户只需要访问到该表中近一年的数据，但在新数据插入时以及索引重建时，有将近3个小时的时间响应缓慢。因此，我们决定通过分区功能来提高性能。
在此实验场景中，数据涵盖了2003年3季度 (OrderDate >= Jul 01, 2003) 到2004年2季度 (OrderDate < Jul 01, 2004)。首先我们将配置分区数据表的实验场景，并创建分区函数以及分区方案。
打开Partitioning Scripts 解决方案，其中包括此实验所需的10个脚本
1. 在Windows 任务栏中，依次点击Start | All Programs | Microsoft SQL Server 2008 | SQL Server Management Studio.
2. 当 SQL Server Management Studio 打开以后会提示进行连接。在Connect to Server 对话框中，输入如下连接属性并点击Connect.
Server type: Database Engine
Server name: (local)\SQLDEV01
Authentication: Windows Authentication
3. 依次点击File | Open | Project/Solution 然后打开C:\AlwaysOn Labs\Partitioning Lab 文件夹中的PartitioningScripts.ssmssln
4. 在SQL Server Management Studio中如果Solution Explorer 窗口没有打开，请在View 下拉菜单中选择Solution Explorer
5. 在Solution Explorer 窗口中可以看到1个解决方案中有3个项目。解决方案名为 ‘PartitioningScripts’ 而3个项目分别叫做Lab Scripts, Whitepaper Scripts 以及Xtra-ILLExtendedExercises. 此实验中我们将主要使用Lab Scripts
为分区数据表创建文件和文件组
1. 在Lab Scripts 项目中，双击Script1 – AddFilegroups.sql
2. 点击Query 菜单，确保选中SQLCMD Mode
3. 在脚本中选中并执行如下语句，创建实验过程中所需的文件夹：
!!mkdir C:\AdventureWorks2008Test . . .
4. 在脚本的第30-51行，我们将创建AdventureWorks2008 数据库的一个副本，并将其命名为AdventureWorks2008Test. 执行如下语句：
TO DISK = N'C:\Ad...Original.BAK'
WITH INIT, STATS = 10
GO
RESTORE DATABASE [AdventureWorks2008Test]
FROM DISK = N'C:\Ad...Original.BAK'
WITH FILE = 1,
MOVE N'AdventureWorks2008_Data'
TO N'C:\...TestData.mdf'
, MOVE N'AdventureWorks2008_Log'
TO N'C:\... TestLog.ldf'
, MOVE N'FileStreamDocuments'
TO N'C:\...FileStream.Documents'
, NOUNLOAD, STATS = 10
GO
新数据库创建完成之后，我们接下来要为其创建文件和文件组。此实验中我们将根据文件组所存放数据的时间来对其进行命名。
5. 创建4个文件组用来存放分区数据表。点击Execute 执行如下脚本：
ADD FILEGROUP [2003Q3]
GO
ALTER DATABASE AdventureWorks2008Test
ADD FILEGROUP [2003Q4]
GO
ALTER DATABASE AdventureWorks2008Test
ADD FILEGROUP [2004Q1]
GO
ALTER DATABASE AdventureWorks2008Test
ADD FILEGROUP [2004Q2]
GO
文件组只是数据库中的一个逻辑概念，数据的物理存放位置是在文件组所包含的文件中。一个文件只能属于一个文件组，但一个文件组可以包含多个文件。当一个文件组中有多个文件时，将按照每个文件剩余空间的比例轮询填充数据，从而更好的利用资源。而当使用分区数据表的时候，由于已经人为规划好数据填充方式，因此通常情况下一个文件组中只放置一个文件。
6. 在每个文件组中创建一个数据文件。执行如下脚本：
ADD FILE
(NAME = N'RPFile1',
FILENAME = N'C:\AdventureWorks2008Test\RPFile1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [2003Q3]
GO
ALTER DATABASE AdventureWorks2008Test
ADD FILE
(NAME = N'RPFile2',
FILENAME = N'C:\AdventureWorks2008Test\RPFile2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [2003Q4]
GO
ALTER DATABASE AdventureWorks2008Test
ADD FILE
(NAME = N'RPFile3',
FILENAME = N'C:\AdventureWorks2008Test\RPFile3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [2004Q1]
GO
ALTER DATABASE AdventureWorks2008Test
ADD FILE
(NAME = N'RPFile4',
FILENAME = N'C:\AdventureWorks2008Test\RPFile4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [2004Q2]
GO
7. 验证AdventureWorks2008Test 数据库已经成功添加了这些文件组以及文件，执行下列脚本查看文件和文件组的属性：
go
sp_helpfile
go
8. 关闭Script1 – AddFilegroups.sql脚本
为了能够对比分区数据表和未分区数据表，我们首先创建一个未经分区的数据表。此操作不涉及任何新功能，仅仅是为了后面的实验做准备。
1. 在Lab Scripts 项目中，双击打开Script2 – CreateOrders.sql
2. 点击Execute 执行此脚本。执行完成后，Orders 数据表中将有2757 条记录
3. 关闭Script2 - CreateOrders.sql文件
创建分区函数
为Orders数据表创建分区函数
1. 在Solution Explorer 窗口的Lab Scripts 项目中，双击打开Script3 – RangePartitionedTable.sql.
2. 执行下面的脚本，创建分区函数：
AS
RANGE RIGHT FOR VALUES
(
'20030701',
'20031001',
'20040101',
'20040401')
GO
注意：一定要确保先将数据库上下文切换到AdventureWorks2008!
创建分区方案
为Orders数据表创建分区方案
在 Script3 – RangePartitionedTable.sql脚本中，查看并执行如下代码，创建分区方案。注意阅读一下以绿色显示的注释:
AS
PARTITION OrderDateRangePFN
TO ([PRIMARY], [2003Q3], [2003Q4],
[2004Q1], [2004Q2])
GO
创建分区数据表
创建分区数据表，加载数据并通过分区函数验证记录所存放的位置
1. 在 Script3 – RangePartitionedTable.sql脚本中，查看并执行如下脚本，创建分区数据表 - OrdersRange:
(
[OrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL ,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] tinyint NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20030701'
AND [OrderDate] < '20040701'),
[TotalDue] [money] NULL
)
ON OrderDatePScheme (OrderDate)
GO
2. 点击Execute 执行下列代码，通过INSERT…SELECT 语句从之前创建的Orders 数据表中将记录加载到OrdersRange 数据表中：
SELECT o.[OrderID]
, o.[EmployeeID]
, o.[VendorID]
, o.[TaxAmt]
, o.[Freight]
, o.[SubTotal]
, o.[Status]
, o.[RevisionNumber]
, o.[ModifiedDate]
, o.[ShipMethodID]
, o.[ShipDate]
, o.[OrderDate]
, o.[TotalDue]
FROM dbo.Orders AS o
GO
为了能够查看到数据位于哪个分区中，我们可以使用$partition. function(value) 函数来进行查看。该函数将跟据分区函数来返回相应结果所在的分区编号
3. 此时数据已经加载到了分区中，点击Execute 执行如下代码，显示每一条记录的OrderDate 字段以及所在的分区编号
$partition.OrderDateRangePFN(OrderDate)
AS 'Partition Number'
FROM OrdersRange
ORDER BY OrderDate
GO
4. 此外，我们不仅能查看每一条记录所在的分区，还可以通过下面的查询语句来显示每一个分区中包含多少条记录以及该分区中OrderDate 字段的最大值和最小值
AS 'Partition Number'
, min(OrderDate) AS 'Min Order Date'
, max(OrderDate) AS 'Max Order Date'
, count(*) AS 'Rows In Partition'
FROM OrdersRange
GROUP BY $partition.OrderDateRangePFN(OrderDate)
ORDER BY 1
在两个数据表中创建聚集索引，以便了解对查询计划所产生的影响
通常来说，拥有聚集索引的数据表结构会更为优化。出色的聚集索引设计可以有效提高查询性能，减少碎片，并有助于各种类型的表访问。在分区数据表的应用场景之下，其实默认已经根据分区键创建了一个理想的聚集索引。但如果只通过分区键创建聚集索引有可能会产生一些负面作用，因为索引键必须是唯一的。最为理想的聚集索引应当定义为OrderDate 列和PurchaseOrderID 列的复合索引。此外，由于聚集索引将决定数据如何存储，因此必须将其创建在分区方案上。
1. 点击Execute 执行如下代码，为分区数据表创建聚集索引：
ON OrdersRange(OrderDate, OrderID)
ON OrderDatePScheme(OrderDate)
GO
2. 为了便于对比，点击Execute 执行如下脚本，为没有经过分区的Orders 数据表创建聚集索引：
ON Orders(OrderDate, OrderID)
ON [PRIMARY]
3. 在工具栏中选中“Include Actual Execution Plan”，然后执行如下脚本，并在Execute plan 选项卡中查看查询计划
SELECT * FROM OrdersRange
GO
4. 在Execution plan 选项卡中，选中“Clustered Index Scan”，并在属性窗口中可以看到， “Actual Partitions Accessed” 为 1..5 且Actual Partition Count 为5，如下图所示：
关闭Script3 - RangePartitionedTable.sql
