Aug 15 2022 10:36 AM
Hi Folks,
I have one query which will give only 192 records from database tables. In that query multiple SQL views are used.
When I am executing that query in Production it is taking 3 mins but when I execute the same query on test environment it is executing in 1 second.
To further drill down the issue I ran the execution plan in production box I am getting index scan and no parallelism but in test environment i am getting Index seek and parallelism.
all the SQL server properties and database properties are same in production and test box.
Please help me to under the issue.
Regards
Sachin Goel
Aug 16 2022 05:28 AM
@madhav33 did you check the number of records that is being traversed in both production and test and are there identical indices in both, can you share the plan here.
Aug 16 2022 09:11 AM
Aug 16 2022 12:45 PM
As mentioned, likely to be a difference in the datasets (ie. Smaller rowcounts in non prod), stats out of date, possibly hardware differences between the environments.
Start with updating the stats and see if that works. The execution plan should hopefully change to what you're looking for👍🏻