CN103399851A

CN103399851A - 一种结构化查询语言（sql）脚本的性能分析与预测方法与系统

Info

Publication number: CN103399851A
Application number: CN2013102564536A
Authority: CN
Inventors: 赵俊
Original assignee: Ctrip Computer Technology Shanghai Co Ltd
Current assignee: Shanghai Ctrip Business Co Ltd
Priority date: 2013-06-25
Filing date: 2013-06-25
Publication date: 2013-11-20
Anticipated expiration: 2033-06-25
Also published as: CN103399851B

Abstract

本发明提供了一种结构化查询语言（SQL）脚本的性能分析与预测方法，其包括：提取结构化查询语言脚本运行的信息；定位数据库中性能消耗占比大于一预设值的结构化查询语言脚本；对定位到的结构化查询语言脚本进行优化；运行优化后的结构化查询语言脚本，并比较结构化查询语言脚本优化前后的性能差异；以及，将比较得到的优化差异记录到经验数据库的步骤。本发明提供了一个数据库性能问题在SQL脚本层面的定位与预测功能：可计算出每句SQL脚本消耗当前数据库服务器的CPU和IO的占比；由此预测不同的调优方法对数据库服务器带来的直接变化，其可大大提高优化效率，降低优化成本和风险。

Description

一种结构化查询语言(SQL)脚本的性能分析与预测方法与系统

技术领域

本发明涉及一种基于对企业数据库中产生的结构化查询语言（SQL）脚本的性能分析与预测的而实现的数据性能监控方法和系统。

背景技术

随着企业信息系统日益庞大、数据量与日俱增，就会出现数据访问、运算等方面的性能问题，例如，某个企业的SQL server可能会运行几万个SQL脚本，而这些脚本如设计不能满足当前实时的业务需求则可能会占用大量个数据库服务器的CPU和IO资源，从而拖累数据库服务器的响应和处理速度。

由于每个企业的数据架构、数据分布、使用方式、使用场景等方面存在巨大差异，所以现在主流数据库管理软件基本只提供一些通用的数据性能监控方面的功能。

SQLserver本身提供了其部属的服务器两次开关机之间的SQL脚本运行的累计logical reads的统计，SQL server使用者可以以此作为脚本的语句运行时服务器CPU消耗的参考量，但由于logical reads的统计本身与CPU没有必然的内在的联系，因此，以此作为SQL脚本的优化的基础往往会白费工夫，即调整的语句可能在实际运行中的CPU消耗并不大。这样，开发人员就很难有效率的定位实际存在问题的SQL脚本的语句并对其进行优化和调整。

发明内容

本发明的目的是解决以上问题，通过搜集、分析数据库管理软件提供的数据性能信息，并集合企业自身各种特性、场景进行研发，提出一种可实现精确定位众多数据脚本中的性能问题和优化方向，并能根据业务预测不同优化方案带来的实际效果的数据库SQL脚本性能监控与预测方法和装置。

为实现以上目的，本发明的一个方面提出了一种结构化查询语言（SQL）脚本的性能分析与预测方法，该方法中，数据库服务器（DB服务器）上除操作系统外仅部署SQL服务器，该方法包括以下步骤：

提取结构化查询语言脚本运行的信息；

定位数据库中性能消耗占比大于一预设值的结构化查询语言脚本；

对定位到的结构化查询语言脚本进行优化；

运行优化后的结构化查询语言脚本，并比较结构化查询语言脚本优化前后的性能差异，以及，将比较得到的优化差异记录到经验数据库。

可以将不同脚本运行环境的脚本优化前后对数据库性能的影响情况记录进入经验数据库。

所述性能差异指数据库服务器的CPU和I/O的占比的变化。

其中，提取SQL脚本运行的信息可通过结构化查询语言服务器提供的DMV（Dynamic Management Views）数据，通过每日提取所有数据库服务器在一段时间内累计运行的SQL脚本和其运行信息实现。

计算结构化查询语言脚本与数据库服务器的性能消耗比采用一下方式实现即通过SQL PLAN来唯一标识多天中运行的SQL脚本，并可以计算出每天每句SQL运行的[schema_name],[object_name],[creation_time],[last_execution_time],[total_physical_reads],[total_logical_reads],[total_logical_writes],[execution_count],[total_worker_time],[total_elapsed_time],[avg_elapsed_time]这些语句中的至少一项。

通过结构化查询语言脚本在数据库服务器的性能消耗占比来排名，其中排名高的SQL脚本一般会引起问题，从而最终确认数据库性能问题是由哪些SQL脚本引起的。

在确认引起数据库性能问题的SQL脚本后，需要对相关的SQL脚本进行优化，再在下一周期内计算优化后的SQL脚本的性能，并与优化前的该脚本的性能进行对比，从而判断优化前后性能差异。

可以会记录每台数据库服务器长期的，例如1年的SQL运行记录，并对这些信息做回归分析，从而形成经验数据库，最后再根据每台服务器实际优化过的案例，来分析预测的误差率。可以用该经验数据库预测后续的优化可能产生的效果，它的作用是告诉开发人员，对某句SQL语句最大可以的优化效果，以此来判断我们是否有必要对其进行优化(如：CPU、I/O等会提升多少性能)。

本发明的另一方面提供了一种结构化查询语言（SQL）脚本的性能分析与预测系统，其包括提取结构化查询语言脚本运行的信息的装置；定位数据库中性能消耗占比大于一预设值的结构化查询语言脚本的装置；对定位到的结构化查询语言脚本进行优化的装置；运行优化后的结构化查询语言脚本，并比较结构化查询语言脚本优化前后的性能差异的装置；以及，将比较得到的优化差异记录到经验数据库的装置。

其中，所述性能差异指数据库服务器的CPU和I/O的占比的变化。

其中，提取SQL脚本运行的信息的装置可通过结构化查询语言服务器提供的DMV数据，通过每日提取所有数据库服务器在一段时间内累计运行的结构化查询语言脚本和其运行信息实现。

计算结构化查询语言脚本与数据库服务器的性能消耗比的装置可以包括：通过SQLPLAN来唯一标识多天中运行的SQL，并可以计算出每天每句SQL运行的

[schema_name],[object_name],[creation_time],[last_execution_time],[total_physical_reads],[total_logical_reads],[total_logical_writes],[execution_count],[total_worker_time],[total_elapsed_time],[avg_elapsed_time]这些语句中的至少一项的差异。

通过结构化查询语言脚本在数据库服务器的性能消耗占比来排名，最终确认数据库性能问题是由哪些SQL脚本引起的。

本发明提供了一个数据库性能问题在SQL脚本层面的定位与预测功能：可计算出每句SQL脚本消耗当前数据库服务器的CPU和IO的占比；由此预测不同的调优方法对数据库服务器带来的直接变化，其可大大提高优化效率，降低优化成本和风险。

附图说明

图1：结构化查询语言脚本在数据库服务器的性能消耗占比。

图2：结构化查询语言脚本前后版本的性能差异，其中可见优化后效果非常明显，这句SQL的性能消耗已经降低到0.005%以下。

图3：优化结构化查询语言脚本对数据库服务器的效果，由该图可见：原先CPU溢出部分的性能问题是由某句SQL引起的，优化此SQL后，这个性能问题消失了。

图4A、4B：显示了如何从网站众多的DB服务器中收集每条SQL语句的运行信息，并进行初步的信息整合。

图5：依据本发明的方法的系统流程图。

图6A、6B：显示了SQL脚本优化前后性能对比。

具体实施方式

下面参照附图对本发明的各种具体实施方式作具体说明。

本发明的SQLserver部属于一台仅具有操作系统的数据库服务器上，从而，SQLserver的运行将占用操作系统上显示的数据库服务器CPU使用率的90%左右。从而，可以将SQLserver的SQL脚本的运行与数据库服务器中CPU的使用率直接对应。

这样可以使得SQLserver执行的各SQL脚本的SQL_IO占比直接反映执行该SQL脚本的数据库服务器的CPU消耗占比。

图4是提取SQL脚本运行的信息的部署图，如图4所示，首先，需要收集每台数据库服务器（DB服务器）的SQL脚本运行的信息和操作系统，例如window的一些性能指标信息。

如图5所示，信息收集可通过以下步骤实现：

通过在每台DB服务器上部署提取SQL SERVER DMV信息(sys.dm_exec_query_stats(nolock)AS qs CROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle)st)的JOB提取单台DB服务器的数据。

通过一个SSIS包轮寻所有DB服务器，把单台DB服务器上收集的数据汇总到一台运算的DB服务器。

其次，由该运算服务器计算SQL脚本与DB服务器的性能消耗比。

在运算服务器上建立一个工作（JOB），每天计算与各DB服务器前一天运行SQL脚本的性能差异。

建立一个SSIS，根据这些差异和DB服务器window的性能指标信息，再计算出具体的性能消耗排名，并把结果同步到一展示结果的WEB程序的DB服务器上。

其中，提取SQL脚本运行的信息可通过每日提取所有数据库服务器在一段时间内，例如8：00-19：00之间，累计运行的结构化查询语言脚本和其运行信息实现。

计算结构化查询语言脚本与数据库服务器的性能消耗比：通过SQL PLAN来唯一标识多天中运行的SQL，并计算出每天每句SQL运行的

[schema_name],[object_name],[creation_time],[last_execution_time],[total_physical_reads],[total_logical_reads],[total_logical_writes],[execution_count],[total_worker_time],[total_elapsed_time],[avg_elapsed_time]。

至此，可以得到一个SQL脚本在执行时SQL_IO消耗量以及在总SQL_IO消耗量中的占比。通过结构化查询语言脚本在数据库服务器的性能消耗占比来排名，最终确认数据库性能问题是由哪些SQL脚本引起的。其中排名高的SQL脚本会引起问题。从图1中可见，有两个SQL脚本SQL_IO消耗量达到了9.09和7.59，而其SQL_IO消耗量的CPU占比达到了9.69%和4.11%。由此可以确定，该两个脚本会引起问题，需要对其进行结构优化。

优化主要是指对SQL脚本的语句结构进行调整，在调整时根据SQL监控器可以直接得到修改前后的logical reads的变化，从而可以推导出SQL_IO消耗量的变化。

如图6A、6B所示，将土6A中的脚本结构调整为图6B中的脚本结构后，Logic reads从47223大大降低至2302，从而可以使得该脚本的SQL_IO消耗量大大降低，并可以使得对应的CPU消耗大大降低。

如图2所示，该脚本调整前的SQL_IO占比达到12.07%，CPU使用占比，即CPU消耗占到4.69%，而调整后，SQL_IO可以降到不足0.0000001%，CPU占比可以降到不足0.001%，即减少4.69%的CPU消耗（由于差异悬殊，图中未示出）。

如图3所示，为真实案例，一句SQL脚本优化后在周四晚上上线，即6月10日更新了SQL脚本，则可以看到上线前的周五和上线后的周五同一时段的SQL服务器上的CPU消耗情况明显下降，达到预期优化目标。

可以会记录每台数据库服务器长期的，例如1年的SQL运行记录，并对这些信息做回归分析，从而形成经验数据库，最后再根据每台服务器实际优化过的案例，来分析预测的误差率。可以用该经验数据库预测后续的优化可能产生的效果，它的作用是告诉开发人员，对某句SQL语句最大可以的优化效果，以此来判断我们是否有必要对其进行优化，例如：CPU、I/O等会提升多少性能。

Claims

1.一种结构化查询语言（SQL）脚本的性能分析与预测方法，其包括以下步骤

提取结构化查询语言脚本运行的信息；

对定位到的结构化查询语言脚本进行优化；

运行优化后的结构化查询语言脚本，并比较结构化查询语言脚本优化前后的性能差异；

以及，

将比较得到的优化差异记录到经验数据库。

2.根据权利要求1中的方法，其特征在于：将不同脚本运行环境的脚本优化前后对数据库性能的影响情况记录进入经验数据库。

3.根据权利要求1中的方法，其特征在于：所述性能差异指数据库服务器的CPU和I/O的占比的变化。

4.根据权利要求1中的方法，其特征在于：其中，提取SQL脚本运行的信息可通过结构化查询语言服务器提供的DMV数据，通过每日提取所有数据库服务器在一段时间内累计运行的结构化查询语言脚本和其运行信息实现。

5.根据权利要求1中的方法，其特征在于：计算结构化查询语言脚本与数据库服务器的性能消耗比：通过SQL PLAN来唯一标识多天中运行的SQL，并可以计算出每天每句SQL运行的[schema_name],[object_name],[creation_time],[last_execution_time],[total_physical_reads],[total_logical_reads],[total_logical_writes],[execution_count],[total_worker_time],[total_elapsed_time],[avg_elapsed_time]这些语句中的至少一项。

6.根据权利要求1中的方法，其特征在于：通过结构化查询语言脚本在数据库服务器的性能消耗占比来排名，最终确认数据库性能问题是由哪些SQL脚本引起的。

7.根据权利要求1中的方法，其特征在于：在确认引起数据库性能问题的SQL脚本后，需要对相关的SQL脚本进行优化，再在下一周期内计算优化后的SQL脚本的性能，并与优化前的该脚本的性能进行对比，从而判断优化前后性能差异。

8.根据权利要求1中的方法，其特征在于：可以会记录每台数据库服务器长期的，例如1年的SQL运行记录，并对这些信息做回归分析，从而形成经验数据库。

9.一种结构化查询语言（SQL）脚本的性能分析与预测系统，其包括

提取结构化查询语言脚本运行的信息的装置；

定位数据库中性能消耗占比大于一预设值的结构化查询语言脚本的装置；

对定位到的结构化查询语言脚本进行优化的装置；

运行优化后的结构化查询语言脚本，并比较结构化查询语言脚本优化前后的性能差异的装置；以及，

将比较得到的优化差异记录到经验数据库的装置。

10.根据权利要求9的装置，其特征在于：提取SQL脚本运行的信息的装置可通过结构化查询语言服务器提供的DMV数据，通过每日提取所有数据库服务器在一段时间内累计运行的结构化查询语言脚本和其运行信息实现。