Identical(?) SQL Server 2005 machines; query takes 2sec on one, 15min on the other

Question

The Environment:

We have two 32-bit Windows Server 2003 R2 machines running SQL Server 2005. The hardware configurations are identical servers with Xeon 5160 CPU, 4GB RAM, and 13GB RAID0. AWE and /3GB flags are not enabled.

The servers were set up side-by-side using a predefined installation checklist, and ALL installed software is the same on both machines.

Every SQL server installation setting and patch level we know to check are identical. One difference is that TEMPDB is 400MB on the fast machine, and 1.2GB on the slow machine. However, in both cases, we don't see any TEMPDB allocation taking place.

The Problem:

There is a stored procedure which runs in 2 seconds on one, but 15 minutes on the other. During the additional 15 minutes, there is little to no disk activity, no memory usage changes, but one CPU core is pinned at 100% the entire time.

This behaviour persists even when the databases are backed up from one and restored to the other.

Since it is a stored procedure, the activity monitor and profiler don't show us any detail about where in the stored procedure this high CPU activity is taking place.

The Question:

What else should we be looking at?

Followup:

The slowness occurs in the FETCH NEXT statements for the following cursor definition:

DECLARE C CURSOR FOR
    SELECT X, Y
    FROM dbo.A
    WHERE X NOT IN (SELECT X FROM dbo.B)
    AND Z <=0
...
<snip>
...
FETCH NEXT FROM C INTO @X, @Y
FETCH NEXT FROM C INTO @X, @Y
...

Each of the FETCH statements--on a table containing only about 1000 rows--requires about 7.25 minutes. (No, I don't know why it does two in a row, need to ask the developers, but it does run correctly on both servers).

I'm a little suspicious of that "NOT IN (SELECT ...)", since it looks like Virtual Reads is really high.

Answer 1

Using a performance troubleshooting methodology like Waits and Queues identify the reason for the high CPU consumption, then appropriate action can be recommended once the bottleneck is identified.

Answer 2

SQL Server is picking a different plan on the other box.

Restoring will typically remove issues based on statistics, so I'd look at server differences.

Some coarse checks first. Don't assume: check

• Check that the SQL Server settings are the same in sys.configuration eg Max degree or parallelism
• Run DBCC USEROPTIONS to see if any ANSI settings are different at run time (ANS settings can affect the chosen plan)
• Check the Windows and SQL Server logs to see if there are any issues

Then jump at the deep end, as per Remus' answer.

Answer 3

If all other things are equal, it is likely (as per @gbn's answer) that a different execution plan is being generated on each server. As an academic exercise, it would be interesting to see both plans so grab them from the plan cache on each server and add them to your question if possible. We can then identify the differences in the plans that are causing such a big variation in performance.

For a quick fix, take a look at the USE PLAN hint. This makes it possible to attach the good plan from the fast server, to the stored procedure on the slow server.

Edit: Following update re: cursor

One other variation on your query to try that I don't see mentioned in other answers:

DECLARE C CURSOR FOR
    SELECT X, Y
    FROM dbo.A
    WHERE NOT EXISTS (SELECT 1 FROM dbo.B WHERE dbo.B.X = dbo.A.X)
    AND Z <=0
...
<snip>
...
FETCH NEXT FROM C INTO @X, @Y
FETCH NEXT FROM C INTO @X, @Y

Answer 4

Humor me, and try replacing:

DECLARE C CURSOR FOR
SELECT X, Y
FROM dbo.A
WHERE X NOT IN (SELECT X FROM dbo.B)
AND Z <=0

with this:

DECLARE C CURSOR FOR
SELECT 
    X, 
    Y
FROM dbo.A

    LEFT OUTER JOIN dbo.B
        ON dbo.A.X = dbo.b.X

WHERE dbo.B.X IS NULL
AND Z <=0

I don't think this should manifest itself as a performance problem in the FETCH NEXT FROM portion of your code, but I haven't had my caffeine injection yet. Give my suggestion a try, and let me know.

Hope this helps,

Matt

Answer 5

Check your indexes and update all of your stats. I've had a very similer issue and it turned out that the stats on one machine were wonky.

Answer 6

I've experienced this same behavior twice and I will tell you what fixed it each time:

1.) I added the hint WITH RECOMPILE to the stored procedure because the cached plan was terrible.

2.) I changed the stored procedure to use temporary tables instead of table variables.

I hope either of those help. Good luck.