SQL Server 2005分区表性能问题及分区原则

时间:2022-01-24 00:38:27
我在测试的时候,按主健[UserName NVARCHAR(16)]值进行分区,数据两2800W,分区规则类似于('8','a', 'j', 'l', 'q', 'v')。数据分布比较平衡。
单记录插入的时间提升倒是很可观,达到非分区表的9倍多
单记录查询的时间和未分区的差不多。根据主健首字母分组查询的时间开销是非分区表的10倍强
单记录更新的时间开销差了一倍

测试环境大体如下描述:
在同一机器上测试。
分区表存储位置:物理磁盘1,盘符F。
非分区表存储位置:物理磁盘2,盘符H
数据库为同一测试数据库Test.磁盘均为普通SATA磁盘。

请问:
1。是不是分区规则建的有问题。对非数值类型的字段分区必须使用正则表达式?
2。是不是必须得在RAID、多CPU的服务器上,分区表的查询、统计性能才能比非分区表的性能更强?
3。分区字段是不是建在某一普通列上,才能体现分区表的性能?
4。仅从性能上考虑,分区表在什么样的条件下(普通机器),才能比非分区表的性能更高

15 个解决方案

#1


UP

#2


引用楼主 pgy8288 的帖子:
1。是不是分区规则建的有问题。对非数值类型的字段分区必须使用正则表达式? 
--按照LZ说的数据分布比较平衡的话,那么你的分区规则是没有问题的.
-- 对非数值类型的字段分区必须使用正则表达式? 貌似没有这样的限制吧.不过一般情况下,个人习惯使用连续性的字段作为分区,比如说时间字段等等.

2。是不是必须得在RAID、多CPU的服务器上,分区表的查询、统计性能才能比非分区表的性能更强? 
-- RAID 是处理冗余和灾难恢复的,和分区表应该没有关系,
-- 不过分区表的文件最好分布在不同的磁盘上,以提高数据读取的I/O,
-- 多CPU貌似有一定的优势.MSDN的原话 *如果具有多个 CPU 的系统中存在一个大型表,则对该表进行分区可以通过并行操作获得更好的性能*

3。分区字段是不是建在某一普通列上,才能体现分区表的性能? 

-- 分区字段一般都建于时间或者排序数组字段中,*范围分区是按照特定和可定制的数据范围定义的表分区。范围分区的边界由开发人员选择,还可以随着数据使用模式的变化而变化。通常,这些范围是根据日期或排序后的数据组进行划分的。*
4。仅从性能上考虑,分区表在什么样的条件下(普通机器),才能比非分区表的性能更高
-- 多CPU多磁盘的会有优势.


可以参考一下MSDN:
SQL Server 2005 中的分区表和索引
http://msdn.microsoft.com/zh-cn/library/ms345146.aspx

#3


等了这么久终于有人UP一下,窃喜一下先

#4


我的水平只能回答第 2 个和 第 4 个问题:
2.1 RAID 对于应用的性能提升,主要取决于具体的RAID方案(0,1,2,5,10等,还区分读/写)和其中各硬盘的“硬”指标(转速、缓存等);
2.2 只能说 SMP 比单CPU强;

4.对于常规PC,分区表文件组分配在不同的物理磁盘上,读写性能均可提升。

#5


一个都不会,看来差得太远了,呵呵

#6


引用楼主 pgy8288 的帖子:
请问: 
1。是不是分区规则建的有问题。对非数值类型的字段分区必须使用正则表达式? 
2。是不是必须得在RAID、多CPU的服务器上,分区表的查询、统计性能才能比非分区表的性能更强? 
3。分区字段是不是建在某一普通列上,才能体现分区表的性能? 
4。仅从性能上考虑,分区表在什么样的条件下(普通机器),才能比非分区表的性能更高



1,为什么会认为用正则?
2,当然
3,不是
4,分区表只有在多磁盘(尤其是raid)多cpu的服务器上才有性能提升,它是利用并行处理来提高性能,普通机器只会更慢。

另外你的测试不能说明非区表和非区表的差别。

#7


1.分区表和非分区表在同样的数据量的情况下,统计和更新居然比非分区表差了一大截,理想中至少也应该差不多吧.想不明白为什么,所以会认为是不是没用正则的结果
2.多物理磁盘多CPU的实验条件暂时无法达到,如果没有这样的硬件条件做测试,那就真的只能练手,而无法实践了:(
3.同样的聚集索引列,非分区表的性能居然比分区表的性能更高,而且观察所使用的CPU也是比非分区表的要高,感觉挺失落:(
4.以上已经回答了,就不用再说了....
5.想问一下,如果想把用户信息表做成分区表,通常各位高手是如何做的?

#8



根据经常执行的查询类型和硬盘配置,对表或索引分区可能会提高查询性能。

对联接查询分区
如果经常执行的查询涉及两个或多个已分区表之间的同等联接,则这些查询的分区依据列应该与联接表所基于的列相同。另外,应当已配置了这些表或它们的索引。这意味着它们要么使用同一命名分区函数,要么使用在以下方面基本相同的不同函数: 

具有相同数量的用于进行分区的参数,对应的参数具有相同的数据类型。


定义了相同数量的分区。


定义了相同的分区边界值。


这样一来,SQL Server 查询优化器就可以更快地处理联接,因为分区可以自行联接。如果查询联接的是联接字段未配置或未分区的两个表,则分区的出现实际上会降低而不是提高查询处理速度。

利用多个磁盘驱动器
可以将分区映射到文件组,每个分区访问一个不同的物理磁盘驱动器,以便提高 I/O 性能。当 SQL Server 针对 I/O 操作执行数据排序时,它会首先按分区对数据进行排序。在此方案下,SQL Server 每次访问一个驱动器,这样可能会降低性能。为了提高性能,更好的解决方案是通过设置 RAID 将多个磁盘中的分区数据文件条带化。这样一来,尽管 SQL Server 仍按分区对数据进行排序,但它可以同时访问每个分区的所有驱动器。不管所有分区是在一个文件组中还是在多个文件组中,都可以设计此配置。有关 SQL Server 如何处理不同的 RAID 级别的详细信息,请参阅 RAID 级别和 SQL Server。

#9


分区策略是根据结构而定的,具体选择哪个字段进行分区看应用.
当数据量大到需要对表进行物理分区,对存储架构要好好的去分析,
依据是资源,还有数据库使用情况,关键看瓶颈在拿? 是CPU性能还是IO.
如果是CPU,那就跟换更高性能的服务器,如果是IO,选择更好的阵列.
钱多就用SAN,钱少就用NAS,还有就是,尽量把分区的数据
文件放在RAID10上,读写性能好很多.

#10


dingo

#11


4,分区表只有在多磁盘(尤其是raid)多cpu的服务器上才有性能提升,它是利用并行处理来提高性能,普通机器只会更慢

楼上的兄弟,这个我刚刚测试了!在普通PC上,是更慢的!而且记录越多越明显。速度大约是1:1.1.
看来要另外想办法了!

#12


XUEXIE

#13


菜鸟路过,学习了。

#14


菜鸟路过,学习了。

#15


最近正在做优化方案,学习一下

#1


UP

#2


引用楼主 pgy8288 的帖子:
1。是不是分区规则建的有问题。对非数值类型的字段分区必须使用正则表达式? 
--按照LZ说的数据分布比较平衡的话,那么你的分区规则是没有问题的.
-- 对非数值类型的字段分区必须使用正则表达式? 貌似没有这样的限制吧.不过一般情况下,个人习惯使用连续性的字段作为分区,比如说时间字段等等.

2。是不是必须得在RAID、多CPU的服务器上,分区表的查询、统计性能才能比非分区表的性能更强? 
-- RAID 是处理冗余和灾难恢复的,和分区表应该没有关系,
-- 不过分区表的文件最好分布在不同的磁盘上,以提高数据读取的I/O,
-- 多CPU貌似有一定的优势.MSDN的原话 *如果具有多个 CPU 的系统中存在一个大型表,则对该表进行分区可以通过并行操作获得更好的性能*

3。分区字段是不是建在某一普通列上,才能体现分区表的性能? 

-- 分区字段一般都建于时间或者排序数组字段中,*范围分区是按照特定和可定制的数据范围定义的表分区。范围分区的边界由开发人员选择,还可以随着数据使用模式的变化而变化。通常,这些范围是根据日期或排序后的数据组进行划分的。*
4。仅从性能上考虑,分区表在什么样的条件下(普通机器),才能比非分区表的性能更高
-- 多CPU多磁盘的会有优势.


可以参考一下MSDN:
SQL Server 2005 中的分区表和索引
http://msdn.microsoft.com/zh-cn/library/ms345146.aspx

#3


等了这么久终于有人UP一下,窃喜一下先

#4


我的水平只能回答第 2 个和 第 4 个问题:
2.1 RAID 对于应用的性能提升,主要取决于具体的RAID方案(0,1,2,5,10等,还区分读/写)和其中各硬盘的“硬”指标(转速、缓存等);
2.2 只能说 SMP 比单CPU强;

4.对于常规PC,分区表文件组分配在不同的物理磁盘上,读写性能均可提升。

#5


一个都不会,看来差得太远了,呵呵

#6


引用楼主 pgy8288 的帖子:
请问: 
1。是不是分区规则建的有问题。对非数值类型的字段分区必须使用正则表达式? 
2。是不是必须得在RAID、多CPU的服务器上,分区表的查询、统计性能才能比非分区表的性能更强? 
3。分区字段是不是建在某一普通列上,才能体现分区表的性能? 
4。仅从性能上考虑,分区表在什么样的条件下(普通机器),才能比非分区表的性能更高



1,为什么会认为用正则?
2,当然
3,不是
4,分区表只有在多磁盘(尤其是raid)多cpu的服务器上才有性能提升,它是利用并行处理来提高性能,普通机器只会更慢。

另外你的测试不能说明非区表和非区表的差别。

#7


1.分区表和非分区表在同样的数据量的情况下,统计和更新居然比非分区表差了一大截,理想中至少也应该差不多吧.想不明白为什么,所以会认为是不是没用正则的结果
2.多物理磁盘多CPU的实验条件暂时无法达到,如果没有这样的硬件条件做测试,那就真的只能练手,而无法实践了:(
3.同样的聚集索引列,非分区表的性能居然比分区表的性能更高,而且观察所使用的CPU也是比非分区表的要高,感觉挺失落:(
4.以上已经回答了,就不用再说了....
5.想问一下,如果想把用户信息表做成分区表,通常各位高手是如何做的?

#8



根据经常执行的查询类型和硬盘配置,对表或索引分区可能会提高查询性能。

对联接查询分区
如果经常执行的查询涉及两个或多个已分区表之间的同等联接,则这些查询的分区依据列应该与联接表所基于的列相同。另外,应当已配置了这些表或它们的索引。这意味着它们要么使用同一命名分区函数,要么使用在以下方面基本相同的不同函数: 

具有相同数量的用于进行分区的参数,对应的参数具有相同的数据类型。


定义了相同数量的分区。


定义了相同的分区边界值。


这样一来,SQL Server 查询优化器就可以更快地处理联接,因为分区可以自行联接。如果查询联接的是联接字段未配置或未分区的两个表,则分区的出现实际上会降低而不是提高查询处理速度。

利用多个磁盘驱动器
可以将分区映射到文件组,每个分区访问一个不同的物理磁盘驱动器,以便提高 I/O 性能。当 SQL Server 针对 I/O 操作执行数据排序时,它会首先按分区对数据进行排序。在此方案下,SQL Server 每次访问一个驱动器,这样可能会降低性能。为了提高性能,更好的解决方案是通过设置 RAID 将多个磁盘中的分区数据文件条带化。这样一来,尽管 SQL Server 仍按分区对数据进行排序,但它可以同时访问每个分区的所有驱动器。不管所有分区是在一个文件组中还是在多个文件组中,都可以设计此配置。有关 SQL Server 如何处理不同的 RAID 级别的详细信息,请参阅 RAID 级别和 SQL Server。

#9


分区策略是根据结构而定的,具体选择哪个字段进行分区看应用.
当数据量大到需要对表进行物理分区,对存储架构要好好的去分析,
依据是资源,还有数据库使用情况,关键看瓶颈在拿? 是CPU性能还是IO.
如果是CPU,那就跟换更高性能的服务器,如果是IO,选择更好的阵列.
钱多就用SAN,钱少就用NAS,还有就是,尽量把分区的数据
文件放在RAID10上,读写性能好很多.

#10


dingo

#11


4,分区表只有在多磁盘(尤其是raid)多cpu的服务器上才有性能提升,它是利用并行处理来提高性能,普通机器只会更慢

楼上的兄弟,这个我刚刚测试了!在普通PC上,是更慢的!而且记录越多越明显。速度大约是1:1.1.
看来要另外想办法了!

#12


XUEXIE

#13


菜鸟路过,学习了。

#14


菜鸟路过,学习了。

#15


最近正在做优化方案,学习一下