Lay the foundation for sqlserver-hard disk fragmentation and index fragmentation

1. Data File fragments
   Two main factors affecting disk read performance: track recording time and polling latency.
   When querying data, there are two disk read Methods: sequential read and random read. Random read occurs when a table or index is scanned, and sequential read occurs when an index is used to search for data. When there are a large number of fragments in the data file, the random read will not be greatly affected, because sqlserver will randomly read the data pages used by the table regardless of the logical order of the records, this is the so-called pre-read method. In sequential read, the corresponding records must be read in the logical order of records. If the data pages that are logically adjacent to each other are not physically distributed, the performance will be greatly compromised by the back-and-forth movement of the head. This is why we sometimes see that table scanning is more efficient than index search.
   When creating a database, we specify an initial size and incremental size for the data file and Log File respectively. If these files are all in separate logical partitions, no disk fragments will be generated. However, if there are other database files in the partition where each file is located. Disk fragments are generated as these files grow, as shown in:
    
   To prevent the generation of these fragments, we should set the size of the file to be larger each time to prevent the generation of so many small fragments. However, if the file grows too large each time, especially when the system is busy, the database performance will inevitably be affected. In order to quickly complete file growth, sqlserver uses Windows's instant file initialization function to quickly complete this task. To use instant file initialization, you must run the MSSQLServer service account under the Windows Account and assign the Windows se_manage_volume_name privilege to the account. This permission is assigned to the Windows Administrator group by default. If you have System Administrator privileges, you can assign this permission by adding a Windows account to the "Perform Volume Maintenance Tasks" security policy. MSSQLServer is started on the LocalSystem account by default, but the se_manage_volume_name privilege of this account is disabled. See http://msdn.microsoft.com/en-us/library/ms684190 (vs.85). aspx
   Conclusion: Perform disk fragmentation on a regular basis and assign an appropriate initial size to the data file. Develop a task plan to adjust the size of the data file based on the actual size of the current data when the system is idle, reducing the overhead caused by file growth when the system is busy.
2. Log File fragments
   Unlike data files, log files cannot be self-grown by using instant file initialization. Therefore, it takes a lot of time to allocate a large amount of self-growth. During this operation, all inset, delete, and update operations will be blocked. Then the overall performance of the database will be greatly affected when the database is disconnected. Just like when a highway suddenly gets congested. In the system, these log files are divided into multiple virtual log files (VLF). You can use DBCC loginfo to view the number of VLF in your current log file. If a large number of results are returned, it indicates that you should maintain the log. This is the same as the disk fragmentation of data files, which will seriously affect the performance. This quantity is determined by the overall size of the log file and the increment used for expansion logs. We cannot control this quantity.
   However, because logs are written in sequence, the real disk fragmentation does not have a significant impact on performance. If your Incremental settings are too small, the VLF will be affected due to frequent Log File adjustment. If the increment you set is too large, it will take up too long file allocation time. Therefore, the best way is to control your transactions as short as possible. At the same time, regularly back up your logs so that the logs can be truncated. This prevents the performance overhead caused by self-growth of log files. There has always been a misunderstanding that the database in the full recovery mode will not automatically cut off the transaction log. If you have never performed a full backup of this database, it can also automatically cut off transaction logs.
   Conclusion: the smaller the VLF, the better. The recommended number is no more than five. Back up transaction logs at regular intervals to cut off the logs as soon as possible for later use.
3. Internal and external fragments of the Index
   All these fragments are logical fragments. I have been discussing index fragmentation all day. I believe this is something everyone should be aware. There are no more details. In summary, internal fragments are affected by the page fill level. If there are too many fragments, the actual number of pages occupied by the table is much larger than that without fragments. Therefore, more I/O operations may occur during table scanning, but the index search will not be greatly affected. External fragments are caused by inconsistent logical order of the page and physical order on the hard disk, or discontinuous partitions. In this case, if the index is used for range search, it will cause the head to move back and forth because it should be recorded in the logical order of records. For more information about index fragmentation maintenance, see the online documentation.
4. File directory storage and file name requirements
   When a file is deleted, the corresponding search or chkdsk.exe command is executed in the metadata of the object to complete the corresponding task. Therefore, if there are too many files or too many directory layers, it will take a longer time to complete. It is recommended that the number of files should not exceed 100,000. Of course, we will never reach this number. At the same time, for Versions later than windwos nt, to provide backward compatibility, after you modify any file in the directory, a 8.3 format file name will be generated for long file names that do not conform to the 8.3 file format. If your directory contains hundreds of long file names, this may cause some performance loss. Therefore, if a 16-bit program is not running on the machine, you can use the registry to set NtfsDisable8dot3NameCreation to 1 and disable generating 8.3 file names. The Registry location is as follows: HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Control \ FileSystem \ NtfsDisable8dot3NameCreation. When will the log file and data file be modified? If you are not afraid to bury your hard disk, create a new northwind database before running each script. You can run the scripts under explain. This example also demonstrates the efficiency of insert into and select.
   Use northwind;
   Go
   Select * into my_customers
   From DBO. Customers where 1 = 0
   Go
   Insert into my_customers
   Select C1 .*
   From DBO. Customers C1, DBO. Customers C2, DBO. Customers C3
   -- Observe the growth of data files and log files before and after running
   -- Insert into is fully recorded in the log, and we find that
   -- Log files have grown a lot. I have grown to more than MB.
   -- After the northwind database is created, run the following script:
   -- Select into, as a large batch operation, only some transactions are recorded.
   -- Therefore, the log growth is not very large, and I grew to 4 MB.
   -- In terms of performance, select into is more efficient than insert.
   Select C1 .*
   Into my_customers
   From DBO. Customers C1, DBO. Customers C2, DBO. Customers C3

5. Hard disk formatted cluster size settings
   The customer gives us a new server. What we can adjust most is the hard disk. The CPU and memory are there, and the customer says there is no better machine. At the same time, hard disk I/O efficiency is also a key factor affecting query performance. Sql2005 has higher and higher requirements on tempdb. if conditions permit, generally, tempdb, data files, index files, and full-text directories are stored in an independent RAID 5 array (sometimes msftesql will suspend the service because the disk I/O is too high ), log files are stored in Raid 1 + 0 or raid 1, and the operating system and SQL Server are stored in Raid 1. The default sector size of a hard disk is 512 bytes. When formatting a new hard disk, which of the following cluster sizes is the most suitable? What is the size of the strip of the array?
   Because a data page is 8 KB, the data page is managed by the extended partition internally. An extended partition contains eight logically consecutive pages. Partition management is through the global allocation ing page (GAM, only save tables with more than 8 pages, unified partition) and the shared global allocation ing page (SGAM, tables with less than 8 data pages and mixed partitions are saved. Gam and SGAM are the 2nd pages of a data file. Each GAM and SGAM can manage 4 GB pages, and each 4 GB will add a GAM and SGAM. When you create a new database, use the DBCC page command to observe the two pages. You can see that the database has been allocated many extended partitions and some partitions are retained. After a new record is added during table creation, if the table occupies less than 8 data pages in total, it will be allocated to SGAM, and more than 8 pages will be allocated to the GAM partition. As mentioned above, the external fragmentation of the index is caused by inconsistent logical order of the page with the physical order on the hard disk or the discontinuous partition. Therefore, if we set the cluster size to 64 K, it is exactly the same as the size of a partition. Once this partition is used by a table, it cannot be used by another table. This reduces the external fragmentation of the data page, but the discontinuous partitions cannot be avoided. So set the cluster size to k? Because when reading data, the disk is read based on the size of the cluster. If the cluster is too large, a lot of useless content will be read at a time. Even if you only read one record, sqlserver will still read the entire page of the record. At this time, the actual disk is read 64 K. However, because the cluster is a continuous sector, the impact on the performance of read-only data is negligible. Because the disk is mainly affected by seek and polling delay.
   I am not very clear about the internal working mechanism for setting the Strip capacity in raid. The conclusion is only as follows: 256 K. However, many introductions on the Internet say that the database application should be smaller than the cluster size, which is inconsistent with the description in Microsoft's document below. For more information, see: http://www.microsoft.com/whdc/archive/subsys_perf.mspx
6. Enable write cache for your hard disk
   Without a dedicated cache controller, this increases the disk I/O efficiency, but increases the risk of data loss. However, this does not cause data inconsistency. Let's take a look at the transaction operation process. It uses the pre-write transaction log (WAL) method to ensure acid. :
   
   After the transaction is committed, the changes are first reflected in the transaction log, which may still exist in the disk cache. If a sudden power failure occurs, the checkpoint operation does not rush to write the committed transaction into the data file. After the service is restarted, the log file does not actually contain the committed transactions. The redo operation fails and your committed transactions are lost. However, if the transaction log is powered off after being written to the disk from the cache, data will not be lost. If the log file is saved in the cache, the data file has been written to the disk from the cache. At this time, the data will not be lost, but the transaction records you submitted will not be seen in the log. Because the data is written to the disk at 8 K, that is, 16 sectors. If data is written to only some sectors, the power is down. At this time, we will receive the 824 error, because the page checksum is incorrect, so that we cannot read this page. In the page_verity option, you can set the database checksum, torn_page_detection, and none options. The overhead decreases sequentially, and the security decreases sequentially. Every time a checksum error occurs, a record is obtained in MSDB. DBO. suspect_pages. If this error occurs and you do not have a backup, you can ignore this page by running the DBCC command at the risk of data loss.
   All of the above comments. If you have any objections, please correct them!