Warning: Constant WP_TEMP_DIR already defined in /var/www/html/blogs/joe/wp-config.php on line 93

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
{"id":496,"date":"2012-05-03T07:24:58","date_gmt":"2012-05-03T07:24:58","guid":{"rendered":"\/blogs\/joe\/post\/Being-Mindful-of-Cursor-Lock-Overhead.aspx"},"modified":"2013-01-02T20:31:57","modified_gmt":"2013-01-03T04:31:57","slug":"being-mindful-of-cursor-lock-overhead","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/being-mindful-of-cursor-lock-overhead\/","title":{"rendered":"Being Mindful of Cursor Lock Overhead"},"content":{"rendered":"

This post is just a reminder to be attentive to the locking overhead of your Transact-SQL server cursors.<\/p>\n

For example, the following cursor is using default options in SQL Server 2012 to iterate row-by-row through the Employee table.  I\u2019m declaring a variable and populating it with the BusinessEntityID from each row (and I\u2019m not doing anything with it \u2013 as I just wanted to avoid the chatter back to my SQL Server Management Studio window):<\/p>\n

\n
   1:  <\/span>DECLARE<\/span> Employee_Cursor CURSOR<\/span> FOR<\/span><\/pre>\n
   2:  <\/span> <\/pre>\n
   3:  <\/span>SELECT<\/span> BusinessEntityID<\/pre>\n
   4:  <\/span>FROM<\/span> [HumanResources].[Employee];<\/pre>\n
   5:  <\/span> <\/pre>\n
   6:  <\/span>SELECT<\/span> properties<\/pre>\n
   7:  <\/span>FROM<\/span> sys.dm_exec_cursors(52);<\/pre>\n
   8:  <\/span> <\/pre>\n
   9:  <\/span>OPEN<\/span> Employee_Cursor;<\/pre>\n
  10:  <\/span> <\/pre>\n
  11:  <\/span>DECLARE<\/span> @BusinessEntityID int<\/span>;<\/pre>\n
  12:  <\/span> <\/pre>\n
  13:  <\/span>FETCH<\/span> NEXT<\/span> FROM<\/span> Employee_Cursor<\/pre>\n
  14:  <\/span>INTO<\/span> @BusinessEntityID;<\/pre>\n
  15:  <\/span> <\/pre>\n
  16:  <\/span>WHILE<\/span> @@FETCH_STATUS<\/span> = 0<\/pre>\n
  17:  <\/span>BEGIN<\/span><\/pre>\n
  18:  <\/span>   <\/pre>\n
  19:  <\/span>    FETCH<\/span> NEXT<\/span> FROM<\/span> Employee_Cursor<\/pre>\n
  20:  <\/span>    INTO<\/span> @BusinessEntityID;<\/pre>\n
  21:  <\/span> <\/pre>\n
  22:  <\/span>END<\/span><\/pre>\n
  23:  <\/span> <\/pre>\n
  24:  <\/span>CLOSE<\/span> Employee_Cursor;<\/pre>\n<\/div>\n