Introduction
Recently, I was informed that a process which executed a distributed query was running longer than was originally anticipated. Initially, I identified that the distributed query was allocating cpu, but the memory and disk io values were increasing at a much slower pace. In fact, so slowly that the memory and disk io values appeared to be frozen. My initial reaction was that this might be the result of a missing/invalid index, and/or a table scan. Using SQL Server Profiler I was able to expose the culprit, which was not an index. Basically, the unresponsive distributed query was utilizing a type of cursor to return the results to the local server.
In the following article I will try to expose the API Server Cursor by providing a brief overview of what an API Server Cursor is, how to identify when a distributed query is utilizing an API Server Cursor, and provide an example of a simple distributed query that eliminates the usage of the API Server Cursor.
What is an API Server Cursor
The SQL Server OLE DB (SQLOLEDB) provider uses one of two methods to fetch the results of a distributed query from a remote server. A default result set will fetch the results of the distributed query from a remote server in a single batch. 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.How is an API Server Cursor used by SQLOLEDB
SQLOLEDB utilizes SQL Server system stored procedures to manage API Server Cursors. Please refer to the API Server Cursors topic on SQL Server 2000/2005 Books Online for additional information about how SQL Server manages Server Cursors.Using SQL Profiler to expose API Server Cursors
SQL Server Profiler exposes the events that occur against a SQL Server. The Detailed Analysis Process section in Chapter 7 of the SQL Server 2000 Operations Guide provides detailed instructions for creating and analyzing a trace. As previously mentioned, the API Server Cursor is managed with system stored procedures so it is simple to identify when a connection is utilizing an API Server Cursor instead of a default result set. For this example, you will need to ensure that SQL Server Profiler is connected to the remote server (linked server) which your distributed query is referencing. Otherwise, you will not see the workload that is being performed on the remote server. I used the following Events in my trace to produce the results that I refer to below:
· Stored Procedures – SP:Completed
· Stored Procedures – SP:StmtCompleted
· Stored Procedures – RPC:Completed
· TSQL – SQL:StmtCompleted
The Stored Procedures – RPC:Completed event exposes the system stored procedures that are used by the API Server Cursor.
Example of Distributed Query that uses an API Server Cursor
The following example has been tested against both SQL Server 2000 and SQL Server 2005 servers. The original distributed query statement utilizes a four-part name to connect to a database on a remote server (linked server). The distributed query joins two tables, both of which reside on the remote server. I anticipated that the distributed query returns about 60 records in total, once the search condition Account = “XYZ” has been applied. Each of the tables referenced in the remote query contain approximately 6000 records each.
use LocalDatabase;
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’
The results of the Profiler trace denote a large number of calls to the sp_cursorfetch system stored procedure were performed on the remote server when the distributed query was executed from SQL Server Query Analyzer. An example of the results from the SQL Server Profiler trace is illustrated below.
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.