We have SQL 2008 R2 servers in our AWS cloud instances. Both environments are in cloud and have same configuration and disk layout.
But one of our SQL query ran with in 0 sec in Test environment and in production it is taking 7 mins to complete.
Test environment is restored with prod backup. So both are exact same copies.
After creating some indexes on Prod server- the query ran in 0sec in production. So i would like to know how we should understand this kind of scenario? How can i found the root cause of this issue as why it happened?
As without indexes in Test environment the query ran faster and with some new indexes(after performing tuning) the query is fixed in Prod.
Can some one suggest what should i be looking at?