Database Optimization Practice: File, file group, partition chapter

Optimization techniques are primarily for DBAs, but I think even developers should have these skills because not every development team is equipped with a dedicated DBA.

Nineth Step: Reasonable organization of database filegroups and files

When you create a SQL Server database, the database server automatically creates a series of files on the file system, and every database object that you create is actually stored in those files. SQL Server has the following three types of files:

1). mdf file

This is the most important data file, each database can have only one master data file, all system objects are stored in the main data file, if you do not create a secondary data file, all user objects (user-created database objects) are also stored in the master data file.

2). NDF Documents

These are secondary data files that are optional and store objects that are created by the user.

3). ldf file

These are transaction log files, ranging in number from one to several, where the transaction log is stored.

By default, the main data file and transaction log files are created automatically when you create the SQL Server database, and you can modify the properties of these two files, such as the Save path.

File groups

For ease of management and better performance, data files are typically grouped properly, and when a new SQL Server database is created, the primary filegroup is automatically created, the primary data file is included in the primary filegroup, and the primary filegroup is set to the default group, so all new The user objects that are built are automatically stored in the primary filegroup (specifically stored in the master data file).

If you want to store your user objects (tables, views, stored procedures, functions, and so on) in a secondary data file, you need to:

1 Create a new filegroup and set it as the default file group;

2 Create a new data file (. ndf), which is attributed to the new filegroup created in the first step.

objects that you create later are all stored in the secondary file group.

Note: The transaction log file does not belong to any filegroup.

File/File group organization Best Practices

If your database is small, the default file/filegroup should meet your needs, but if your database becomes large (assuming 1000MB), you can (should) adjust the file/filegroup for better performance, and the best practices for adjusting files/filegroups are as follows:

1 The primary filegroup must be completely independent, it should only store system objects, all user objects should not be placed in the primary filegroup. The primary filegroup should also not be set as the default, separating the system objects from the user objects to achieve better performance;

2 If you have more than one hard disk, you can assign each file in each filegroup to each disk, which can achieve distributed disk I/O, greatly improve the speed of data reading and writing;

(3) Put the frequently accessed tables and their indexes in a separate filegroup so that reading the table data and indexes will be faster;

4 Place a table of frequently accessed columns containing the text and image data types into a separate filegroup. It is a good idea to place the text and image column data on a separate hard disk so that the speed is not affected by the text and image columns when retrieving the table's non-text and image columns ;

5 The transaction log files on a separate hard drive, do not share a hard disk with the data file, log operations are write-intensive operations, so it is important to ensure that log writes have good I/O performance;

6 The Read-only table is placed in a separate filegroup, and the write-only table is placed in a single filegroup, so that read-only tables are retrieved more quickly and write-only updates faster;

7 Do not overuse the "automatic Growth" feature of SQL Server, because the cost of automatic growth is actually very high, set the "automatic growth" value of a suitable value, such as a week, also do not overuse the "auto shrink" feature, it is best to disable automatic contraction, to manually shrink the database size, Or use a scheduling operation to set a reasonable time interval, such as one months.