本次数据库调优的目的是从SQLServer配置选项和SQL Server2005/2008的“新”特性入手，从整体或局部上找到提升数据库性能的方法或建议。本次项目不会针对业务中的具体功能、结构、数据、查询进行优化，但是会使用它们作为对比、验证的素材。
1. SQL Server性能配置选项
SQL Server配置选项会影响SQLServer运行环境和执行计划，其中对性能调优有影响的包括： Affinity Mask、大内存开关、内存配置选项、并行性开销阈值、最大并行度、填充因子(%)、最大工作线程等。
下表列出了详细的配置选项说明、设置范围及其默认值。配置选项按以下字母代码标记：
l A ——高级选项，需要将show advanced options设为1。
l RR ——需要重新启动数据库引擎的选项。
l SC ——自配置选项。
| 配置选项 | 中文名称 | 选项说明 | 最小值 | 最大值 | 默认值 | 建议 |
| (A,RR) | 处理器IO关联掩码 | SQL Server处理线程与指定的 CPU 子集绑定。 位掩码形式。 | -2147483648 | 2147483647 | 0(不绑定) | 原理是减少线程在不同CPU之间的转移，从而节省开销，主要应用于多CPU（如8个或更多）和多SQL Server实例的场景。 建议一般采用默认值。 |
| (A) | 处理器关联掩码 | SQL Server 磁盘 I/O 与指定的 CPU 子集绑定。 位掩码形式。 | -2147483648 | 2147483647 | 0(不绑定) | 原理是减少IO控制在不同CPU之间的转移，从而节省开销，主要应用于多CPU（如8个或更多）等高端应用场景。 建议一般采用默认值。 |
| (A,RR) | 大内存开关 | 允许SQL Server使用超出系统虚拟内存的物理内存。 | 0 | 1 | 0(不启用) | 启用大内存，需要操作系统支持，即开启PAE(boot.ini)开关。 在32位操作系统中，如果物理内存超过4G，建议开启AWE开关。 在64 位操作系统上不需要也不能配置 AWE。 |
| (A,RR) | 最大内存 | SQL Server 实例所使用的缓冲池的最大内存量 (MB)。 | 16 | 2147483647 | 2147483647 | 如果服务器上运行多个SQL Server实例，且需要定量分配内存时，才需要考虑设置。 建议一般采用默认值。 |
| min server memory (A,RR) | 最小内存 | SQL Server 实例所使用的缓冲池的最小内存量 (MB)。 | 0 | 2147483647 | 0 | 建议一般采用默认值。 |
| (A) | 最大并行度 | 设置并行计划执行时所用的处理器数。 | 0 | 64 | 0 | 受affinity(64) mask选项的影响。设置参考affinity(64) mask选项。 建议一般采用默认值。 |
| cost threshold for parallelism (A) | 并行度开销阀值 | SQL Server 创建和运行并行查询计划的阈值(秒)。 | 0 | 32767 | 5 | 原理是处理一定规模、较长时间的操作时，将使用更多线程，交给多个CPU处理，以提高性能。但是在更多线程也意味着更多开销。需要根据实际情况设置并验证该值。 建议一般采用默认值。 |
| (A,RR) | 填充因子 | SQL Server使用现有数据创建新索引时将每页填满到什么程度 | 0 | 100 | 0(填充满) | 原理是减少增加索引时避免页拆分，从而提高性能，但是会增加读负担。 建议一般采用默认值。 另外，对于ERP来说最好能够使用顺序ID替换GUID。 |
| (A,RR) | 最大工作线程 | SQL Server 进程可使用系统的工作线程数。 | 128 | 32767 | 0 | 当服务器有很多连接时，每个连接时，每个连接使用一个线程就可能占用大量的系统资源，使用max worker threads 选项可以使 SQL Server为大量连接创建一个工作线程池，共用线程，从而提高性能。 对于32位SQL Server，建议最大为1024；对于64位SQL Server，建议最大为 2048。 |
注：以上选项多为建议采用默认值，对于需修改默认值的场景，必须分析验证后才能更改。
2. SQL Server性能相关特性分析测试
SQL Server 2008推出了许多新的特性和关键的改进，涉及到各个方面，其中对性能调优有影响的包括：数据压缩、表值参数、分区表、包含列、稀疏列、分组集合、HIERARCHY ID、日期数据类型、备份压缩等。
2.1.数据压缩
SQL Server 2008开始支持数据压缩功能，数据压缩分为行压缩和页压缩两种模式。
行压缩主要是通过压缩变长数据类型以及空值等实现的；
页级别压缩是在行压缩的基础上，进一步压缩了页内的一行或多行冗余数据，效果比页压缩更好。
行页压缩可以减少数据占用空间，减少数据访问的I/O和内存占用，但是会增加还原时CPU和内存的负担。
页压缩也是本次项目主要测试的特性，一共进行了以下几组测试：
2.1.1. 简单的表扫描测试
l 测试目的
验证在最普通的表扫描的情况下页压缩的表现。
l 测试数据
新建测试表test，5个字段，插入10w条记录，无索引。
l 测试查询
| select * from test |
l 测试结果
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 压缩前 | 88 | 2116 | 594 | 0 | 100000 |
| 行压缩后 | 120 | 2083 | 512 | 0 | 100000 |
| 页压缩后 | 167 | 2079 | 196 | 0 | 100000 |
注：测试结果为执行3次查询平均值，以下同。
l 测试小结
测试过程中，服务器CPU、内存、磁盘都没有出现高负荷的状态。
对数据压缩后，数据库读取次数明显降低（绝对值都不高），但是CPU执行时间也明显提高了，因此整个执行时间基本持平。
2.1.1. 高IO的表扫描测试
l 测试目的
验证在高IO的表扫描的情况下页压缩的表现。
l 测试数据
测试表，64个字段，1000w条记录，在Iid上创建聚集索引。
l 测试查询
| select * from TestPerf whereName='名称：.2.1.1.2.21' |
l 测试结果
| 轮次 | CPU | Duration | 逻辑读 | 物理读 | 预读 |
| 压缩前 | 12907 | 85821 | 898299 | 12185 | 892737 |
| 页压缩后 | 10031 | 10348 | 108717 | 733 | 101431 |
l 测试小结
压缩前，服务器CPU和内存都有一定负载、磁盘出现高负荷的状态，压缩后，磁盘负荷下降明显。
对数据压缩后，数据库读取次数大幅降低，CPU执行时间也明显降低提高（分析主要体现在CPU花费对磁盘IO的控制上），整个执行时间大幅度降低。
2.1.1. 表查找测试
l 测试目的
验证在使用索引（聚集或者非聚集）的表查找的情况下页压缩的表现。
l 测试数据
测试表，64个字段，1000w条记录，在Iid上创建聚集索引。
l 测试查询
| select * from TestPerf whereIid='406953' |
l 测试结果
| 轮次 | CPU | Duration | 逻辑读 | 物理读 | 预读 |
| 压缩前 | 0 | 3 | 4 | 0 | 0 |
| 页压缩后 | 0 | 8 | 3 | 0 | 0 |
l 测试小结
测试过程中，服务器CPU、内存、磁盘都没有出现高负荷的状态。
读取和执行时间都很少，压缩前后基本持平。
2.1.1. ERP高IO的查询测试
l 测试目的
以ERP项目跟踪文件IO Top20的实际查询和实际业务数据库作为测试环境，选取其中部分查询，测量页压缩的表现。
l 测试数据
备份的数据库。
l 测试查询
Rank2
| SELECT bnxfb.bunameAS '公司名称', bnxfb.projnameAS '项目名称', bnxfb.xmfqnameAS '分期', bnxfb.bldnameAS '楼栋名称', r.roomAS '房间编码', c.cstallnameAS '客户名称', c.contractnoAS '合同编号', c.payformnameAS '付款方式', c.zqdateAS '签约日期', c.rmbhttotalAS '合同总价', c.ajbankAS '按揭银行', c.ajtotalAS '按揭金额', c.ywyas '业务员' ,(selectserviceproc froms_SaleService ss where ss.serviceitemlike '按揭贷款'and ss.contractguid=c.contractguid)AS'按揭贷款进程' ,(selectcompletedate froms_SaleService ss where ss.serviceitemlike '按揭贷款'and ss.contractguid=c.contractguid)AS'按揭贷款时间' ,(selectyhfht from s_SaleService ss where ss.serviceitemlike '按揭贷款'and ss.contractguid=c.contractguid)AS'银行返合同时间' ,(selectserviceproc froms_SaleService ss where ss.serviceitemlike '合同登记'and ss.contractguid=c.contractguid)AS'合同登记进程' ,(selectcompletedate froms_SaleService ss where ss.serviceitemlike '合同登记'and ss.contractguid=c.contractguid)AS'合同登记时间' ,(selectserviceproc froms_SaleService ss where ss.serviceitemlike '预告登记'and ss.contractguid=c.contractguid)AS'预告登记进程' ,(selectcompletedate froms_SaleService ss where ss.serviceitemlike '预告登记'and ss.contractguid=c.contractguid)AS'预告登记时间' ,(selectserviceproc froms_SaleService ss where ss.serviceitemlike '抵押登记'and ss.contractguid=c.contractguid)AS'抵押登记进程' ,(selectcompletedate froms_SaleService ss where ss.serviceitemlike '抵押登记'and ss.contractguid=c.contractguid)AS'抵押登记时间' FROM s_Contractc INNER JOINp_room r ON c.roomguid=r.roomguid INNER JOINvs_BuNameOrXmFqOrBld bnxfbON r.BldGUID= bnxfb.BldGUIDand r.bldguidin ( '27515d2a-f6a3-4625-b3bd-a33097ae7f3b',. . . . . ,'eb64076d-addb-4e63-a4ff-4b73e32e012c') |
Rank7
| exec usp_s_JtRbQylb @dtQueryDate ='2010-07-28' |
Rank10
| SELECT SurName,GivenName, CstName, Gender,MobileTel, OfficeTelQh, OfficeTel,OfficeTelFj, HomeTel, HomeTelQh,Preferred, Email, IsGfJcz,Country, BirthDate, CardID,Address, PostCode, IsYz,CstGUID, CstType, BizLicence,Corporation, JzYt,Family, Znsl, BuyHouseTimes,Work, EduLevel,isBmTz, CognizeAve,MtZl, GfYs, Gfyt,XqTs, GzQs, GzJg,Competitor, XmGz1, XmGz2,XmGz3, Probability, ywy, Status,OppSource, OppStatus, WorkArea,HomeArea, null as NextGjDate,null as NextGjNr, OppGUID,UserGUID, FailType, statusReason, Closedon,Description,userName,dfNum, Gjfs, NextGjNras GjNr,Bz, Gjxw, CstProjGUID FROM vs_ClientOpp WHERE (CstProjGUID='8673F638-E3A4-4702-955D-855CE99B193301248036-1BBB-42FE-9A61-72B6920FC691') |
l 测试结果
Rank2
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 压缩前 | 1828 | 1983 | 397514 | 0 | 18712 |
| 页压缩后 | 1844 | 2063 | 397392 | 0 | 18712 |
Rank7
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 压缩前 | 156 | 319 | 24405 | 0 | 19 |
| 页压缩后 | 172 | 342 | 5176 | 0 | 19 |
Rank10
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 压缩前 | 12921 | 8189 | 56866 | 0 | 1 |
| 页压缩后 | 13580 | 8371 | 28014 | 0 | 1 |
l 测试小结
因为数据原因只选取了TOP20其中3条语句进行测试。
页压缩普遍的结果是在可以不同程度的减少读取（取决于结构、数据和查询），但是都会造成CPU处理时间增加，总的执行时间都有小幅度的增加。分析主要有：
首先，ERP系统中的查询基本上都已经建立了索引，页压缩的作用已经被弱化了；
其次，在读取数量并不大的情况下（Rank7和Rank10），页压缩的作用也不明显；
再次，因为数据库本身具有缓存机制，也会弱化页压缩的作用；
另外，对于读取数量很大的Rank2的情况，主要是因为结构和查询的原因，导致读取也不能下降。
2.2.1. 批量数据插入测试
l 测试目的
使用测试数据，对比验证使用普通参数逐条插入数据和使用表值参数批量插入数据在执行时间和资源开销上的差异。
l 测试数据
测试数据库，一个简单的id和name的表，插入1万条记录。
l 测试查询
普通参数逐条插入
| declare @i int set @i=1 while @i<10000 begin exec usp_i1@i,'name' set @i=@i+1 end |
表值参数批量插入
| declare @u as tp10 declare @i int set @i=1 while @i<10000 begin INSERT @u VALUES(@i,'name') set @i=@i+1 end exec usp_i2@u |
l 测试结果
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 普通参数逐条插入 | 1204 | 5943048 | 10285 | 33 | 29998 |
| 表值参数批量插入 | 408 | 571046 | 21398 | 62 | 39997 |
注：以上数据为1w次插入之和
l 测试小结
从以上的测试结果来看，使用表值参数批量插入相对于普通参数逐条插入在执行时间上提升幅度非常大，使用的CPU资源也大幅降低，在IO上会有所增加，但是增加比例不大，而且都是逻辑读写，物理读写并不会增加。总的说来，建议在批量插入数据时，使用表值参数批量插入。
另外，使用ADO.NET编程也非常方便使用表值参数，DataTable即可以作为表值参数使用，如以上查询改为ADO.NET实现：
| using (connection) { DataTable dt = GetMyTable(); string sqlInsert = "INSERT INTO test10 SELECT * from @u" SqlCommand insertCommand = new SqlCommand( sqlInsert, connection); SqlParameter tvpParam = insertCommand.Parameters.AddWithValue( "@u", dt); tvpParam.SqlDbType = SqlDbType.Structured; tvpParam.TypeName = "tp10"; insertCommand.ExecuteNonQuery(); } |
2.2.1. IdString查询测试
l 测试目的
使用测试数据，对比验证当Id很多时（比如测试使用的200个），使用字符串形式的IdString查询和使用表值参数形式的IdString查询在执行时间和资源开销上的差异。
l 测试数据
测试数据库，一个简单的id（uniqueidentifier）和name的表，10万条记录。
l 测试查询
字符串形式IdString查询
| select * from test13 whereid in('C403DBAA-B7F3-4CB1-9AD4-F2A06BCFDDA4',. . . . . .,'D0F292D4-8264-40D5-8112-690B0A8B7204') |
表值参数形式IdString查询
| declare @p tp13 insert into@p values('C403DBAA-B7F3-4CB1-9AD4-F2A06BCFDDA4') . . . . . . insert into@p values('D0F292D4-8264-40D5-8112-690B0A8B7204') select * from test13 whereid in(selectid from @p) |
l 测试结果
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 字符串形式IdString查询 | 390 | 394 | 489 | 0 | 112 |
| 表值参数形式IdString查询 | 47 | 90 | 618 | 0 | 224 |
注：使用表值参数形式IdString查询为所有语句之和
l 测试小结
字符串形式IdString查询，因为字符串很长，SQL Server解析字符串消耗的CPU时间很长；而使用表值参数形式IdString查询，则能大大的降低CPU时间和总的执行时间。读取次数会少量增加，但是相对于查询本身来说非常小，而且增加的都是逻辑读写，物理读写并不会增加。建议在IdString查询时，使用表值参数形式。
使用ADO.NET中的DataTable就可以作为表值参数传递，用法参考上一个例子。
2.1.分区表
SQL SERVER 2005引入了分区表的功能。分区表从物理上将一个大表分成若干个小表，从逻辑上提供和普通表相同的功能。
分区表可以根据实际需要访问数据，避免整个大表的访问，提高查询性能；而且分区表对应的数据可以保存在不同的文件和磁盘中，这在磁盘压力较大的时候也可以对性能有很大提升。
因为分区表的存储特性是按分区字段物理拆分成若干小表，因此分区表的聚集索引只能建立在该分区字段上，如果另外建立，分区表将转换为普通表。通过建立聚集索引并且应用分区方案，也可以将普通表转换为分区表。
l 测试目的
使用测试数据，针对以上对性能可能存在影响的点，对比验证使用普通表和分区表在查询时的效果。特别关注索引情况。
l 测试数据
两个测试表，字段都包括编号、姓名、月度、数量。
表一为普通表，在月度建立聚集索引、编号上建立唯一非聚集索引，120w条记录。
表二为分区表，采用月度作为分区字段，实际包含12个小表，每个小表10w条记录。编号上建立唯一非聚集索引。1—6月的数据文件保存在D盘，7—12月的数据文件保存在E盘。
测试完毕后去掉索引。
l 测试查询
根据编号取一条记录
| select *from test11 where id=800000 select * from test12 whereid=800000 |
分组取某个月统计数据
| select MAX(num),[MONTH]from test11 where [MONTH]=12group by [MONTH] select MAX(num),[MONTH]from test12 where [MONTH]=12group by [MONTH] |
取某些月份数据，并且加上一些计算
| select *,num2=case[MONTH]%2 when 0 then num*1.111111+100else num*1.222222+80end from test11 where [MONTH]between 2 and 5 union select *,num2=case[MONTH]%2 when 0 then num*1.111111+100else num*1.222222+80end from test11 where [MONTH]between 8 and 11
select *,num2=case[MONTH]%2 when 0 then num*1.111111+100else num*1.222222+80end from test12 where [MONTH]between 2 and 5 union select *,num2=case[MONTH]%2 when 0 then num*1.111111+100else num*1.222222+80end from test12 where [MONTH]between 8 and 11 |
分组取某个月统计数据，去掉索引
| select MAX(num),[MONTH]from test11 where [MONTH]=12group by [MONTH] select MAX(num),[MONTH]from test12 where [MONTH]=12group by [MONTH] |
分组取某个月统计数据，只在表一的月度上建立非聚集索引
| select MAX(num),[MONTH]from test11 where [MONTH]=12group by [MONTH] select MAX(num),[MONTH]from test12 where [MONTH]=12group by [MONTH] |
l 测试结果
根据编号取一条记录
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 普通表 | 0 | 0 | 6 | 0 | 1 |
| 分区表 | 0 | 0 | 6 | 0 | 1 |
分组取某个月统计数据
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 普通表 | 47 | 37 | 438 | 0 | 1 |
| 分区表 | 31 | 36 | 437 | 0 | 1 |
取某些月份数据，并且加上一些计算
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 普通表 | 1141 | 13765 | 3480 | 0 | 799992 |
| 分区表 | 1265 | 13150 | 3496 | 0 | 799992 |
分组取某个月统计数据，去掉索引
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 普通表 | 250 | 121 | 5213 | 0 | 1 |
| 分区表 | 47 | 41 | 437 | 0 | 1 |
分组取某个月统计数据，只在表一的月度上建立非聚集索引
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 普通表 | 238 | 126 | 5213 | 0 | 1 |
| 分区表 | 47 | 45 | 437 | 0 | 1 |
l 测试小结
分区表最大的作用就是根据分区函数，确定小表，避免大表中查询，从而减少开销，提升性能，这点在“分组取某个月统计数据，去掉索引”和“分组取某个月统计数据，只在表一的月度上建立非聚集索引”测试中可以明显体现（实际上两者的执行效率是一样的），该测试中因为分区表只需要访问12个小表中的1个小表，数据读取减少为1/12，查询时间也大大的减少。但是分区表的局限性也在于此，它只是起了部分聚集索引的作用，如果把聚集索引建立好，减少访问的作用就消失了，如测试“根据编号取一条记录”和“分组取某个月统计数据”中，普通表和分区表的各方面表现基本是一致的。
对于ERP系统而言，我觉得如果遇到类似问题，首先需要考虑的，还是我们的索引是否合理，而不是我们是否需要建立分区表。如果考虑采用分区表，我们需要结合查询和索引的情况综合考虑，确保它能够对查询带来帮助。
其次，分区表可以把数据文件分别存储在不同的磁盘中，这在磁盘负荷很大的时候，分散磁盘压力、提高访问速度应该有一定好处（实际上，普通表也可以分别放在不同的文件和磁盘中，也是在性能调优时可以考虑的手段），不过在测试“取某些月份数据，并且加上一些计算”中，受环境限制，未能充分证明。
另外，分区表在大数据量的并发操作、数据插入页拆分等方面应该都有好处，这些只是简单分析，没有深入去验证。
使用分区表，在备份数据库时，如果选择了基于文件或文件组的备份策略，则应执行文件或文件组备份。如果选择了基于整个数据库的备份策略，则可以执行完整数据库备份或差异备份。
使用分区表只需在数据层进行维护，对于应用程序而言没有影响。
总的说来，暂时没有发现使用分区表会带来需要关注的负面影响，在ERP中如果有些数据量很大的表中，数据具有明显的分段标识，而且某一时刻或者某一场景只会访问其中某段数据，那么建议采用分区表。
2.2.包含列
在SQLServer2005中，可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。这样做有以下好处：可以是不允许作为索引键列的类型；可以在避免索引键列数或索引键大小的限制（最大键列数为 16，最大索引键大小为 900 字节）。如果查询的字段被包含在包含列中，还可以避免再次的堆或者聚集索引查询。
l 测试目的
使用测试数据，对比验证使用包含列索引覆盖查询和不使用索引以及使用普通索引对的差别。对于因为索引列限制无需测试。
l 测试数据
测试表，10w条记录。包含编号、名称、编码、说明以及一些数字，假设最常使用名称、编码作为条件查询编号、名称、编码和某些数字字段数据。在编号上建立唯一聚集索引，再分别建立名称、编码的覆盖索引和包含列覆盖索引。
l 测试查询
| select Id,name,code,n1,n2,n3,n4,n5from test14 where name='name70'and code=170 |
l 测试结果
空间
| 轮次 | rows | reserved | data | Index_size | unused |
| 不使用索引 | 100000 | 7584 KB | 7312 KB | 48 KB | 224 KB |
| 覆盖索引 | 100000 | 10304 KB | 7312 KB | 2568 KB | 424 KB |
| 包含列覆盖索引 | 100000 | 12216 KB | 7312 KB | 4592 KB | 312 KB |
执行计划
| 轮次 | 计划 |
| 不使用索引 | |
| 覆盖索引 | |
| 包含列覆盖索引 |
查询
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 不使用索引 | 15 | 14 | 918 | 0 | 50 |
| 覆盖索引 | 6 | 125 | 189 | 0 | 50 |
| 包含列覆盖索引 | 1 | 2 | 3 | 0 | 50 |
l 测试小结
不使用索引时读取很大，但是表扫描的执行时间并不慢；
使用普通覆盖索引时，因为需要的数据不包含在非聚集索引叶级节点中，产生了嵌套循环（也就是Bookmark Lookup），导致执行时间更慢，但是确实它的读取相对于不使用索引少了很多；
使用包含列覆盖索引后，完全就是索引查找，读写和执行时间都大大提升。
由此可见，在我们需要经常查询一些数据，而这些数据并不大（太大的话，也会造成索引增大，反而使读取性能下降），而且不会作为查询条件时，我们应该把这些列包含在索引中；另外在遇到索引列（数量和长度）限制时，我们应该采用包含列覆盖索引。
目前在ERP中，包含列索引用得不多，共计2500多个列上存在索引，其中22个为包含列。
2.3.稀疏列
SQL Server 2008支持稀疏列属性，稀疏列主要是为了提供一种更有效的存储机制，稀疏列当列为空值时就不占空间可以节省空间，从而达到节省空间，降低IO的效果。但是使用稀疏列也会带来额外的开销，并且稀疏列中当列不为空值时占用的空间更大。
l 测试目的
使用测试数据，验证稀疏列能节省多少存储空间和IO，对于性能正面和负面的影响以及影响的大致数量。
l 测试数据
测试数据库，五个表，分别使用varchar(32)和bigint普通列和稀疏列（集），10万条记录。
l 测试查询
| select *from test5 --varchar(32) select *from test6 --varchar(32) SPARSE NULL select *from test7 --bigint select * from test8 --bigint SPARSE NULL select * from test9 --bigint SPARSE NULL COlUMN SET |
l 测试结果
空间
| 轮次 | rows | reserved | data | Index_size | unused |
| varchar(32) 普通列 | 100000 | 5000 KB | 4976 KB | 8 KB | 16 KB |
| varchar(32) 稀疏列 | 100000 | 4808 KB | 4768 KB | 8 KB | 32 KB |
| bigint 普通列 | 100000 | 11016 KB | 10960 KB | 8 KB | 48 KB |
| bigint 稀疏列 | 100000 | 4488 KB | 4472 KB | 8 KB | 8 KB |
| bigint 稀疏列集 | 100000 | 4488 KB | 4472 KB | 8 KB | 8 KB |
查询
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| varchar(32) 普通列 | 94 | 2125 | 626 | 0 | 100000 |
| varchar(32) 稀疏列 | 103 | 2135 | 600 | 0 | 100000 |
| bigint 普通列 | 78 | 2132 | 1380 | 0 | 100000 |
| bigint 稀疏列 | 141 | 1984 | 565 | 0 | 100000 |
| bigint 稀疏列集 | 1657 | 23396 | 565 | 0 | 100000 |
l 测试小结
稀疏列主要用于处理一个实体有很多属性，但很多属性都可能为空值，而且非空值比例不高的场景，这也被称为“属性集问题”。这种场景下，为了节省空间，往往在表结构设计时会考虑用主从表的方式，在从表中用行表示列属性，这样就会给查询带来额外的开销，这种应用也适合用稀疏列解决。
稀疏列是对空值采用优化的存储方式的列，“稀疏”只是列的属性，而并非一个新的类型。稀疏列减少了空值的空间需求，但代价是检索非空值的开销增加。节省的空间和IO，和列的类型以及非空值比例有密切关系，特别是对定长的列特别有效，只有当使用稀疏列能节省较大的空间和IO时才需要考虑使用。
另外，稀疏列往往和列集一起使用，列集是所有稀疏列的集合，使用XML格式返回，当定义了列集时，单独查询稀疏列需要加上Not Null条件。稀疏列是通过使用CREATE TABLE或ALTER TABLE语句定义的，对于应用程序没有影响，但是使用列集时返回的是非空值集合的XML结构数据，这可能适用于特定场景。使用列集查询，性能会下降很多。
2.4.分组集合
SQL Server 2008支持分组集合（GROUPINGSETS）功能，分组集合是对GROUP BY条件语句的扩展，它使得用户可以在同一个查询中定义多个分组。分组集合生成一个单独的结果集，这个结果集相当于对不同分组的行进行了UNION ALL的操作，这使得聚合查询更加简单和快速。
l 测试目的
使用测试数据，对比普通分组统计和采用分组集合统计的差异。
l 测试数据
测试数据库，一个表，包含三个维度统计字段，10万条记录。
l 测试查询
| select d1,SUM(num)from test15 group by d1 select d2,SUM(num)from test15 group by d2 select d3,SUM(num)from test15 group by d3
select d1,d2,d3,SUM(num)from test15 group by GROUPINGSETS (d1,d2,d3) |
l 测试结果
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| 普通分组统计1 | 94 | 92 | 368 | 0 | 3 |
| 普通分组统计2 | 78 | 96 | 368 | 0 | 3 |
| 普通分组统计3 | 96 | 115 | 368 | 0 | 3 |
| 普通分组统计和 | 268 | 303 | 1104 | 0 | 9 |
| 分组集合统计 | 203 | 277 | 385 | 0 | 9 |
l 测试小结
测试结果很明显，使用分组集合统计可以将数据IO大幅度减少，提升查询性能。在对同一个表中不同的维度进行分组统计时（如报表类型的应用），建议使用分组集合统计。
2.5.HierarchyId
SQL Server 2008通过CLS提供新的HierarchyId类型，用于层级代码的实现。该类型通过一些列的方法实现层级树的查找功能。
l 测试目的
使用测试数据，对比验证使用HierarchyId类型是否能够相对于ERP的层级代码提供更高的性能和更强的功能。
l 测试数据
两个测试表，分别使用HierarchyId类型和ERP层级代码保存树形结构数据，5级，约7500条记录，在层级代码上创建唯一、非聚集索引。
l 测试查询
| --获取节点1.4的所有子节点 select * from test3 wherehid like '1.4%' order byhid
declare @parent hierarchyid set @parent=CAST('/1/4/'as hierarchyid) select hid.ToString(),uid,namefrom test4 where hid.IsDescendantOf(@parent)=1order by hid |
l 测试结果
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| ERP层级代码 | 0 | 101 | 37 | 0 | 91 |
| HierarchyId | 0 | 147 | 34 | 0 | 91 |
l 测试小结
HierarchyId类型主要优势在于能够提供更丰富、更灵活的树形结构查询功能，能较为简便的实现某些功能（如下取所有上级），另外，因为HierarchyId类型采用二进制存储，能少量节省存储空间。
但是HierarchyId类型查询性能方面并不会比字符串的索引查找更有提升，而且使用HierarchyId需要CLR支持，还需要掌握该类型专门的语法，这套语法与ERP目前对层级代码的使用是不兼容的。
附：用HierarchyId类型取所有上级
| --获取节点.4.3.2的所有父节点 declare @child hierarchyid set @child=CAST('/1/4/3/2/'as hierarchyid) select hid.ToString(),uid,namefrom test4 where @child.IsDescendantOf(hid)=1order by hid |
附：HierarchyId类型提供的主要方法
n GetAncestor：取得某一个级别的祖先
n GetDescendant：取得某一个级别的子代
n GetLeve：取得级别
n GetRoot：取得根
n IsDescendantOf：判断某个节点是否为某个节点的子代
n Parse：将字符串转换为hierarchyid。该字符串的格式通常都是/1/这样的
n Read：Read 从传入的 BinaryReader 读取 SqlHierarchyId 的二进制表示形式，并将 SqlHierarchyId 对象设置为该值。不能使用 Transact-SQL 调用 Read。请改为使用 CAST 或 CONVERT。
n GetReparentedValue：可以用来移动节点（或者子树）
n ToString：将hierarchyid转换为字符串，与parse正好相反
n Write：Write 将 SqlHierarchyId 的二进制表示形式写出到传入的 BinaryWriter 中。无法通过使用 Transact-SQL 来调用 Write。请改为使用 CAST 或 CONVERT。
2.6.使用Date类型
SQL Server 2008支持新的日期类型Date，Date类型不包含时间部分，长度从DateTime的8位减少为3位，在不需要时间的日期类型中使用Date类型可以减少磁盘空间、数据IO以及相应的CPU控制。
l 测试目的
使用测试数据，对比采用DateTime和Date类型的查询，验证Date类型的效果。
l 测试数据
两个测试表，各10万条记录、 6个字段，其中一个DateTime或Date类型字段。
l 测试查询
| select *from test1 select * from test2 |
l 测试结果
| 轮次 | CPU | Duration | Reads | Writes | RowCounts |
| DateTime | 94 | 2043 | 594 | 0 | 100000 |
| Date | 78 | 1975 | 529 | 0 | 100000 |
l 测试小结
对于ERP中的DateTime类型而言，绝大部分不需要Time部分，因此可以考虑采用Date类型。使用Date类型对性能有小幅提升。
可以使用“alter table t alter column d date”将DateTime类型改为Date类型，修改后将去掉时间部分，日期部分不变。在应用系统中访问Date类型和DateTime类型是一样的，也就是说应用需不需要修改。
2.7.备份压缩
SQL Server 2008支持备份压缩功能，备份压缩采用与数据压缩相同的原理，在数据库备份时实现压缩备份，从而减少备份文件磁盘空间占用，并降低备份操作IO。
l 测试目的
使用测试数据，对比是否采用压缩备份，验证备份压缩的效果。
l 测试数据
测试数据库，一个表，1000万条记录。
l 测试查询
| BACKUP DATABASEPerfTest TO DISK='D:\PerfTest.BAK'WITH NO_COMPRESSION BACKUP DATABASEPerfTest TO DISK='D:\PerfTest1.BAK'WITH COMPRESSION |
l 测试结果
| 轮次 | DATABASE_NAME | BACKUP_SIZE | COMPRESSED_BACKUP_SIZE |
| 不压缩 | PerfTest | 7458816 | 7458816 |
| 压缩 | PerfTest | 7458816 | 1088465 |
l 测试小结
在备份时可以考虑采用备份压缩。
总结
究其原因，我认为除开受个人对业务应用和专业技能认知程度的限制外，更重要的原因还是出发的目的，就当前基于SQL Server数据库的应用系统来说，性能调优投入产出比最高的在于数据库设计和应用程序，对于硬件、操作系统和数据库配置而言，投入产出比较低，大致如图：
也就是说，未来我们数据库调优的重点还应该是在数据库设计和应用程序上。本次项目有关SQL Server2005/2008的“新”特性的研究、测试，目的就在于此。本次梳理的对ERP有价值的一些特性汇总于下表：
| 特性 | 2005 支持 | 2008 支持 | I/O | CPU | 使用建议 |
| 数据压缩 | × | √ | ↓ | ↑ | 在CPU负载较低、磁盘和数据库读写负载较高的情况下，可以考虑采用数据压缩。 |
| 表值参数 | × | √ | ↑ | ↓ | 在批量插入和批量查询等场景，建议采用。 注：这里增加的I/O是逻辑IO，而非物理I/O。 |
| 分区表 | √ | √ | ↓－ | ↓－ | 如果大量数据具有明显的分段标识，且业务通常只会访问其中某段数据，建议采用分区表。 |
| 包含列 | √ | √ | ↓ | ↓ | 如果经常查询的数据不大，而且不会作为查询条件，建议采用包行列；另外在遇到索引列也可以考虑采用包行列。 |
| 稀疏列 | × | √ | ↓ | ↑ | 当使用稀疏列能节省较大的空间和IO时才考虑使用。 |
| 分组集合 | × | √ | ↓ | ↓ | 当对同一数据不同维度进行分组统计时（如报表类型的应用），建议使用分组集合统计。 |
| Hierarchy Id | × | √ | － | － | 提供了更灵活强大的功能，性能基本和层级代码相当。如果重新设计，可以考虑采用。 |
| Date类型 | × | √ | ↓ | ↓ | 对不需要时间部分的日期类型，建议采用。 |
| 备份压缩 | × | √ | ↓ | ↑ | 数据备份时建议采用。 |
849
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
