运用工具优化数据库设计(Database Engine Tuning Advisor)

喜欢ヅ旅行 2022-09-18 10:50 29阅读 0赞

Data Engine Tuning Advisor 是SqlServer自带的数据库性能调优工具之一,我们一起来学习一下怎么用这个工具优化数据库设计

Database Engine Tuning Advisor Tuning 的功能

*  Recommend the best mix of indexes for databases by using the query optimizer to analyze queries in a workload.  
    \[翻译\]通过查询优化器分析实际工作负载下的查询,推荐最好的索引组合
 *  Recommend aligned or non-aligned partitions for databases referenced in a workload.  
    \[翻译\]为工作状态下用到的数据库推荐对齐(aligned)和非对齐(non-aligned)分区
 *  Recommend indexed views for databases referenced in a workload.  
    \[翻译\]为工作状态下用到的数据库推荐带索引的试图
 *  Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.  
    \[翻译\]分析工作状态下,对某种变更的效果进行评估,包括索引用法,表间查询分布,查询效果
 *  Recommend ways to tune the database for a small set of problem queries.  
    \[翻译\]对一些有问题的查询进行调优推荐
 *  Allow you to customize the recommendation by specifying advanced options such as disk space constraints.  
    \[翻译\]可以通过高级设置,比如磁盘空间大小限制来影响其作出的推荐方案
 *  Provide reports that summarize the effects of implementing the recommendations for a given workload.  
    \[翻译\]提供工作状态中其推荐方案的具体实现的效果报告
 *  Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.  
    \[翻译\]分析替代方案,你可以提供不同假设的配置下的设计方案供Database Engine Tuning Advisor分析

**索引优化**

我们结合示例来看看怎么用调优工具优化索引:

**准备工作**

我们使用一个缺少索引测试表,来看看在简单的查询条件下,工具能给出什么推荐方案

在我的数据库中有Orders表,其字段如下图,其中大约有50w行数据,在这里我故意没有给Info字段建立索引

![TableAndIndex.gif][]

创建一个sql语句,模拟实际情况的查询,在这个例子里我们使用一个简单的查询语句

Use \[ZhuYiTestDataBase2\]--注意一定要记住加上Use的数据库  
select \* from Orders where Info<>'abc'--我们用了一个没有索引的字段作为查询条件,性能一定不高,看看DETA会有什么高见

并保存为一个.sql文件(在实际运用中,一般一个查询可能要复杂的多)

**开始优化**

1)运行Database Engine Tuning Advisor,并创建一个Session

![CreateSession.gif][]

2)配置session

在新建的session的配置选项卡中点击General选项卡(如图),进行一些常用配置,有如下项需要配置

![General.gif][]

*  Session Name:可以为Session起一个名字,默认为用户加创建时间
 *  Workload:工作负载,可以从一个文件,可以从表,我们这个例子里就是我们刚才创建的sql文件,DETA会分析这个语句的执行情况,给出优化建议
 *  Select database and table to tune:选择需要优化的数据库和表,在本例中就是我们刚才看到Orders表
 *  Save tuning log:是否保存优化日志

点击TuningOption选项卡,进行一些高级选项的配置(如图)

![TuningOption.gif][]

*  首先我们要选择需要对哪些配置进行优化,在"Physical Design Structures To Use In DataBase"的复选框组中中我们可以需要优化指定索引,索引视图,聚焦索引,或者非聚焦索引,我在本例里面选择Index,这个选项包括聚焦和非聚焦索引.
 *  接着还有分区(partion)的选择,这个复选框组在实际运用中非常有用,通过查询,可以分析出哪些数据访问频率高,哪些低,从而给出分区的方案,在本例里面我们简单的选择no partition

3)运行分析

点击Action菜单下的Start Analysis,或者运行快捷方式栏的Start Analysis(如图)

![result.gif][]

4)查看分析结果

等待一段时间后,程序给出了分析结果

从中可以看到聪明的DETA发现了查询中作为条件的Info字段没有索引,建议我们创建一个索引,点击可以看到他的创建脚本(如图)

**总结**

Database Engine Tuning Advisor的功能远不只这些,我会在接下来的文章里陆续讲一些其他的用法,这里给出一些资源连接,大家可以自己学习

techNet上的参考

[http://technet.microsoft.com/en-us/library/ms166805(SQL.90).aspx][http_technet.microsoft.com_en-us_library_ms166805_SQL.90_.aspx]

繁体中文版本的

[http://technet.microsoft.com/zh-tw/library/ms173448(SQL.90).aspx][http_technet.microsoft.com_zh-tw_library_ms173448_SQL.90_.aspx]

[TableAndIndex.gif]: /images/20220828/0769c557148e4d77abbe8d50300003c8.png
[CreateSession.gif]: /images/20220828/d3ef6c373c4d49009143bf8223603770.png
[General.gif]: /images/20220828/46ae6b58441d4acfb2da8ea60d4f1276.png
[TuningOption.gif]: /images/20220828/17f2f92677b94ee3bc19034e5a04d3a2.png
[result.gif]: /images/20220828/3e1efc38eca7481a8082521c0c40ef9e.png
[http_technet.microsoft.com_en-us_library_ms166805_SQL.90_.aspx]: http://technet.microsoft.com/en-us/library/ms166805%28SQL.90%29.aspx
[http_technet.microsoft.com_zh-tw_library_ms173448_SQL.90_.aspx]: http://technet.microsoft.com/zh-tw/library/ms173448%28SQL.90%29.aspx