Same Query producing different plans on different servers.

Environment: SQL Server 2012 SP2 Developer; Memory 16GB; CPU 16 procs. I have a query that produce different query plans when I run it on 2 different servers. What I know: All indexes that are involved in the query are rebuilt on both servers. Stats are up to date. Data set is the same on both servers. SP_Configure values are the same. Differece: SQL Server 2012 SP2 Developer vs SQL Server 2012 SP1 Enterprise. Also I noticed that a one of the plans have Index Seek and the other has Index Scan on one of the tables. The Index Scan plan runs for over 10hrs without completing. The Index Seek plan runs for 6mins and completes successfully. What could be the cause of the different plans? I will send the other plan seperately.

QA_Plan.pesession (16.2 kB)
avatar image By Amadou 1 asked Mar 28 at 09:01 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Apr 11 at 09:40 PM

A lot of possible reasons: Different Plans for "Identical" Servers .

10|10000 characters needed characters left

1 answer: sort voted first

This is the second plan that takes longer.

prod-plan.sqlplan (349.9 kB)
avatar image By Amadou 1 answered Mar 28 at 09:07 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x580
x396
x4

asked: Mar 28 at 09:01 PM

Seen: 40 times

Last Updated: Apr 11 at 09:40 PM