SQL Server query performance optimization-heap tables, shards, and indexes (2)

This article summarizes the SQL Server query performance optimization-heap tables, fragments, and indexes (I.

First, the SQL Server query performance is optimized. The key information displayed after set statistics io in heap table, fragment, and index (I) is as follows:

Table 't_epz_inout_entry_detail '. 1 scan count, 4825 logical reads, 6 physical reads, and 19672 pre-reads.

This sentence is explained. (Some content comes from the network, and some content is understood internally .)

 

When the SQL server database engine encounters a query statement, the SQL SERVER database engine generates an execution plan (occupying CPU and memory resources) separately ), at the same time, the storage engine reads IAM to generate a list of disk addresses that must be read. This allows SQL Server to optimize its I/O to large-scale ordered reads based on their locations on the disk. The data to be retrieved from the disk (occupying I/O resources, which is pre-read). Note that the two steps are parallel, in this way, SQL server allows computing to overlap with I/O, making full use of CPU and disk, thus improving performance.

 

Scan count:The number of visits to the involved table or the number of scans of the involved index when querying data. In our example, no matter whether it is a table scan (Example 1 and Example 2) or an index scan (Example 3), only one table or index is accessed, this information is not very useful because the query does not include the connection command. However, if the query contains one or more connections, this information is very useful.
The scanning count of a table outside the cycle is 1, but the value of the table in the cycle is the number of cycles. As you can imagine, for a table in a loop, the smaller the scan Count value, the fewer resources it uses, and the higher the query performance. Therefore, when adjusting the performance of a query with a connection, you need to pay attention to the scan Count value. When adjusting the value, observe whether it increases or decreases.

Logical read:This is the most useful data provided by the set statistics io or set statistics time command. We know that before SQL Server operates on any data, it must first read the data page or index page of the data from the disk, and store the data page or index page in the data buffer cache.
So what is the significance of logical read? Logical read refers to the number of pages that SQL Server must read from the data buffer cache to obtain the query results. When a query is executed, SQL Server does not read more or less data than the actual requirement. Therefore, when the same query is executed on the same dataset, the obtained logical read numbers are always the same.
Why is it important to know the logical read value when SQL Server executes a query during query performance adjustment? This is because this value does not change every time the same query is executed. Therefore, when tuning the query performance, this is a good standard for measuring whether your adjustment measures are successful.
When the query performance is adjusted, if the logic read value drops, it indicates that the server resources used for the query are reduced, and the query performance is improved. If the logical read value increases, the adjustment reduces the query performance. When other conditions remain unchanged, the less logical reads a query uses, the higher the efficiency, and the faster the query speed.

Physical read:What I want to talk about here may seem a little self-contradictory at the beginning, but as long as you think twice, you will understand the true meaning.
Physical read means that before performing a real query operation, SQL Server must read the required data from the disk to the data buffer cache. Before SQL Server starts to execute a query, the first thing it needs to do is to check whether the required data is in the data buffer cache. If so, it will read the data from it. If not, SQL Server must first read the required data from the disk to the data buffer cache.
As we can imagine, SQL Server requires more Server resources when performing physical reads than executing logical reads. Therefore, we should avoid physical read operations as much as possible.
The following part sounds confusing. When tuning the query performance, you can ignore physical reads and focus only on logical reads. You will be bored. Didn't you say that physical reads require more server resources than logical reads?
This is indeed the case. The number of physical reads required by SQL Server during query execution cannot be reduced through performance adjustment. Reducing the number of physical reads is an important task of DBA, but it involves adjusting the performance of the entire server, not just the query performance. When adjusting the query performance, we cannot control the cache size of the data buffer, the server's busyness, and whether the data required to complete the query is in the data buffer or on the disk, the only data we can control is the number of logical reads required to obtain the query results.
Therefore, in the query performance adjustment, we can safely ignore the physical read value provided by the set statistics io command. (One way to reduce the number of physical reads and speed up the running of SQL Server is to ensure that the physical memory of the Server is sufficient .)

Pre-Read:Like physical reads, this value is also useless in query performance tuning. Preread indicates the data page or index page that SQL Server reads from the disk when executing the pre-read mechanism. To optimize its performance, the SQL Server Data Engine first predicts the data and index pages required for executing the query execution plan, and then reads them into the buffer cache before the query actually uses these pages. Based on the accuracy of SQL Server's prediction of data requirements, pre-read data pages may be useful or useless.

Note: a buffer zone is an 8 KB Memory Page.

 

Most of the above texts are from the internet. I have made a few changes to some of them, or I have my own understandings.

 

　

 

Second: Conclusion on the impact of fragmentation on performance:

The basic unit of data storage in SQL Server is a page, which contains 8 KB data. Disk I/O operations are performed on pages. That is to say, the basic unit for SQL Server to read or write data is data pages. Eight consecutive pages form a partition (extent ). Data insert and update operations can cause split pages to generate fragments. If the split two pages are in the same region, this fragment is called an internal fragment. If the split two pages are in different regions, this fragment is called an external fragment.

I. Both internal and external fragments have a negative impact on data retrieval performance.

1. the generation of internal fragments distributes data in a large number of pages, which increases the disk I/O operations required to read pages to the memory, increases the number of logical reads to retrieve data from memory.

2. External fragments cause the index page on the disk to be discontinuous. The new leaf page and the original leaf page are far away. The physical order is different from the logical order. For better performance, the sequence I/O is preferred because it can read the entire partition (8 8 KB pages) in one disk I/O ). Non-consecutive pages require non-sequential or random I/O operations to read data from the disk. A Random I/O can only read one page (8 KB ).

 

2. In the heap table, when a record row in the middle of the data link is deleted, an empty page will appear. As the empty page accumulates, the utilization of the zone will also decrease, resulting in internal fragmentation and external fragmentation. An indexed table may also contain external fragments. For example, if a row is inserted in an existing clustered index, this row causes the existing page space to be unable to accommodate new rows, leading to paging, if the two pages after the page are exactly distributed in two zones, it is an external fragment. When external fragments exist, the following problems may occur: deadlocks often occur during table processing; using Large I/O operations or increasing the size of the I/O buffer cannot change the slow I/O speed.

 

3. A table with an index will be displayed on pages due to the inserted records. However, when a record is deleted, the page will be released. to form data across several zones. to access the data, you must traverse several zones, which will lead to the increase of I/O operations, the query record time is greatly extended, and the database performance is relatively high at the beginning, however, after a period of time, the performance may degrade. In fact, the closer the data is arranged in the storage space, the faster the SQL Server accesses. Eliminating fragments helps improve the system performance and make more effective use of the data storage space. (For example, the access speed of Example 2 is faster than that of Example 1 ).

 

4. For queries that scan some or all tables, these table fragments may cause additional page reads, which prevents parallel data scanning.