Partitioning of SQL Server 2005

SQL Server 2005 is a database that Microsoft launched in the five years after the launch of SQL Server 2000. There has been a qualitative improvement over the SQL Server2000. It provides us with many new features, such as replication, partitioning, dynamic management views, CTE, performance Advisor, and so on.

This is how you would describe partitioned tables and partitioned indexes on Microsoft TechNet:

instruction partitioned tables and indexed data into cells that are distributed across filegroups in one database. Data is partitioned horizontally, so multiple sets of rows are mapped to a single partition. When you query or update the data, the table or index is treated as a single logical entity. All partitions of a single index or table must be in the same database.

Partitioned tables and partitioned indexes support all properties and features associated with designing and querying standard tables and indexes, including constraints, defaults, identity and timestamp values, and triggers. Therefore, if you want to implement a partitioned view that is local to the server, you might need to implement partitioned tables instead.

Deciding whether to implement a partition depends largely on the current size or future size of the table, how the table is used, and the degree to which the table performs user queries and maintenance operations.

Typically, a large table may be suitable for partitioning if the following two conditions are met:

The table contains (or will contain) a large amount of data that is used in many different ways.

Queries or updates cannot be performed on the table as expected, or the maintenance overhead exceeds the predefined maintenance period.

For example, if the data for the current month is primarily performed with INSERT, UPDATE, and DELETE operations, and the data for the previous month is primarily a SELECT query, it may be easier to manage the table if the table is partitioned by month. This is especially true if regular maintenance operations on a table are for only one subset of data. If the table does not have partitions, then you need to perform these operations on the entire dataset, which consumes a lot of resources. For example, through partitioning, a maintenance operation similar to index regeneration and defragmentation can be performed for a single month with write-only data, while read-only data can still be used for online access. As described above, partitioning is the ability to distribute operational pressure on data to individual partitioned filegroups, and each time the application accesses data only on a data partition, it can improve the performance of the database accordingly.

Find a table with a data volume of about 200W, which is a ledger type table that can be divided into 12 partitions based on the time date as the partitioning column, and the 200W data by month. Then execute the business stored procedures for testing:

SP exec proc_partitiontest @HCompany=N’0002’,@HOrg=N’00000000000000000223’,
@FiscalYear=N’2008’,@FiscalPeriod=N’01’,
@HWareHouseID=N’’,@SpeStock=N’’,@ofObject=N’’ ,......

Execution results Comparison:

Pre-partition: Execution time 52s, IO cost:

Table ' Partitionaccount '. Scan count 2, logical reads 1735, physical reads 0, Read-ahead reads 0, LOB logical reads 0, LOB physical reads 0, LOB Rea D-ahead reads 0.

After partition: Execution time 47s, IO cost:

Table ' Partitionaccount '. Scan count 2, logical reads, physical reads 0, Read-ahead reads 0, LOB logical reads 0, LOB physical reads 0, LOB read- Ahead reads 0.

Is the performance improvement very general, is the reason for the small amount of data? is the query application partitioning key not reasonable? For some reasons, this SP's specific wording can not be posted, please friends understanding. Here, please friends to talk about the key to zoning, thank you.