sql - Create a scheduler to re-compile stored procedure -

i facing issue on sql server in stored procedure becomes slow after couple of days.

i came know recompiling stored procedure work. however, not want recompile stored procedure every time gets called.

is way create job on sql server execute following statement?

exec sp_recompile n'sp_name'; 

will cause performance issues?

below sp structure.

@start_value int=null, @end_value int=null`enter code here` @uid nvarchar(max)=null, begin    select   dbo.table1.id,                row_number()  on (order table1.updated_on desc) rn,                  convert(varchar(10), dbo.table1.date, 101) tdate,               category               =(                 select top 1 column1 table5 ct1                   table1.category = ct1.category_id               )               ,               typetext              =(                 select top 1 column1 table6 ct1                   table1.type = ct1.type_id              ),               image = stuff(( select distinct ',' + cast(pm.c1 varchar(12))                 table2 pm                 pm.id = table1.id , pm.c1 not null , pm.c1 <> ''                xml path('')), 1, 1, '' )into #temprecords                            dbo.table1                                         ((@uid null or  dbo.table1.id = @uid )             order table1.updated desc                 select @count = count(*) #temprecords;         select *,convert([int],@count) 'total_records' #temprecords          #temprecords.rn between convert([bigint],@start_value) , convert([bigint],@end_value)         end  go 

at first: replace sub-queries in select statement sub-queries in from clause.

here options resolve 'slowing': 1. if procedure executed not try use recompile option. 2. @ statistics. maybe easier update statistics recompile sp. 3. if statistics problem, may try use filtered statistics. 4. if none of these options works, can delete query plan procedure in automated manner query cache , procedure daily recompiled itself.

however, @ first, rebuild main query.