One of SQL Server's ideas for solving the hot page problem is using Table Partitioning.

One of SQL Server's ideas for solving the hot page problem is using Table Partitioning.

What is hot page?

ApplicationProgramA large number of concurrent statements are sent to modify or insert records in the same table. The table Architecture Design and user business logic make these modifications and inserts concentrated on the same data page,

Or a small number of data pages. These pages are also calledHot page. Such a bottleneck usually only occurs in a typical OLTP system with a large number of concurrent users.

 

Introduction to pagelatch:

To put it simply, after sqlserver reads data to the memory, the data is stored in "pages". If two users modify or insert data on the same page at the same time,

Sqlserver will put a pagelatch (lightweight) lock on the page. These locks are different from intention sharing locks, exclusive locks, row locks, and key locks. These latch locks only exist in the memory.

When user a wants to modify the data page number to 100, sqlserver will add a pagelatch lock on the page 100 to prevent other users from simultaneously modifying this page.

In order to get a modification order, after user a completes the modification, pagelatch will be released for user B to modify.

Generally, you can use the wait_type column of the following SQL statement to see which session needs to wait for the pagelatch lock to be obtained.

1 Select * FromSYS.[Dm_exec_requests]
 

 

After introduction of hotpage and introduction of pagelatch, do you know the bottlenecks and dangers caused by this hotpage?

Don't know? Okay, let's talk about it:

If a large number of concurrent users access the same data page at the same time, sqlserver will apply for release of pagelatch on this data page,

Continuously apply for and release. If sqlserver processes user insertion or modification requests slowly"Blocking ".

 

Generally, hotpage occurs in the following situations:

For example, there is a stock trading system where each transaction has a sequential number that is incremental and repeatable, and the customer sends a transaction request,

Must be stored in the same transaction table. A new record is inserted for each new transaction. If the designer chooses to create a clustered index on the serial number (this is also natural)

It is easy to encounter page latch resource bottlenecks of hot pages. At the same time, only one user can insert a transaction

 

How can we alleviate this bottleneck?

(1) the simplest method: Create a clustered index for a data column instead of the Identity field (for example, the transaction serial number above.

In this way, the data in the table will be sorted by other means, and the insertion at the same time will have the opportunity to be dispersed on different pages.

(2) If you must create a clustered index on the identity field, we recommend that you create Several partitions in the table based on another data column,

Dividing a table into Several partitions increases the number of pages that receive new data.

For example:

The above stock trading system is used as an example. Different stocks belong to different industries. Developers can divide a transaction table into Several partitions Based on the stock industry attributes.

In sqlserver, each partition of a partitioned table is an independent storage unit. Data rows belonging to different partitions are stored strictly separately.

Therefore, transaction records generated at the same time are stored in different partitions in different industries. In this way, you can insert

Transaction records. Hot pages on each partition (pages with new data inserted) are not so hot.

 

Therefore, creating a clustered index on the auto-increment column depends on the situation. Otherwise, hotpage oh o (partition _ partition) O will be easily encountered.

Supplement

Partition is a double-edged sword. If partition is used, the number of scans increases.
If the table has no partitions, scan the table once. For example, if the table has two partitions
Scan twice, and each partition requires 2 I/O. If the data is in partition 2
4 times I/O