sql server - SQL queries slowing down every few days -

i have query has been running fine years, started slowing down lot. instant 25 seconds. after research turned out problem "broken" indexes. reorganizing , rebuilding indexes of 4 tables query uses fixed problem.

great, done thought. after few days same problem again. rebuilding index fixed started coming every few days. query slows down => rebuild indexes. tables pretty constant, few hundred items added in week , have 300k records.

what cause of this?

edit:

it's query, have no idea how make more efficient without breaking things

alter procedure [dbo].[odat_pricesbydealer] (     @dealer_id          int,     @dealergroup_id     int,     @startdate          datetime,     @pricetype_id       int,     @product_groep      nvarchar(30) )     set nocount on      if @pricetype_id = 0         set @pricetype_id = null      if @product_groep = '0'         set @product_groep = null      select a.price_id          , a.make_id         , e.make_code_name         , a.dealergroup_id         , c.product_id         , c.product_code         , c.product_department         , c.product_code_name                , c.product_groep         , c.product_subgroep1         , c.product_subgroep2         , c.product_producent         , b.pricerow_lowerbound         , b.pricerow_upperbound         , b.pricerow_value         , aa.startdate startdate         , a.price_enddate enddate         , d.pricetype_id         , d.pricetype_name         , 3 price_level     dbo.tblprices (nolock)      join udf_pricesbydealer(@dealer_id, @startdate) aa on a.price_startdate = aa.startdate         , a.product_id = aa.product_id         , a.pricetype_id = aa.pricetype_id         , a.make_id = aa.make_id         , a.dealergroup_id = aa.dealergroup_id         , a.dealer_id = aa.dealer_id     join dbo.tblpricerows b (nolock) on a.price_id = b.price_id     join dbo.tblproducts c (nolock) on a.product_id = c.product_id     join dbo.tblpricetypes d (nolock) on a.pricetype_id = d.pricetype_id     join dbo.tblmakes e (nolock) on a.make_id = e.make_id     a.make_id <> 0          , a.dealergroup_id = @dealergroup_id         , a.dealer_id = @dealer_id         , (d.pricetype_id = @pricetype_id or @pricetype_id null)         , (c.product_groep = @product_groep or @product_groep null)         , a.price_authorized = 1         , c.product_exclude_from_pricelist = 0 

and udf in join looks this

alter function [dbo].[udf_pricesbydealer]  (           @dealer_id  numeric,     @startdate  datetime ) returns table       return       (          select max(a.price_startdate) startdate         , a.make_id         , a.product_id         , a.pricetype_id         , a.dealergroup_id         , a.dealer_id            dbo.tblprices (nolock)     join udf_getmakeidfordealer(@dealer_id) b on a.make_id = b.make_id       ( (a.price_startdate <= @startdate , a.price_enddate >= @startdate)              or              (a.price_startdate <= @startdate , a.price_enddate < a.price_startdate) )          , a.make_id <> 0               , a.dealergroup_id <> 0         , a.dealer_id = @dealer_id     group a.make_id         , a.product_id         , a.pricetype_id         , a.dealergroup_id         , a.dealer_id            ) 

and join on udf

alter function [dbo].[udf_getmakeidfordealer] (     @dealer_id numeric ) returns table return (     select m.make_id         , m.make_code_name     [dbo].[tblmakes] m (nolock)     (m.make_id in  (select make_id                          tbldealergroupmakes (nolock)                         dealergroup_id = (select dealergroup_id                                                  tbldealers (nolock)                                                 dealer_id = @dealer_id))             or              m.make_id in (select make_id                          tbldealermakes (nolock)                         dealer_id = @dealer_id , dealermake_exclude = 0))         , m.make_id not in (select make_id                              tbldealermakes (nolock)                             dealer_id = @dealer_id , dealermake_exclude = 1) ) 

punter015 posted, have tune database e.g. maintance plan. did not know if necessary or not (i run same issue once) made investigations, found samples on net , made procedure.

it checks if reindex needed , execute reindex then. run cron task (sqlcmd.exe) every night. don't know if best approach works fine me.

create procedure [dbo].[proc_reorganizeindexes] begin    declare @table nvarchar(100);   declare @index_name nvarchar(100);   declare @ext_frag float;   declare @int_frag float;   declare @sql nvarchar(max);   declare local_index_cursor cursor    select object_name(dt.object_id) tablename       ,si.name indexname       ,dt.avg_fragmentation_in_percent externalfragmentation       ,dt.avg_page_space_used_in_percent internalfragmentation     (       select object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent       sys.dm_db_index_physical_stats (db_id(db_name()),null,null,null,'detailed'   )   index_id <> 0) dt    inner join sys.indexes si on si.object_id=dt.object_id           , si.index_id=dt.index_id , dt.avg_fragmentation_in_percent>10          , dt.avg_page_space_used_in_percent<75 order avg_fragmentation_in_percent desc     open local_index_cursor   fetch next local_index_cursor   @table, @index_name, @ext_frag, @int_frag      while @@fetch_status = 0     begin        print 'need rebuild: ' + @table+ ' index ' + @index_name + ' fragmentation (int/ext): ' + cast(@int_frag varchar(100)) + ' / ' + cast(@ext_frag varchar(100))     set @sql = 'alter index on ' + @table + ' rebuild (fillfactor=90)'       exec sp_executesql @sql        fetch next local_index_cursor       @table, @index_name, @ext_frag, @int_frag     end    close local_index_cursor   deallocate local_index_cursor  end