I was researching some performance issues today on some SQL Server 2000 machines that were experiencing high CPU spikes and I noticed some strange cursors being called on one of the referenced servers. The funny thing was that none of the queries were explicitly creating the cursor themselves. How is this possible, you may ask yourself? Well, the two servers were communicating with one another via the 4 part naming conventions( Server.database.dbo.tablename ) via a SQLOLEDB configured linked server. SQL Server was then creating server side cursors to pass the data back between to two servers. In a profiler trace I saw numerous sp_cursorfetch calls and the CPU started going through the roof. I also noticed that it seemed to be calling sp_cursorfetch for EACH ROW of the recordset. As you can imagine if it is a large table then the number of roundtrips will be immense and in my case that’s exactly what was occurring. To fix the problem I used OPENQuery to run the query statement on the remote server. This allowed me to return the resultset once instead of having a roundtrip for each record.
Here is a sample of a server side cursor that would get created
DECLARE @P1 INT
SET @P1 = 180150000
DECLARE @P2 INT
SET @P2 = 8
DECLARE @P3 INT
SET @P3 = 1
DECLARE @P4 INT
SET @P4 = 109
EXEC Sp_cursoropen @P1 OUTPUT ,
N'select name from sysdatabases
where name not in (''master'',''tempdb'',''model'',''pubs'')
and name not like ''%sqltech%''' ,
@P2 OUTPUT ,
@P3 OUTPUT ,
@P4 OUTPUT
SELECT @P1,@P2,@P3,@P4
Then you will see numerous lines like the following:
exec sp_cursorfetch 180150000, 16, 1, 1
When you execute the OPENQuery on the remote server this doesn’t occur. It executes it once and returns the whole resultset to you.
