SQL事件探查器与索引优化向导

 

捕获供索引优化向导使用的事件探查器信息     

 

SQL   事件探查器和索引优化向导的结合使用，形成了一个功能非常强大的工具组合，它可以帮助数据库管理员确保在表和视图上放置正确的索引。SQL   事件探查器可以将查询的资源耗用情况记录到三个位置上。可以将输出定向到   .trc   文件、SQL   Server   表或监视器。之后，索引优化向导从   .trc   文件或   SQL   Server   表读取捕获的数据。索引优化向导对捕获的工作负荷中的信息和有关表结构的信息进行分析，然后针对改进性能提出应该创建哪些索引的建议。有了索引优化向导，     

 

  您就能自动完成以下任务：为数据库创建正确的索引、调度稍后进行的索引创建、生成可以手动检查和执行的   Transact-SQL   脚本。     

 

  分析查询负载需要完成以下步骤：     

 

  设置   SQL   事件探查器     

 

  在工具菜单上选择   SQL   事件探查器，从   SQL   Server   企业管理器中启动   SQL   事件探查器。     

  按   CTRL+N   键新建一个   SQL   事件探查器跟踪。在连接到   SQL   Server   对话框中，选择要连接到的服务器。     

  从下拉列表框中选择   SQLProfilerTuning   模板。     

  选中另存为文件或另存为表复选框。另存为表选项将打开连接对话框，在该对话框中，您可以将跟踪信息保存到探查查询的服务器以外的其他服务器。如果要将跟踪的活动同时另存为文件和表，请选中这两个复选框。如果要另存为   .trc   文件，请指向有效的目标和文件名。如果以前已运行过跟踪，现在再次运行同一跟踪，请指向现有的跟踪表；如果这是第一次将跟踪活动捕获到表中，您也可以提供新的表名。单击确定。     

  单击运行。     

  运行工作负荷若干   (3-4)   次     

 

  从   SQL   Server   企业管理器或开始菜单启动   SQL   查询分析器。     

  连接到   SQL   Server，然后将当前数据库设为在其中创建测试表的数据库。     

  在   SQL   查询分析器的查询窗口中输入下列查询：     

  select   ckey1   from   testtable   where   ckey1   =   'a'   

  select   nkey1   from   testtable   where   nkey1   =   5000   

  select   ckey1,col2   from   testtable   where   ckey1   =   'a'   

  select   nkey1,col2   from   testtable   where   nkey1   =   5000   

 

 

  按   CTRL+E   键执行查询。反复执行该步骤三至四次，生成工作负荷样本。     

  停止   SQL   事件探查器     

 

  在“SQL   事件探查器”窗口中，单击红色方块，停止该事件探查器跟踪。     

  将跟踪文件或表装载到索引优化向导中     

 

  在   SQL   事件探查器中，选择工具菜单上的索引优化向导，以启动该向导。单击下一步。     

  选择要分析的数据库。单击下一步。     

  选择是否要保留现有索引的选项，或者添加索引视图。     

  选择一种优化模式（快速、适中或彻底）。在“快速”优化模式下，索引优化向导执行分析所需的时间较少，但分析不够彻底，在“彻底”模式下生成的分析最彻底，但所需的分析时间最长。     

  要查找用   SQL   事件探查器创建的跟踪文件/表，请选择我的工作负荷文件或   SQL   Server   跟踪表。单击下一步。     

  在选择要优化的表对话框中，选择要分析的表，然后单击下一步。     

  索引优化向导将分析跟踪的工作负荷和表结构，然后在索引建议对话框中确定应创建的正确索引。单击下一步。     

  该向导提供了几个选项：立即创建索引、安排索引创建的时间（之后会自动执行的任务），或者创建包含用于创建索引的命令的   Transact-SQL   脚本。选择首选项，然后单击下一步。     

  单击完成。     

  索引优化向导针对样本数据库和工作负荷生成的   Transact-SQL     

 

  /*   Created   by:   Index   Tuning   Wizard   */   

  /*   Date:   9/6/2000   */   

  /*   Time:   4:44:34   PM   */   

  /*   Server   Name:   JHMILLER-AS2   */   

  /*   Database   Name:   TraceDB   */   

  /*   Workload   File   Name:   C:\Documents   and   Settings\jhmiller\My   Documents\trace.trc   */   

  USE   [TraceDB]     

  go   

  SET   QUOTED_IDENTIFIER   ON     

  SET   ARITHABORT   ON     

  SET   CONCAT_NULL_YIELDS_NULL   ON     

  SET   ANSI_NULLS   ON     

  SET   ANSI_PADDING   ON     

  SET   ANSI_WARNINGS   ON     

  SET   NUMERIC_ROUNDABORT   OFF     

  go   

  DECLARE   @bErrors   as   bit   

 

  BEGIN   TRANSACTION   

  SET   @bErrors   =   0   

 

  Create   CLUSTERED   INDEX   [testtable1]   ON   [dbo].[testtable]   ([ckey1]   ASC   )   

  IF(   @@error   <>   0   )   SET   @bErrors   =   1   

 

  Create   NONCLUSTERED   INDEX   [testtable2]   ON   [dbo].[testtable]   ([nkey1]   ASC   )   

  IF(   @@error   <>   0   )   SET   @bErrors   =   1   

 

  IF(   @bErrors   =   0   )   

  COMMIT   TRANSACTION   

  ELSE   

  ROLLBACK   TRANSACTION   

 

 

  索引优化向导为样本表和数据建议的索引正是我们所需要的：在   ckey1   上创建聚集索引，在   nkey1   上创建非聚集索引。ckey1   只有五个唯一值，每个值有   4000   行。假定其中一个样本查询   (select   ckey1,   col2   from   testtable   where   ckey1   =   'a')   需要根据   ckey1   中的某个值来检索表，则适合在   ckey1   列上创建聚集索引。第二个查询   (select   nkey1,   col2   from   testtable   where   nkey1   =   5000)   根据   nkey1   列的值提取一行。因为   nkey1   是唯一的，并且有   20,000   行，所以适合在该列上创建非聚集索引。     

 

  在使用了很多表并且要处理很多查询的实际数据库服务器环境中，将   SQL   事件探查器和索引优化向导组合使用，功能会非常强大。在数据库服务器处理典型的一组查询时，使用   SQL   事件探查器记录   .trc   文件或跟踪表。随后，将跟踪装载到索引优化向导中，以确定要建立的正确索引。按照索引优化向导中的提示执行操作，以自动生成索引，或安排索引创建作业在非高峰时间运行。您可能希望定期运行   SQL   事件探查器和索引优化向导的组合（也许每周一次或每月一次），以查看目前在数据库服务器上执行的查询是否发生了重大变化，这样就有可能会需要不同的索引。定期组合使用   SQL   事件探查器和索引优化向导，有助于数据库管理员在查询工作负荷不断变化和数据库日渐增大的情况下，仍保持   SQL   Server   处于最佳运行状态