毕业设计外文资料翻译-在SQL Server 2008中优化查询

1、毕业设计外文资料翻译学 院： 专业班级： 学生姓名： 学 号： 指导教师： 外文出处：(外文) Scientific Bulletin -Ecomomic Sciences,Vol.9(15) -Information techonology 附 件：1.外文资料翻译译文； 2.外文原文 指导教师评语：该生外文翻译符合毕业设计要求。签名： 2015年 10 月 14日1外文资料翻译译文在SQL Server 2008中优化查询Professor Ph.D. Ion LUNGU , Nicolae MERCIOIU , Victor VL ADUCU3摘 要从需要开发高效的IT系统开始，我们打算

2、检讨SQL Server数据库管理员可以通过使用基于微软技术的应用开发商的优化方法和工具，专注于最新专有的DBMS的版本，SQL Server 2008中，我们将考虑改进SQL Server实例的性能，我们将解决分析和优化查询，我们将描述大部分使用的技术“优化特设工作负载”，“计划冻结”和“优化未知”等新选项，伴随着相关的代码示例。关键字：查询，SQL Server 2008，优化JEL分类：C88，D801.简介提高数据库管理系统的性能包括识别瓶颈及其原因，使用适当的技术和工具解决这些问题和评价所添加的性能。人们普遍认为，旨在实现最大理论性能是不现实的，适得其反，因为超越达到“足够好”的投资

3、成本时间的性能增益指数增加。大多数时候，在考虑效率和性能的设计时，最后的标准往往是使用一个新的数据库中的应用程序。在这些标准之后，系统投产才变得重要。有时似乎应用程序不显示该在对数据库的合理的时间请求或不能显示它的完全信息，超出设置超时的原因可能与应用程序的设计有关，但是在许多情况下，数据库管理系统返回数据的速度不够快是由于没有使用索引，查询设计的缺陷或数据库模式过度分散，使用不准确统计，故障重用执行计划或使用游标不当。一旦全套运行SQL查询捕捉，应查明施加查询对系统资源和那些运行最慢的压力。使用处理组件优化SQL Server中的查询来确定执行最有效的方法查询，考虑到可能的计划，并决定哪一个

4、是最好的。优化基于实际成本的策略是一个运行“成本”的估算每一个可能的计划，允许磁盘I / O操作方面选择具有最低“成本”的执行计划，CPU负载，内存负载等。2. 目标改进SQL Server实例的性能用于提高性能的元件已经发现，重要性的顺序：Windows操作系统下，SQL Server实例，硬件，数据库和应用程序。据微软称，为了改进SQL Server要考虑的最重要的目标表现是：- 设计一个高效的数据架构。- 优化查询。- 优化索引，存储过程和交易。- 分析执行计划，避免编译它们。- 监测的数据访问。设计一个高效的数据架构需要进行初始正常化和随后归一化（例如人/机构地址），如果需要的话。反向

5、方法将涉及其他活动旨在确保数据的一致性。要考虑的另一个问题是使用声明引用完整性。这种方法比使用触发器和使用系统临时表更有效。采用主，外键和唯一键约束有助于建立有效的执行计划。此外，还建议定义数据类型尽可能接近到以假乱真，因为显性和隐性转换会花费密集计算资源。当信息不经常更新，一个非常重要的方面来是需要使用索引视图，因为索引视图的物理存储为表。在优化索引和存储过程的过程中，他们允许快速响应的选择操作，同时减慢插入，更新和删除操作应该被考虑在内。一般来说，索引的创建和使用应该在读写操作之间进行平衡，即索引可以提高读取操作，但是产生正面或负面改写操作。至于交易，应尽可能的越短越好。需要用户干预的交易

6、是要避免的，在开始事务之前，建议进行数据验证。存储过程必须包括SET NOCOUNT ON命令。这个命令可以防止发送关于受影响的记录在此过程中进行的每个操作数的消息。它是在分析和运行上有代表性的数据执行计划，使得由优化器提出最佳的建议方案。在这方面，应避免涉及扫描表和索引计划。扫描仅含有多达数百个的记录的表值得的。另外，主要的CPU和内存资源消费者排序记录和过滤操作。一般来说，编译执行计划，应避免由于在性能上的损失。这可以通过使用参数化查询和存储过程和过度临时表游标来避免。然而，当优化器能够创建更高效的执行计划时，重新编译的计划可能带来好处。它通过统计来监控（如果它们保持最新），并使用分析器运

7、行很长一段时间的查询，以及用于扫描和监测资源的使用是非常重要的。查询优化将在下面详细加以解决。3. 查询分析和优化技术分析和优化技术需要个人的方式也有一整套查询的，其前提是，虽然个人查询可以足够优化，整套性能可能会很差。首先，也是最重要的优化技术是通过限制记录在SELECT列表中指定的字段的数目（WHERE子句）并限制返回的数据的量。这将导致有效地利用索引。原则上，WHERE子句应该是有选择性的，因为它是一个建立在列上使用索引。为有效利用索引，根据该系统的使用率要求，SQL Server提供群集和非聚集型索引。其表的频繁更新 - - 联机事务处理计划内的聚集索引推荐，但在尽可能少的列可能。大量

8、在这些系统指标会影响INSERT，UPDATE的性能，DELETE和MERGE命令，各项指标必须在表中的数据被修改作相应调整。集群式像时， =，和运营商之间的=被使用，因为被发现含有的第一个值的记录后，随后与索引值的记录是物理相邻指标是有效的。此外，如果查询包含JOIN一样，ORDER BY或GROUP BY子句中，集群式索引是最合适的。非聚集索引的使用是只推荐用于更新频繁的数据库，并给出了“精确类型查询的最佳解决方案。查询可以有效地使用索引只有在WHERE子句功能算术运算都尽可能避免使用。例如，使用LIKE代替SUBSTRING函数会导致优化使用索引的列：SELECT Name FROM A

9、dventureWorks.Production.Location WHERE SUBSTRING(Name,1,1) =P优化建议：SELECT Name FROM AdventureWorks.Production.Location WHERE Name LIKE P%同样地，在排除条件，！=，！，！，NOT EXISTS，NOT IN，不喜欢，或或类似的例子“将确定DBMS的优化不是在WHERE子句中使用的列上的索引。取而代之的是，夹杂物的条件下，之间以及在与实施例“，允许优化以提高查询性能，因为在SQL Server会发现在索引的记录，将返回相邻的记录过，只要在条件WHERE子句仍是如

10、此。另一个优化的方法是使用，如果可能，在BETWEEN子句代替IN或OR条件。在SQL Server 2008将通过访问索引的次数等于值的数量由搜索解决IN条件。使用BETWEEN子句，优化器将它变成一对条件 = = id SELECT * FROM Sales.SalesOrderDetail JOIN Sales.SalesOrderHeader ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID WHERE Sales.SalesOrderHeader.SalesOrderID =1对于

11、在执行存储过程或脚本每个查询时，SQL Server将返回受影响的记录数（2323333行（S）的影响）为了节省资源，最好是使用SET NOCOUNT ON SET NOCOUNT OFF序列。4. SQL Server 2008中新的优化选项有些应用程序在一个会话（特设工作负载）运行脚本一次。这一事实在SQL Server存储用于在所使用的内存的过度增加的可能再利用的结果各自执行计划，从而导致该实例的一个较低的效率。为了解决这个问题，微软推出优化这种查询的选项 - 优化特设工作负载 - 为SQL Server 2008的所有变种一旦这个选项被激活，当编译脚本的第一次，在DBMS将只保存即席查

12、询执行计划的一小部分。这部分将在后面的阶段帮助确定脚本是否已被编译。如果重新执行该脚本，在SQL Server 2008中删除了最初编译计划的小部分，将重新编译脚本，以获得完整的执行计划。上下面的脚本的第一运行，所述第二查询将返回一个结果，显示出第一查询执行计划被存储。DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS GO USE AdventureWorks GO SELECT * FROM Person.Contact GO SELECT usecounts, cacheobjtype, objtype, text FROM sys.dm_exec_cach

13、ed_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts 0 AND text like %SELECT * FROM Person.Contact% ORDER BY usecounts DESC; GO重新执行脚本之前，我们启动了优化选项，并更改略有LIKE子句中的第二个查询（文字，如“SELECT * FROM Person.Contact）：SP_CONFIGURE show advanced options,1 RECONFIGURE GO SP_CONFIGURE optimize for ad h

14、oc workloads,1 RECONFIGURE GO 在这种情况下，第二次查询将不会返回任何结果，反映了没有执行计划已被存储在第一查询的事实。如果我们去重新执行脚本，我们会注意到在SQL Server 2008中保存查询执行计划，无论讨论选项的状态。因此，我们可以激活优化特设工作负载选项只有新的执行计划受到影响，那些已经记忆不会受到影响后得出这样的结论。在SQL Server 2008中引入的版本，另一个特点是一个允许为现有的SQL Server内存查询的执行计划直接创造的指南（计划冻结）的。此功能增加了为执行支持扩展计划指南对所有DML命令。另外，在2005年版本的SQL Server

15、中，此功能仅用于SELECT命令是可用的和所涉及的可能性，以指定的建议（指南）为不能从应用直接改变了的查询。在下面的例子中，我们会为了得到这稍后将被“冻结”的执行计划首先运行一个查询。USE AdventureWorks GO DBCC FREEPROCCACHE GO SET STATISTICS XML ON EXEC sp_executesql NSELECT * FROM Sales.SalesOrderDetail JOIN Sales.SalesOrderHeader ONSales.SalesOrderHeader.SalesOrderID= Sales.SalesOrderDe

16、tail.SalesOrderID SET STATISTICS XML OFF GO下一步，我们将“冻结”先前创建的执行计划。DECLARE plan_handle varbinary(1000) SELECT plan_handle = plan_handle FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) sqt WHERE text like %SalesOrderDetail% SELECT plan_handle EXEC sp_create_plan_guide_fro

17、m_handleTEST_Plan_Guide_1,plan_handle=plan_handle从存储过程sp_create_plan_guide使我们能够确保优化器将始终使用相同的计划，针对特定的查询。我们将使用“冻结”执行计划，并再次运行查询：SET STATISTICS XML ON EXEC sp_executesql NSELECT * FROM Sales.SalesOrderDetail JOIN Sales.SalesOrderHeader ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.Sale

18、sOrderID SET STATISTICS XML OFF GO我们可以很容易地看到使用的计划是以前创建的。对于未知的选项的优化将导致使用被永久使用的标准算法，以产生一个查询计划优化程序。代替使用提交的应用程序的参数的实际值的，优化器将咨询所有的统计数据，以确定哪些值可用于生成一个有效的计划。从SQL Server早期版本已知的方法是使用这使节能和重用执行计划，以避免重新编译参数化查询。问题出现了，当在回顾查询发送的参数值不媲美最初传输。在第一次执行时，SQL Server将编译并保存一个有效的计划，这些值。随后，同样的计划将导致一个次优查询执行。DECLARE C1 INT DECLAR

19、E C2 INT SET C1 = 5000000 SET C2 = 400 SELECT * FROM TEST WHERE COL1 C1 or COL2 C2 ORDER BY COL1;当由前一查询创建的计划将被删除和非典型值1和90中给出的两个变量C1和C2，优化程序，在查询的重新执行，这可能是由不同的俯视 第一。在实践中，作为查询是相同的，优化器将保持并使用最初创建计划，具有性能相应损失，为对C1和C2变量的后续值。为了避免该查询的参数的这种情况和利益，该先前的查询可以被重写为：SELECT * FROM TEST WHERE COL1 C1 or COL2 C2 ORDER BY

20、 COL1 OPTION (OPTIMIZE FOR (C1 UNKNOWN, C2 UNKNOWN)5.总结优化过程是迭代的，并且包括像识别瓶颈，其中，测量的变化的影响，并重新评估来自第一步骤的系统确定是否取得了令人满意的性能的步骤。这个过程让一个逐步完善的性能，但我们应该始终牢记，业绩依赖于数据量和用户的应用程序中的分布。这些元素在时间，所以需要定期的绩效考核。有许多方面应以实现最佳性能的查询和SQL实例加以解决。原则上，优越的性能可以通过在应用程序级写一个有效码并正确尽管如此使用设计和数据库开发，改变的SQL Server配置不会导致性能的改善而得到。参考文献1 G. Fritchey,

21、 S. Dam, “SQL Server 2008 Query Performance Tuning Distilled” Apress, 2009.2 MSDN, “Data Type Conversion“.3 MSDN, “Microsoft Patterns & Practices”.4 Pinal Dave, “Journey to SQL Authority ”.2.外文原文OPTIMIZING QUERIES IN SQL SERVER 2008Professor Ph.D. Ion LUNGU , Nicolae MERCIOIU , Victor VLADUCU31Acade

22、my of Economic Studies, Bucharest, Romania,2Prosecutors Office attached to the High Court of Cassation and Justice3Prosecutors Office attached to Vlcea CourtAbstract:Starting from the need to develop efficient IT systems, we intend to review the optimization methods and tools that can be used by SQL

23、 Server database administrators and developers of applications based on Microsoft technology, focusing on the latest version of the proprietary DBMS, SQL Server 2008. Well reflect on the objectives to be considered in improving the performance of SQL Server instances, we will tackle the mostly used

24、techniques for analyzing and optimizing queries and we will describe the “Optimize for ad hoc workloads”, “Plan Freezing” and “Optimize for unknown new options, accompanied by relevant code examples. Keywords: Query, SQL Server 2008, Optimization.JEL Classification: C88, D801. IntroductionImproving

25、the performance of a DBMS includes identifying bottlenecks and their causes, using appropriate techniques and tools for solving them and evaluating the added performance obtained. It is generally accepted that aiming to achieve maximum theoretical performance is unrealistic and counterproductive, as

26、 the investment cost beyond reaching the good enough time increases exponentially with the performance gain. Most times, efficiency and performance are the last criteria considered when designing and developing new applications using a database. These criteria become important only after the system

27、goes into production. Sometimes it appears that the application does not display the information requested to the database in a reasonable time or completely fails to display it, the set timeout being exceeded. The reasons may be related to the application design, but in many cases the DBMS does not

28、 return the data quickly enough, due to the nonuse of indexes, deficient design of the queries and/or database schema,excessive fragmentation, use of naccurate statistics, failure to reuse the execution plans or improper use of cursors. Once the full set of running SQL queries captured, one should i

29、dentify the queries exerting high pressure on system resources and those running the slowest. The component dealing with optimizing queries in SQL Server attempts to determine the most efficient way to execute a query, taking into account possible plans, and decides which one is the best. The optimi

30、zation based on actual cost strategy involves the estimation of a runtime cost for every possible plan, allowing choosing the execution plan with the lowest “cost” in terms of disk I/O operations, CPU load, memory load etc. 2. GOALS FOR IMPROVING PERFORMANCE OF A SQL SERVER INSTANCEThe elements to b

31、e focused on for increasing performance have been found to be, in ascending order of importance: Windows operation system, SQL Server instance, hardware, database and application. According to Microsoft, the most important objectives to be considered in order to improve the performance of SQL Server

32、 are: - Designing an efficient data schema. - Optimizing queries. - Optimizing indexes, stored procedures and transactions. - Analyzing execution plans and avoiding recompiling them. - Monitoring access to data. Designing an efficient data schema requires initial normalization and subsequent de-norm

33、alization (e.g. persons/institutions address), if necessary. A reverse approach would involve additional activities meant to insure the data consistency. Another issue to be considered is the use of the declarative referential integrity. This approach is more efficient than using triggers employing

34、system temporary tables. The use of primary, foreign and unique key contributes to the creation of effective execution plans. It is also recommended to define data types as close as possible to the real ones, given that implicit and explicit conversions are intensive consumers of computational resou

35、rces. A very important aspect comes from the need to use indexed views, when the information is not frequently updated, as indexed views are stored physically as a table. In optimizing indexes and stored procedures the fact that they allow a rapid response to selection operations but slow down inser

36、t, update and delete operations should be taken into consideration. Generally, the creation and use of indexes should be balanced among read and write operations i.e. indexes improve read operations but may positively or negatively alter write operations. Indexes have to be also created for all fore

37、ign keys on tables that are often queried and do not contain image or bit type fields. As regards transactions, they should be kept as short as possible. Transactions requiring user intervention are to be avoided and data validation is recommended before starting the transaction. Stored procedures m

38、ust include the SET NOCOUNT ON command. This command prevents sending the message regarding the number of affected records for each operation carried out within this procedure. It is important to analyze and run execution plans on representative data so that the best plan suggested by the optimizer

39、may be chosen. In this regard, plans involving scanning tables and indexes should be avoided. Scanning is worthy only for tables containing up to hundreds of records. Also, major CPU and memory resources consumers are the records sorting and filtering operations. In general, recompiling execution pl

40、ans should be avoided due to the loss in performance. This can be avoided by using parameterized queries and stored procedures and avoiding cursors over temporary tables. However, recompiling plans may bring benefits when the optimizer is able create a more efficient execution plan. It is very impor

41、tant to monitor through statistics (if they are kept up to date) and use the profiler for queries running a long time, as well as for scanning and monitoring the use of resources. The queries optimization will be tackled in detail below. 3. QUERY ANALYSIS AND OPTIMIZATION TECHNIQUESAnalysis and opti

42、mization techniques require individual approach but also of the whole set of queries, on the premise that although individual queries can be optimized enough, the set performance may be First and most important optimization technique is to limit the amount of returned data limiting the number of rec

43、ords (the WHERE clause) and fields specified in the SELECT This will lead to an efficient use of the indexes. In principle, a WHERE clause should selective as it is the one establishing the use of indexes on For an efficient use of indexes, according to the utilization requirements for the system, S

44、QL Server provides clustered and non-clustered type indexes. Within the Online Processing schemeswhose tables are frequently updated the clustered indexes recommended, but on as few columns as possible. A large number of indexes in systems will affect the performance of the INSERT, UPDATE, DELETE an

45、d MERGE commands, as all indexes must be accordingly adjusted when data in the tables are modified. Clustered type index esare effective when operators like BETWEEN, , =, and = are used, because after the record containing the first value is found,subsequent records with indexed values are physicall

46、y adjacent. Also, if the query contains clauses like JOIN, ORDER BY or GROUP BY, the clustered type indexes are most appropriate. Non-clustered indexes use is recommended only for databases where updates are infrequent and gives the optimal solution for the exact match type queries. Queries can effe

47、ctively use indexes only if within the WHERE clause functions and arithmetic operations are as much as possible avoided. For example, using the LIKE clause instead of the SUBSTRING function will cause the optimizer to use the index on the Name column:SELECT Name FROM AdventureWorks.Production.Locati

48、on WHERE SUBSTRING(Name,1,1) =P The recommended option is: SELECT Name FROM AdventureWorks.Production.Location WHERE Name LIKE P%Likewise, the exclusion conditions , ! =, !, !, NOT EXISTS, NOT IN, NOT LIKE IN, OR or the LIKE example % will determine the DBMSs optimizer not to use the indexes on colu

49、mns in the WHERE clause. Instead, the inclusion conditions, BETWEEN and the LIKE example %, allow the optimizer to increase query performance because the SQL Server will find the record in the index and will return the adjacent records too, as long as the condition in the WHERE clause remains true.

50、Another optimization method is using, where possible, the BETWEEN clause instead the IN or OR conditions. The SQL Server 2008 will resolve the IN condition by accessing the index for a number of times equal to the number of values by which to search. Using the BETWEEN clause, the optimizer will turn

51、 it into a pair of conditions = = id SELECT * FROM Sales.SalesOrderDetail JOIN Sales.SalesOrderHeader ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID WHERE Sales.SalesOrderHeader.SalesOrderID =1 For each query in an executed stored procedure or script, the SQL Server wil

52、l return the number of affected records (2323333 row(s) affected) To save resources, it is preferable to use the SET NOCOUNT ON SET NOCOUNT OFF sequence.4. NEW OPTIMIZING OPTIONS IN SQL SERVER 2008Some applications run scripts once in a session ( ad hoc workloads). The fact that the SQL Server store

53、s each execution plan for a possible reuse results in an excessive increase of the used memory, leading to a lower efficiency of the instance. In order to solve this problem, Microsoft introduced the option of optimizing this kind of queriesoptimize for ad hoc workload for all its variants of SQL Se

54、rver 2008. Once this option activated, when the script is compiled for the first time, the DBMS will save only a small part of the ad hoc query execution plan. This part will help in a later phase to determine whether that script has been compiled. If the script is re-executed, the SQL Server 2008 r

55、emoves that small part of the originally compiled plan and will recompile the script in order to get the complete execution plan. On first running of the following script, the second query will return a single result, showing that the first query execution plan was memorized.DBCC FREEPROCCACHE DBCC

56、DROPCLEANBUFFERS GO USE AdventureWorks GO SELECT * FROM Person.Contact GO SELECT usecounts, cacheobjtype, objtype, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts 0 AND text like %SELECT * FROM Person.Contact% ORDER BY usecounts DESC; GOBefore re-exec

57、uting the script, we activate the optimization option and change slightly the LIKE clause in the second query (text like SELECT * FROM Person.Contact%): SP_CONFIGURE show advanced options,1 RECONFIGURE GO SP_CONFIGURE optimize for ad hoc workloads,1 RECONFIGURE GO In this case, the second query will

58、 not return any results, reflecting the fact that no execution plan has been memorized for the first query. If we went on re-executing the script, we would notice that the SQL Server 2008 is saving the query execution plan, regardless of the state of the discussed option. Thus we can conclude that a

59、fter activating the optimize for ad hoc workload option only new execution plans are affected and those already memorized are not affected. Another feature introduced in the SQL Server 2008 version is the one allowing a direct creation of the guide (Plan Freezing) for any execution plan for a query

60、existing in the SQL Server memory. This feature adds to the support extension for the execution plans guides for all DML commands. Note that in the 2005 version of the SQL Server, this feature was available only for the SELECT command and involved the possibility to specify suggestions (guides) for