SQL Server optimization skills

1. Data and log files are stored separately on different disks.

Operations on data files and log files generate a large amount of I/O. If possible, log files should be stored on a hard disk different from the data files where the data and indexes are located to distribute I/O, and facilitate database disaster recovery.

　　2. The tempdb database is stored separately on different disks.

The tempdb database is a temporary database that all other databases may use. When select into is used and Orderby is executed on columns without an index, a temporary table is generated in the tempdb database to store intermediate data. Creating and filling temporary tables will seriously reduce system performance, so you should create an index for the columns to be sorted as much as possible. At the same time, the tempdb database is shared by all users and applications. Therefore, if a user occupies all the space of the tempdb database, other databases will no longer be available. If possible, the tempdb database should be placed separately on a faster hard disk or RAID array. Separates I/O operations on the tempdb database to accelerate performance. The tempdb database should have appropriate capacity to meet user needs. The automatic increase in the space of the tempdb database should be allowed. If it is set to not allow automatic growth, when the query operation creates a temporary table that exceeds the tempdb database capacity, the operation cannot be completed.

Appropriately set the growth margin of the tempdb database. A small growth margin will generate more external fragments and occupy more resources.

　　3. Avoid hotspot data

Before SQLServer7.0, for tables without clustered indexes (heap tables), newly inserted data rows are always placed at the physical end of the table on the disk. If a large number of concurrent users insert or update data to the table at the same time, this may lead to data hotspots at the end of a very busy table. Concurrent I/O operations are performed on a few pages in a centralized manner, which will lead to a decline in database performance.

In SQLServer, the physical storage space of new data rows is allocated through the PFS page. The PFS Page Management Algorithm disperses the insert operations to avoid data hotspots.

When designing application systems and databases, avoid creating primary keys on columns with natural growth, which may lead to hotspot data.

　　4. Fewer Data Types

Use as few data types as possible when designing tables. Such a data page can save the most information. There are fewer data pages and fewer I/O operations on the retrieved data pages, which results in high efficiency.

　　5. Monitor and organize space fragments

The automatic increase of file space increases the automatic management, but may lead to space fragmentation. The logical space of physical space and data is no longer consecutive. Regular monitoring and space fragmentation helps improve I/O performance.

　　6. Use primary and secondary data files

One primary data file of each database belongs to the primary file group. For a database of about 1 GB, a data file is enough. If there are secondary data files, the primary data file contains pointers for managing secondary data files.

When multiple data files are used, the primary data files are used to store system objects and tables, and secondary data files are used to store user data and indexes. If possible, the primary and secondary data files can be separately stored on different disks to distribute I/O.

If multiple data files are used, it is recommended that the primary data file store system data and secondary data files store user data and indexes, which will help improve I/O performance.