首先分析问题一，使用profiler跟踪数据库语句的执行情况，发现针对个别表的访问速度大大低于预期，发现此表日常记录维持在100w量级，并且数据操作非常频繁。使用sp_spaceused返回结果发现此表
rowsinfo reserved datainfo index_size unused
1105481 539248 KB 169744 KB 294280 KB 75224 KB
数据量和索引量都不小，结合此表的特征判断应该是索引的问题影响了效率，查看表设计虽然建立了一些非聚集索引，但是重复性很大，效率不高；另外通过DBCC SHOWCONTIG查找了一下表的健康情况，返回如下：
/*
DBCC SHOWCONTIG 正在扫描 'SC_CIG_INFO' 表...
表: 'SC_CIG_INFO' (1099150961)；索引 ID: 1，数据库 ID: 7
已执行 TABLE 级别的扫描。
- 扫描页数................................: 20563
- 扫描区数..............................: 2994
- 区切换次数..............................: 7394
- 每个区的平均页数........................: 6.9
- 扫描密度 [最佳计数:实际计数].......: 34.77% [2571:7395]
- 逻辑扫描碎片 ..................: 36.29%
- 区扫描碎片 ..................: 91.02%
- 每页的平均可用字节数........................: 375.3
- 平均页密度(满).....................: 95.36%
*/
可见健康指数（扫描密度，Scan Density）非常低下。故而判断原因应该如此。下面的工作就是优化索引项目和重建索引，此处不再多说。
在此顺带说一下索引的一些知识：
大多数SQL Server表需要索引来提高数据的访问速度，如果没有索引，SQL Server 要进行表格扫描读取表中的每一个记录才能找到索要的数据。索引可以分为簇索引和非簇索引，簇索引通过重排表中的数据来提高数据的访问速度，而非簇索引则通过维护表中的数据指针来提高数据的索引。
1. 索引的体系结构
为什么要不断的维护表的索引？首先，简单介绍一下索引的体系结构。SQL Server在硬盘中用8KB页 面在数据库文件内存放数据。缺省情况下这些页面及其包含的数据是无组织的。为了使混乱变为有序，就要生成索引。生成索引后，就有了索引页和数据页，数据页 保存用户写入的数据信息。索引页存放用于检索列的数据值清单（关键字）和索引表中该值所在纪录的地址指针。索引分为簇索引和非簇索引，簇索引实质上是将表 中的数据排序，就好像是字典的索引目录。非簇索引不对数据排序，它只保存了数据的指针地址。向一个带簇索引的表中插入数据，当数据页达到100%时，由于页面没有空间插入新的的纪录，这时就会发生分页，SQL Server 将 大约一半的数据从满页中移到空页中，从而生成两个半的满页。这样就有大量的数据空间。簇索引是双向链表，在每一页的头部保存了前一页、后一页地址以及分页 后数据移动的地址，由于新页可能在数据库文件中的任何地方，因此页面的链接不一定指向磁盘的下一个物理页，链接可能指向了另一个区域，这就形成了分块，从 而减慢了系统的速度。对于带簇索引和非簇索引的表来说，非簇索引的关键字是指向簇索引的，而不是指向数据页的本身。
为了克服数据分块带来的负面影响，需要重构表的索引，这是非常费时的，因此只能在需要时进行。可以通过DBCC SHOWCONTIG来确定是否需要重构表的索引。
2. DBCC SHOWCONTIG用法
下面举例来说明DBCC SHOWCONTIG和DBCC REDBINDEX的使用方法。以应用程序中的Employee数据表作为例子，在 SQL Server的Query analyzer输入命令：
use database_name
declare @table_id int
set @table_id=object_id('Employee')
dbcc showcontig(@table_id)
输出结果：
DBCC SHOWCONTIG scanning 'Employee' table...
Table: 'Employee' (1195151303); index ID: 1, database ID: 53
TABLE level scan performed.
- Pages Scanned................................: 179
- Extents Scanned..............................: 24
- Extent Switches..............................: 24
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 92.00% [23:25]
- Logical Scan Fragmentation ..................: 0.56%
- Extent Scan Fragmentation ...................: 12.50%
- Avg. Bytes Free per Page.....................: 552.3
- Avg. Page Density (full).....................: 93.18%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
通过分析这些结果可以知道该表的索引是否需要重构。如下描述了每一行的意义：
信息 描述
Pages Scanned 表或索引中的长页数
Extents Scanned 表或索引中的长区页数
Extent Switches DBCC遍历页时从一个区域到另一个区域的次数
Avg. Pages per Extent 相关区域中的页数
Scan Density[Best Count:Actual Count]
Best Count是连续链接时的理想区域改变数，Actual Count是实际区域改变数，Scan Density为100%表示没有分块。
Logical Scan Fragmentation 扫描索引页中失序页的百分比
Extent Scan Fragmentation 不实际相邻和包含链路中所有链接页的区域数
Avg. Bytes Free per Page 扫描页面中平均自由字节数
Avg. Page Density (full) 平均页密度，表示页有多满
从上面命令的执行结果可以看的出来，Best count为23 而Actual Count为25这表明orders表有分块需要重构表索引。下面通过DBCC DBREINDEX来重构表的簇索引。
3. DBCC DBREINDEX 用法
重建指定数据库中表的一个或多个索引。
语法
DBCC DBREINDEX
( [ 'database.owner.table_name'
[ , index_name
[ , fillfactor ]
]
]
)
参数
'database.owner.table_name'
是要重建其指定的索引的表名。数据库、所有者和表名必须符合标识符的规则。有关更多信息，请参见使用标识符。如果提供 database 或 owner 部分，则必须使用单引号 (') 将整个 database.owner.table_name 括起来。如果只指定 table_name，则不需要单引号。
index_name
是要重建的索引名。索引名必须符合标识符的规则。如果未指定 index_name 或指定为 ' '，就要对表的所有索引进行重建。
fillfactor
是创建索引时每个索引页上要用于存储数据的空间百分比。fillfactor 替换起始填充因子以作为索引或任何其它重建的非聚集索引（因为已重建聚集索引）的新默认值。如果 fillfactor 为 0，DBCC DBREINDEX 在创建索引时将使用指定的起始 fillfactor。
同样在Query Analyzer中输入命令：
dbcc dbreindex('database_name.dbo.Employee','',90)
然后再用DBCC SHOWCONTIG查看重构索引后的结果：
DBCC SHOWCONTIG scanning 'Employee' table...
Table: 'Employee' (1195151303); index ID: 1, database ID: 53
TABLE level scan performed.
- Pages Scanned................................: 178
- Extents Scanned..............................: 23
- Extent Switches..............................: 22
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 100.00% [23:23]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 509.5
- Avg. Page Density (full).....................: 93.70%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
通过结果我们可以看到Scan Denity为100%。
注意：
需要注意的是，重建非聚集索引时该表会暂时加上共享锁，对用户不可进行SELECT以外的操作；重建聚集索引时该表会暂时加上排外锁，不允许任何用户访问。因此需要制定好计划来预防可能的访问问题。
======从上面我们可以看出fillfactor这个指标是一个决定因子，那么它应该如何设置更合适呢？==========
REBUILD有一个fill factor参数，如果fill factor设置为100%，这意味着每一个索引页都是完全满的，如果fill factor设置为50%意味着每个索引页都是半满的。对于fill factor 100%，每次新插入或更新一个记录，由于当前页没有空间可用，可能有分页情况产生。过多的分页会降低SQL Server的性能。下面具体举个例子：
假设您在一张表上建立了一个使用默认fill factor的新索引。当SQL Server创建索引时，它会把索引放置在连续的物理页上，以使数据顺序地被读，I/O访问最优化。但当表因INSERT，UPDATE，DELETE等操作增长改变时，分页发生，SQL Server在磁盘的其他地方分配新的页，导致新的页与原物理页不连续，增加了随机I/O，访问索引页变慢。
那么fill factor的合适值应该为多少？这取决于表的读/写比：
低更新表（读/写比：100比1）：100% fill factor
高更新表（写超过读）：50%-70% fill factor
居中：80%-90% fill factor
过低的fill factor会增加页的数量，也会导致更多的页需要被移至缓存，缓存中有用的数据减少。默认的fill factor为0（即100% fill factor），通常这不是个好的选择，特别是对于聚集索引。
如果您无法判断设置什么fill factor,您首先需要确定磁盘的读/写比.方法就是使用如下两个计数器:
Physical Disk Object: % Disk Read Time 和 Physical Disk Object: % Write Time。另外一个可能有用的计数器就是：SQL Server Access Methods: Pages Splits/Sec。这个计数器测量SQL Server内每秒分页的次数。如果该数值过高，您需要降低fill factor防止新的分页。
如果您想确认您的索引因分页产生的碎片程度，您可以运行DBCC SHOWCONTIG命令。如果看特定表和特定索引，您可以运行如下代码：
结果集中最重要的参数是Scan Density，越接近100%越好。如果Scan Density小于75%，那么您可能需要重建表中的索引。
--获取每个索引的碎片情况
--Declare variables
DECLARE
@ID int,
@IndexID int,
@IndexName varchar(128)
--Set the table and index to be examined
SELECT @IndexName = 'index_name' --enter name of index
SET @ID = OBJECT_ID('table_name') --enter name of table
--Get the Index Values
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName
--Display the fragmentation
DBCC SHOWCONTIG (@id, @IndexID)
--获取当前表基本索引的碎片情况
--Declare variables
DECLARE
@ID int
--Set the table and index to be examined
SET @ID = OBJECT_ID('table_name') --enter name of
--Display the fragmentation
DBCC SHOWCONTIG (@id)
对于小于100数据页，重建索引并不会有明显的性能改善。这是因为物理硬件缓存，SQL Server缓存和SQL Server预读机制隐藏了碎片的负面作用。但对于非常大的表，重建索引会使它受益匪浅，因为涉及大量磁盘I/O操作。
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
另外，更新了索引最好也重新更新一下统计信息，好处在于：1. index建立后，优化器是否使用该index，优化器需要借助一些统计信息来做判断
2. 根据统计信息，预估采用嵌套循环连接，合并连接， 哈希连接等哪一个连接
3. 根据统计信息判断表的估计最佳的成本（最佳的执行顺序），
通过如下方式观察数据文件过大的情况：
--获取数据库的物理文件信息
SELECT df.[name], df.physical_name, df.[size], df.growth, f.[name][filegroup], f.is_default
FROM sys.database_files df
JOIN sys.filegroups f
ON df.data_space_id = f.data_space_id
--获取数据库磁盘占用情况
exec sp_spaceused
--@objname = 'CL_BILL_PC' ,
@updateusage = 'TRUE'
GO
--获取数据库各个表的磁盘占用情况
if not exists
(select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table tablespaceinfo --创建表空间统计临时表
(
nameinfo varchar(50) ,
rowsinfo int ,
reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20)
)
--清空统计临时表
delete from tablespaceinfo
--存储各个表名称
declare @tablename varchar(255)
--存储查询语句
declare @cmdsql varchar(500)
--创建针对表对象的游标
DECLARE Info_cursor CURSOR FOR
select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1
and o.name not like N'#%%' order by o.name
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
--获取当前表的磁盘占用情况，并存储至临时表
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename
FETCH NEXT FROM Info_cursor INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--显示空间统计临时表信息
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
从中可以看出来各个表占用空间情况：
我们从中可以看出来某些表记录量比较大，数据文件占用空间也比较大，对此我们可以通过表分区的手段减小单个数据文件的占用空间。
在表分区操作之前，先来了解一些基础知识
1、文件和文件组的含义与关系
每个数据库有一个主数据文件.和若干个从文件。文件是数据库的物理体现。 文件组可以包括分布在多个逻辑分区的文件，实现负载平衡。文件组允许对文件进行分组，以便于管理和数据的分配／放置。例如，可以分别在三个硬盘驱动器上创 建三个文件（Data1.ndf、Data2.ndf 和 Data3.ndf），并将这三个文件指派到文件组 fgroup1 中。然后，可以明确地在文件组 fgroup1 上创建一个表。对表中数据的查询将分散到三个磁盘上，因而性能得以提高。在 RAID（磁盘冗余阵列)条带集上创建单个文件也可以获得相同的性能改善。然而，文件和文件组使您得以在新磁盘上轻易地添加新文件。另外，如果数据库超过 单个 Microsoft Windows 文件的最大大小，则可以使用次要数据文件允许数据库继续增长。
2、文件、文件组在实践应用中常见的问题
通常情况下我们构造的数据库都只有两个文件,mdf文件和ldf文件.但是这样有两个缺点：
(一)容易导致文件过大
我们知道,mdf文件是数据库文件,这样的话也就意味着随着数据库的增大mdf就会相应的增大,显然在现在的应用中数据膨胀是太常见的事情了,当你的应用 变大后,mdf文件也会变大,然而windows对文件的大小是有要求的,这样的话很容易导致mdf文件达到windows所允许的文件大小的界限(于是 数据库就崩溃了)。
(二)没有利用到磁盘阵列
大型的服务器好多都有磁盘阵列,你可以把磁盘阵列简单的假象成n个一块转动的磁盘,磁盘阵列的设计是希望通过多个磁盘的串联来得到更大的读写效率.但是如 果你的数据库只有一个mdf文件(ldf文件暂时不考虑),那么你总是只能够利用这个磁盘阵列里面的一个磁盘而已.那样的话昂贵的磁盘阵列的效率就由并联 变成串联了.试想如果我们能够让mdf分散成多个文件,比如说磁盘阵列上的每个磁盘中都分配一个文件,然后把mdf中的数据分散到各个文件中,我在读取的 时候就是串联的读取了,这样就充分的利用了磁盘阵的存取效能.
这两个问题平常我们没有遇到过(条件不具备),但是做大型的服务开发的时候这几乎是致命的.
3、MSDN官方解释
了解文件和文件组
每个 SQL Server 数据库至少具有两个操作系统文件：一个数据文件和一个日志文件。数据文件包含数据和对象，例如表、索引、存储过程和视图。日志文件包含恢复数据库中的所有事务所需的信息。为了便于分配和管理，可以将数据文件集合起来，放到文件组中。
à数据库文件
SQL Server 数据库具有三种类型的文件，如下所示
主要數據文件
主要数据文件包含数据库的启动信息，并指向数据库中的其他文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是 .mdf.
次要数据文件
是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。另外，如果数据库超过了单个 Windows 文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。次要数据文件的建议文件扩展名是 .ndf。
事务日志文件
事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。事务日志的建议文件扩展名是 .ldf.
à文件组
每个数据库有一个主要文件组。此文件组包含主要数据文件和未放入其他文件组的所有次要文件。可以创建用户定义的文件组，用于将数据文件集合起来，以便于管理、数据分配和放置。
例如，可以分别在三个磁盘驱动器上创建三个文件 Data1.ndf、Data2.ndf 和 Data3.ndf，然后将它们分配给文件组 fgroup1。然后，可以明确地在文件组 fgroup1 上创建一个表。对表中数据的查询将分散到三个磁盘上，从而提高了性能。通过使用在 RAID（独立磁盘冗余阵列）条带集上创建的单个文件也能获得同样的性能提高。但是，文件和文件组使您能够轻松地在新磁盘上添加新文件。
以下列出了存储在文件组中的所有数据文件。
主文件组
主要包含主要文件的文件组。所有系统表都被分配到主要文件组中。
用户定义文件組
用户首次创建数据库或以后修改数据库时明确创建的任何文件组
默认文件组
如果在数据库中创建对象时没有指定对象所属的文件组，对象将被分配给默认文件组。不管何时，只能将一个文件组指定为默认文件组。默认文件组中的文件必须足够大，能够容纳未分配给其他文件组的所有新对象。
PRIMARY 文件组是默认文件组，除非使用 ALTER DATABASE 语句进行了更改。但系统对象和表仍然分配给 PRIMARY 文件组，而不是新的默认文件组。
好处总之一句话“利用文件组可以提高数据库的i/o性能，同时也可以规划数据库的数据！！！！！”
鉴于此，我们开始
--第1步：创建分区函数
CREATE PARTITION FUNCTION
PART_FUNC_SC_CIG_INFO_TEMP(DATETIME) AS
RANGE RIGHT
FOR VALUES('20091106 00:00:00.000',
'20091107 00:00:00.000',
'20091109 00:00:00.000',
'20091110 00:00:00.000',
'20091111 00:00:00.000',
'20091112 00:00:00.000',
'20091113 00:00:00.000',
'20091114 00:00:00.000',
'20091116 00:00:00.000',
'20091117 00:00:00.000',
'20091118 00:00:00.000',
'20091119 00:00:00.000',
'20091120 00:00:00.000',
'20091121 00:00:00.000');
GO
PRINT('创建分区函数完成 10%')
--第2步：创建逻辑文件组
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091105]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091106]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091107]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091109]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091110]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091111]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091112]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091113]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091114]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091116]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091117]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091118]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091119]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091120]
ALTER DATABASE SORT_CEN ADD FILEGROUP [FG_CIG_INFO_TEMP20091121]
PRINT('创建逻辑文件组完成 20%')
--第3步：创建物理文件
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091105',FILENAME = N'H:\FileGroup\cig_info_temp20091105.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091105]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091106',FILENAME = N'H:\FileGroup\cig_info_temp20091106.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091106]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091107',FILENAME = N'H:\FileGroup\cig_info_temp20091107.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091107]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091109',FILENAME = N'H:\FileGroup\cig_info_temp20091109.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091109]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091110',FILENAME = N'H:\FileGroup\cig_info_temp20091110.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091110]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091111',FILENAME = N'H:\FileGroup\cig_info_temp20091111.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091111]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091112',FILENAME = N'H:\FileGroup\cig_info_temp20091112.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091112]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091113',FILENAME = N'H:\FileGroup\cig_info_temp20091113.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091113]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091114',FILENAME = N'H:\FileGroup\cig_info_temp20091114.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091114]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091116',FILENAME = N'H:\FileGroup\cig_info_temp20091116.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091116]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091117',FILENAME = N'H:\FileGroup\cig_info_temp20091117.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091117]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091118',FILENAME = N'H:\FileGroup\cig_info_temp20091118.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091118]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091119',FILENAME = N'H:\FileGroup\cig_info_temp20091119.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091119]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091120',FILENAME = N'H:\FileGroup\cig_info_temp20091120.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091120]
ALTER DATABASE SORT_CEN
ADD FILE
(NAME = N'cig_info_temp20091121',FILENAME = N'H:\FileGroup\cig_info_temp20091121.ndf',SIZE = 3MB)
TO FILEGROUP [FG_CIG_INFO_TEMP20091121]
PRINT('创建物理文件完成 30%')
--第4步：创建分区架构并关联到分区函数
CREATE PARTITION SCHEME PART_FUNC_SC_CIG_INFO_TEMP_SCHEME
AS PARTITION PART_FUNC_SC_CIG_INFO_TEMP
TO (
[FG_CIG_INFO_TEMP20091105],
[FG_CIG_INFO_TEMP20091106],
[FG_CIG_INFO_TEMP20091107],
[FG_CIG_INFO_TEMP20091109],
[FG_CIG_INFO_TEMP20091110],
[FG_CIG_INFO_TEMP20091111],
[FG_CIG_INFO_TEMP20091112],
[FG_CIG_INFO_TEMP20091113],
[FG_CIG_INFO_TEMP20091114],
[FG_CIG_INFO_TEMP20091116],
[FG_CIG_INFO_TEMP20091117],
[FG_CIG_INFO_TEMP20091118],
[FG_CIG_INFO_TEMP20091119],
[FG_CIG_INFO_TEMP20091120],
[FG_CIG_INFO_TEMP20091121],
[PRIMARY]);
GO
PRINT('创建分区架构并关联到分区函数完成 50%')
--第5步：移动表到新的分区架构上
ALTER TABLE dbo.SC_CIG_INFO_TEMP
DROP CONSTRAINT PK_SC_CIG_INFO_TEMP
WITH(
MOVE TO PART_FUNC_SC_CIG_INFO_TEMP_SCHEME(CI_RECV_TIME))
PRINT('移动表到新的分区架构完成 60%')
--第6步：重建分区索引
EXEC sp_helpindex N'SC_CIG_INFO_TEMP' --查看orders中使用的索引
DROP INDEX PK_SC_CIG_INFO_TEMP
ON SC_CIG_INFO_TEMP;
GO
CREATE CLUSTERED INDEX PK_SC_CIG_INFO_TEMP
ON SC_CIG_INFO_TEMP(CI_BCIG_BRAND,CI_ID,CI_RECV_TIME)
ON PART_FUNC_SC_CIG_INFO_TEMP_SCHEME(CI_RECV_TIME);
GO
CREATE NONCLUSTERED INDEX IDX_BB_MLS_US_IDS_CUT
ON SC_CIG_INFO_TEMP(CI_ID,CI_BCIG_BRAND,CI_IS_DELETE_STATE,CI_MOVE_LOCK_STATE,CI_USE_STATE,CI_USE_TIME);
GO
PRINT('重建分区索引完成 70%')
--查看分区及分区范围的情况
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('SC_CIG_INFO_TEMP');
SELECT * FROM SYS.PARTITION_RANGE_VALUES;
--查看分区架构情况
SELECT * FROM SYS.PARTITION_SCHEMES;
PRINT('创建分区完成 100%')
如此就完成了整个过程，那么有几个需要注意的地方：
1.分区键列必须是主键的一部分
2.使用如下方式对新建表进行分区处理
CREATE TABLE [dbo].[ObjTeaching](
[TeachingID] [uniqueidentifier] NOT NULL,
[TeacherID] [uniqueidentifier] NULL,
[TeacherName] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[ClassID] [uniqueidentifier] NULL,
[ClassName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[CourseID] [uniqueidentifier] NULL,
[CourseName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[CourseSequenceID] [uniqueidentifier] NULL,
[TeachingDate] [datetime] NOT NULL,
[IsUsingEqt] [bit] NULL,
[ScoreID] [uniqueidentifier] NULL,
......
) ON FiveYearDateRangePScheme(TeachingDate)
ALTER TABLE [ObjTeaching]
ADD CONSTRAINT [ObjTeaching_PK]
PRIMARY KEY CLUSTERED ([TeachingID], [TeachingDate])
GO
3.对分区及分区数据的管理
分区适用于可以缩放的大型表，所以随着时间和环境的变化，就会产生对分区的拆分、合并、移动的需求。
3.1. 拆分与合并分区
通过拆分或合并边界值更改分区函数。通过执行 ALTER PARTITION FUNCTION，可以将使用分区函数的任何表或索引的某个分区拆分为两个分区，也可以将两个分区合并为一个分区。
注意：多个表或索引可以使用同一分区函数。ALTER PARTITION FUNCTION 在单个事务中影响所有这些表或索引。
ALTER PARTITION FUNCTION 语法如下：
ALTER PARTITION FUNCTION partition_function_name()
{
SPLIT RANGE ( boundary_value )
| MERGE RANGE ( boundary_value )
} [ ; ]
参数说明：
partition_function_name
要修改的分区函数的名称。
SPLIT RANGE ( boundary_value )
在分区函数中添加一个分区。 boundary_value 确定新分区的范围，因此它必须不同于分区函数的现有边界范围。根据 boundary_value，Microsoft SQL Server 2005 数据库引擎 将某个现有范围拆分为两个范围。在这两个范围中，新 boundary_value 所在的范围被视为是新分区。
重要提示：
文件组必须处于联机状态，并且必须由使用此分区函数的分区方案标记为 NEXT USED，以保存新分区。在 CREATE PARTITION SCHEME 语句中，将把文件组分配给分区。如果 CREATE PARTITION SCHEME 语句分配了多余的文件组（在 CREATE PARTITION FUNCTION 语句中创建的分区数少于用于保存它们的文件组），则存在未分配的文件组，分区方案将把其中的某个文件组标记为 NEXT USED。该文件组将保存新的分区。如果分区方案未将任何文件组标记为 NEXT USED，则必须使用 ALTER PARTITION SCHEME 添加一个文件组或指定一个现有文件组来保存新分区。可以指定已保存分区的文件组来保存附加分区。由于一个分区函数可以参与多个分区方案，因此所有使用分区 函数（您向其中添加了分区）的分区方案都必须拥有一个 NEXT USED 文件组。否则，ALTER PARTITION FUNCTION 将失败并出现错误，该错误显示缺少 NEXT USED 文件组的一个或多个分区方案。
MERGE [ RANGE ( boundary_value) ]
删除一个分区并将该分区中存在的所有值都合并到剩 余的某个分区中。RANGE (boundary_value) 必须是一个现有边界值，已删除分区中的值将合并到该值中。如果最初保存 boundary_value 的文件组没有被剩余分区使用，也没有使用 NEXT USED 属性进行标记，则将从分区方案中删除该文件组。合并的分区驻留在最初不保存 boundary_value 的文件组中。boundary_value 是一个可以引用变量（包括用户定义类型变量）或函数（包括用户定义函数）的常量表达式。它无法引用 Transact-SQL 表达式。boundary_value 必须匹配或可以隐式转换为其对应列的数据类型，并且当值的大小和小数位数不匹配其对应 input_parameter_type 时，将无法在隐式转换过程中被截断。
示例：
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]
备注：
ALTER PARTITION FUNCTION 在单个原子操作中对使用该函数的任何表和索引进行重新分区。但该操作在脱机状态下进行，并且根据重新分区的范围，可能会消耗大量资源。
ALTER PARTITION FUNCTION 只能用于将一个分区拆分为两个分区，或将两个分区合并为一个分区。若要更改其他情况下对表进行分区方法（例如，将 10 个分区合并为 5 个分区），可以尝试使用以下任何选项。根据系统配置，这些选项可能在资源消耗方面有所不同：
使用所需的分区函数创建一个新的已分区表，然后使用 INSERT INTO...SELECT FROM 语句将旧表中的数据插入新表。
为堆创建分区聚集索引。
注意：
删除已分区的聚集索引将产生分区堆。
通过将 Transact-SQL CREATE INDEX 语句与 DROP EXISTING = ON 子句一起使用来删除并重新生成现有的已分区索引。
执行一系列 ALTER PARTITION FUNCTION 语句。
ALTER PARITITION FUNCTION 所影响的全部文件组都必须处于联机状态。
如果使用分区函数的任何表中存在已禁用的聚集索引，ALTER PARTITION FUNCTION 都将失败。
Microsoft SQL Server 2005 不对修改分区函数提供复制支持。必须在订阅数据库中手动应用对发布数据库中的分区函数的更改。
例：
合并分区
USE AdventureWorksDW
ALTER PARTITION FUNCTION pf_OrderDateKey()MERGE RANGE (185)
GO
ALTER PARTITION FUNCTION pf_OrderDateKeyArchive ()MERGE RANGE (185)
GO
拆分分区
USE AdventureWorksDW
ALTER PARTITION FUNCTION pf_OrderDateKey()SPLIT RANGE (915)
GO
3.2. 移动分区数据
可以使用 ALTER TABLE ....... SWITCH 语句按一下方式快速有效地移动数据子集：
将某个表中的数据移动到另一个表中；
将某个表作为分区添加到现存的已分区表中；
将分区从一个已分区表切换到另一个已分区表；
删除分区以形成单个表。
使用这些方案移动数据时，无论集合有多大，此方案都能快速有效地进行传输，因为操作并不以物理方式移动数据，只有关于存储位置的元数据会从一个分区变为另一个分区。
ALTER TABLE .... SWITCH 的语法如下：
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
SWITCH [ PARTITION source_partition_number_expression ]
TO [schema_name].target_table
[ PARTITION target_partition_number_expression ]
}
[ ; ]
例：
USE AdventureWorksDW
ALTER TABLE FactInternetSalesPartitioned SWITCH PARTITION 1 TO FactInternetSalesArchive PARTITION 1
GO
4. 查看分区的相关情况
--查看分区及分区范围的情况
select * from sys.partitions where object_id = object_id('orders');
select * from sys.partition_range_values;
--查看分区架构情况
select * from sys.partition_schemes;
--查看某一特定分区列值属于哪个分区
select Performance.$partition.Part_func_orders('20050325') as partition_num;
--查看某一特定分区的记录
select * from orders where Performance.$partition.Part_func_orders(orderdate) = 2
--查看各分区所包含的记录数
select $partition.Part_func_orders(orderdate) as partition_num,
count(*) as record_num
from orders
group by $partition.Part_func_orders(orderdate)
order by $partition.Part_func_orders(orderdate);
5.分区的管理
--增加分区值,增加分区之前应先增加或设置新分区使用的文件组
alter database Performance
add filegroup [FG5];
go
alter database Performance
add file
(name = FG5_data,filename = 'D:\SQL_Data\Performance\FG5_data.ndf',size = 3MB )
to filegroup [FG5];
go
alter partition scheme Part_func_orders_scheme
next used [FG5];
go
alter partition function Part_func_orders()
split range('20061231 23:59:59.997')
go
insert into orders
select 10000001,'C0000012906',213,'I','20070101','a'
union all select 10000002,'C0000019995',213,'I','20070109','a'
union all select 10000003,'C0000019996',410,'I','20070512','a';
go
select * from orders where Performance.$partition.Part_func_orders(orderdate) = 6
--合并分区
--合并分区后，以下将新增的三条记录放到了第5个分区中
alter partition function Part_func_orders()
merge range('20061231 23:59:59.997');
go
##另外有一篇文章可以了解分区表技术在微软产品上应用的历史
SQL Server 2005中处理表分区问题
作者：Baya Pavliashvili 2007-06-18
数据库性能调优是每一个优秀SQL Server管理员最终的责任。虽然保证数据的安全和可用性是我们的最高的目标，但是假如数据库应用程序无法满足用户的要求，那么DBA们会因为性能低下 的设计和实现而受到指责。SQL Server 2005在数据库性能方面得到了很多提高，尤其是表分区的技术。如果你还没不了解表分区的特征，那么请你花点时间读这篇文章。
表分区的概念不是一个新的概念；只要你当过一段时间的SQL Server DBA，那么你可能已经对一些频繁访问的表进行过归档，当这个表中的历史数据变的不再经常被访问的时候。比如，假设你有一个打印时间报表的应用，你的报告 很少会查询1995年的数据，因为绝大部分的预算规划会基于最近几年的数据。
在SQL Server的早期版本中，你可以创建多个表。每一个表都具有相同的列结构，用来保存不同年份的数据。这样，当存在着对历史数据访问的必要的时候，你可以 创建一个视图来对这些表进行查询处理。将数据保存在多个表中是很方便的，因为相对于查询时扫描整个大表，扫描小表会更快。但是这种好处只有在你预先知道哪 些时间段的数据会被访问。同时，一旦数据过期，你还需要创建新表并且转移新产生的历史数据。
SQL Server 7和SQL Server 2000支持分布式分区视图(distributed partitioned views，又称为物化视图，materialized views)。分布式分区视图由分布于多台服务器上的、具有相同表结构的表构成，而且你还需要为每一个服务器增加链接服务器定义(linked server definitions)，最后在其中一台服务器上创建一个视图将每台服务器上返回的数据合并起来。这里的设计思想是数据库引擎可以利用多台服务器的处理 能力来满足查询。
但是，分布式分区视图(DPV)受到很多限制，你可以在SQL Server的在线帮助文档中阅读到。虽然DPV在一些情况下能够提供性能上的提高，但是这种技术不能被广泛的应用。已经被证明它们不能满足逐步增长的企 业级应用的要求。何况，DPV的实现是一个费力的过程，需要DBA进行很多工作。
SQL Server 2005开始支持表分区，这种技术允许所有的表分区都保存在同一台服务器上。每一个表分区都和在某个文件组(filegroup)中的单个文件关联。同样的一个文件/文件组可以容纳多个分区表。
在这种设计架构下，数据库引擎能够判定查询过程中应该访问哪个分区，而不用扫描整个表。如果查询需要的数据行分散在多个分区中，SQL Server使用多个处理器对多个分区进行并行查询。你可以为在创建表的时候就定义分区的索引。 对小索引的搜索或者扫描要比扫描整个表或者一张大表上的索引要快很多。因此，当对大表进行查询，表分区可以产生相当大的性能提升。
现在让我们通过一个简单的例子来了解表分区是如何发挥作用的。在这篇文章中，我不想深入到分区的语法细节当中，这些你可以在SQL Server的在线帮助文档中找到。下面的例子基于存储着一个时间报表系统的数据的数据仓库。除了默认的文件组，我另外创建了7个文件组，每一个文件组仅 包含一个文件，这个文件将存储由分区函数定义的一部分数据。
为了测试表分区的性能提升，我向这个分区表中插入了一千五百万行，同时向另外一个具有相同表结构、但是没有进行分区的表插入了同样的数据。对分区表执行的 INSERT语句运行的更快一些。甚至在我的内存不到1G的笔记本电脑上，对分区表的INSERT语句比不分区的表的INSERT语句要快上三倍。当然， 查询的执行时间依据硬件资源的差异而所有变化，但是你还是能够在你的环境中感到不同程度的提升。
我将检查更深入了一步，通过分别检查同一条返回所有行的、简单SELECT语句在分区表和非分区表上的执行计划，返回的数据范围通过WHERE语句来指 定。同一条语句在这两个不同的表上有不同的执行计划。对于分区表的查询显示出一个嵌套的循环和索引的扫描。从本质上来说，SQL Server将两个分区视为独立的表，因此使用一个嵌套循环将它们连接起来。对非分区的表的同一个查询则使用索引扫描来返回同样的列。当你使用同样的分区 策略创建多个表，同时在查询中连接这些表，那么性能上的提升会更加明显。
你可以使用下面的查询来了解每一个分区中的行的个数：
SELECT $PARTITION.TimeEntryDateRangePFN(time_entry_date) AS Partition,COUNT(*) AS [COUNT] FROM fact_time_entry GROUP BY $PARTITION.TimeEntryDateRangePFN(time_entry_date) ORDER BY Partition
表分区对交易环境和数据仓库环境来说，都是一个重要的特征。数据仓库用户最主要的抱怨是移动事实表(fact table)会花费太多时间。当装载数据到事实表的时候，用户查询(立方体处理查询)的性能会明显下降，甚至是完全无法成功。因此，装载大量的数据到事实 表的时候常常需要停机。如果使用表分区，就不再出现这样的情况——确切的讲，你一眨眼的工夫就可以移动事实表。为了演示这是如何生效的，我使用上面例子中 相同的分区函数和表结构来创建一个新的表，这个表叫做fact_time_entry2。表的主键从五千万开始，这样fact_time_entry2就 不会包含表fact_time_entry中已经有的数据。
现在我把2007年的数据移动到这张fact_time_entry2中。同时让我们假设fact_time_entry表中包含着2007年之前的数 据。在fact_time_entry2表完成数据的转移，我执行下面的语句： ALTER TABLE fact_time_entry2 SWITCH PARTITION 8 TO fact_time_entry PARTITION 8
这条语句将编号为8的分区，这个分区恰好包含着2007年的数据，从fact_time_entry2移动到了fact_time_entry表中，在我 的笔记本电脑上，这个过程只花费了3毫秒。在这短短的3毫秒中，我的事实表就增加了五百万条记录!的确，我需要在交换分区之前，将数据移动到中间表，但是 我的用户不需要担心——事实表随时都可以查询!在这幕后，实际上没有数据移动——只是两张表的元数据发生了变化。
我可以使用类似的查询删除事实表中不在需要的数据。例如，假设我们决定我们不再关心2004年的记录。下面的语句可以将这些记录转移到我们创建的工作表 中： ALTER TABLE fact_time_entry SWITCH PARTITION 2 TO fact_time_entry2 PARTITION 2
这样的语句依旧在毫秒级内完成了。现在，我可以删除fact_time_entry2或者将它移到其他的服务器上。我的事实表不会包含2004年的任何记 录。这个分区还是需要在目的表中存在，而且它必须是空的。你不能将分区转移到一个包含重复数据的表中。源表和目的表的分区必须一致，同时被转移的数据必须 在同一个文件组中。即使受到这么多的限制，转换分区和无需停机就可以移动数据表的功能必将让数据仓库的实现变的前所未有的轻松。
No comments:
Post a Comment