Good Morning,

I have an exact copy of a database on 2 instances of SQL server (dev and test - dev is restored from test).

There's a view that I select top 1000 rows from that runs extremely slow on both instances (DEV and TEST) however on DEV the execution plan specifies that I'm missing an index however in TEST the execution plan does not specify that I'm missing an index.

Why would this be the case???

Many thanks,

MDB 

can you check the execution plan of query on dev and test- are they same??.

my guess is that on dev- the query plan was cached and it is using not-so optimal plan which was cached.

since you just restored the copy from dev, test server - QO had to build a new query plan - which is optimal.

Just for your peace of mind: 

https://technet.microsoft.com/en-us/library/ms345524%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

"The missing indexes feature is on by default. No controls are provided to turn the feature on or off, or to reset any of the tables returned when the dynamic management objects are queried. When SQL Server is restarted, all of the missing index information is dropped.

This feature can only be disabled if an instance of SQL Server is started by using the -x argument with the sqlservr command-prompt utility. "