SQL Server -- stored procedure performance Issue

Question

I have a table that's being inserted every night and then queried as a reporting table.

The stored proc that works for the query, has dynamic SQL string, paging and two temporary tables within it.

It works well for the first week

Starting from the second week, its performance starts to drop sharply (takes 3.5 minutes to return)

I have acquired and then run the output string of the dynamic SQL, it's dramatically faster(2 Seconds), so I guess it could be related to compiler

Then I did some optimization, like changing count(*) to count(event_id), the performance is immediately back, but the next morning the performance is down again.

Then I changed select into to declaring the temp table explicitly, the performance is immediately back, but the next morning the performance is down again.

Then I changed declaring the temp table explicitly back to select into, the performance is immediately back, but the next morning the performance is down again.

So I guess it has nothing to do with code optimization, it seems every time the SP got compiled, the performance can be better for only less than 24 hours

I am thinking about the nightly insertion, which is also 24 hours' cycle, then I found this with (nolock) thing, which could have locked up Table1

After adding Nolock, the stored procedure ran well for a week, after which we got the same problem again, except that this time, only the web page calling the SP is slow, running SP from DB is fast…

Here is the dynamic sql stored proc:

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  '*

Here's a snapshot of static SQL as an attempt to follow your suggestions:

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

I suggest you to completely avoid dynamic SQL, you can replace some code like this one :

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

by

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

Of course, don't forget to create an index on table2.client_id !

Answer 2

I suggest you try use option WITH RECOMPILE to refresh execution plan every time sp starts.

And there is some more technics for optimize execution plan for such cases: http://msdn.microsoft.com/en-us/library/ms181714.aspx

for example:

OPTIMIZE FOR

or

PARAMETERIZATION

Hope that will help.

Answer 3

It is likely the statistics the query is using to create a plan are gradually becoming out of date as time goes on.

Consider updating the statistics every 6 hours on the tables affected by the query - test this out in a dev environment if possible.