Files and filegroups in SQL Server use

Transferred from: https://www.cnblogs.com/woodytu/p/5821827.html

In today's article, I want to talk about a very important topic in SQL Server: How SQL Server handles file groups for files. SQL Server creates 2 files for you when you create a simple database with the Create databases command:

• A data file (. mdf)
• A transaction log file (. ldf)

The data file itself is created in a single primary filegroup. By default, in the primary filegroup, SQL server stores the data (user tables, system tables, and so on) that are known. What is the purpose of the additional files and filegroups? Let's see.

Multiple filegroups

When you create additional filegroups for your data, you can store the tables and indexes you define in them, which will help you in many ways

• You can keep your primary file group very small.
• You can split your data into multiple filegroups (for example, you can use file partitioning in the Enterprise Edition).
• You can perform backup and restore operations at the filegroup level. This gives you more granular control over your backup and restore strategy.
• You can run the DBCC CHECKDB command at the filegroup level instead of the database level.

Usually, you should have at least one from a filegroup, where you can store the database objects you create yourself. You should not store other system objects created by SQL Server for you in the primary filegroup.

Multiple files

When you create your own filegroup, you also need to put at least one file in it. Additionally, you can add additional files to the filegroup. This will also improve your load performance because SQL Server will walk the data across all the files, known as the polling scheduling allocation algorithm (Round Robin Allocation algorithm). the first 64K is stored in the first file, the second 64k is stored in the second file, the third zone is stored in the first file (in your filegroup, you have 2 files).

Using this method, SQL Server can latch multiple copies of a bitmap page (Pfs,gam,sgam) in the buffer pool and improve your load performance. You can also use this method to resolve the same problem as the default configuration in tempdb. In addition, SQL Server ensures that all files of the filegroup are full at the same point in time-through the so-called proportional padding algorithm (proportional fill algorithm). Therefore, it is important that all of your files have the same initial size and auto-growth parameters in the filegroup. Otherwise, polling the dispatch allocation algorithm will not work properly.

Example Demo

Now let's look at an example of how to create an extra file group with multiple files in the database. The following code shows the CREATE DATABASE command you have to use to accomplish this task.

--Create A new databasecreate DATABASE multiplefilegroups on PRIMARY (--PRIMARY File Group NAME = ' Multiplefilegro UPS ', FILENAME = ' C:\Program Files\Microsoft SQL Server\mssql11. Sql2012\mssql\data\multiplefilegroups.mdf ', SIZE = 5MB, MAXSIZE = UNLIMITED, filegrowth = 1024KB),--secondary Fi Le Groupfilegroup FileGroup1 (--1st file in the first secondary file Group NAME = ' MultipleFileGroups1 ', Filenam E = ' C:\Program Files\Microsoft SQL Server\mssql11. Sql2012\mssql\data\multiplefilegroups1.ndf ', SIZE = 1MB, MAXSIZE = UNLIMITED, filegrowth = 1024KB), (--2nd fi Le in the first secondary File Group NAME = ' MultipleFileGroups2 ', FILENAME = ' C:\Program Files\Microsoft SQL Server \mssql11. Sql2012\mssql\data\multiplefilegroups2.ndf ', SIZE = 1MB, MAXSIZE = UNLIMITED, filegrowth = 1024KB) LOG on (--L og File NAME = ' Multiplefilegroups_log ', FILENAME = ' C:\Program Files\Microsoft SQL Server\mssql11. Sql2012\mssql\data\multiplefilegroupS.ldf ', SIZE = 5MB, MAXSIZE = UNLIMITED, filegrowth = 1024KB) GO 

After creating the database, the question is how do you put a table or index into a specific filegroup? You can use the ON keyword to create a filegroup, as shown in the following code:

CREATE TABLE Customers (   FirstName char () NOT NULL,   LastName char (a) not NULL,   Address CHAR (+) not null,< C5/>zipcode CHAR (5) Not NULL,   Rating INT not NULL,   ModifiedDate DATETIME not NULL,) on [Filegroup1]go

Another option, you mark a specific filegroup as the default filegroup. SQL Server then automatically creates a new database object in a filegroup that does not have the ON keyword specified.

--FileGroup1 Gets the default filegroup, where new database objects--'ll be createdalter database multiplefilegroups MO Dify FILEGROUP FileGroup1 Defaultgo

This is the method I usually recommend because you don't need to think about it after you've created your database object. So now let's create a new table, which is automatically stored in the FILEGROUP1 filegroup.

--the table would be created in the file group "FileGroup1" CREATE table Test (    Filler CHAR (8000)) GO

Now let's do a simple test: We insert 40,000 records into the table. 8K size per record. So we inserted 320MB data into the table. This is the polling scheduling assignment algorithm that I just mentioned, and it will do the following: SQL Server distributes data between 2 files: The first file has 160M of data, and the second file has 160M of data.

--Insert 40.000 Records, results in about 312MB data (40.000 x 8kb/1024 = 312,5MB)--they is distributed in a Round-ro Bin fashion between the files in the file group "FileGroup1" – each file would get about 160MBDECLARE @i INT = 1WHILE (@i & lt;= 40000) BEGIN    INSERT into Test VALUES    (        REPLICATE (' x ', 8000)    )        SET @i + = 1ENDGO

Next you can look at the hard drive and you will see 2 files in the same size.

When you put these files on a different physical hard disk, you can access them at the same time. That's the power of having multiple files in a filegroup.

You can also use the following script to get information about a database file.

--View database file and filegroup information select    name as [database_name],    count (*) as [datafiles],    count (DISTINCT data_space_id) as [Filegroups],    SUM (size) *8/1024 as [Size (MB)]    --default kbfrom sys.master_fileswhere [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 Filestreamgroup by [Database_id],name; GO

　　

--Retrieve file statistics information about the created database Filesdeclare @dbId intselect @dbId = database_id from S ys.databases WHERE name = ' multiplefilegroups ' SELECT     sys.database_files.type_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_idGO

Files and filegroups in SQL Server use