Tuesday, December 29, 2009

sql server queries taking long time to execute in production

query execution in the production taking long time

one of the query in the production taking almost 40 minutes instead seconds where it used to, but interestingly the same query in the development server just took 5 seconds to execute. both the environments are same WRT user contention.

The quick steps to troubleshoot in this scenario is

  1. compare the pre-execution plans (Ctrl + L) on both the production and development.

    Note : pre-execution plans are helpful to look at the execution plan with out executing the query as executing query in production with out knowing about is not recommended in production.

    Just look at both the plans on whether they are look same or not with out going in details
    if both are not same then its due to the statistics are not same.
  2. so just check when the statistics in production are updated using the below query

    SELECT STATS_DATE(object_id, stats_id) AS statistics_update_date
    FROM sys.stats
    WHERE object_id = OBJECT_ID(Sales.Customer);

    -- to check the statistics on indexes , instead sys.stats use sys.indexes

    SELECT STATS_DATE(object_id, index_id) AS statistics_update_date
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(Sales.Customer);

  3. if these dates are too old then update the statistics using the below command
    UPDATE STATISTICS Person.Address
    check the performance of the query in production .

Note: even if the “auto create statistics” and “auto update statistics are on” , SQL Server wont create or update for each and every row modification . it will only do when it reaches certain threshold.

No comments:

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...