Anatomy of SQL Server eighth Orcamdf now supports multi-data file database (translation)

Anatomy of SQL Server eighth Orcamdf now supports multi-data file database (translation)

http://improve.dk/orcamdf-now-supports-databases-with-multiple-data-files/

Orcamdf one of the newest features is a database that supports multiple data files. This requires minor changes to be made in the resolution above, in fact most of them are bug fix codes

As a result of supporting only a single data file. However, this does require some significant refactoring to leave Mdffile's main entry point, and now uses the database wrapper class to encapsulate a data file variable

Assigning proportional fills

Orcamdf supports the proportional fill schema for standard database tables, which have NDF files in addition to MDF files, which are in the primary filegroup, for example, you might create the following data file or schema

CREATE DATABASE    [sampledatabase] on  PRIMARY(NAME=N'sampledatabase_data1', FILENAME=N'C:sampledatabase_data1.mdf', SIZE=3072KB, FileGrowth=1024KB), (NAME=N'Sampledatabase_data2', FILENAME=N'C:SAMPLEDATABASE_DATA2.NDF', SIZE=3072KB, FileGrowth=1024KB), (NAME=N'sampledatabase_data3', FILENAME=N'C:SAMPLEDATABASE_DATA3.NDF', SIZE=3072KB, FileGrowth=1024KB)LOG  on(NAME=N'Sampledatabase_log', FILENAME=N'C:sampledatabase_log.ldf', SIZE=3072KB, FileGrowth= Ten%    )GO UsesampledatabaseGOCREATE TABLEMyTable (Aint Identity, Buniqueidentifier default(newid()), CChar(6000))GOINSERT  intoMyTableDEFAULT VALUESGO  -

This causes MyTable to scale up to three data files (the role of column C in order to have SQL Server allocate 100 pages to load the data so that three data files can be filled)

In order to resolve this situation, we need to do the following work

varFiles =New[]    {        @"C:sampledatabase_data1.mdf",        @"C:SAMPLEDATABASE_DATA2.NDF",        @"C:SAMPLEDATABASE_DATA3.NDF"    };using(vardb =NewDatabase (Files)) {    varScanner =NewDatascanner (DB); varresult = Scanner. Scantable ("MyTable"); Entityprinter.print (result);}

The result after the run is

Attention to all:

A (4 bytes) +b (16 bytes) +c (6,000 bytes) = 6020 bytes

Just a record page, the following is said, SQL Server after allocating a zone, a zone 8 pages, when a zone is allocated, SQL Server

Will go to the SAMPLEDATABASE_DATA2.NDF data file to continue assigning the page, the assigned value is 9~16, a zone is allocated and then to

SAMPLEDATABASE_DATA3.NDF data file continues to be assigned the page, the assigned value is 17~24

Since the increment will be up to 100, notice that column A is spaced because of the fact that we allocate it in a round robin manner in one area of each data file.

Id1~8 in the first data file, 9~16 at the end of the second data file 17~24 in the third data file. Because of this, page 25~32 is assigned in the first data file and continues like this

Because it is a heap table, we use file allocation order scanning, which leads us to get results 1~8,25~32,49~56,73~80,97~100 all from the first file, and then 9~16,33~40

Read from the second data file and then to the remaining pages of the last data file. Think it's weird, okay, SQL Server is exactly the same.

Do not understand the child shoes can look at this article "SQL Server allocation scan and range scan" or

Microsoft SQL Server 2008 Technology Insider: T-SQL query notes has a description

File group support

Orcamdf also supports the use of filegroups, including prorated padding in a separate filegroup, for example, you might create the following database and schema

CREATE DATABASE    [sampledatabase] on  PRIMARY(NAME=N'sampledatabase_data1', FILENAME=N'C:sampledatabase_data1.mdf', SIZE=3072KB, FileGrowth=1024KB)LOG  on(NAME=N'Sampledatabase_log', FILENAME=N'C:sampledatabase_log.ldf', SIZE=3072KB, FileGrowth= Ten%    )GOALTER DATABASE    [sampledatabase]ADDFILEGROUP[Secondfilegroup]GOALTER DATABASE    [sampledatabase]ADD FILE(NAME=N'Sampledatabase_data2', FILENAME=N'C:SAMPLEDATABASE_DATA2.NDF', SIZE=3072KB, FileGrowth=1024KB), (NAME=N'sampledatabase_data3', FILENAME=N'C:SAMPLEDATABASE_DATA3.NDF', SIZE=3072KB, FileGrowth=1024KB) toFILEGROUP[Secondfilegroup]GO UsesampledatabaseGOCREATE TABLEMyTable (Afloat default(Rand()), Bdatetime default(getdate()), Cuniqueidentifier default(newid()), DChar( the))  on [Secondfilegroup]GOINSERT  intoMyTableDEFAULT VALUESGO  -

This will cause mytable to be allocated proportionally to the second and third data files (column D is used to occupy the position, make sure that SQL Server allocates 100 pages to load the data so that the two data files in the filegroup are

Allocation fill) data is populated only for second and third data files, and the master data file is not affected

As explained in the previous example, the results are as follows

will continue until 100.

End of the eighth chapter

Anatomy of SQL Server eighth Orcamdf now supports multi-data file database (translation)