After searching for a while, I decided to post this question for lack of finding an answer and apologize if there is a similar question/answer out there.
When running the query below on two similarly set up SQL servers, we encounter different execution plans which affects performance and we need help figuring out the cause.
SELECT process_id INTO #temp FROM revrep_revenue_fact WHERE process_id = 284 DROP TABLE #temp
Execution plan of server B Server B http://s2.postimg.org/z9fjrfv4n/server_B.png
You'll notice that server B has the TOP physical operation in the actual execution plan and we're trying to figure out why. Both queries use the same index in the Index Seek.
Here are some details of server A and server B
Server A and B are both
Windows Server 2008 R2 Standard Service Pack 1
64-bit operating system
SQL Server 2012 Versions obtained by using (SELECT SERVERPROPERTY('ProductVersion'))
Server A SQL version 11.0.3000.0
Server B SQL version 11.0.5058.0
What we've tried
Why does server B have the TOP in the execution plan? In this simple query example there is no real issues but in a larger query the cost rises for the TOP and we see a performance hit. Any help debugging this would be much appreciated and we can get you any additional information you may need to help.
TOPoperator suggests a non-zero
SET ROWCOUNTwas previously executed on that connection. Try again with after executing
SET ROWCOUNT 0;. Also, the SQL versions you posted are not SQL 2008 R2. Run
SELECT SERVERPROPERTY('ProductVersion');on the 2 servers to get the actual SQL Server version.
GOafter the rowcount line. Can you also post the properties of the Top operator?