sql server表分区_SQL Server 2016增强功能– SQL截断表和表分区

sql server表分区

The idea behind this article is to discuss the importance and the implication of SQL Partition and understand the SQL truncate command partitioning enhancements in SQL 2016

本文背后的想法是讨论SQL分区的重要性和含义，并了解SQL 2016中SQL 截断命令分区增强功能

One of the biggest challenges for a DBA is to identify the right candidate for table partitioning, as it requires expertise in design and implementation.

对于DBA来说，最大的挑战之一是确定合适的表分区候选者，因为它需要设计和实现方面的专业知识。

The following are the various truncating options available in SQL 2016

以下是SQL 2016中可用的各种截断选项

• Truncate individual partitions
  截断单个分区
• Truncate multiple individual partitions
  截断多个单独的分区
• Truncate a Range of partitions
  截断分区范围
• Truncate a Range with multiple individual partitions
  截断具有多个单独分区的范围

文章重点 (Article Highlights)

• Define the importance and the implication of SQL table partitioning
  定义SQL表分区的重要性和含义
• Identify the right candidate for table partitioning
  确定合适的表分区候选者
• Provide inline comparison of features available in the different editions of SQL 2016
  提供SQL 2016不同版本中可用功能的内联比较
• Discuss the SQL truncate partition enhancements in SQL 2016
  讨论SQL 2016中SQL截断分区增强功能
• Demonstrate the SQL truncate table partition use cases
  演示SQL截断表分区用例

SQL分区 (SQL Partition)

Let’s understand the objective of SQL partitioning, why we need partitioning, and the factors that are vital to decide on a Table Partitioning Strategy.

让我们了解SQL分区的目标，为什么需要分区以及决定表分区策略至关重要的因素。

Partitions are logical mapping of the physical data. A well-designed partition gives us an option to scale out the data. It optimizes the performance and simplifies the management of data by partitioning each table into multiple separate partitions. Although, not all tables are good candidates for partitioning. If the answer is ‘yes’ to all or most of the following questions, table partitioning may be a viable database design strategy; if the answer is ‘no’ to most of the following questions, table partitioning may not be the right solution for that table.

分区是物理数据的逻辑映射。 精心设计的分区为我们提供了扩展数据的选项。 通过将每个表划分为多个单独的分区，它可以优化性能并简化数据管理。 虽然，并非所有表都适合分区。 如果对以下所有或大多数问题的回答为“是”，则表分区可能是可行的数据库设计策略； 如果对以下大多数问题的回答为“否”，则表分区可能不是该表的正确解决方案。

• Is the table large enough?
  Large fact tables are good candidates for table partitioning. If we have millions or billions of records in a table, we may see performance benefits from breaking that data up into logically smaller chunks. Since smaller tables are less susceptible to performance problems, the administrative overhead of maintaining the partitions will outweigh any performance benefits we might see by partitioning.
• 桌子够大吗？
  大事实表是表分区的理想选择。 如果我们在一个表中有数百万或数十亿条记录，我们可能会发现将数据分解为逻辑上较小的块可以提高性能。 由于较小的表不太容易受到性能问题的影响，因此维护分区的管理开销将超过我们通过分区可能看到的任何性能优势。
• Does your application or system maintain a window of historical data? 您的应用程序或系统是否维护历史数据窗口？
  Another consideration for partition design is your organization’s data retention policy. For example, your data warehouse may require that you keep the data from the past twelve months. If the data is partitioned by month, you can easily drop the oldest monthly partition from the warehouse and load current data into the most recent monthly partition.
  分区设计的另一个考虑因素是组织的数据保留策略。 例如，您的数据仓库可能要求您保留过去十二个月的数据。 如果按月对数据进行分区，则可以轻松地从仓库中删除最早的每月分区，并将当前数据加载到最新的每月分区中。
• Are you experiencing performance issues on database maintenance tasks? 您是否在数据库维护任务中遇到性能问题？
  As we deal with larger tables, it is more difficult to perform the rebuilding operations. In such scenarios, one can rely on table partitioning, so that maintenance operations can be seamlessly performed.
  当我们处理更大的表时，执行重建操作会更加困难。 在这种情况下，可以依靠表分区，以便可以无缝执行维护操作。
• Can the data be divided into equal parts based on a certain criteria? 可以根据一定的标准将数据分为相等的部分吗？
  Choose the partition criteria that will divide the data as evenly as possible. This will let the SQL Query Optimizer to decide and select the best plan for query execution. The monthly partition as mentioned in the second point is a good example for this.
  选择将尽可能均匀地划分数据的分区标准。 这将使SQL查询优化器可以决定并选择最佳的查询执行计划。 第二点提到的每月分区就是一个很好的例子。
• Is concurrency