0

I have a query which selects a couple of rows from a view based on the variables declared. I ran the same query in lower environments and to my surprise, I got the result in less than 3 minutes whereas when I ran the query on Production environment the query never completed.

DECLARE @TodayDt    INTEGER,
        @YestDt     INTEGER;


SET @TodayDt = (SELECT date FROM Calendar WHERE Calendar_Date = DATEADD(DAY, -365, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)));
SET @YestDt = (SELECT date FROM Calendar WHERE Calendar_Date = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME));


SELECT  Cast(Emp_Name as varchar(10)), 
        CAST(Emp_Division AS VARCHAR(3)),
        Cast(Emp_Sales as varchar(10)), 
        Cast(Emp_Join_Dt as int)
    FROM dbo.View_Employee_Records
    WHERE Emp_ID IN ('281','274')
          AND   JoinDt >= @TodayDt
          AND   ResignDt <= @YestDt

Also, If I run my query without variables on Production environment It gives me result in very less time. Can anyone suggest me a possible cause and resolution for this issue.

  • 3
    have you looked at the execution plans in each environment? Most likely the issue is a lack of indexes and different amounts of data and data distributioni – Alen Feb 26 at 15:24
  • @Alen, I have the same of data and index created on the table – l.lijith Feb 26 at 15:34
  • 1
    You'll need to add the table and view definitions, and some information about the amount of data. – David Browne - Microsoft Feb 26 at 16:39
  • 2
    This type of issue 90% of the time is because in production, your tables' statistics are not updated or not updated accurately. Try to update your table statistics with full scan (if possible), and then run your query again. – jyao Feb 26 at 16:45
  • 1
0

When I checked the history of my maintenance plan created on this specific database, I could see the Rebuild and Update statistics was not run from a couple of weeks.

I ran Rebuild Index, Update statistics and Recompile to fix this issue and it worked.

0

Check the execution plans on each server and make sure they are using the same indexes, same number of rows, etc.

I've had a similar issue which was caused by a difference in distribution of data in production and I found it by checking the statistics on each server

      SELECT 
       --into ##SQL_STATS_INFO
        --comment out h. columns for non-sql 2016 servers
       distinct 
    (select db_name()) as Database_name
    ,o.name as Table_name
    ,c.name AS column_name 
    ,s.name AS statistics_name

    ,p.last_updated
    ,p.rows
    ,p.rows_sampled
    ,h.range_high_key
  ,h.range_rows
  ,h.equal_rows
  ,h.distinct_range_rows
  ,h.average_range_rows
  ,p.modification_counter 
  ,h.step_number
   --,sc.stats_column_id  
        FROM sys.stats AS s  
        cross apply sys.dm_db_stats_properties(s.object_id, s.stats_id) p 
        cross apply sys.dm_db_stats_histogram(s.object_id, s.stats_id) h
        INNER JOIN sys.stats_columns AS sc   
        ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
        INNER JOIN sys.columns AS c   
        ON sc.object_id = c.object_id AND c.column_id = sc.column_id  
        inner join sys.objects o
         on o.object_id = s.object_id
   WHERE o.object_id > 100
   and p.rows > 1000
    and p.modification_counter > 100
   order by p.last_updated desc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.