sqlserver⾥的⽂件和⽂件组使⽤
概要
那么你应该有多少个数据⽂件和⽂件组？没有正确的答案，⽽且甜蜜的地⽅会因你⽽异。
⼀些⼀般的经验法则：
随着数据库变⼤，您将更需要多个⽂件和⽂件组
多个⽂件组为您提供增强的可能性，以实现有针对性的灾难恢复，更轻松的可管理性和I / O⼦系统布局
每个⽂件组⾄少应有2-4个⽂件，tempdb是⼀种特殊情况
⽆论您做什么，不要忘记测试以出⼯作负载的最佳配置。
在今天的⽂章⾥，我想谈下SQL Server⾥⾮常重要的话题：SQL Server如何处理⽂件的⽂件组。当你⽤CREATE DATABASE命令创建⼀个简单的数据库时，SQL Server为你创建2个⽂件：
⼀个数据⽂件（.mdf）
⼀个事务⽇志⽂件（.ldf）
数据⽂件本⾝在有且只有⼀个主⽂件组⾥创建。默认情况下，在主⽂件组⾥，SQL Server存储素有的数据（⽤户表，系统表等）。那有额外的⽂件和⽂件组的⽬的是什么？我们来看下。
多个⽂件组
当你为你的数据创建额外的⽂件组，你可以在它们⾥⾯存储你定义的表和索引，这个会在多个⽅⾯帮助你
你可以保持你的主⽂件组很⼩。
你可以把你的数据分割到多个⽂件组（例如，你可以在企业版⾥使⽤⽂件分区）。
你可以在⽂件组级别进⾏备份和还原操作。这给你在你的备份和还原策略上更多细粒度的控制。
你可以在⽂件组级别运⾏DBCC CHECKDB命令，⽽不是数据库级别。
通常，你应该⾄少有⼀个从⽂件组，这⾥你可以存储你⾃⼰创建的数据库对象。你不应该在主⽂件组⾥存储SQL Server为你创建的其他系统对象。
多个⽂件
当你创建了你⾃⼰的⽂件组，你也要⾄少放⼀个⽂件进去。另外，你可以增加额外的⽂件到⽂件组。这也会提⾼你的负荷性能，因为SQL Server会散步数据在所有的⽂件间，即所谓的轮询调度分配算法（Round Robin Allocation Algorithm）。第⼀个64K在第⼀个⽂件存储，第⼆个64k在第⼆个⽂件存储，第三个区在第⼀个⽂件存储（在你的⽂件组⾥，你有2个⽂件时）。
使⽤这个⽅法，SQL Server可以在缓冲池⾥闩锁分配位图页（PFS,GAM,SGAM）的多个副本，并提⾼你的负荷性能。你也可以⽤这个⽅法解决在TempDb⾥默认配置的同个问题。另外，SQL Server也会确保⽂件组的所有⽂件在同⼀时间点满——通过所谓的⽐例填充算法（Proportional Fill Algorithm）。因此，在⽂件组⾥你的所有⽂件有同样的初始⼤⼩和⾃动增长参数⾮常重要。不然轮询调度分配算法就不能正常⼯作。
实例演⽰
现在我们来看下⼀个实例，如何创建额外⽂件组⾥有多个⽂件在⾥⾯的数据库。下列代码展⽰了你必须⽤到的CREATE DATABASE命令来完成这个任务。
-- Create a new database
CREATE DATABASE MultipleFileGroups ON PRIMARY
(
-- Primary File Group
NAME = 'MultipleFileGroups',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\MultipleFileGroups.mdf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
),
-- Secondary File Group
FILEGROUP FileGroup1
(
-- 1st file in the first secondary File Group
NAME = 'MultipleFileGroups1',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\MultipleFileGroups1.ndf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
),
(
-- 2nd file in the first secondary File Group
sqlserver2012数据库还原NAME = 'MultipleFileGroups2',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\MultipleFileGroups2.ndf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
(
-- Log File
NAME = 'MultipleFileGroups_Log',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\MultipleFileGroups.ldf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
GO
-------------向现有数据库添加⽂件组和⽂件
--使⽤sql语句添加⽂件组和数⽂件
--向E_Market数据库进⾏操作
USE E_Market
--添加组FG5
ALTER DATABASE E_Market ADD FILEGROUP FG5
GO
--为新建的⽂件组添加数据⽂件
ALTER DATABASE E_Market ADD FILE
(
name='FG5_E_Market_tada',                                  --数据⽂件逻辑名称
filename='F:\Prejoct\FG5_E_Market_tada.ndf',      --存放位置--辅助数据⽂件
size=5MB,                                                                --⽂件初始⼤⼩5mb
FILEGROWTH=10%,                                              --按百分之10增长
MAXSIZE=500MB                                                    --最⼤考验增到500mb
)
TO FILEGROUP FG5
-
-将FG5设为默认⽂件组
ALTER DATABASE E_Market
MODIFY FILEGROUP FG5 DEFAULT
GO
设为默认⽂件组就是等于在创建⽂件的时候，默认⽂件组是为FG5
把表或索引放到特定的⽂件组
创建完数据库后，问题是如何把表或索引放到特定的⽂件组？你可以⽤ON关键字⼈为制定⽂件组，如下代码所⽰：
CREATE TABLE Customers
(
FirstName CHAR(50) NOT NULL,
LastName CHAR(50) NOT NULL,
Address CHAR(100) NOT NULL,
ZipCode CHAR(5) NOT NULL,
Rating INT NOT NULL,
ModifiedDate DATETIME NOT NULL,
)
ON [FileGroup1]
GO
设置默认⽂件组
另⼀个选项，你标记特定⽂件组为默认⽂件组。然后SQL Server⾃动创建新的数据库对象在没有指定ON关键字的⽂件组⾥。
-- FileGroup1 gets the default filegroup, where new database objects
-- will be created
ALTER DATABASE MultipleFileGroups MODIFY FILEGROUP FileGroup1 DEFAULT
GO
这是我通常推荐的⽅法，因为你不需要再考虑，在创建完你的数据库对象后。因此现在让我们创建⼀个新的表，它会⾃动存储在FileGroup1⽂件组。
-- The table will be created in the file group "FileGroup1"
CREATE TABLE Test
(
Filler CHAR(8000)
)
GO
数据测试
现在我们进⾏简单的测试：我们插⼊40000条记录到表。每条记录8K⼤⼩。因此我们插⼊了320MB数据到表。
这是我刚才提的轮询调度分配算法，会进⾏操作：SQL Server会在2个⽂件间发放数据：第⼀个⽂件有160M的数据，第⼆个⽂件也会有160M的数据。
-- Insert 40.000 records, results in about 312MB data (40.000 x 8KB / 1024 = 312,5MB)
-- They are distributed in a round-robin fashion between the files in the file group "FileGroup1"
-- Each file will get about 160MB
DECLARE@i INT=1
WHILE (@i<=40000)
BEGIN
INSERT INTO Test VALUES
(
REPLICATE('x', 8000)
)
SET@i+=1
END
GO
接下来你可以在硬盘上看下，你会看到2个⽂件时同样的⼤⼩。
当你把这些⽂件放在不同的物理硬盘上，你可以同时访问它们。那就是在⼀个⽂件组⾥有多个⽂件的强⼤。
你也可以使⽤下列脚本获取数据库⽂件的相关信息。
查看数据库⽂件与⽂件组信息
--查看数据库⽂件与⽂件组信息
SELECT
name as [database_name],
COUNT (*) AS [DataFiles],
COUNT (DISTINCT data_space_id) AS [Filegroups],
SUM (size)*8/1024 AS [Size(MB)]    --default Kb
FROM sys.master_files
WHERE [type_desc] = N'ROWS' -- filter out log files/data_space_id 0
AND [database_id] > 0  -- filter out system databases
AND [FILE_ID] != 65537 -- filter out FILESTREAM
GROUP BY [database_id],name;
GO
-- Retrieve file statistics information about the created database files
DECLARE @dbId INT
SELECT @dbId = database_id FROM sys.databases WHERE name = 'MultipleFileGroups'
SELECT
sys.pe_desc,
sys.database_files.physical_name,
sys.dm_io_virtual_file_stats.* FROM sys.dm_io_virtual_file_stats
(
@dbId,
NULL
)
INNER JOIN sys.database_files ON sys.database_files.file_id = sys.dm_io_virtual_file_stats.file_id
GO

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。