Best Practices Analyzer Tool for Microsoft S

from : http://www.cnblogs.com/yxp132/articles/49224.html

Best Practices Analyzer Tool for Microsoft SQL Server 2000 是 Microsoft SQL Server 开发团队开发的一个数据库管理工具，可以让你检测设计的数据库是否遵循 SQL Server 操作和管理的最佳实践准则。这些准则公认有助于提高数据库的性能和效率，并让应用程序易于维护。

 

1，安装SQL BPA后，启动界面如下所示：

2，开始使用SQL BPA最佳实践分析器

安装完成后会有一个 SQL Server Best Practices Analyzer User Guide 的 Word 文档，如何使用讲解的很清楚，基本步骤如下：

（ 1 ）登录 SQL BPA

（ 2 ）添加分析 / 检测的 SQL Server 实例

这里需要输入 SQL Server 实例名称， Friendly Name 用来和后面创建 Best Practice Group 相关联（和 SQL Server 实例名称保持一样就可以了）。 Database List 的缺省值为 * ，表示包含当前 SQL Server 实例的所有数据库。但是， BPA 会跳过对‘ master ’ ,  ‘ tempdb ’ , ‘ msdb ’ ,  ‘ pubs ’ , and  ‘ northwind ’等数据库的检测。

（ 3 ）管理 Best Practice Groups （最佳实践组）

首先需要创建一个 Best Practice Group ，其实是组合了一些 Rules ，并和前面输入的 SQL Server 实例进行关联。

（ 4) 分析 SQL Server 实例

将前面创建 Best Practice Group 移到 Best Practice Groups to be Executed 列表中，就可以按照前面定义的 Rules 来执行，并产生 Report 提供改进的建议和准则。

 

3，SQL BPA v1.0包括的Rules

我觉得这个是重点，因为只有明白了这些 SQL Server 操作和管理的最佳实践准则，才能在设计数据库和编写 T-SQL 脚本时，尽量按照这些 Rules 来操作，提高 SQL Server 和应用程序的性能和效率。

其实所有的 Rules 都在这里（ English Version ） file:///C:/Program%20Files/Microsoft%20SQL%20Server%20Best%20Practices%20Analyzer/html/RuleInformation.html#_Rule:_Explicit_Index_Creation ，请注意我是采用默认路径安装的 SQL BPA ，如果你改变的安装路径，就不在这里了。

 

下面将一些自己比较感兴趣的 Rules 整理了一下：

（ 1 ）数据库设计

Rule: Tables without Primary Keys or Unique Constraints

检测数据库确保所有的 table 都有定义一个 Primary Key 或一列有 Unique Constraint 的定义。

 

Rule: User Object Naming （用户对象的命名）

检测以 sp_, xp_, or fn_ 为前缀命名的用户对象，避免和 SQL Server 的内置对象发生命名冲突。如果 SQL Server 发现存储过程以 sp_ 作为前缀，就会先到 master 数据库中查询这个存储过程，影响性能呵。

因此，要符合下列准则：

不要使用 sp_ 前缀来命名用户定义的存储过程；

不要使用 xp_ 前缀来命名用户定义的扩展存储过程；

不要使用 fn_ 前缀来命名用户定义的函数。

其实，可以通过使用 usp_, uxp_, or ufn_ 等前缀来命名就可以了， u 表示 user defined 。

 

（ 2 ） T-SQL

Rule: Cursor FOR UPDATE column list

检测 stored procedures, functions, views and triggers 中 FOR UPDATE 子句。当一个 cursor 定义了 FOR UPDATE 子句，则推荐提供明确的 column 列。 FOR UPDATE 用来定义 cursor 内可更新的列。如果提供了  OF column_name ，则只允许修改列出的列。如果在没有指定列的列表，除非指定了 READ_ONLY 并发选项，否则所有列均可更新。 SQL Server 可以基于指定的列优化操作。

 

Rule: Cursor Usage

检测 stored procedures, functions, views and triggers 中是否正确定义 cursor 可更新性。在如下情况下，会报告失败：

当一个 cursor 没有定义 FOR UPDATE 子句，但通过 cursor 来更新；

当一个 cursor 定义了 FOR UPDATE 子句，却没有通过 cursor 来更新。

 

不过，一般我们尽量避免使用服务器端 cursor ，因为比较占用服务器内存资源，影响 SQL Server 的性能。可以使用嵌套查询或者 WHILE 语句，来代替 cursor 。即使使用 cursor ，也应注意定义 cursor 的一些选项，如 FAST_FORWARD 。

 

Rule: Explicit Index Creation

推荐使用 CLUSTERED or NONCLUSTERED 显式创建 index 。

 

Rule: INSERT Column List

要求在使用 INSERT 时，明确提供 column 列表，提高代码的可维护性。

 

Rule: Nested Triggers Configuration

检测由于 nested triggers 的配置问题，未触发的 triggers 。这个比较少有，直接贴过来了。

When 'nested triggers' configuration option is set to 0, any AFTER trigger defined on tables/views updated inside an INSTEAD OF trigger is not fired. This rule:

1) Checks the value of the configuration option and exits if it is not 0.

2) Scans all INSTEAD OF triggers and generates a list of tables/view being target of DML from within a trigger.

3) Checks whether any of the identified DML targets have AFTER triggers defined on them.

4) Reports non-compliance for any such case.

 

Rule: NOCOUNT Option in Triggers

检测 triggers ，确保在 triggers 前面写有 SET NOCOUNT ON 。

SQL Server 在每一条语句执行完成后，都会发送 ’done’ 信息。这些信息会导致触发 trigger 的应用程序可能产生一些意外的后果。因此，在 trigger 前面加上 SET NOCOUNT ON 是一个良好的设计习惯。

 

当然，在 stored procedures, functions 中都推荐在前面添加 SET NOCOUNT ON 。这样一系列 SQL 命令执行影响的行数不会传回客户端，减少网络流量，提高性能。

 

Rule: NULL Comparisons

检测 stored procedures, functions, views and triggers 中涉及 NULL 常量的等于或不等于比较。推荐设置 ANSI_NULLS 为 ON ，并且使用 IS 关键字来呵 NULL 常量进行比较。

 

Rule: Results in Triggers

检测 triggers ，确保 triggers 没有数据返回给调用者。因此，不推荐在 triggers 中使用如下语句：

PRINT statement

SELECT (without assignment or INTO clause)

FETCH (without assignment)

 

Rule: Scoping of Transactions

检测 stored procedures and triggers 中的 transaction 范围，推荐 transaction 的开始和结束在同一 T-SQL 结构内。

 

一般而言，尽量缩小 transaction 的范围，避免占用大量的资源，影响 SQL Server 性能。

 

Rule: SELECT *

检测 stored procedures, functions, views and triggers 中 SELECT * 的使用。尽管 SELECT *  比较方便，但是会降低程序的可维护性。对 table or view 的改变，可能会引起错误或性能的改变。

因此，推荐在 SELECT 语句后面显式指定字段列表。

 

Rule: SET Options

检测 stored procedures and triggers 中如下 SET 语句的使用。

推荐如下选项设置为 ON:

• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• ARITHABORT
• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER

推荐如下选项设置为 OFF:

• NUMERIC_ROUNDABOUT

Rule: Temp Table Usage

检测 stored procedures and triggers 中临时表的使用。当创建临时表时，需要创建 CREATE INDEX ，并且在使用完成后，需要释放该临时表。

因为临时表会产生大量的磁盘 IO 操作，因此推荐采用 TABLE 变量替换临时表的使用。

不过，由于并发执行的限制和统计信息的维护，当有大量的数据插入临时表时，仍推荐采用临时表。

 

Rule: TOP without ORDER BY

检测 stored procedures, functions, views and triggers 中缺少 ORDER BY 的 TOP 语句。在使用 TOP 语句时，推荐指定排序条件。否则，产生的结果将于 SQL 执行计划相关而导致异常的行为。

 

Rule: Use of Schema Qualified Tables/Views

检测 stored procedures, functions, views and triggers 中引用 tables and views 时，拥护者 owner 是否指定。虽然在 SQL Server 中引用特定的对象时，可以不指定 server, database and owner(schema) ，也就是说不用 server_name.database_name.owner_name.*** 这么麻烦，但是 SQL Server 推荐当在 stored procedure, function, view or trigger 中引用 table or view 时，最好指定 table or view 的拥有者。

 

当 SQL Server 查询未指定 owner 的 table/view 对象时，首先查询缺省的 owner ，然后才是 dbo 。这样，会导致 SQL Server 产品额外的运行成本。通过指定 owner ，可以改进 SQL Server 的性能。（第一次听到这种说法）

 

4，参考文档及相关资源

工具下载 URL:

http://www.microsoft.com/downloads/details.aspx?FamilyId=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

视频下载 URL:

http://www.microsoft.com/china/msdn/events/webcasts/shared/msdntv/episode.aspx?xml=/china/msdn/events/webcasts/msdntv/20040610sqlserverck/manifest.xml

Best Practices Analyzer Tool for Microsoft SQL Server 2000