Is API Cursor is there

Once again, I find something that can hamper the performance of my SQL query, please have a look, it may be quite informative.

Some time the SQL statement take so long time (in distributed  query in remote server environment) to execute and it takes a long CPU time and sometimes it's so slow that u can go outside take some snakes and till it is running.

In this type of condition we always think about missing index or table scan is the main culprit. By using profiler for any others executions tolls you find that alls are ok the main one is API server cursor, so I try to illustrate this in my blog.

SQL Server OLEDB provider use 2 types of methods for fetching data form distributed server.

1.    A default result set will fetch the results of the distributed query from a remote server in a single batch. 

2.    The other method utilizes a cursor, referred to as an API Server Cursor, to fetch the results of the distributed query from the remote server.

When using an API server cursor in OLE DB, ODBC, and ADO, use the functions or methods of the API to:

1. Open a connection.
2. Set attributes or properties defining the characteristics of the cursor the API automatically maps over each result set.
3. Execute one or more Transact-SQL statements.
4. Use API functions or methods to fetch the rows in the result sets.

In SQL profiler when we find the "Stored procedure-RPC compiled" that means that the procedure that used by API Server cursors.

Example of distributed query

SELECT  *

FROM    RemoteServer.RemoteDatabase.dbo.RemoteTable0 as t0

              INNER JOIN RemoteServer.RemoteDatabase.dbo.RemoteTable1 as t1

              ON t0.Account = t1.Account

WHERE   t0.Account = 'XYZ'

 

In SQL server profiler trace

The TextData column for the RPC:Completed events expose the execution of the system stored procedure, sp_cursorfetch.

 
For each execution of this system stored procedure a round-trip between the two servers is made.  The previous statement and the results of the SQL Server Profiler trace should clearly illustrate why using an API Server Cursor has the potential to quickly become a major performance problem.  Elaborating on this, the sp_cursorfetch system stored procedure is executed as a series of batches until all of the data has been returned from the remote server.  The last parameter passed into the sp_cursorfetch system stored procedure is the number of records that are processed for each execution. 

 

In this case, 100 records are processed for each execution of the sp_cursorfetch system stored procedure – Therefore, my example processes about 6000 records in total, for a series of 60 executions with each execution making a round-trip between the servers.

For better understanding http://msdn.microsoft.com/en-gb/library/aa172588(SQL.80).aspx

Posted By : MR. JOYDEEP DAS