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 =