Two separate instances of SQL Server with a different explanation plan

Question

Here I need help from SQL administrators. I have two separate instances of SQL Server on Amazon EC2. One of them is our intermediate environment, and the other is our production environment, but they are configured in the same way (generated from the same image).

We had a database that we copied from the stage onto our production environment last week. The way we copy db to production, we take a backup on it on our intermediate site and restore the backup to production. In any case, we found that in production one specific complex request was turned off after an hour, but this exact request in our intermediate environment ended in 10 minutes.

The explanation plan for both was almost the same, except that on one server he performed a PK scan on a large table (rows 8M), and on the other table he searched for an index. We assume that was the difference. Thus, one server did a lot of disk I / O, and the other did not.

So my question is, what are the reasons that one SQL server installation decides to use the index, while the other ignores it - assuming the same versions of the SQL server and the same dataset? Even better, what are the best ways to figure out why SQL ignores an index?

Answer 1

That was our mistake.

After a thorough investigation, we found that one of our developers added several additional indexes to the db product after the transfer. This was the case when additional indexes actually forced the query optimizer to choose a less efficient route in the production environment.

Removing these additional indexes seems to have raised the performance issue for a particular query, and both plan explanations now match.

Answer 2

SQL Server uses statistics to determine a query execution plan.

Usually they should be the same on the same data sets, but there is a chance of outdated statistics on one of the machines.

Use sp_updatestats to update statistics on both machines.

In addition, I am not familiar with Amazon EC2 , but it may be possible that computers running two instances will have a different amount of CPU installed (or available for use by SQL Server ). This is also taken into account by the optimizer.

Answer 3

Sniffing parameter?

SP will use a query plan that was deemed most appropriate based on the parameters passed to it when it was executed (and therefore compiled) for the first time.

Database recovery erases the plan cache; if the SP on the copy of the database was started with parameters that favored the search for the index, then what will be subsequently used.

You can check this on sp_recompile 'both and run them again with the same parameters.