Friday 16 August 2013

1.09 - API CURSORS and linked servers - Grrrrr!!

Like I always say; Every day is a school day.
A normally well performing system today suddenly started using 99% utilisation constantly. After taking a look I noticed that there were some strange requests on the server which all related to FETCH_APICURSORXXXXXXX. Now I could only see what these were by by querying the sys.dm_exec_cursors dmv. I then looked at profiler and saw the millions of sp_cursorfetch transactions and realised that there was a big issue. This was using a cursor because SQL was using an OLEDB connection on the linked server resulting in millions of rows individually returned  for a sizeable dataset.

After a bit of research OPENQUERY resolves this issue but it doesn't use parameters, so you need to use dynamic sql or call sp_executesql from the target server.

I hope this hopes anyone and reduces the the hours that it took me to sort out.

A good reference : http://www.sql-server-performance.com/2006/api-server-cursors/#comment-3847

No comments:

Post a Comment