none
请教一个调优问题,怎么科学的评价存储过程调优的效果呢? RRS feed

答案

  • Hi Tim-2009,

     

    你可以在执行存储过程前加上SET STATISTICS PROFILE ON  ;SET STATISTICS TIME ON; SET STATISTICS IO ON 分别查看它得执行计划,分析、编译和执行各语句所需的毫秒数,以及磁盘活动量信息。更多详情,请参考:https://docs.microsoft.com/zh-cn/sql/t-sql/statements/set-statistics-io-transact-sql?view=sql-server-2017

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • 已标记为答案 Tim-2009 2019年7月31日 2:11
    2019年7月11日 7:50
  • 一个完整的例子😃。

    由于问题是怎么评价存储过程调优的效果,所以例子就炒鸡简单。

    题目:一张表记录学生的基本信息;另一张表记录学生各个学科的成绩。要求查询出哪些学生挂了科😃

    1. 创建测试数据

    -- 准备测试数据
    create table test_student(
    	 student_id bigint identity(1,1) primary key,
    	 student_name char(64) not null
    );
    create table test_student_grade(
    	 student_grade_id bigint identity(1,1) primary key,
    	 student_id bigint not null,
    	 subject_name char(64) not null,
    	 grade int not null
    );
    create nonclustered index idx_student_id on test_student_grade(student_id) include(grade);
    go
    
    declare @count int;
    set @count = 1;
    while(@count <= 1000)
    begin
    	insert into test_student(student_name)
    	values('name'+CONVERT(char(4),@count));
    	set @count = @count + 1;
    end
    go
    
    declare @count int;
    set @count = 1;
    while(@count <= 1000)
    begin
    	insert into test_student_grade(student_id,subject_name,grade)
    	values
    	(@count,'数学分析',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'高等代数',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'几何',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'概率统计',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'数学模型',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'离散数学',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'微分方程',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'物理学',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'信息处理',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'信息编码与信息安全',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'计算智能',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'计算机科学基础',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'数值计算方法',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'最优化理论',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'计算机图形学',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'c/c++语言',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'java语言',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'汇编语言',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'算法与数据结构',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'软件系统',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'操作系统',58 + cast(ceiling(rand() * 42) as int));
    	set @count = @count + 1;
    end
    go

    2. 两种查询方式(当然这里是为了举例,一般不会这么写😃)

    -- 开启io统计
    set statistics io on;
    -- 开启时间统计
    set statistics time on;
    go
    
    -- 清空所有缓存
    dbcc DROPCLEANBUFFERS;
    go
    -- 执行第一个查询sql
    select distinct(ts.student_name)
    from test_student ts with(nolock)
    inner join test_student_grade tsg with(nolock) on ts.student_id = tsg.student_id and tsg.grade < 60;
    go
    
    -- 清空所有缓存
    dbcc DROPCLEANBUFFERS;
    go
    -- 执行第二个查询sql
    select ts.student_name
    from test_student ts with(nolock)
    where exists (
    	select top 1 1 from test_student_grade tsg with(nolock) where tsg.student_id = ts.student_id and tsg.grade < 60
    );
    go
    
    -- 关闭时间统计
    set statistics time off;
    -- 关闭io统计
    set statistics io off;
    go

    3. 分析两个语句的查询成本

    新用户暂时没法贴图,所以先放个图片链接:

    https://social.msdn.microsoft.com/Forums/getfile/1461311

    https://social.msdn.microsoft.com/Forums/getfile/1461314

    说明:

    这里是用的SSMS的查询计划视图,执行前在菜单栏或者右键,选择“包含实际的执行计划”。这样就可以以视图的方式直观的看到两个语句的查询计划,以及会将每一步执行计划的成本汇总。对比两个语句总的开销比例,可以初步判断优略;

    使用 set statistics io on  和 set statistics time on 分别开启 io 统计 和 时间统计,直观的对比两个语句引起的 物理IO次数、逻辑IO次数、编译时间、执行时间;

    在两个语句执行前都使用 dbcc DROPCLEANBUFFERS 清除了所有缓存,把影响因素降到最低。生产环境千万别用这个

    4. 时间少就是好?开销低就是好?

    以上应该算是比较科学的评价了😃,另外题主说的是“存储过程”,那么可以忽略语句“编译时间”这个选项,存储过程只要不是太复杂的都语句拼接,都会预编译的。

    最后,有时候执行时间少的语句 但是 开销更高,这种就需要经验去取舍了;比如开销只高了一点点,多出的物理IO自己也能接受,再假设语句本身执行的频率很低,那么用一点空间换取时间是值得的。望采纳!


    • 已建议为答案 欧小川 2019年7月15日 13:29
    • 已编辑 欧小川 2019年7月15日 13:32 表述错误
    • 取消建议作为答案 欧小川 2019年7月16日 7:39
    • 已标记为答案 Tim-2009 2019年7月25日 5:34
    2019年7月15日 13:25

全部回复

  • 一般地,可以看sys.dm_exec_proc_stats,比较这个存储过程调优前后的执行时间,IO

    想不想时已是想,不如不想都不想。

    2019年7月11日 6:54
    版主
  • Hi Tim-2009,

     

    你可以在执行存储过程前加上SET STATISTICS PROFILE ON  ;SET STATISTICS TIME ON; SET STATISTICS IO ON 分别查看它得执行计划,分析、编译和执行各语句所需的毫秒数,以及磁盘活动量信息。更多详情,请参考:https://docs.microsoft.com/zh-cn/sql/t-sql/statements/set-statistics-io-transact-sql?view=sql-server-2017

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • 已标记为答案 Tim-2009 2019年7月31日 2:11
    2019年7月11日 7:50
  • Hi Tim-2009,

     

    你可以在执行存储过程前加上SET STATISTICS PROFILE ON  ;SET STATISTICS TIME ON; SET STATISTICS IO ON 分别查看它得执行计划,分析、编译和执行各语句所需的毫秒数,以及磁盘活动量信息。更多详情,请参考:https://docs.microsoft.com/zh-cn/sql/t-sql/statements/set-statistics-io-transact-sql?view=sql-server-2017

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    请问有缓存看时间是不是不准啊?
    2019年7月11日 11:23
  • 是的,建议每次测试执行前,清理一下缓存计划

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年7月12日 1:16
  • 一个完整的例子😃。

    由于问题是怎么评价存储过程调优的效果,所以例子就炒鸡简单。

    题目:一张表记录学生的基本信息;另一张表记录学生各个学科的成绩。要求查询出哪些学生挂了科😃

    1. 创建测试数据

    -- 准备测试数据
    create table test_student(
    	 student_id bigint identity(1,1) primary key,
    	 student_name char(64) not null
    );
    create table test_student_grade(
    	 student_grade_id bigint identity(1,1) primary key,
    	 student_id bigint not null,
    	 subject_name char(64) not null,
    	 grade int not null
    );
    create nonclustered index idx_student_id on test_student_grade(student_id) include(grade);
    go
    
    declare @count int;
    set @count = 1;
    while(@count <= 1000)
    begin
    	insert into test_student(student_name)
    	values('name'+CONVERT(char(4),@count));
    	set @count = @count + 1;
    end
    go
    
    declare @count int;
    set @count = 1;
    while(@count <= 1000)
    begin
    	insert into test_student_grade(student_id,subject_name,grade)
    	values
    	(@count,'数学分析',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'高等代数',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'几何',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'概率统计',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'数学模型',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'离散数学',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'微分方程',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'物理学',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'信息处理',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'信息编码与信息安全',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'计算智能',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'计算机科学基础',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'数值计算方法',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'最优化理论',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'计算机图形学',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'c/c++语言',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'java语言',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'汇编语言',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'算法与数据结构',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'软件系统',58 + cast(ceiling(rand() * 42) as int)),
    	(@count,'操作系统',58 + cast(ceiling(rand() * 42) as int));
    	set @count = @count + 1;
    end
    go

    2. 两种查询方式(当然这里是为了举例,一般不会这么写😃)

    -- 开启io统计
    set statistics io on;
    -- 开启时间统计
    set statistics time on;
    go
    
    -- 清空所有缓存
    dbcc DROPCLEANBUFFERS;
    go
    -- 执行第一个查询sql
    select distinct(ts.student_name)
    from test_student ts with(nolock)
    inner join test_student_grade tsg with(nolock) on ts.student_id = tsg.student_id and tsg.grade < 60;
    go
    
    -- 清空所有缓存
    dbcc DROPCLEANBUFFERS;
    go
    -- 执行第二个查询sql
    select ts.student_name
    from test_student ts with(nolock)
    where exists (
    	select top 1 1 from test_student_grade tsg with(nolock) where tsg.student_id = ts.student_id and tsg.grade < 60
    );
    go
    
    -- 关闭时间统计
    set statistics time off;
    -- 关闭io统计
    set statistics io off;
    go

    3. 分析两个语句的查询成本

    新用户暂时没法贴图,所以先放个图片链接:

    https://social.msdn.microsoft.com/Forums/getfile/1461311

    https://social.msdn.microsoft.com/Forums/getfile/1461314

    说明:

    这里是用的SSMS的查询计划视图,执行前在菜单栏或者右键,选择“包含实际的执行计划”。这样就可以以视图的方式直观的看到两个语句的查询计划,以及会将每一步执行计划的成本汇总。对比两个语句总的开销比例,可以初步判断优略;

    使用 set statistics io on  和 set statistics time on 分别开启 io 统计 和 时间统计,直观的对比两个语句引起的 物理IO次数、逻辑IO次数、编译时间、执行时间;

    在两个语句执行前都使用 dbcc DROPCLEANBUFFERS 清除了所有缓存,把影响因素降到最低。生产环境千万别用这个

    4. 时间少就是好?开销低就是好?

    以上应该算是比较科学的评价了😃,另外题主说的是“存储过程”,那么可以忽略语句“编译时间”这个选项,存储过程只要不是太复杂的都语句拼接,都会预编译的。

    最后,有时候执行时间少的语句 但是 开销更高,这种就需要经验去取舍了;比如开销只高了一点点,多出的物理IO自己也能接受,再假设语句本身执行的频率很低,那么用一点空间换取时间是值得的。望采纳!


    • 已建议为答案 欧小川 2019年7月15日 13:29
    • 已编辑 欧小川 2019年7月15日 13:32 表述错误
    • 取消建议作为答案 欧小川 2019年7月16日 7:39
    • 已标记为答案 Tim-2009 2019年7月25日 5:34
    2019年7月15日 13:25
  • 多谢小川给了一个完整的例子,最近忙,没有上来,多谢了!
    2019年7月25日 5:34