MS SQL Server Cursors

API Server cursors

The downside of server side cursors is that each row has to be fetched one by one, meaning a round trip from the client to the server for each row.

The advantage of server side cursors is that the entire rowset doesn’t need to be shipped over the network when the query is executed. However, this is only useful if the client typically only needs a handful of rows at a time, slowly iterating through the rowset, perhaps based on user input, for example an input form with “next” and “previous” buttons.

If the entire result set needs to be retrieved for example to build a report, it is very inefficient to use server side cursors.


Basic cursor test

As a test the following query is run, once as a normal transact-sql statement and again as a cursor, using the fetch command.

select * from "APPT6" with ( NOLOCK) Where ("APPT6"."bAuditAppt" = 0) and ("APPT6"."room" = 'Surgery 12_2') and ("DELETED" = 0) Order By "APPT6"."bAuditAppt" ASC,"APPT6"."room" ASC,"APPT6"."dtFollowup" ASC,"APPT6"."RecordNum" ASC

Running this query results in 10521 rows being retrieved in 2 seconds.
Execution consumed 281 milliseconds of CPU time.

Now

DECLARE test_cursor CURSOR
FOR
select * from "APPT6" with ( NOLOCK) Where ("APPT6"."bAuditAppt" = 0) and ("APPT6"."room" = 'Surgery 12_2') and ("DELETED" = 0) Order By "APPT6"."bAuditAppt" ASC,"APPT6"."room" ASC,"APPT6"."dtFollowup" ASC,"APPT6"."RecordNum" ASC


OPEN test_cursor

FETCH NEXT FROM test_cursor

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM test_cursor

END

close test_cursor

deallocate test_cursor

Running the while loop to fetch the same 10521 rows takes 17 seconds.
Execution with the cursor consumed 1110 milliseconds of CPU time


Simulation test

Cursor performance penalties can be clearly shown using a test application. The test app is written in c# and performs a simple select query that is present in the TITANIUM application. The query is run using two different methods, with cursors and without cursors.

The application spawns 50 threads and each thread executes the same query 20 times with a 125 millisecond pause between each execution. The queries are run against the TITANIUM_TEST database. The SQL server instance has virtually zero load on it, so the CPU usage can be clearly seen.

Running with cursors the application produces around 50 cursor requests per second, equivalent to the average cursor request rate on production. The SQLSVR process %CPU Time can be seen to roughly follow the cursor request rate and matches the production CPU Time. Also overall CPU time can be seen to average around 30-40%.

Running without cursors the exact same query has a much lower %CPU Time and executes in less than half the time.

With Cursors, notice the SLQSVR (Yellow) requires a scale of 400% because of the quad processor host.









Without Cursors





Simulation code

C# Test code

public static void fast_query()
{


OleDbConnection nwindConn =
new OleDbConnection("Provider=SQLOLEDB;Data Source=10.8.199.88;Password=whatf0r;User ID=sa;Initial Catalog=TITANIUM_TEST");



OleDbCommand catCMD = nwindConn.CreateCommand();
catCMD.CommandText = "select * from \"CHART5\" with ( NOLOCK) Where (\"CHART5\".\"ridPatient\" = '0000022500:00019:00005') and (\"DELETED\" = 0) Order By \"CHART5\".\"ridPatient\" ASC,\"CHART5\".\"wUniquecode\" ASC,\"CHART5\".\"RecordNum\" ASC";

nwindConn.Open();


for(int i = 0; i < 20; i++)
{


OleDbDataReader myReader = catCMD.ExecuteReader();





while (myReader.Read())
{
string ls = myReader.GetString(0);
//Console.WriteLine("\t{0}", myReader.GetString(0));
}




myReader.Close();

Thread.Sleep(150);

}
nwindConn.Close();


}









public static void slow_query()
{

ADODB.Connection cn = new ADODB.Connection();
cn.Open("Provider=SQLOLEDB;Data Source=10.8.199.88;Password=whatf0r;User ID=sa;Initial Catalog=TITANIUM_TEST", "sa", "whatf0r", 0);

ADODB.Recordset rs = new ADODB.Recordset();
rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
rs.CursorType = ADODB.CursorTypeEnum.adOpenDynamic;
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic;

//"select * from \"APPT6\" with ( NOLOCK) Where (\"APPT6\".\"bAuditAppt\" = 0) and (\"APPT6\".\"room\" = 'Surgery 12_2') and (\"DELETED\" = 0) Order By \"APPT6\".\"bAuditAppt\" ASC,\"APPT6\".\"room\" ASC,\"APPT6\".\"dtFollowup\" ASC,\"APPT6\".\"RecordNum\" ASC"

for (int i = 0; i < 20; i++)
{
rs.Open("select * from \"CHART5\" with ( NOLOCK) Where (\"CHART5\".\"ridPatient\" = '0000022500:00019:00005') and (\"DELETED\" = 0) Order By \"CHART5\".\"ridPatient\" ASC,\"CHART5\".\"wUniquecode\" ASC,\"CHART5\".\"RecordNum\" ASC", cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
//rs.ActiveConnection = null;


bool finished = false;
while (!finished)
{
rs.MoveNext();

if (rs.EOF)
{
finished = true;
rs.MoveLast();

}
else
{


string ls = (string)rs.Fields[0].Value;
//Console.WriteLine(rs.Fields[0].Value);
}
}

rs.Close();

Thread.Sleep(150);
}
cn.Close();



}

Production Trace

The SQL Server trace also shows that cursor fetches are more prevalent that any other statement. The observable cursors are API cursors that result from ADO, ODBC or OLE DB server side cursors. No block fetches can be seen in the trace and this indicates that a fetch is being executed for each row in the result set.

http://msdn2.microsoft.com/en-gb/library/aa172588(SQL.80).aspx

As described in the above article API server cursors can be identified in the trace by the sp_cursoropen and sp_cursorfetch procedures being present.

Below is an excerpt from a profiler trace on the TITANIUMDENTAL user. One of the common queries can be seen. It is being requested as an API server cursor. A long list of fetch calls can be seen, the fourth parameter to the fetch call shows the number of rows being fetched which is 1 row per fetch.

The trace indicates that the duration from cursor open to the last fetch call is just under half a second. This provides adequate performance for the client; however the server sustains a CPU overhead for processing so many fetch calls in quick succession. This can be observed by watching the % CPU time by sql server threads.

In real time it can be seen that each thread spikes to around 30-40% CPU Time momentarily as each session executes its cursor fetches. Cumulatively it is witnessed as an average utilisation of about 30-40%