Shulou(Shulou.com)05/31 Report--

How to optimize the SQL Server server, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1. Data and log files are stored on separate disks

The operation of data files and log files will produce a large number of Ipicuro. Where possible, log files should be stored on a different hard disk from the data files in which the data and indexes are located to disperse IBO and also facilitate disaster recovery.

2.tempdb databases are stored separately on different disks

The tempdb database is a temporary database that can be used by all other databases. When you use select into to execute Orderby on columns that are not indexed, temporary tables are generated in the tempdb database to store intermediate data. Because creating and populating temporary tables can seriously degrade system performance, you should index the columns to be sorted whenever possible. At the same time, the tempdb database is shared by all users and applications, so if one user occupies all the space of the tempdb database, other databases will no longer be available. Where possible, the tempdb database should be placed separately on a faster hard disk or RAID array. Detach the tempdb database's iUnip O operation to speed up performance. The tempdb database should have the appropriate capacity to meet the needs of users. The space of the tempdb database should be allowed to grow automatically. If set to do not allow automatic growth, the operation cannot be completed when the query operation creates temporary tables that exceed the capacity of the tempdb database.

Properly set the growth rate of tempdb database, too small growth rate will produce more external fragments, will take up more resources.

3. Avoid the occurrence of hot data

Prior to SQLServer7.0, newly inserted rows of data were always placed at the physical end of the table on disk for tables that did not have a clustered index (heap table). If there are many concurrent users who are inserting or updating data on the table at the same time, this will make it possible to generate data hotspots at the end of the very busy table. The concurrent Icano operations focus on a small number of pages, which will lead to the decline of database performance.

In SQLServer, physical storage space for new data rows is allocated through PFS pages. The PFS page management algorithm distributes the insert operation to avoid generating data hotspots as much as possible.

When designing applications and databases, avoid establishing primary keys on naturally growing columns, which may lead to hot data.

4. Fewer data types

When designing tables, use as few data types as possible. The most information can be saved on such a data page. There are fewer data pages, and there are fewer Icano operations to retrieve data pages, so it will be more efficient.

5. Monitoring and defragmentation of space debris

The automatic growth of file space improves automatic management, but can lead to space debris. The physical space is no longer continuous with the logical space of the data. Regular monitoring and space debris defragmentation are beneficial to improve the performance of Imap O.

6. Use primary and secondary data files

One master data file for each database belongs to the master filegroup. For a database about the size of 1GB, one data file is enough. If there is a secondary data file, there is a pointer to manage the secondary data file in the primary data file.

When multiple data files are used, the primary data file is used to store system objects and tables, and the secondary data file is used to store user data and indexes. Where possible, primary data files and secondary data files can be stored separately on different disks to disperse the IWeiO.

After reading the above, have you mastered how to optimize the SQL Server server? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.