Database Reference
In-Depth Information
The additional rows generated by this example increase the size of the result set considerably. Depending on the
size of the client-network buffer, only part of the result set can be cached. On execution of the
Ado.Recordset.Open
statement, the default result set on the client machine will get part of the result set, with SQL Server waiting on the
other end of the network to send the remaining rows.
On my machine during this period, the locks shown in Figure
22-2
are held on the underlying
Test1
table as
obtained from the output of
sys.dm_tran_locks
.
Figure 22-2.
sys.dm_tran_locks output showing the locks held by the default result set while processing the large
result set
The (IS) lock on the table will block other users trying to acquire an (X) lock. To minimize the blocking issue,
follow these recommendations:
•
Process all rows of the default result set immediately.
•
Keep the result set small. As demonstrated in the example, if the size of the result set is
small, then the default result set will be able to read all the rows during the cursor open
operation itself.
Cursor Overhead
When implementing cursor-centric functionality in an application, you have two choices. You can use either a T-SQL
cursor or a database API cursor. Because of the differences between the internal implementation of a T-SQL cursor
and a database API cursor, the load created by these cursors on SQL Server is different. The impact of these cursors on
the database also depends on the different characteristics of the cursors, such as location, concurrency, and type. You
can use Extended Events to analyze the load generated by the T-SQL and database API cursors. The standard events
for monitoring queries are, of course, going to be useful. There are also a number of events under the category of
cursor
. The most useful of these events includes the following:
cursor_open
•
cursor_close
•
cursor_execute
•
cursor_prepare
•
The other events are useful as well, but you'll need them only when you're attempting to troubleshoot specific
issues. Even the optimization options for these cursors are different. Let's analyze the overhead of these cursors one
by one.
