1.4升级到SQLServer2005的好处_SQLServer2005数据库开发详解

1.4 升级到 SQL Server 2005 的好处
如上所述，SQL Server 2005 增加了许多新的功能，但若仅是将旧系统升级而不做任何改变，大概用不到上述这些好处，困为你必须重新学习这些功能的定义。

然而将旧有版本升级成 SQL Server 2005 仍有许多立即的好处，除了提供学习研发的机会外，SQL Server 2005 在针对旧有功能的性能提升、增加系统稳定性上，也下了极大的功夫。本节讨论的是你仅需完成升级，就可以立刻享有到的好处。

◎全文检索

首先是针对全文检索，新版建立 Catalog 内容时，扩展（Index population）的速度较旧版快一倍以上，而查询效率也提升 30%-50%。另外，全文检索服务支持多实例（multi-instanced），各 SQL Server 实例使用各自的全文检索服务实例，不会互相干扰。全文检索也支持聚集架构以提高可获得性。

SQL Server 2005 可以备份/还原、分离/附加（Detach/attach）全文检索的 catalog，让你在还原数据库或迁移数据库之后，无须耗时重新扩展以建立 catalog。

◎查询引擎化

当我们输入 SQL 语句到 SQL Server 服务器端时，若查询引擎越聪明，就能够越快速且正确地判断用户的意图，找到的执行计划，才能越有效率地完成用户的需求。在此看一下 SQL Server 2005 的查询引擎较 2000 更为聪明的地方：

若执行的 update 语句实际并未改变数据内容，则 SQL Server 不会浪费资源去重新维护索引。

有效地加强文字符号（Symbolic）查询的解读。当 T-SQL 语句格式是

exp1 Op exp2

而 exp 操作数分别是字段名称和常数；op 为 <>、=、< 或 > 等运算符时，SQL 可以做更正确的语意判断，如：

l ? a > 5 and a>3 and a=0 直接会传回 false，因为 a 不可能既等于 0 又大于 5。

?（a between 1 and 100） and （a between 100 and 200） 直接将条件改成 a=100。

l 若条件是 a>3 and a>5 ，则实际条件是 a>5，在通过索引寻找时，自动使用条件比较严苛的 a>5。

l 增强 Indexed view 可用性，提供更多使用情境的对比。

l 对等联结（Equi-join）的条件使用如下语句时：

r.x=s.x and s.x > 10

其实隐含着以下的意义：

r.x=s.x and s.x > 10 and r.x >10

SQL Server 2005 除了在 s.x 字段测试是否有可用的索引，也会尝试在 r.x 字段上看看有没有可用的索引。

上述正规与减化符号查询的机制，将更能有效地建立的执行计划。

◎统计信息

若要正确判断执行计划，SQL Server 先要对所存储的数据内容分布建立详细而正确的统计。2005 关于存储数据统计的部分提供以下的强化：

l 以往自动更新统计在数据量很大时，可能会阻止 OLTP 应用程序的执行。现在维护统计数据时，可以异步更新统计，因而不会卡住正在执行的查询。若建立新的统计正做到一半，目前想要执行的查询就用旧的统计。通过启动如下的数据库设置，可以启动异步更新统计。

ALTER DATABASE [db名称] SET AUTO_UPDATE_STATISTICS_ASYNC ON

l 统计的数据更为精确，提供 string summary statistics，可以支持如 like %字符串% 形式的过滤条件。以更精确的统计减少建立执行计划时的猜测。

l 针对计算字段手动或自动建立统计。例如：因为能自动对计算字段建立统计，所以可准确预测如下的 where 条件。而如同 Indexed View，在下述实例中 Where 条件并未引用 b 字段，但因为定义相同，SQL Server 就会自动引用这个建立在计算字段 b 上的统计：

create table t1（a int,b as a+1）

create table t2（a int, c int）

SELECT * FROM t1 JOIN t2 ON t1.a=t2.a

WHERE t1.a+1 > 10

l 以平行运算的方式更有效地建立 Full scan 的统计。

l sp_updatestats 系统存储过程只更新需要更新的统计。该存储过程可以通过 UPDATE STATISTICS 语句更新目前数据库内用户自定义以及系统数据表相关的统计。在 SQL Server 2005 版的该存储过程只更新需要更新的统计，没有数据更改的就自动跳过，因此整体效率更好。

◎执行计划的共享与重用

若多条连接或同一条连接重复执行相同 SQL 语句时，执行计划能够共享与重用，将可减少 CPU 建立执行计划的负荷。为此需求，SQL Server 2005 提供了以下的功能。

l 针对在批次内或存储过程的多句语句，可使用语句层次的重新编译，不必完整重新编译全部批次或存储过程。例如几千行的存储过程中，用到了临时数据表（temp table），在重新调用该存储过程时，只需要重新编译使用到缓存数据表的那几行语句。

l 强迫使用参数化查询，自动将条件中使用的常数当作参数，避免当相同查询语句以不同参数输入时，重新编译执行计划。例如你前端程序两次输入的 Where 条件是：Where col=1 和 Where col=2，虽然主体的查询语句相同，但因为整句话不同，这时 SQL 可能会重新编译执行计划。因此是 Where col=@input，让编译执行计划时，不管参数值为何，都直接使用相同的查询语句。

在 SQL Server 2005 中你可使用数据库的设置，强制启动自动将常数当作参数来编译执行计划，设置语句如下：
ALTER DATABASE [db 名称] SET PARAMETERIZATION FORCED

以往 SQL Server 2000 其实就有部分实现上述的功能，不过 2005 版可以让你强制启动，以尽量将语句中所有的常数都当作变量。

另外，在执行查询时，语句包含动态对象，如数据表类型变量、触发器等的执行计划仍可以被缓存。

◎执行查询

精确地建立统计，参照该统计分析出有效率地执行计划，聪明的高速缓存避免重复建立执行计划后，接着还需要执行查询并返回结果。SQL Server 2005 针对在不同服务器上执行的分布式查询可以异步执行，也就是当批次内分别在不同的链接服务器（Iinked Server）下查询语句时，SQL Server 2005 会以多线程的方式同时对不同的服务器下查询语句，再将结果综合返回到前端。以往采用顺序的方式，需要等到第一句语句在某台服务器执行完毕后，才将下一句话放到另一台服务器上执行，无法发挥多台服务器平行运作的优势。

另外，当通过 SQL Server Profiler 工具程序录制 SQL Server 执行的状况时，提供更为丰富的信息，如分布式查询、以 XML 及图形化描述死锁信息。据此，你可以完成监控、调试、及性能调教等操作。

最后，你还可以在重建索引的同时执行查询语句。

◎存储引擎

针对存储引擎，SQL Server 2005 也进行了许多的强化：

l 动态字符串的数据列表可以跨页，通过 MAX 数据类型的声明，数据大小可以超过 8060 字节的限制。

l 更有效地使用 tempdb[1]。

l 自动快速缓存数据表和数据表变量的第一页。

l 更有效地使用资源，如取得数据页（page）、减少使用事务记录。

l 备份

? 数据备份和 Log 备份可以同时进行，批次执行的 Log shipping 在备份 log 时不会
被数据备份卡住。

? Restore Verifyonly 语句现在会读取每一数据页，以确认数据，所以比以往的版本耗时。

l 文件 I/O

? 默认启动新提供的 Page Checksum 功能，提升系统的可信赖度（reliability）。

? 可以单独还原损毁的数据页。

l Insert：化写入大量数据到 b-tree 数据区块，效率与 bulk load 相似，但不需要使用 bulk load 语句。

l 数据库恢复（recovery）：更有效率，数据库在 undo 阶段就可以使用。但这需要企业版才支持。

l 事务管理提供单条记录的版本（row version），可在数据库级别设置 read committed snapshot，不必在连接（session）级别设置，就可以提供 snapshot isolation 的功能。数据库的相关设置如下：

? ALLOW_SNAPSHOT_ISOLATION

? READ_COMMITTED_SNAPSHOT

连接维持在原 Read Committed 事务级别

? 在 T-SQL 语句中可以通过 ReadCommittedLock 这个 Query hint；在启动 row
version 后，仍使用原 Read Committed 事务级别的行为。

l 触发器会利用 Row-level versioning 的技术，在读取新旧记录时，会自动读取 row-level version，不管数据库是否启动 snapshot isolation。

? 不像以往需要从事务记录找寻新旧改变，尤其在多人同时连接时，避免同时抢 log
读取的操作。

l 以相同的字段重建聚集索引（Clustered Index）时，如下列的语句所示，不会导致非聚集索引一起重建[2]。

? DBCC DBREINDEX（tbl,idx）

? CREATE CLUSTERED INDEX idx ON Tbl（col） WITH DROP_EXISTING

? ALTER INDEX idx ON tbl REBUILD 新语句

SQL Server 内部的 OS（SQLOS）[3]更稳定更有效率

l 启动 AWE 设置后，仍可动态使用存储器。

l 更准确地在高速缓存中保留有用的数据页（page）。

l 加上对存储器数据页的 check sum 检查，以早期发现存储器硬件的错误。

l 保留一条 Dedicated Admin Connection（DAC） 资源，在 SQL Server 资源用尽时，可以通过该连接进入管理。

l 以 XML 格式输出死锁的相关数据，SQL Profiler 可用图2-24显示死锁信息。

l 以往线程（thread）是绑在 session 上，现在是绑在批次（batch）上，因此，当一个 session 传递多个批次，而每个批次都很耗 CPU 资源，现在可以分散到多颗 cpu 同时执行。

除了上述条列的各项功能外，另外还有在单一机器可以同时安装多个 SQL Server 数据库引擎、多个 Analysis Services、多个 Reporting Services 实例。通过 SQL Server 界面区配置提升安全管理的方便性。SQL Server Profiler 现今也可以记录Analysis Services 的执行内容等等。只要升级后，立刻可感受到上述功能的优点，而不必另行改变数据库架构与设计应用程序。

总的来说，因为 SQL Server 引擎更为聪明，更能有效地使用硬件资源，因此直接升级后，不改任何程序代码与设置的状态下，便可以提升性能、稳定性与管理方便性，尤其对于大型数据库更能发挥所长。当然，升级后，若你能稍作改变，使用SQL Server 新提供的功能是更佳的做法。

[1] tempdb 的使用建议：建立与 CPU 数目相同的文件数（参考该 SQL Server 实例的 affinity mask 设置），且多个文件的大小要相同。

[2]若聚集索引不是的，因为维持的值在以往可能会随着 rebuild 而重建，则非聚集索引凭着聚集索引指向数据的指针也需要重建，现在 SQL 2005 会在重建聚集索引时，维护这个原有的值。
[3]因为 SQL Server 的核心某部分需要做 Memory allocation、建立、分配与替 thread 排各 cpu 的调度等等，因此该部分也称为 SQLOS。