Join order difference | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Join order differenceWe are using SQL 2008 std on two SQL server computers. One is the publisher and the other one is subscriber. We have a query running (see below) on both the publishing server and the subscriber server. When I check the execution plan, They have a slight difference, which is the join order. Here are the query and some details.
Select 1 from tblMember_Activity ma
inner join tblSchedule_Activity sa on ma.activity_id = sa.activity_ID
inner join tblSchedule_location sl on sa.SchAct_ID = sl.SchAct_ID
where sl.SchLoc_ID = xxxxxx
tblMember_Activity has the primary key of Memeber_ID and Activity_ID
tblSchedule_Activity has the primiary key of SchAct_ID. Activity_ID is one of the fields in this table.
tblSchedule_location has the primary key of SchAct_ID and SchLoc_ID
On the publisher SQL server, the execution plan shows it uses a nested loops join between tblMember_Activity and tblSchedule_location first and then the result set is used for another nested loop join with tblSchedule_Activity. This order of joins totally doesn’t make any sense as there is no key to join between tblMember_Activity and tblActivity_location. In fact, the nested loop join shows a yellow triangle warning indicates that there is no join predicate. On the subscriber server, the join order is followed as how the query is written. The subscriber one executes in less than couple secs. The publisher one takes > 1 minute. If I use the query hint ‘Force Order’, then the publisher one would be done in less than couple secs. Since the tables involved are in the replication, they have the same # of records and indexes. Index statistics update are done the same way and same time (once every day). Optimizer on both servers chose the same indexes and the only difference is the Join order.
Can someone help me understand why the join order is different?
Just because the 2 databases are replicated does not mean that the indexes need to be identical (I’m assuming you do mean replication and not log shipping?). However, you say the QEP uses the same indexes in each case, so I guess they’re OK. Can you confirm that the SQLServer versions (including Sp / CU levels) are identical? Is the hardware different? (eg. does one server have more CPU cores available, and you’re allowing parallelism)? How are you evaluating the query plans? Are you retrieving them from memory using DMVs, or are you running the queries on each server in turn using SSMS? If so, are all the execution options in SSMS identical? (Particularly watch out for the ARITHABORT setting – this can make a huge difference to the query plan being generated). Just some suggestions