Cursor is a powerful tool in hands of a programmer that knows how and where to use it. But in many organizations database specialists aren't the only ones who write database code. In my own organization we have 6 database specialists and about 20 Web programmers that also create database objects - tables, stored procedures etc. And I've found out - in a pretty painful way - that cursor can be "powerful" in wrong direction while misused. The history starts from the phone call from our support center - in the middle of Sukkot night (about 23:30 PM) - telling that they got an alert: transaction log backup failed due to lack of free space on disk. OK, wine out, laptop in - connect to the server, check the disk - really gone out of space. But what for God's sake caused transaction log backups taken every 15 minutes to grow from about 50-100M on average to 3GB each file??? After 5 minutes of active sessions monitoring (I have hotkey set for it), I've found a suspect - session that executed simple cursor but remained active for all these 5 minutes. Even more - it was superactive! Its writes counter grew up quicker than I thought our storage is capable of. I killed the session, checked what was inside that cursor. 8 rows in a cursor set; processing them in a loop instead of cursor took less then 1 second. I tried cursor again - manually from Management Studio. It never returned (until I killed the session). I've put counter inside the cursor that wrote to another table outside, executed cursor and, while it was running, checked counter table from another session. After about 2 seconds of cursor's execution counter showed something like 10000. Wow, cursor went nuts. I quickly checked in syscomments - another 120 stored procedures contained the word "cursor". Here I began to sweat. I'll save all the investigation history, luckily I didn't have to re-write all those 120 procedures. I would like to show a small demo as an example of this behavior (I showed it in Israely SQL Server User Group). For the demo I created table with 1000 manufacturers and another table with Products - 8 per each manufacturer. 

CREATE DATABASE BestPracticesDemo
GO

ALTER DATABASE [BestPracticesDemo] SET RECOVERY SIMPLE WITH NO_WAIT
GO

USE [BestPracticesDemo]
GO

CREATE TABLE dbo.Manufacturers
(
   ManufacturerID INT NOT NULL PRIMARY KEY,
   [Name] NVARCHAR(64) NOT NULL,
   ServiceContactName VARCHAR(128),
   [Address] NVARCHAR(512),
   LastUpdatedTimeStamp TIMESTAMP NOT NULL
)

CREATE TABLE dbo.Products
( 
   ProductID INT NOT NULL PRIMARY KEY CLUSTERED,
   [Name] NVARCHAR(64) NOT NULL,
   Price DECIMAL(8,2) NOT NULL DEFAULT 0,
   CurrencyID INT NOT NULL DEFAULT 1,
   CreationDate DATETIME NOT NULL DEFAULT GETDATE(),
   ManufacturerID INT NOT NULL,
   [Description] NVARCHAR(1024),
   LastUpdatedTimeStamp TIMESTAMP NOT NULL
)
GO

ALTER TABLE dbo.Products WITH CHECK ADD CONSTRAINT FK_Products_Manufacturers
FOREIGN KEY (ManufacturerID) REFERENCES dbo.Manufacturers(ManufacturerID)
GO

CREATE NONCLUSTERED INDEX IX_Products_ManufacturerID ON dbo.Products
(
   ManufacturerID ASC, 
   LastUpdatedTimeStamp ASC
)
INCLUDE(Price)
GO

-- Populate Manufacturers table (1000 rows)
;WITH Numbers1to1000_CTE( Num )
AS
(
   SELECT Num = 1 
   UNION ALL
   SELECT Num = Num + 1 FROM Numbers1to1000_CTE WHERE Num < 1000
)
INSERT INTO dbo.Manufacturers( ManufacturerID, [Name] )
SELECT Num AS ManufacturerID, 'Manufacturer' + CAST(Num AS VARCHAR) AS [Name]
FROM Numbers1to1000_CTE
OPTION (MAXRECURSION 1000)
GO

-- Populate Products table (8 products per Manufacturer)
;WITH Numbers1to8_CTE( Num )
AS
(
   SELECT Num = 1 
   UNION ALL
   SELECT Num = Num + 1 FROM Numbers1to8_CTE WHERE Num < 8
)
INSERT INTO dbo.Products( 
   [ProductID], [Name], [Price], [CurrencyID], [CreationDate], 
   [ManufacturerID], [Description] )
SELECT
   (m.ManufacturerID - 1) * 8 + num.Num AS ProductID,
   m.[Name] + '_Product' + CAST(num.Num AS VARCHAR) AS [Name],
   CHECKSUM((num.Num - 1) * 1000 + m.ManufacturerID) % 1000 + 1 AS [Price],
   m.ManufacturerID % 3 + 1 AS [CurrencyID],
   GETDATE() - m.ManufacturerID % 365 AS [CreationDate],
   m.ManufacturerID,
   REPLICATE('Description', 50) AS [Description]
FROM Numbers1to8_CTE num
   CROSS JOIN dbo.Manufacturers m
GO

CREATE TABLE dbo.CursorMonitor( ProductID INT, CurrentTime DATETIME)
GO
Products table contains index on ManufacturerID + LastUpdatedTimeStamp with Price column included. Now let's create simple cursor that will loop on every product of a single manufacturer (should be 8 products) and change their price (in real-life logic would be more complicated - otherwise cursor is not required). You can verify that we really have 8 product for advertiser 23: 
SELECT ProductID FROM dbo.Products WHERE ManufacturerID = 23 
Another small preparation before we start - open another sessions and prepare the cursor counter checking query:
SELECT COUNT(*) FROM CursorMonitor WITH(NOLOCK)
Now the cursor itself:

SET NOCOUNT ON

BEGIN

   DECLARE @ManufacturerID INT, @v_productID INT
   SET @ManufacturerID = 23

   DECLARE cur CURSOR
       FOR SELECT ProductID
       FROM dbo.Products
       WHERE ManufacturerID = @ManufacturerID

   OPEN cur 
   FETCH NEXT FROM cur INTO @v_productID
   WHILE @@FETCH_STATUS = 0 

   BEGIN 

       INSERT INTO dbo.CursorMonitor(ProductID, CurrentTime) SELECT @v_productID, GETDATE()

       UPDATE dbo.Products SET Price = Price + 1 WHERE ProductID = @v_productID
 
       FETCH NEXT FROM cur INTO @v_productID

   END 

   CLOSE cur 
   DEALLOCATE cur 

END
GO
Now query the counter. Surprised? Cursor got stuck in an endless loop. What's the reason? Let's examine FETCH statement execution plan:

|--Dynamic Cursor(cur, Optimistic)
  |--Fetch Query(Fetch Query)
    |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] =             [BestPracticesDemo].[dbo].[Products].[ProductID],[CWT].[COLUMN1]             = [BestPracticesDemo].[dbo].[Products].[LastUpdatedTimeStamp],
          [CWT].[ROWID] = [Expr1005]))
      |--Compute Scalar(DEFINE:([Expr1005]=CWT_ROWID()))
        |--Index Seek(OBJECT:([BestPracticesDemo].[dbo].[Products].             [IX_Products_ManufacturerID]),SEEK:([BestPracticesDemo].[dbo].
          [Products].[ManufacturerID]=[@ManufacturerID]) ORDERED FORWARD)

So every FETCH statement performs Seek operation on IX_Products_ManufacturerID index - in ascending order. Aftewards UPDATE operation updates selected row - and what happens to the index? ManufacturerID remains the same. But LastUpdatedTimeStamp column changes thus moving recently updated row from the beginning to the end of cursor set. Second row goes next etc - after processing 8th row, cursor continues and processes same rows again and again.
What's the cure? For this particular case use STATIC in cursor definition. It creates worktable in tempdb after first FETCH statement and queries this table instead of going to real table for every next row.
The point is - it is very difficult to anticipate such problems in advance. So cursors should be used only by those who know its drawbacks. Personally, I prohibited Web developers from adding any more cursors without review by db professional. Now they just insert would-be cursor set into table-type variable using ROW_NUMBER function and loop over it - same behavior without dangers of the cursor.