Performance issue in Production box

[madhav33]

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 

[#2]

@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.

[#3]

Also, ensure that the indexes are the same across both environments and check statistics on the production instance. They may be stale and need to be updated.

[#4]

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👍🏻