SQL queries slowing down every few days

Question

I have some query which has been running fine for years, but suddenly it started slowing down a lot. From almost instant to 25 seconds. After some research it turned out that the problem was "broken" indexes. reorganizing and rebuilding all indexes of the four tables that the query uses fixed the problem.

great, all done I thought. But after a few days it was the same problem again. rebuilding the index fixed it but then it started coming back every few days. query slows down => rebuild indexes. The tables are pretty constant, a few hundred items are added in a week and they have like 300k records.

What could be the cause of this?

edit:

It's about this query, I have no idea how to make this 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)
)
AS
    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 AS startdate
        , a.price_enddate AS enddate
        , d.pricetype_id
        , d.pricetype_name
        , 3 AS price_level
    FROM dbo.tblPrices a WITH (NOLOCK) 
    JOIN udf_PricesByDealer(@dealer_id, @startdate) aa ON a.price_startdate = aa.startdate
        AND a.product_id = aa.product_id
        AND a.pricetype_ID = aa.pricetype_ID
        AND a.make_ID = aa.make_ID
        AND a.dealergroup_ID = aa.dealergroup_ID
        AND a.dealer_ID = aa.dealer_ID
    JOIN dbo.tblPriceRows b WITH (NOLOCK) ON a.price_ID = b.price_ID
    JOIN dbo.tblProducts c WITH (NOLOCK) ON a.product_ID = c.product_ID
    JOIN dbo.tblPriceTypes d WITH (NOLOCK) ON a.pricetype_ID = d.pricetype_ID
    JOIN dbo.tblMakes e WITH (NOLOCK) ON a.make_ID = e.make_ID
    WHERE a.make_ID <> 0 
        AND a.dealergroup_ID = @dealergroup_id
        AND a.dealer_ID = @dealer_id
        AND (d.pricetype_id = @pricetype_id OR @pricetype_id IS NULL)
        AND (c.product_groep = @product_groep OR @product_groep IS NULL)
        AND a.price_authorized = 1
        AND c.product_exclude_from_pricelist = 0

and the UDF in the JOIN looks like this

ALTER FUNCTION [dbo].[udf_PricesByDealer] 
(      
    @dealer_ID  numeric,
    @startdate  datetime
)
RETURNS TABLE
AS      
RETURN      
(     
    SELECT MAX(a.price_startdate) AS startdate
        , a.make_id
        , a.product_id
        , a.pricetype_id
        , a.dealergroup_id
        , a.dealer_id       
    FROM dbo.tblPrices a WITH (NOLOCK)
    JOIN udf_GetMakeIdForDealer(@dealer_id) b ON a.make_ID = b.make_ID  
    WHERE ( (a.price_startdate <= @startdate AND a.price_enddate >= @startdate) 
            OR 
            (a.price_startdate <= @startdate AND a.price_enddate < a.price_startdate) ) 
        AND a.make_ID <> 0      
        AND a.dealergroup_id <> 0
        AND a.dealer_id = @dealer_ID
    GROUP BY a.make_id
        , a.product_id
        , a.pricetype_id
        , a.dealergroup_id
        , a.dealer_id           
)

and another join on a UDF

ALTER FUNCTION [dbo].[udf_GetMakeIdForDealer]
(
    @dealer_ID numeric
)
RETURNS TABLE
AS
RETURN
(
    SELECT m.make_ID
        , m.make_code_name
    FROM [dbo].[tblMakes] m WITH (NOLOCK)
    WHERE (m.make_ID in  (SELECT make_ID 
                        FROM tblDealerGroupMakes WITH (NOLOCK)
                        WHERE dealergroup_ID = (SELECT dealergroup_ID 
                                                FROM tblDealers WITH (NOLOCK)
                                                WHERE dealer_ID = @dealer_ID))
            OR 
            m.make_ID IN (SELECT make_ID 
                        FROM tblDealerMakes WITH (NOLOCK)
                        WHERE dealer_ID = @dealer_ID AND dealermake_exclude = 0))
        AND m.make_ID NOT IN (SELECT make_ID 
                            FROM tblDealerMakes WITH (NOLOCK)
                            WHERE dealer_ID = @dealer_ID AND dealermake_exclude = 1)
)

YvesR · Accepted Answer

Punter015 already posted, that you have to tune your database e.g. with a maintance plan. As you did not know if it is necessary or not (I run into the same issue once) I made some investigations, found some samples on the net and finally made this procedure.

It checks if reindex is needed and execute a reindex then. I run it with a cron task (sqlcmd.exe) every night. Don't know if this is the best approach but works fine for me.

CREATE PROCEDURE [dbo].[proc_ReorganizeIndexes]
AS
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 FOR

  SELECT object_name(dt.object_id) Tablename
      ,si.name IndexName
      ,dt.avg_fragmentation_in_percent AS ExternalFragmentation
      ,dt.avg_page_space_used_in_percent AS InternalFragmentation
  FROM
  (
      SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
      FROM sys.dm_db_index_physical_stats (db_id(DB_NAME()),null,null,null,'DETAILED'
  )
  WHERE index_id <> 0) AS dt 
  INNER JOIN sys.indexes si ON si.object_id=dt.object_id 
         AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
         AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 

  OPEN local_index_cursor
  FETCH NEXT FROM local_index_cursor
  INTO @table, @index_name, @ext_frag, @int_frag

    WHILE @@FETCH_STATUS = 0
    BEGIN

      PRINT 'Need rebuild: ' + @table+ ' for index ' + @index_name + ' Fragmentation (int/ext): ' + CAST(@int_frag AS varchar(100)) + ' / ' + CAST(@ext_frag AS varchar(100))
    SET @sql = 'ALTER INDEX ALL ON ' + @table + ' REBUILD WITH (FILLFACTOR=90)'
      EXEC sp_executesql @sql

      FETCH NEXT FROM local_index_cursor
      INTO @table, @index_name, @ext_frag, @int_frag
    END

  CLOSE local_index_cursor
  DEALLOCATE local_index_cursor

END

SQL queries slowing down every few days

Answers (2)

Related Questions