12

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.

5
  • How may records in dbo.B and is dbo.B.X indexed? Oct 6, 2011 at 17:18
  • 1
    i'm curious if there would be a performance difference if you went with this: select dbo.a.x, dbo.a.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
    – DForck42
    Oct 6, 2011 at 20:23
  • One more thought to throw in the mix. Are you certain the slowdown is due to the cursor fetch? Are you determining this from the execution plan (which is all about estimates) or from a profile trace? Oct 12, 2011 at 0:53
  • It's from a profile trace.
    – ryandenki
    Oct 12, 2011 at 3:50
  • Are the execution plans the same? It's possible that one of them is using a bad execution plan.
    – Zane
    Feb 6, 2013 at 16:38

6 Answers 6

7

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.

6

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.

3
  • Thanks for the hints. Both sys.configurations and DBCC USEROPTIONS are identical between the two machines. No errors or warnings in any Windows or SQL server logs.
    – ryandenki
    Oct 6, 2011 at 5:42
  • 1
    And they also run the identical database layout? No admin plan doing optimizations on on (index rebuild etc.), the databases have the same statistics for relevant objects and teh same disc layout? Same patch level?
    – TomTom
    Oct 6, 2011 at 6:36
  • Yes, same disk, DB layout, and patch level. In fact, the database on the fast machine is a restored backup from the slow machine. And there are no admin plans that vary, as far as I can see.
    – ryandenki
    Oct 6, 2011 at 8:50
6

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
1
  • This is good advice, we are checking the query plans. Actually, the slowdown in the stored procedure seems to be tied to a cursor. See edit.
    – ryandenki
    Oct 6, 2011 at 8:51
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

4

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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