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