Microsoft® SQL Server® 2012 Bible

513

Chapter 19: Confi guring SQL Server

19

Cursor-Confi guration Properties

The cursor-confi guration properties, as shown in Table 19-8, are used to control cursor

behavior in SQL Server.

TABLE 19-8 Cursor-Confi guration Properties

Property Level* Graphic Control Code Option

Cursor

Threshold

S Management Studio EXEC sp_configure 'cursor

threshold'

Cursor Close on

Commit

SDC Management Studio ALTER DATABASE <DB Name> SET

cursor_close_on_commit

Cursor Default D Management Studio ALTER DATABASE <DB Name> SET

cursor_default

* The confi guration level refers to server, database, or connection.

To view information about the open cursors in various databases, query the sys.dm_exec_cursors dynamic

management view.

Cursor Threshold

The cursor threshold property sets the number of rows in a cursor set before the cursor

keysets are asynchronously generated. The query optimizer estimates the number of rows

that will be returned from the result set. If the estimated number of rows is greater than the

cursor threshold, then the cursor is asynchronously generated; if it is synchronously

generated causing a delay, the query must wait until all rows are fetched. Every cursor key-

set will be asynchronously generated if the cursor threshold property is set to 0.

The default of -1 causes all keysets to be synchronously generated, which is okay for

smaller keysets. For larger cursor keysets, though, this may be a problem.

In Management Studio, you can set the cursor threshold option to the wanted value in

the “Cursor Threshold box on the Server Properties Advanced tab (refer to Figure 19-10).

When you work with cursors, the following code permits synchronous cursor keysets for

cursors of up to 10,000 rows:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'cursor threshold', 10000;

RECONFIGURE;

c19.indd 513c19.indd 513 7/30/2012 5:43:09 PM7/30/2012 5:43:09 PM

http://www.it-ebooks.info