sqlserver中filegroup与partition怎么使用

本篇内容介绍了“sql server中filegroup与partition怎么使用”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

创新互联长期为上1000+客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为湖滨企业提供专业的成都网站设计、成都做网站，湖滨网站改版等技术服务。拥有十余年丰富建站经验和众多成功案例,为您定制开发。

1.基础知识

一直对于表分区和filegroup的概念不是很清晰，今天通过具体的实例来学习什么事filegroup和partition，以及他们的作用。

1.1通过文件组来管理文件的特性

对于用户角度来说，需对创建的对象指定存储的文件组只有三种数据对象：表,索引和大对象（LOB）
使用文件组可以隔离用户和文件，使得用户针对文件组来建立表和索引，而不是实际磁盘中的文件。也就是可以指定将表和索引存储在不同的文件上面。
使用文件组来管理文件可以使得同一文件组内的不同文件分布在不同的硬盘中，极大的提高了IO性能.
SQL SERVER会根据每个文件设置的初始大小和增长量会自动分配新加入的空间，假设在同一文件组中的文件A设置的大小为文件B的两倍，新增一个数据占用三页(Page),则按比例将2页分配到文件A中，1页分配到文件B中.

1.2文件的分类

首要文件:这个文件是必须有的，而且只能有一个。这个文件额外存放了其他文件的位置等信息.扩展名为.mdf
次要文件:可以建任意多个，用于不同目的存放.扩展名为.ndf，用于存放数据，而不是日志。
日志文件：存放日志,扩展名为.ldf

在SQL SERVER 2008之后，还新增了文件流数据文件和全文索引文件.
我们可以通过sys.database_files这个视图查看数据库中的文件情况:

双击代码全选
1	`select*from sys.database_files`

1.3创建filegroup，并将索引创建在指定的filegroup中

可以通过TSQL语句来创建文件组，也可以通过SSMS来创建文件组，这个在后面会提到。这里不再重复。下面我们重点来介绍如何将索引创建在指定的 filegroup中，而不跟数据放在一起。首先来看我创建好的filegroup，已经这些filegroup所对应的files，如下图所示：

然后我们通过如下TSQL语句来测试

双击代码全选
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28	`use TESTDB` `--step1.插入数据` `select` `*` `into` `OrderDetail` `from` `AdventureWorks2008R2.Sales.SalesOrderDetail` `--step2:查看表的索引信息，发现所有页都在pagefid=1上面，并且indexid都为0.因为没有创建聚集索引之前是堆表` `dbcc ind ( TESTDB, [dbo.OrderDetail], -1)` `--step3:在分区上创建聚集索引，聚集索引不要放在IndexStorage这个filegroup当中，因为聚集索引就是数据本身。` `--如果将聚集索引on IndexStorage的话，那么所有数据都将会在IndexStorage这个filegroup所对应的文件上` `create` `clustered` `index` `idx_c_SSalesOrderDetailID` `on` `OrderDetail(SalesOrderDetailID)` `--step4:此时发现原先indexid=0的都变成了index=1` `dbcc ind ( TESTDB, [dbo.OrderDetail], -1)` `--step5:在IndexStorage这个file group上面创建非聚集索引` `CREATE` `NONCLUSTERED` `INDEX` `idx_nc_SalesOrderID` `ON` `dbo.OrderDetail(SalesOrderID)` `on` `IndexStorage` `CREATE` `NONCLUSTERED` `INDEX` `idx_nc_CarrierTrackingNumber` `ON` `dbo.OrderDetail(CarrierTrackingNumber)` `on` `IndexStorage` `CREATE` `NONCLUSTERED` `INDEX` `idx_nc_UnitPrice` `ON` `dbo.OrderDetail(UnitPrice)` `on` `IndexStorage` `--step6:再次查看页信息我们发现只有indexid=1的pagefid=1，也就是说聚集索引都在TESTDB.MDF这个文件上，` `--而indexid=2,3,4所对应的pagefid=3，表明已经将索引建立到IndexStorage这个filegroup上面去了，对应的是IndexStorage.ndf这个文件。` `dbcc ind ( TESTDB, [dbo.OrderDetail], -1)` `--step7:创建复合索引，` `CREATE` `NONCLUSTERED` `INDEX` `idx_nc_com` `ON` `dbo.OrderDetail(SalesOrderID,CarrierTrackingNumber,UnitPrice)` `--step8:默认情况下会使用Primary这个filegroup，filefid=1.` `dbcc ind ( TESTDB, [dbo.OrderDetail], -1)`

总结：

在分区上创建聚集索引，聚集索引不要放在IndexStorage这个filegroup当中，因为聚集索引就是数据本身。如果将聚集索引on IndexStorage的话，那么所有数据都将会在IndexStorage这个filegroup所对应的文件上。
在创建非聚集索引的时候，通过在创建索引语句的最后加上 on [filegroup]指定需要将这个索引放在哪一个filegroup当中，如果不加的话会使用默认filegroup，我们这里的默认filegroup是priamry。

1.4使用多个文件的好处

使用多个文件分布数据到多个硬盘中可以极大的提高IO性能.放在一个磁盘中基本没有效果。

场景描述

应用程序发来大量的并发语句在修改同一张表格里的记录，而表格架构设计以及用户业务逻辑使得这些修改都集中在同一个页面，或者数量不多的几个页面上。这些页面有的时候也被称为Hot Page。这样的瓶颈通常只会发生在并发用户比较多的、典型的OLTP系统上。这种瓶颈是无法通过提高硬件配置解决的，只有通过修改表格设计或者业务逻辑，让修改分散到尽可能多的页面上，才能提高并发性能。
在现实环境里，可以试想下面的情形。一个股票交易系统，每一笔交易都会有一个流水号，是递增且不可重复的。而客户发过来的交易请求，都要存储在同一张交易表里。每一个新的交易，都要插入一条新记录。如果设计者选择在流水号上建聚集索引（这也是很自然的），就容易遇到Hot Page的PAGELATCH资源瓶颈。在同一时间，只能有一个用户插入一笔交易。
怎样才能解决或者缓解这种瓶颈呢？

最简单的方法，是换一个数据列建聚集索引，而不要建在Identity的字段上。这样表格里的数据就按照其他方式排序，同一时间的插入就有机会分散在不同的页面上。
如果实在是一定要在Identity的字段上建聚集索引，建议根据其他某个数据列在表格上建立若干个分区（Partition）。把一个表格分成若干个分区，可以使得接受新数据的页面数目增加。

还是以上面那个股票交易系统为例子。不同的股票属于不同的行业。开发者可以根据股票的行业属性，将一张交易表分成若干个分区。在SQL Server里，已分区表（Partitioned Table）的每个分区都是一个独立的存储单位。分属不同分区的数据行是严格分开存储的。所以同一个时间发生的交易记录，因其行业不同，也会被分别保存在不同的分区里。这样，在同一个时间点，可以插入不同行业的交易记录。每个分区上的Hot Page（接受新数据插入的page）就不那么hot了。
在我的事例中，是有一张SalesOrderDetail表，其数据量很大，我希望按照UnitPrice这个字段进行分区。下面来看具体步骤。

step1：创建filegroup

在sql server中好像没有create filegroup的说法，只是在现成的数据库中添加filegroup而已。下面的代码中首先创建数据库，然后添加四个filegroup，tsql代码如下所示：

双击代码全选
1 2 3 4 5 6 7 8 9 10 11 12	`--step1------` `--创建数据库` `create` `database` `TEST` `USE MASTER` `GO` `--40万行分成5个文件组，PRIMARY加下面四个文件组，` `--命名规则:FG_数据库名_表名_字段名_流水号` `ALTER` `DATABASE` `TEST` `ADD` `FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_1;` `ALTER` `DATABASE` `TEST` `ADD` `FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_2;` `ALTER` `DATABASE` `TEST` `ADD` `FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_3;` `ALTER` `DATABASE` `TEST` `ADD` `FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_4;` `GO`

“sql server中filegroup与partition怎么使用”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！

分享文章：sqlserver中filegroup与partition怎么使用
路径分享：http://6mz.cn/article/jishie.html

网站建设知识