回归命令行 SQLServer的命令行工具们(1) --dta.exe

    谨以此文送给以前的那些同事们，每每回想其在TARA上一起度过的ORACLE访问的日子，就会觉得现在的SQLServer那些花里胡哨的图形界面简直是在亵渎数据库分析师，而深负犯罪感。顺便赞下TOAD，强大的怪兽！

     之所以第一篇写dta，是因为虽然dta是功能最强大的工具之一，但是因为万恶的图形化界面程序导致dta可能是最不为人使用的一个废柴程序了.拿它开刀，就算写错了几乎都没人知道。^_^

     dta 实用工具可以分析工作负荷，并可为该工作负荷推荐可改进服务器性能的物理设计结构。工作负荷可以是 SQL Server Profiler 跟踪文件或跟踪表，也可以是 Transact-SQL 脚本。物理设计结构包括索引、索引视图和分区。分析了工作负荷后，dta 实用工具将生成数据库物理设计结构建议，并可生成实现该建议所需的脚本。可以在命令行提示符处，使用 -if 或 -it 参数指定工作负荷。也可以在命令提示符处，使用 -ix 参数指定 XML 输入文件。在这种情况下，在 XML 输入文件中指定工作负荷。

   实际上dta提供了比图形化数据库引擎优化顾问更强的功能。通过命令 dta.exe /?可以获得dta的命令信息(如下):

DTA.EXE [-S ServerName[/Instance]] [-U LoginId] [-P 密码] [-E] [-d DatabaseName

] [-D DatabaseName[, DatabaseName]] [-Tl TableName[, TableName]] [-Tf TableListF

ileName] [-if WorkloadFileName] [-it WorkloadTableName] [-s SessionName] [-of [S

criptFileName]] [-or [ReportFileName]] [-rl 报告[, 报告]] [-ox [OutputXmlFileNam

e]] [-F] [-ID SessionID] [-ix InputXmlFileName] [-A TuningTime] [-n NumberOfEven

ts] [-m MinimumImprovement] [-fa PhysicalDesignStructure] [-fp PartitionStrategy

] [-fk PhysicalDesignStructure] [-fx] [-B StorageSize] [-c MaxKeyColumnsInIndex]

[-C MaxColumnsInIndex] [-e TuningLogTable] [-N OnlineOption] [-q] [-u] [-x] [-a

] [-?]

参数列表

-S ServerName[/Instance]        要连接的 SQL Server 实例的名称。此为将进行所有优

化分析和支持表更新的服务器。

-U LoginId                      在建立与 SQL Server 的连接时使用的登录 ID (通过

-S 选项指定)。

-P 密码                         指定的登录 ID 的密码。

-E                              使用可信连接连接到服务器。

-d DatabaseName                 优化时要连接的数据库。

-D DatabaseName                 要优化的数据库名称列表。用逗号分隔名称。

-Tl TableName                   应进行优化的表名的列表。用逗号分隔名称。如果通过

-D

                                选项只指定了一个数据库，则不需要使用数据库名称来

限定表名。否则，每个表都需要使用

                                [数据库].[架构].[表] 格式的完全限定名称。

-Tf TableListFileName          包含要优化的表列表的文件名。该文件中列出的表必须

位于单独的行中，并且名称必须使用数据库名称和架

                                构名称(可选)进行限定。可通过在表名后面加一个数字

来调用可选的表缩放功能，此数字指示该表中提取

                                的行数，例如，"[myDatabase].[dbo].[myTable] 500"

。

-if WorkloadFileName            要用作优化输入的工作负荷文件的路径和文件名。接受

的格式:

                                *.trc - SQL Server Profiler 跟踪文件；

                                *.xml - SQL 事件探查器 XML 跟踪文件；

                                *.sql - SQL Server 脚本。

-it WorkloadTableName           包含要优化的工作负荷跟踪的表名。该名称以 [数据库

].[架构].[表] 格式指定。

-s SessionName                  新优化会话的名称。

-of ScriptFileName              指示应将 Transact-SQL

                                脚本和建议写入文件。如果提供了文件名，则将建议写

入该目标，否则，将根据会话名称生成文件名。

-or ReportFileName              指示应将报告写入文件。如果提供了文件名，则将报告

写入该目标，否则，将根据会话名称生成文件名。

-rl 报告                                指定要生成的分析报告列表

                                (选择一个或多个):

                                ALL             - 生成所有报告

                                NONE            - 不生成任何报告

                                STMT_COST       - 语句开销报告

                                EVT_FREQ        - 事件频率报告

                                STMT_DET        - 语句详细报告

                                CUR_STMT_IDX    - 语句-索引关系报告(当前)

                                REC_STMT_IDX    - 语句-索引关系报告(建议)

                                STMT_COSTRANGE - 语句开销范围报告

                                CUR_IDX_USAGE   - 索引使用情况报告(当前)

                                REC_IDX_USAGE   - 索引使用情况报告(建议)

                                CUR_IDX_DET     - 索引详细报告(当前)

                                REC_IDX_DET     - 索引详细报告(建议)

                                VIW_TAB         - 视图-表关系报告

                                WKLD_ANL        - 工作负荷分析报告

                                DB_ACCESS       - 数据库访问报告

                                TAB_ACCESS      - 表访问报告

                                COL_ACCESS      - 列访问报告

                                默认情况下，生成所有报告。

-ox OutputXmlFileName           应用程序将输出信息写入到的 XML 文件的名称。

-F                              允许数据库引擎优化顾问覆盖现有输出文件(使用任何

o? 选项指定)。

-ID SessionID                   数据库引擎优化顾问应为其生成结果的会话 ID。

-ix InputXmlFileName            指定用户配置(用户指定的配置)的 XML 文件名。注意:

                                如果文件中出现重复的参数，则命令行选项优先。

-A TuningTime                   数据库引擎优化顾问将在优化上花费的最长时间(分钟)

。通常，时间越长，建议的质量越高。如果将值指

                                定为 0，则对优化时间没有限制。如果未指定值，则将

优化时间限制为 8 小时。

-n NumberOfEvents               要优化的事件数。

-m MinimumImprovement           指定数据库引擎优化顾问只应在估计的性能提高程度达

到或超过提供的值(百分比)时提供建议。如果未指

                                定值，则无论性能提高的幅度有多大都提供建议。

-fa PhysicalDesignStructure     指定数据库引擎优化顾问应考虑为其提供新建议的物理

设计结构。

                                可用选项(选择一项):

                                IDX_IV - 聚集索引和非聚集索引以及索引视图

                                IDX     - 聚集索引和非聚集索引

                                IV      - 索引视图

                                NCL_IDX - 非聚集索引

                                如果未指定任何选项，则使用 IDX。

-fp PartitionStrategy           指定数据库引擎优化顾问应考虑请求添加的分区支持。

                                可用选项(选择一项):

                                NONE    - 不考虑任何分区策略

                                FULL    - 完全分区(最佳性能)

                                ALIGNED - 对齐分区(最佳可管理性)

                                如果未指定任何选项，则使用 NONE。

-fk PhysicalDesignStructure     指定数据库引擎优化顾问不能从现有数据库架构中删除

的物理设计结构。

                                可用选项(选择一项):

                                ALL     - 保留现有的所有物理设计结构

                                NONE    - 不保留现有的任何物理设计结构

                                CL_IDX - 保留聚集索引

                                IDX     - 保留聚集索引和非聚集索引

                                ALIGNED - 保留对齐分区

                                如果未指定任何选项，则使用 ALL。

-fx                             指定数据库引擎优化顾问将评估现有物理设计结构的用

途，并按照建议删除使用率低的结构。此选项不能与

                                 -fa 和 -fp 选项一起使用。

-B StorageSize                  全部建议可以占用的最大空间(MB)。

-c MaxKeyColumnsInIndex         索引中应用程序建议的最大键列数。

-C MaxColumnsInIndex            索引中应用程序建议的最大列数。

-e TuningLogTable               数据库引擎优化顾问将优化过程中出现的日志消息写入

到的表或文件的名称。应该以

                                [数据库].[架构].[表] 格式提供表名。将在进行优化

的服务器上创建该表。文件名必须使用

                                xml 扩展名。如果未传递任何表名或文件名，将使用默

认表。

-N OnlineOption                 指定应在线创建对象、离线创建对象还是尽可能在线创

建对象。如果要创建在线索引，则会将标记

                                "ONLINE=ON" 追加到所有可以或应该在线创建的对象的

DDL 脚本中。

                                可用选项(选择一项):

                                OFF - 仅离线

                                MIXED - 尽可能在线

                                ON - 仅在线

                                “离线”是默认选项。

-q                              静默模式。不会在控制台中写入任何信息，包括进度和

标头信息。

-u                              启动数据库引擎优化顾问图形用户界面，并传递提供的

所有命令行参数作为初始配置设置。

-x                              启动会话并退出。仅将所有结果写入数据库。稍后可通

过提供 -ID 参数生成输出。

-a                              不作提示，优化并应用建议。

-?                              显示使用信息。

下面是这些参数的详细解释（注意，区分大小写）：

1．首先是连接方面的：

-U login_id

指定用于连接 SQL Server 的登录 ID。

-P password

指定登录 ID 的密码。如果不使用此选项，则 dta 将提示输入密码。

-E

使用可信连接而不请求密码。必须使用指定登录 ID 的 -E 参数或 -U 参数。

-S server_name[ /instance]

指定要连接到的 SQL Server 计算机和实例的名称。如果未指定 server_name，则 dta 将连接到本地计算机的 SQL Server 默认实例。如果连接到命名实例或从网络上的远程计算机执行 dta，则必须使用此选项。

-D database_name

-D 参数是必需的。指定要优化的每个数据库的名称。第一个数据库是默认数据库。可以指定多个数据库，各数据库名称用逗号进行分隔。例如：

dta –D database_name1, database_name2…

此外，也可以在指定多个数据库时，使用 –D 参数逐个指定数据库名称。例如：

dta –D database_name1 -D database_name2… n

如果尚未指定 -d 参数，则 dta 将默认连接到工作负荷中第一个 USE database_name 子句指定的数据库。如果工作负荷中没有显式的 USE database_name 子句，则必须使用 -d 参数。

例如，如果有一个工作负荷未包含显式的 USE database_name 子句，则在使用以下 dta 命令时将不会生成建议：

dta -D db_name1, db_name2…

但是，如果使用同一工作负荷时使用了以下带 -d 参数的 dta 命令，则将生成建议：

dta -D db_name1, db_name2 -d db_name1

-d database_name

指定优化工作负荷时 dta 连接到的第一个数据库。只能为此参数指定一个数据库。例如：

dta -d adventureworks …

   如果指定多个数据库名称，dta 将返回错误。-d 参数是可选的。

   如果使用 XML 输入文件，则可使用 TuningOptions 元素下的 DatabaseToConnect 元素来指定 dta 连接的第一个数据库。如果只优化一个数据库，则 -d 参数的功能将类似于 sqlcmd 实用工具中的 -d 参数的功能，但不执行 USE database_name 语句。

-ID  session_ID

   为优化会话指定一个数字标识符。如果不指定该标识符，则 dta 将生成一个 ID 号。可以使用此标识符查看现有优化会话的信息。如果不指定 -ID 值，则必须用 -s 指定会话名。

-s session_name

   指定优化会话的名称。如果未指定 -ID，则必须使用此选项。

-u

    启动数据库引擎优化顾问 GUI。所有参数均被视为用户界面的初始设置。

-x

    启动优化会话，然后退出。

2．初始化和输出设置：

-A time_for_tuning_in_minutes

     指定优化时间限制 (分钟)。dta 使用指定的时间优化工作负荷，并生成一个包含建议的物理设计更改的脚本。默认情况下，dta 采用的优化时间为 8 小时。指定 0 可以不限制优化时间。dta 可能在限制时间之前完成整个工作负荷的优化。但是，为确保整个工作负荷都得到优化，建议指定不受限制的优化时间 ，虽然这个时间在大部分情况下会相当的漫长。

-a

    优化工作负荷和应用建议时不作提示。

-q

         设置静默模式。不向控制台写入信息，其中包括进度和标题信息。

-if  workload_file

         指定用作优化输入的工作负荷文件的路径和文件名。该文件必须采用下列格式之一：.trc（SQL Server Profiler 跟踪文件）、.sql（SQL 文件）或 .log（SQL Server 跟踪文件）。必须指定一个工作负荷文件或一个工作负荷表。

-F

允许 dta 覆盖现有的输出文件。如果已经存在同名输出文件，并且没有指定 -F，则 dta 将返回错误。-F 可以与 -of、-or 或 -ox 配合使用。

-of output_script_file_name

         指定 dta 使用 Transact-SQL 脚本将建议写入指定的文件名和目标。

可以与此选项一起使用 -F 。请确保文件名是唯一的，尤其在使用了 -or 和 -ox 时更应注意。

-or output_xml_report_file_name

         指定 dta 使用 XML 将建议写入输出报告。如果提供了文件名，建议就会被写入该文件名。否则，dta 将使用会话名生成文件名，并将该文件名写入当前目录。

可以与此选项一起使用 -F 。请确保文件名是唯一的，尤其在使用了 -of 和 -ox 时更应注意。

-ox output_XML_file_name

         指定 dta 使用 XML 文件将建议写入指定的文件名和目标。请确保数据库引擎优化顾问有写入目标目录的权限。

可以与此选项一起使用 -F 。请确保文件名是唯一的，尤其在使用了 -of 和 -or 时更应注意。

-e  tuning_log_name

         指定 dta 记录其无法优化的事件的表或文件的名称。表的创建位置是执行优化的服务器。

如果使用表，则用以下格式指定其名称：[database_name].[owner_name].table_name。下表显示了每个参数的默认值：

参数

默认值

database_name

使用 –D 选项指定的 database_name

owner_name

dbo

注意：

owner_name 必须为 dbo。如果指定了其他值，则 dta 执行将失败并返回错误。

table_name

无

如果使用文件，则指定 .xml 作为其扩展名。例如，TuningLog.xml。

注意：

如果删除该会话，则 dta 实用工具不会删除用户指定的优化日志表的内容。优化超大型工作负荷时，建议为优化日志指定一个表。由于优化大型工作负荷可导致优化日志增大，因此使用表时删除会话的速度可快得多。

-ix input_XML_file_name

         指定包含 dta 输入信息的 XML 文件的名称。该文件必须是符合 DTASchema.xsd 的有效 XML 文档。在命令提示符中指定的优化选项参数将覆盖与其冲突的 XML 文件中的相应值。唯一的例外情况是：在计算模式下，在 XML 输入文件中输入用户指定的配置。例如，如果在 XML 输入文件的 Configuration 元素中输入了配置，并将 EvaluateConfiguration 元素指定为优化选项之一，则 XML 输入文件中指定的优化选项将覆盖命令提示符中输入的任何优化选项。

3.优化选项

-B  storage_size

     指定推荐的索引和分区可以使用的最大空间 (MB)。优化多个数据库时，建议对所有数据库都进行空间计算。默认情况下，dta 采用下列存储大小中较小的一个：当前原始数据大小的三倍，包括数据库中表的堆和聚集索引的总大小。所有附加的磁盘驱动器的可用空间加上原始数据的大小。默认存储大小不包括非聚集索引和索引视图。

-C  max_columns_in_index

         指定 dta 推荐的索引中的最大列数。该最大值视 SQL Server 的版本而定。对于 SQL Server 2000，该最大值为 16；对于 SQL Server 2005，该最大值为 1024。默认情况下，该参数设置为 16。

-c  max_key_columns_in_index

         指定 dta 推荐的索引中的最大键列数。默认值为 16，即 SQL Server 2005 允许的最大值。该参数只适用于 SQL Server 2005。dta 也会考虑创建包含性列索引。建议的包含性列索引可能会超出此参数中指定的列数。

-fx  drop_only_mode

         指定 dta 只考虑删除现有物理设计结构。没有考虑任何新的物理设计结构。如果指定此选项，dta 将评估现有物理设计结构的使用情况，并建议删除很少使用的结构。此参数不带任何值，不能与 -fa、-fp 或 -fk ALL 参数一起使用。

-fa  physical_design_structures_to_add

         指定 dta 应在建议中包括的物理设计结构的类型。下表列出并说明了可为此参数指定的值。如果未指定值，dta 将使用默认值 -fa IDX。

值

说明

IDX_IV

索引和索引视图。（索引视图适用于 SQL Server 2005 的所有版本，而仅适用于 SQL Server 2000 的 Enterprise Edition。）

IDX

仅限索引。

IV

仅限索引视图。（索引视图适用于 SQL Server 2005 的所有版本，而仅适用于 SQL Server 2000 的 Enterprise Edition。）

NCL_IDX

仅限非聚集索引。

-fk  keep_existing_option

         指定 dta 在生成其建议时必须保留的现有物理设计结构。下表列出并介绍了可以为此参数指定的值：

值

说明

NONE

无现有结构

ALL

所有现有结构

ALIGNED

所有分区对齐结构。

CL_IDX

表上的所有聚集索引

IDX

表上的所有聚集索引和非聚集索引

-fp partitioning_strategy

         指定 dta 建议的新物理设计结构（索引和索引视图）是否应进行分区以及如何进行分区。下表列出并介绍了可以为此参数指定的值：

值

说明

NONE

不分区

FULL

完全分区（选择该值可增强性能）

ALIGNED

仅限对齐分区（选择该值可增强可管理性）

ALIGNED 表示在 dta 生成的建议中，每个建议的索引都完全按定义该索引的基础表所用的方式进行分区。索引视图上的非聚集索引与索引视图对齐。只能为此参数指定一个值。默认值为 -fp NONE。

-N online_option

         指定是否联机创建物理设计结构。下表列出并说明了可为此参数指定的值：

值

说明

OFF

建议的物理设计结构都无法在线创建。

ON

所有建议的物理设计结构都可以在线创建。

MIXED

数据库引擎优化顾问会尝试建议可以在线创建的物理设计结构。

         如果索引是联机创建的，则在其对象定义中追加 ONLINE = ON。

-it workload_trace_table_name

         指定包含用于优化的工作负荷跟踪的表名。用以下格式指定该名称：[database_name].[owner_name].table_name。

下表显示了每个参数的默认值：

参数

默认值

database_name

使用 –D 选项指定的 database_name。

owner_name

dbo。

table_name

无。

注意：

owner_name 必须为 dbo。如果指定了其他任何值，则 dta 执行将失败并返回错误。另请注意，必须指定一个工作负荷表或一个工作负荷文件。

-m minimum_improvement

         指定推荐的配置必须满足的最小改进百分比。

4．优化范围

-n number_of_events

指定 dta 应在工作负荷中优化的事件数。如果指定此参数，并且工作负荷是包含持续时间信息的跟踪文件，则 dta 将按持续时间的降序优化事件。此参数可用于比较物理设计结构的两个配置。若要比较两个配置，请为这两个配置指定相同的要优化的事件数，再为这两个配置指定不受限制的优化时间。如下所示：

dta -n number_of_events -A 0

在此示例中，必须指定不受限制的优化时间 (-A 0)。否则，数据库引擎优化顾问将采用默认的 8 小时优化时间。

-rl analysis_report_list

指定要生成的分析报告列表。下表列出并说明了可为此参数指定的值：

值

报告

ALL

所有分析报告

STMT_COST

语句开销报告

EVT_FREQ

事件频率报告

STMT_DET

语句详细报告

CUR_STMT_IDX

语句-索引关系报告（当前配置）

REC_STMT_IDX

语句-索引关系报告（建议配置）

STMT_COSTRANGE

语句开销范围报告

CUR_IDX_USAGE

索引使用报告（当前配置）

REC_IDX_USAGE

索引使用报告（建议配置）

CUR_IDX_DET

索引详细报告（当前配置）

REC_IDX_DET

索引详细报告（建议配置）

VIW_TAB

视图-表关系报告

WKLD_ANL

工作负荷分析报告

DB_ACCESS

数据库访问报告

TAB_ACCESS

表访问报告

COL_ACCESS

列访问报告

指定多个报告时，用逗号分隔各个值。例如：

… -rl EVT_FREQ, VIW_TAB, WKLD_ANL …

-Tf table_list_file

         指定包含要优化的一组表的文件名。文件中列出的每个表应另起一行。表名的命名应限定为三个部分，例如，adventureworks.dbo.department。为了调用表的扩展功能，可以选择在现有表名后加上一个数字，指示表中的预定行数。数据库引擎优化顾问在优化或评估引用这些表的工作负荷中的语句时，将考虑这些预定行数。请注意，number_of_rows 计数和 table_name 之间可以有一个或多个空格。

以下是 table_list_file 的文件格式：

database_name.[schema_name].table_name [number_of_rows]

database_name.[schema_name].table_name [number_of_rows]

database_name.[schema_name].table_name [number_of_rows]

此参数是在命令提示符中输入表列表的替代方式 (-Tl)。如果使用了 -Tl，请不要使用表列表文件 (-Tf)。如果同时使用这两个参数，dta 将失败并返回错误。

如果省略 -Tf 和 -Tl 参数，则将考虑对指定数据库中的所有用户表进行优化。

-Tl table_list

         在命令提示符中指定要优化的一组表。各表名间用逗号分隔。如果只用 -D 参数指定一个数据库，则无需用数据库名限定表名。在其他情况下，使用以下格式的完全限定名：database_name.schema_name.table_name，每个表都必须使用此格式。

此参数是使用表列表文件 (-Tf) 的替代方法。如果同时使用 -Tl 和 -Tf，则 dta 将失败并返回错误。