Query Execution in the production taking long timeone 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
- 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.
- 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
WHERE object_id = OBJECT_ID(Sales.Customer);
SELECT STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes WHERE object_id = OBJECT_ID(Sales.Customer);
- if these dates are too old then update the statistics using the below command
UPDATE STATISTICS Person.Address
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.