性能调优10:调整分区

未分区的表,相当于只有一个分区,只能存储在一个FileGroup中;对表进行分区后,每一个分区都存储在一个FileGroup,或分布式存储在不同的FileGroup中。对表进行分区的过程,实际上是将逻辑上完整的一个表,按照特定的字段拆分成多个分区,分散到相同或不同的FileGroup中,每一个部分叫做表的一个分区(Partition),一个分区实际上是一个独立的,内部的物理表。也就是说,分区表在逻辑上是一个表,而在物理上是多个完全独立的表。

一,分区对性能的提升

分区对性能的提升,主要体现:系统的物理硬盘分担IO,把资源的争用局限在特定的分区中。

1,分摊IO

对表分区后,把表数据分散到不同的文件组中,每个文件组可以存放在不同的物理硬盘上,这就使得数据的查询可以分摊到不同的物理硬盘上,以并发方式调用各个硬盘的IO资源,也就是说,把数据分配给多个物理硬盘,这些物理硬盘同时读写数据,成倍提升数据读写的速度。

当查找的数据位于某一个分区内时,那么分区使得对数据的操作被局限在一个分区中,而不需要从整个数据集中进行读写,从而减少读写数据所需要的IO次数。

2,降低锁粒度

当表分区后,加锁的粒度从表级别降低到分区级别,这使得对一个分区执行更新操作,同时不会影响另一个分区的读取操作。因此,分区可以降低并发查询系统产生死锁和阻塞的概率,增加数据操作的并发度,从整体上提高系统的性能。

二,调整分区

分区能够提升性能,就在于把数据分散,如果数据不能分散到不同的物理硬盘,那么对性能的提升将大打折扣,因此,在对表进行分区时,应尽量使得每个分区存储的数据相同。分区大小增加的速度跟文件组所在的硬盘空闲空间有关,空闲空间越大,SQL Server分配给该空间的数据量越大,导致该分区的大小增加越快。为了使每个分区的数量尽量均衡,应使得每个文件组的大小保持相同,不同的物理硬盘容量也尽量保持相同。

另外,数据访问的频次是不同的,有些数据可能被经常访问,而有些数据很少被访问。根据数据被访问的频次,可以把常用的数据切换到性能最好的文件组中,也就是说,该文件组中的文件创建在性能最好的硬盘上,把不常用的数据切换到性能较差的文件组中,这样,常用数据的访问会以最快的速度读写。对于那些归档的数据,可以使用分区切换到临时表,再转移到备用的数据库服务器中。

还有,要合理选择分区列,尽量把可能同时修改的数据分散到不同的分区中,减少资源争用,使互斥的操作在不同的分区同时进行,增加系统执行查询的并发度。

调整分区,不仅以来业务逻辑,还要对分区的统计数据有所了解。

1,查看分区

通过系统视图:sys.partitions 查看分区对象(Table或index)的基本信息,在该视图中,如果index_id=0,表示基础表(Underlying Table)是堆(Heap)结构,堆表是没有创建的聚集索引的存储结构;如果index_id=1,表示基础表是平衡树(B-Tree)结构,在堆表上创建聚集索引,可以把堆表结构转换为平衡树(B-Tree)结构;如果index_id>1,表示索引是非聚集索引。由于基础表要么是堆表结构,要么是B-Tree结构,因此,index_id只会是0,或者1,不可能同时存在1和0。

data_compression 字段表示每个分区的数据压缩类型,rows字段表示每个分区的近似的数据总行数。通过统计rows字段,能够快速统计基础表近似的总的数据行数量,设置条件 index<=1,表示只统计聚集索引或堆表的总数据行数量。

select sum(rows) as ApproximateTotalRows
from sys.partitions
where object_id=object_id('xx.yyy','U')
    and index_id<=1

2,统计每个分区占用的存储空间

在做分区时,应尽量保证每个分区的数据行总量均匀分布,每个分区占用的存储空间均匀分布,避免单个分区过大,系统视图 sys.dm_db_partition_stats 能够查看每个分区所占用的Pages数量。

select ps.partition_id,
    ps.object_id,
    ps.index_id,
    ps.partition_number,
    ps.in_row_data_page_count,
    ps.in_row_used_page_count,
    ps.in_row_reserved_page_count,
    ps.lob_used_page_count,
    ps.lob_reserved_page_count,
    ps.row_overflow_used_page_count,
    ps.row_overflow_reserved_page_count,
    ps.used_page_count,
    ps.reserved_page_count,
    ps.row_count
from sys.dm_db_partition_stats ps
where ps.object_id=object_id('xx.xx','U')

3,统计每个分区对象的占用的总的Pages数量

select 
    ps.object_id,
    ps.index_id,
    sum(ps.in_row_data_page_count) as data_pages,
    sum(ps.used_page_count) as used_pages,
    sum(ps.reserved_page_count) as reserved_pages
from sys.dm_db_partition_stats ps
where ps.object_id=object_id('xx.xx','U')
group by ps.object_id,ps.index_id

4,查看每个分区的分配单元(Allocation Unit)

SQL Server为每个分区分配了一个分配单元(allcotion unit),用于为该分区分配存储空间,通过系统内部视图:sys.system_internals_allocation_units, 能够查看该alloction unit分配的Page类型等信息。

select *
from sys.system_internals_allocation_units
where container_id=72057621135294464  --partition id

参考文档:

sys.dm_db_partition_stats (Transact-SQL)

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
posted @ 2019-01-17 09:04  悦光阴  阅读(987)  评论(0编辑  收藏  举报