性能测试—用DTA调校索引

作者：川石学院　日期：2021-06-16 16:53:31　点击数：

　　本章主要讲解性能测试中如何用DTA调校索引，首先，Database Engine Tuning Advisor是一个物理数据库设计工具，它是建立在SQL Server 2000的Index Tuning Wizard的技术上，并取而代之，它接收形式为包含一组Select、delete、update语句的T-SQL脚本的工作负荷或SQL Profiler跟踪作为输入，而且会输出一个由对索引、索引的视图和统计进行创建、删除和划分的建议组成的T-SQL脚本，它还会给出性能改进的估计，DTA的结构图如图11-42所示。　　

性能测试—用DTA调校索引(图1)

　　图11-42 DTA结构图

　　DTA可以对单个查询进行分析也可以对SQL Server Profiler Trace文件进行分析，分析后DTA会显示优化查询性能的建议，根据DTA提供的优化建议可以对查询语句进行优化，优化后再次对查询进行度量，直到达到性能要求为止。

　　对单个查询进行调校，包含两个步骤，一是统计查询执行期间的等待时间;二是使用DTA进行调校;

　　在开始调校之前，需要捕获开始度量，为了得到一致的结果，需要捕获一个冷时间和多个热时间并对这些热时间求平均值。冷时间是指SQL Server第一次运行一个查询所需要的时间，执行计划和数据不在缓存中，因此所有的工作必须从零开始，查询后续运行将会由于缓存的缘故快很多(已热身)，这样更能够代表一个活动的系统，评估相对于平均热时间的性能收益将会提供一个繁忙系统上对性能收益的期望。

　　实例：在数据库people中创建people和boysnames两张表，代码如下：

　　CREATE DATABASE [people] ON PRIMARY
　　( NAME = N'people',
　　FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\people.mdf' ,
　　SIZE = 409600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
　　LOG ON
　　( NAME = N'people_log',
　　FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\people_log.ldf' ,
　　SIZE = 10240KB , MAXSIZE = 102400KB , FILEGROWTH = 10240KB )
　　GO
　　CREATE TABLE people
　　(
　　personId UNIQUEIDENTIFIER DEFAULT newsequentialid(),
　　firstname VARCHAR(80) not null,
　　lastname VARCHAR(80) not null,
　　dob DATETIME not null,
　　dod DATETIME null,
　　sex CHAR(1) not null
　　)
　　go
　　CREATE TABLE boysnames
　　(
　　ID INT IDENTITY(0,1) not null,
　　[name] VARCHAR(80) not null
　　)
　　Go
　　分别为这两张表插入10000条记录，代码如下：
　　declare @i int
　　set @i=1
　　while @i <10000
　　begin
　　insert into people values(@i,'abc','123','1')
　　set @i=@i+1
　　end
　　declare @i int
　　set @i=1
　　while @i <10000
　　begin
　　insert into boysnames values(@i,'0')
　　set @i=@i+1
　　end

　　现在通过统计信息来统计一个查询语句的执行期间的等待时间，代码如下：

　　use People
　　go
　　-- *********************************************
　　--
　　-- WARNING This script empties the people table and fills it with just 8 people!!!
　　--
　　-- *********************************************
　　truncate table people
　　go
　　dbcc dropcleanbuffers
　　dbcc freeproccache
　　go
　　-- either do stats time and stats IO, OR showplan_text. Using all three makes he OP a little hard to read!
　　set statistics time on
　　set statistics io on
　　go
　　-- set showplan_text on
　　-- go
　　-- Cold run
　　select * from people
　　where personid = (select id from boysnames where name = '123')
　　go
　　-- first warm run
　　select * from people
　　where personid = (select id from boysnames where name = '123')
　　go
　　-- second warm run
　　select * from people
　　where personid = (select id from boysnames where name = '123')
　　go
　　-- third warm run
　　select * from people
　　where personid = (select id from boysnames where name = '123')
　　go
　　-- set showplan_text off
　　set statistics time off
　　set statistics io off
　　go
　　-- we ran the SP to insert 2 people 4 times, so there will be 8 people in the DB
　　select count (*) from people
　　go

　　执行该过程的结果如下：

　　DBCC execution completed. If DBCC printed error messages, contact your system administrator.
　　DBCC execution completed. If DBCC printed error messages, contact your system administrator.
　　SQL Server Execution Times:
　　CPU time = 0 ms, elapsed time = 0 ms.
　　SQL Server Execution Times:
　　CPU time = 0 ms, elapsed time = 0 ms.
　　SQL Server parse and compile time:
　　CPU time = 15 ms, elapsed time = 31 ms.
　　Table 'people'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0.
　　Table 'boysnames'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 26.
　　SQL Server Execution Times:
　　CPU time = 0 ms, elapsed time = 64 ms.
　　(0 行受影响)
　　SQL Server parse and compile time:
　　CPU time = 0 ms, elapsed time = 1 ms.
　　Table 'people'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0.
　　Table 'boysnames'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0.
　　SQL Server Execution Times:
　　CPU time = 0 ms, elapsed time = 1 ms.
　　(0 行受影响)
　　SQL Server parse and compile time:
　　CPU time = 0 ms, elapsed time = 1 ms.
　　Table 'people'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0.
　　Table 'boysnames'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0.
　　SQL Server Execution Times:
　　CPU time = 0 ms, elapsed time = 1 ms.
　　(0 行受影响)
　　SQL Server parse and compile time:
　　CPU time = 0 ms, elapsed time = 1 ms.
　　Table 'people'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0.
　　Table 'boysnames'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0.
　　SQL Server Execution Times:
　　CPU time = 0 ms, elapsed time = 1 ms.
　　(0 行受影响)
　　SQL Server parse and compile time:
　　CPU time = 0 ms, elapsed time = 0 ms.

　　分析输出信息可以发现，冷运行共用时64秒，热运行共用时1秒，冷运行中共进行了26次逻辑读操作，主要对是boysnames进行了逻辑读操作。

　　接下来使用DTA对查询语句进行分析，需要分析的语句如下：

　　select * from people
　　where personid = (select id from boysnames where name = '123')

　　将分析语句保存为后缀名为.sql的文件，单击开始菜单->所有程序->Microsoft SQL Server 2005，选择性能工具中的数据库引擎优化顾问，或在Micosoft SQL Server Management Studio主界面中单击工具菜单，在下位菜单中选择数据库引擎优化顾问选项，弹出如图11-43所示的连接对话框。　　

性能测试—用DTA调校索引(图2)