asked
Same Query showing different execution Plans on two different Servers.
I have a qry and it runs on two different Subscribers, Bith subscribers have exactly same set of indexes and data. how ever on SUB1 the Qry returns in 3 secs and on SUB2 it takes seven minutes. I ran index rebuilds and still the same. I have Looked into the execution plan, SUB1 is using Hash Match while SUB2 is not. Any help in this really apprecaited.
Did you rebuild indexes on both servers? Not just the one that is now taking 7 minutes?
I have rebuild indexes on both server. The both srever have SQL SERVER 2008 SP1, SAme hardware and configurations. And Have the same fill factor. And I have ALLOW_PAGE_LOCKS = ON. I will run stats and check Thanks for you help
Not only the other questions being asked above, same version, same edition, same patch level, same hardware, but also same connection settings, same cost threshold for parallelism, same collations, etc. There are any number of things that can make execution plans be different. You talk about index rebuilds, but what about statistics updates? It sounds like it might be parameter sniffing, but you need to check all that other stuff first.
It sounds like a problem with statistics, but that should have been updated when the indexes where rebuilt (not reorganize). You should also make sure that you got rid of the fragmentation. There is a bug with rebuild index when you are using multiple threads with indexes that's using allow_page_locks=off You should also make sure that you are using the same fill factor on the indexes, or if you haven't specified it on index level, you should look at default fill factor on server level. (I have seen many terrible examples of fill factor settings or the lack of it. You can force the execution plan by using the execution plan from the other server but it's not sure you'll get any performance improvements.
There are a lot of good questions in the comments and answers here already, (I particularly think Grant and Hakan are on the right track with statistics) but there are two I didn't see that I would look at closely: 1. What other queries are running on the SQL Server on the slower machine? If it is being utilized a lot more than the faster machine, that will slow down everything. If there is another query which could be creating a block, that will slow down that particular query potentially by a great deal. 3. What other programs are running on the slower machine? If the slower machine is also hosting several other instances or also acting as a web server, that too could slow things down.
