10

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.

The query:

SELECT process_id
INTO #temp
FROM revrep_revenue_fact
WHERE process_id = 284 
DROP TABLE #temp

Execution plan of server A Server B

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

24GB RAM

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

  1. Clearing procedure caches
  2. Rebuilding indexes
  3. Refreshing statistics
  4. SET ROWCOUNT 0 with rowcount and go

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.

Improve this question
4
  • The TOP operator suggests a non-zero SET ROWCOUNT was 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.
    – Dan Guzman
    Nov 1, 2015 at 13:34
  • I updated the original post with the sql versions using the select query provided. The servers that sql is installed on are windows 2008 R2 servers while the sql servers installed are 2012. As previously suggested by another member then deleted, we tried setting ROWCOUNT to 0 with no change in the execution plan. See the updated question
    – Arthur Yegiazaryan
    Nov 1, 2015 at 14:00
  • Can you put a GO after the rowcount line. Can you also post the properties of the Top operator?
    – Rob Farley
    Nov 1, 2015 at 15:07
  • Rob, added the GO and properties from TOP to original post
    – Arthur Yegiazaryan
    Nov 1, 2015 at 15:14

2 Answers 2

Reset to default
12

Check to ensure the database compatibility level is the same on the 2 servers. I ran a quick test on a SQL Server 2012 instance and see the TOP operator is introduced if the compatibility level is 100 or lower. Unless you have a specific reason to do otherwise, it is best to use the 110 (SQL Server 2012) compatibility level on a SQL Server 2012 instance.

Improve this answer
0
2

I reckon you have a value for ROWCOUNT. Do:

SET ROWCOUNT 0

...to turn it off.

Improve this answer
3
  • 1
    SET ROWCOUNT 0 did not remove the TOP from the execution plan
    – Arthur Yegiazaryan
    Nov 1, 2015 at 12:17
  • 1
    @Arthur While that wasn't the solution in your case, it could be the reason for the behavior for future readers. (Sorry Rob, I took the liberty of undeleting your post because this really could be the issue for the same symptom.)
    – Aaron Bertrand
    Nov 2, 2015 at 6:14
  • Aaron, agreed and thanks for bringing it back. Not the solution for me but could be for others.
    – Arthur Yegiazaryan
    Nov 2, 2015 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.