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.
Best Answer
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.