[SQL优化工具]Quest.Central.For.Databases——SQL Tuning for SQL Server

迈不过友情╰ 2022-09-23 12:49 21阅读 0赞

随着企业数据库的急剧膨胀和日益复杂，DBA为保证数据库性能所付出的努力与日俱增，手工或使用多种无法集成的管理工具，都会给日常管理和维护带来不必要的困难。 Quest Central for Databases是一种集成化、图形化、跨平台的数据库管理解决方案，可以管理异构环境下的 Oracle、DB2和 SQL server 数据库。Quest Central for Databases消除了企业IT人员管理多种数据库时面临的技术障碍，提高了IT人员工作效率，改善了数据库性能和数据库应用的可用性。为灵活满足用户的不同需求，Quest Central for Databases采用了模块化产品架构。

该产品包括下列功能模块:

1.数据库管理（DBA）

2.数据库监控（Monitoring Pack）

3.数据库诊断 (Spotlight Diagnostics)

4.数据库分析 (Database Analysis)

5.SQL优化 (SQL Tuning)

6.空间管理 (Space Management)

7.压力测试 (Load Generator)

8.数据生成 (Data Generator)

9.PL/SQL 开发 (TOAD)

10.专家建议 (Knowledge Expert)

今天主要介绍其中的SQL优化工具—— SQL Tuning for SQL Server

**一、SQL Tuning for SQL Server简介**

SQL语句的优化对发挥数据库的最佳性能非常关键。然而不幸的是，应用优化通常由于时间和资源的因素而被忽略。SQL Tuning (SQL优化)模块可以对比和评测特定应用中SQL语句的运行性能，提出智能化的优化建议，帮助用户改善应用的响应时间。SQL优化模块具有非介入式SQL采集、自动优化和专家建议等功能，全面改善SQL优化工作。

**二、SQL Tuning for SQL Server的使用**

1、打开Quest Database Management Solutions弹出窗口如图1所示![Databases1.jpg][]  
  
图1

2、在红色标记处打开SQL Tuning  优化SQL

（1）建立连接。

在Quest Central主界面上的“Database”树上选择“SQL Server”，然后在下方出现的“Tools”框中选择“SQL Tuning”选项，打开“Lanch SQL Tuning for SQL Server Connections”对话框（图2、图3）。我们在这里建立数据库服务器的连接，以后的分析工作都会在它上面完成。

![Databases2.jpg][]  
  
图2“建立连接”对话框

![Databases3.jpg][]  
  
图3

双击“New Connection”图标，在弹出窗口中输入数据库的信息，单击“OK”，然后单击“Connect”即可。

（2）分析原始SQL语句，在单击“Connect”后将弹出一个新窗口，如图4

![Databases4.jpg][]  
  
 图4

在打开窗口的“Oriangal SQL”文本框内输入需要分析的原始SQL语句，红色标记处选择对应的数据库名，SQL语句代码如下：

图5  分析原始SQL语句

![ContractedBlock.gif][] 原始SQL语句

然后点击工具栏上的“Execute”按钮，执行原始的SQL语句，SQL Tuning会自动分析SQL的执行计划，并把分析结果显示到界面上（图5）。 ![Databases5.jpg][]

（3）优化SQL。

现在我们点击工具栏上的“Optimize Statement”按钮,让SQL Tuning开始优化SQL，完成后，可以看到SQL Tuning产生了19条与原始SQL等价的优化方案（图6）。

![Databases6.jpg][]  
  
图6 SQL优化方案

（4）获得最优SQL。

接下来，我们来执行上面产生的优化方案，以选出性能最佳的等效SQL语句。在列表中选择需要执行的优化方案(默认已全部选中)，然后点击工具栏上的“Execute”按钮旁边的下拉菜单，选择“Execute Selected”。等到所有SQL运行完成后,点击界面左方的“Tuning Resolution”按钮，  
可以看到最优的SQL已经出来啦，运行时间竟然可以提高21%！（图7）

![Databases7.jpg][]  
  
图7“Tuning Resolution”界面

最优的SQL语句如下：

5）学习书写专家级的SQL语句。

![复制代码][copycode.gif]

![ExpandedBlockStart.gif][] 优化后的SQL语句

SELECT  dbo.Person\_BasicInfo. \* ,  
dbo.Graduater\_GraduaterRegist.RegistNO  AS  RegistNO,  
dbo.Graduater\_GraduaterRegist.RegistTime  AS  BaoDaoTime,  
dbo.Graduater\_GraduaterRegist.RegistMan  AS  RegistMan,  
dbo.Graduater\_Business.ComeFrom  AS  ComeFrom,  
dbo.Graduater\_Business.Code  AS  Code,  
dbo.Graduater\_Business.Status  AS  Status,  
dbo.Graduater\_Business.ApproveResult  AS  ApproveResult,  
dbo.Graduater\_Business.NewCorp  AS  NewCorp,  
dbo.Graduater\_Business.CommendNumber  AS  CommendNumber,  
dbo.Graduater\_Business.EmployStatus  AS  EmployStatus,  
dbo.Graduater\_Business.NewCommendTime  AS  NewCommendTime,  
dbo.Graduater\_Business.GetSource  AS  GetSource,  
dbo.Graduater\_Business.EmployTime  AS  EmployTime,  
dbo.Graduater\_Business.Job  AS  Job,  
dbo.Graduater\_Business.FillMan  AS  FillMan,  
dbo.Graduater\_Business.FillTime  AS  FillTime,  
dbo.Graduater\_Business.IsCommendOK  AS  IsCommendOK,  
dbo.Graduater\_Business.ApproveUser  AS  ApproveUser,  
dbo.Graduater\_Business.ApproveTime  AS  ApproveTime,  
dbo.Graduater\_Business.RegistTime  AS  RegistTime,  
dbo.Graduater\_Business.EmployCorp  AS  EmployCorp,  
dbo.Graduater\_Business.JobRemark  AS  JobRemark,  
 CASE  WHEN  dbo.Graduater\_Business.ComeFrom  =  ' WS '  THEN  ' 网上登记 '   
 WHEN  dbo.Graduater\_Business.ComeFrom  =  ' HP '  THEN  ' 华普大厦 '   
 WHEN  dbo.Graduater\_Business.ComeFrom  =  ' JD '  THEN  ' 精典大厦 '   
 WHEN  dbo.Graduater\_Business.ComeFrom  =  ' MC '  THEN  ' 赛马场 '   
 WHEN  ComeFrom  =  ' ZX '  THEN  ' 高指中心 '  END  AS  ComeFromName,  
dbo.Person\_Contact.Address  AS  Address,  
dbo.Person\_Contact.Zip  AS  Zip,  
dbo.Person\_Contact.Telephone  AS  Telephone,  
dbo.Person\_Contact.Mobile  AS  Mobile,  
dbo.Person\_Contact.Email  AS  Email,  
dbo.Person\_Contact.IM  AS  IM,  
dbo.Person\_Skill.ForeignLanguage  AS  ForeignLanguage,  
dbo.Person\_Skill.ForeignLanguageLevel  AS  ForeignLanguageLevel,  
dbo.Person\_Skill.CantoneseLevel  AS  CantoneseLevel,  
dbo.Person\_Skill.MandarinLevel  AS  MandarinLevel,  
dbo.Person\_Skill.Language  AS  Language,  
dbo.Person\_Skill.TechnicalTitle  AS  TechnicalTitle,  
dbo.Person\_Skill.ComputerLevel  AS  ComputerLevel,  
dbo.Person\_EmployPurpose.JobType  AS  JobType,  
dbo.Person\_EmployPurpose.Vocation  AS  Vocation,  
dbo.Person\_EmployPurpose.JobPlace  AS  JobPlace,  
dbo.Person\_EmployPurpose.Salary  AS  Salary,  
dbo.Person\_EmployPurpose.OnJobDate  AS  OnJobDate,  
dbo.Person\_EmployPurpose.CorpType  AS  CorpType,  
dbo.Person\_EmployPurpose.Job  AS  RequireJob,  
 YEAR ( GETDATE ())  \-  YEAR (dbo.Person\_BasicInfo.Birthday)  AS  Age,  
dbo.Graduater\_Business.EmployType  AS  EmployType,  
dbo.Graduater\_Business.EmployTypeCode  AS  EmployTypeCode,  
dbo.Graduater\_Business.EmployCorpType  AS  EmployCorpType,  
 CASE  WHEN  dbo.Graduater\_Business.PrintStatus  =  ' 已打印 '  THEN  ' 已打印 '   
 ELSE  ' 未打印 '  END  AS  PrintStatus,  
dbo.Graduater\_Business.PrintTime  AS  PrintTime,  
 CASE  WHEN  dbo.Graduater\_Business.EmployStatus  =  ' 是 '  THEN  ' 已就业 '   
 ELSE  ' 未就业 '  END  AS  EmployStatusView  
 FROM  dbo.Person\_BasicInfo  
 INNER  JOIN  dbo.Graduater\_Business  
 ON  dbo.Person\_BasicInfo.PersonID  =  dbo.Graduater\_Business.PersonID  
 LEFT  OUTER  JOIN  dbo.Graduater\_GraduaterRegist  
 ON  dbo.Graduater\_Business.GradBusinessID  =  dbo.Graduater\_GraduaterRegist.GraduaterGUID  
 INNER  JOIN  dbo.Person\_Contact  
 ON  dbo.Person\_BasicInfo.PersonID  =  dbo.Person\_Contact.PersonID  
 INNER  JOIN  dbo.Person\_Skill  
 ON  dbo.Person\_BasicInfo.PersonID  =  dbo.Person\_Skill.PersonID  
 INNER  JOIN  dbo.Person\_EmployPurpose  
 ON  dbo.Person\_BasicInfo.PersonID  =  dbo.Person\_EmployPurpose.PersonID  
 OPTION  (FORCE  ORDER )

![复制代码][copycode.gif]

（

通过上面的步骤，我们已经可以实现自动优化SQL语句，但更重要的是，我们还可以学习如何书写这样高性能的SQL语句。点击界面左方的“Compare Scenarios”按钮，我们可以比较优化方案和原始SQL中的任意2条SQL语句，SQL Tuning会将它们之间的不同之处以不同颜色表示出来，  
还可以在下方的“执行计划”中，通过比较两条SQL语句的执行计划的不同，来了解其中的差异（图8）。

![Databases8.jpg][]  
  
 图8“Compare Scenarios”界面

**三、总结**

SQL Tuning等人工智能自动SQL优化工具的出现，为我们节省出大量的时间和精力。借助这些工具的帮助，书写专家级的SQL语句将不再是难事。  
  
Quest.Central.For.Databases下载地址：  
http://pan.baidu.com/s/1c2yV3P2  
  
SQL Tuning操作手册英文版:

[ http://files.cnblogs.com/dreamof/SQL.Tuning.rar][http_files.cnblogs.com_dreamof_SQL.Tuning.rar]

[Databases1.jpg]: /images/20220720/ecf1a15b498e49cea7d5dd3d6fb104f7.png
[Databases2.jpg]: /images/20220720/81e7e7b2dc6741d99ddefbf975be31da.png
[Databases3.jpg]: /images/20220720/f654b0d94664450993f0bf6dfc216170.png
[Databases4.jpg]: /images/20220720/5bd6c765f25b472c9ca2ac21e1fe116e.png
[ContractedBlock.gif]: /images/20220720/dc77a7bdc80b45b0b408fa4fd4f72ee8.png
[Databases5.jpg]: /images/20220720/9a66c8434d764c298d7aca4725bffea9.png
[Databases6.jpg]: /images/20220720/641f198a62c843079113e526f447f961.png
[Databases7.jpg]: /images/20220720/6068249c02d941b7a6f2daaab8e8b40a.png
[copycode.gif]: /images/20220720/bc73f3c9322246ecbc1057784e65ce6c.png
[ExpandedBlockStart.gif]: /images/20220720/3532617580564cc6af72f2865b021e1b.png
[Databases8.jpg]: /images/20220720/84497058f1cc412fbea4b908192e4577.png
[http_files.cnblogs.com_dreamof_SQL.Tuning.rar]: http://files.cnblogs.com/dreamof/SQL.Tuning.rar