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.