SQL Server Performance

sp_cursoropen

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by electricsk8, Nov 21, 2006.

  1. electricsk8 New Member

    A query that accesses a linked server is causing me issues.

    When I traced the query from the remote server (Server B), I can see that the connection is using a cursor to return results to the remote server (Server A) where the query is run from. The sp_cursorfetch statement is processing in batches of 100 records, and apparently is generating a round-trip (to Server A) for each completion. However, when I issue the same query from Server A to Server B using OPENQUERY - no cursor is used, and the results are returned immediately.

    I've researched everything I can imagine, but with no results ...

    API Server Cursors link:http://msdn2.microsoft.com/en-us/library/aa172588(SQL.80).aspx

    It appears that this option is enabled/disabled at the provider level, but where? I'm using SQLOLEDB, with the following options configured at the server level (Allow Inprocess; Nested Queries).
  2. satya Moderator

    You can find such options under the ProviderOptions button when you're defining the LInked server details using Enterprise manager.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. MohammedU New Member

    I don't think there is way to configure this property either in EM or SQLMS. I belive it is some thing to do with provider.

    Can you check server type in your linked server property. Is it default to sql server or other provider. If it is checked other provider what is the name of the provider?
  4. satya Moderator

    Yes, as long as that provided is compatible with SQL server you can set it.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page