Ms SQL Server database partition for high performance website construction

What is database partition?
Database partitioning is a type of horizontal table segmentation. This technology is provided by SQL Server 2005 Enterprise Edition and later SQL Server versions. This type of horizontal table segmentation is different from that in SQL Server 2000, it is transparent to users, and users do not feel that the tables are split horizontally. (In 2000, the horizontal table division is to create n tables. For example, to create one table per month by time, the table names are different and a large view needs to be created at last)

For details about how to partition, please refer to database partition drill http://www.cnblogs.com/yukaizhao/archive/2008/05/07/ SQL _partition_test.html

Why partition?
Obviously, partitions are used to improve the read/write performance of the database and improve the efficiency of the database;

Can a partition always improve efficiency?
Partitioning is a double-edged sword and does not always improve efficiency, which is related to the specific situation.
The partition technology can improve the performance if it is used well. On the one hand, the partition divides a large piece of data into N small pieces, so that the query is quickly located on a small part, addressing in small blocks is much faster; on the other hand, the CPU is much faster than the disk Io, and the hardware has multiple disks, or raid (Redundant Array of cheap disks ), the database can drive the CPU to read and write different disks at the same time, which may improve the efficiency.
Partitions sometimes do not improve the read/write efficiency. For example, we often see the msdn example of partitioning by date field. In this instance, partitions are generated based on the record generation time, divide the data of one year into 12 partitions, one for each month. Such a partition does not allow the CPU to write data synchronously and improve the write performance, because in the same period of time, the CPU is always written to the disk corresponding to the latest partition. Another question is: Can partitions improve read performance? The answer is not necessarily. It depends on the fields to query. If the query is based on time and reports are generated based on time, such partitions will certainly improve the query efficiency, however, if a customer queries the customer's bill data for the last year, and the data is distributed to different partitions, the efficiency may not be improved, this depends on the high read performance of data distribution in the same partition, or the CPU reads data synchronously from several disks, and then the performance of data merging is higher, this is also related to the number of records reading data.

How to partition? What fields are used for partitioning?
How to partition is related to the involved business depends on what is the most frequent write and read operations on the business, and then consider the partition policy.

Since it is related to a specific business, we assume a business environment. If we want to create a forum, we will partition the posts and reply tables of the Forum.
The most common write operation in the Forum is 1) post 2) reply to post,
The most common read operations are:
1) display post details and post replies by Page Based on the post ID
2) read the Post list data by Page Based on the layout ID on the post layout post list page
How can partition be more appropriate? There are no accurate answers yet. I have two possible solutions. Write them down for discussion.
Solution 1. partition by post id region segments (1-w a partition,-w a partition ...), In this way, the reading speed of the detailed page of the post can be improved theoretically, but it does not benefit the write operation performance and may be helpful for reading the list page of the post based on the layout ID.
Solution 2. partition by layout ID. This improves the write performance. different partitions correspond to different la S. When two la s have post replies at the same time, they may write concurrently. Obtaining the page number of the post list based on the layout ID can also improve the performance, but it does not affect the performance of the post details page.

How much data requires partitioning?
I can only say one internal standard. If the number of records in a table exceeds 1000 million and the data volume increases by 1 million per month, partitions are required. If you have different opinions, please reply to the discussion.

For details about how to partition, please refer to database partition drill http://www.cnblogs.com/yukaizhao/archive/2008/05/07/ SQL _partition_test.html