SQL Server --存储过程性能问题

Question

我有一个每天晚上都会插入的表，然后作为一个报告表进行查询。

用于查询的存储进程中有动态SQL字符串、分页和两个临时表。

它在第一周工作得很好

从第二周开始，其性能开始急剧下降(需要3.5分钟才能恢复)

我已经获取并运行了动态SQL的输出字符串，它的速度快得多(2秒)，所以我猜这可能与编译器有关

然后我做了一些优化，比如将count(*)改为count(event_id)，性能立即恢复，但第二天早上性能又下降了。

然后我将select into更改为显式声明临时表，性能立即恢复，但第二天早上性能再次下降。

然后我更改了将临时表显式声明回select into，性能立即恢复，但第二天早上性能再次下降。

所以我猜这与代码优化无关，似乎每次编译SP时，性能只会在不到24小时内得到改善

我在考虑夜间插入，这也是24小时的周期，然后我发现了这个with (nolock)的东西，它可能会锁定Table1

添加Nolock后，存储过程正常运行了一周，之后我们又遇到了同样的问题，只不过这次只有调用SP的网页很慢，从数据库运行SP是快速…

下面是动态sql存储过程：

CREATE PROCEDURE [dbo].[fs_1_usp_query]
 @paramerter_client_id   int = null,   
 @paramerter_event_type_id  int = null,   
 @paramerter_start_date         datetime = null,   
 @paramerter_end_date           datetime = null,   
 @paramerter_page_index   int = 1,
 @paramerter_sort_direction varchar(20),   
 @paramerter_page_count   int = 30   
AS   
BEGIN       
 SET ARITHABORT ON;   
 SET NOCOUNT ON;   

 declare @sql nvarchar(max)   

 set @sql =      '

    create table #output2    
    (   
            page_index                                      int,       
                rownumber                                         int, 
            page_count                          int,    
            client_id                           int,    
            date                                            datetime,   
      )   




        --insert into #output1   
  select   
    page_count = count(event_id) over(),       
    table1.*
    into #output1'   
  set @sql = @sql + '   
   from   
    table1 table1 with (nolock)   
   inner join   
    table2 table2 with (nolock)   
   on   
    ............................
   inner join   
    table3 table3 with (nolock)   
   on   
    ............................
   inner join   
    table4 table4
   on   
    ............................
   where   
    ............................

 if (@paramerter_client_id is not null)   
  set @sql = @sql + ' and table2.client_id = @paramerter_client_id'   

 if (@paramerter_event_type_id is not null)   
  set @sql = @sql + ' and table2.event_type_id = @paramerter_event_type_id'   

 if (@paramerter_start_date is not null)   
  set @sql = @sql + ' and table2.created_date >= @paramerter_start_date'   
 if (@paramerter_end_date is not null)   
        set @sql = @sql + ' and table2.created_date <= @paramerter_end_date'   

 declare @lv_begin_index int   
 declare @lv_end_index int   
 set @lv_begin_index = ((@paramerter_page_index - 1) * @paramerter_page_count) + 1     
 set @lv_end_index = @lv_begin_index + @paramerter_page_count    

 set @sql = @sql +   ' 

 UPDATE #output1
    SET osat_rating = ''-''   
    WHERE LEFT( osat_rating , 1 ) = ''-''         

 insert into #output2 
 select    
  page_index = ' + convert(varchar, @paramerter_page_index) + ',       
     row_number() over (order by [' + @paramerter_sort_expression + '] '+ @paramerter_sort_direction + ') as rownumber, 
     #output1.* 
 from #output1

 select #output2.* 
 from #output2 
 where   
  rownumber >= ' + convert(varchar, @lv_begin_index) + '   
 and   
  rownumber < ' + convert(varchar, @lv_end_index)    '   

 set @sql = @sql + '    
 drop table #output1    
 drop table #output2  '*

以下是静态SQL的快照，以尝试遵循您的建议：

Where
    Column3 = Coalesce(@parameter3, Column3)
   and
    (@start_date is null or Column_created_date >= @start_date)

   and
    (@param_1 is null 
            or 
                (@param_1 not in (‘ConstantString1’, 'ConstantString2') and Column1 = @param_1)
        or 
            (@param_1 = ‘ConstantString1’ and Column1 like 'ConstantString1%')
        or 
            (@param_1 = ‘ConstantString2’ and (Column1 is null or Column1 = ''))
)
If(@parameter_sort_direction = 'DESC')
 Begin
     insert into #temp_table_result
     select     
      page_index = convert(varchar, @parameter_page_index),        
      row_number() over 
        (
            order by CASE 
                    WHEN @parameter_sort_expression = 'Column1' THEN Column1 
                    WHEN @parameter_sort_expression = 'Column2' THEN Column2 
                    WHEN @parameter_sort_expression = 'Column3' THEN Column3 
                    WHEN @parameter_sort_expression = 'Column4' THEN Column4 
                    WHEN @parameter_sort_expression = 'Column5' THEN Column5 
                    WHEN @parameter_sort_expression = 'Column6' THEN Column6 
                    WHEN @parameter_sort_expression = 'Column7' THEN Column7
                    WHEN @parameter_sort_expression = 'Column8' THEN Column8 
                END desc--CASE 
                --      WHEN @parameter_sort_direction = 'ASC' THEN asc 
                --      WHEN @parameter_sort_expression = 'DESC' THEN     desc              
                --END
        ) as rownumber,  
      #temp_table_staging.*  
     from #temp_table_staging 
 END

Answer 1

随着时间的推移，查询用来创建计划的统计信息很可能会逐渐过时。

考虑每6小时更新一次受查询影响的表的统计信息-如果可能的话，在开发环境中进行测试。

Answer 2

我建议您尝试使用选项WITH RECOMPILE在每次启动sp时刷新执行计划。

对于这样的情况，还有一些优化执行计划的技术：http://msdn.microsoft.com/en-us/library/ms181714.aspx

例如：

OPTIMIZE FOR

或

PARAMETERIZATION

希望这能有所帮助。

Answer 3

我建议你完全避免使用动态SQL，你可以像这样替换一些代码：

     if (@paramerter_client_id is not null)   
         set @sql = @sql + ' and table2.client_id = @paramerter_client_id'  

通过

and (@paramerter_client_id IS NULL OR table2.client_id = @paramerter_client_id)

当然，不要忘记在table2.client_id上创建索引！