Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

SQL Server scrollable server cursors, use them carefully! - SQL Server Knowledge Sharing Network (@sqlmaster)

SQL Server scrollable server cursors, use them carefully!

One sentence, if you haven't used the Cursors sensibly then database performance will be cursed!

This is for newbie users and DBAs to be aware that on general recommendation is server side cursors  are better than client side one, in this you need to be careful using the scrollable API server cursor methods within your environment, that too in a multi-server database environment. As the design this API cursor will be at connection sharing for multiple clients, where the users will assigned by any available connection on the server whenever the client requests are made.

In this case users do not necessarily use the same connection each time on the server, say if user created a cursor within one connection and next time if the user submits a fetch process through the data then the server will allow a different connection, where the cursor will not be available. So to solve this issue or to go back to the previously created connection for the entire result set to the application is to use global temporary tables as a type of insensitive cursor to hold the data results. In this case make sure you use a sequential key (identity) on the table so that you can easily grab the desired result set rather than the complete data set. 

Also you should be aware about bit drawback on using API server cursors for unsupported SQL statements such as:

  • Batches or stored procedures that return multiple result sets.
  • SELECT statements that contain COMPUTE, COMPUTE BY, FOR BROWSE, or INTO clauses.
  • An EXECUTE statement referencing a remote stored procedure.

In any case within SQL Server 2005 TEMPDB is used extensively where the potential performance is most desired for other application processes to, make sure to size the TEMPDB in this case when the result sets are large to accomodate such a data within the global temp tables. I would say only use cursors as a last resort that too for a limited size of data when there is no set-oriented solution is feasible. Ensure to keep the cursor processing on server side within stored procedures (better caching) and if there are multiple fetches keep in mind about network contention then ensure to use API server cursors. Also you can use the USE PLAN query hint with queries that specify cursor requests, documentation refers that "Do not try to force a noncursor plan for a cursor query or vice versa. Plan forcing might fail if you do this, even if the cursor query and noncursor query are the same".

To wrap up the topic here is a sample syntax on creating sample plan guide from BOL:

exec sp_create_plan_guide 
@name = N'CursorGuide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''http://schemas.microsoft.com/sqlserver/2004/07/showplan'''' Version=''''0.5'''' Build=''''9.00.1116''''><BatchSequence><Batch><Statements><StmtSimple>
Useful cursor resource blog post.
Published Monday, March 10, 2008 7:09 AM by ssqa.net


No Comments