How to use SQL Server T-SQL cursors

The general advice within the SQL Server community is do not use any T-SQL cursors at any time.
I must say that I have to agree, although if you have browsed my blog you've noticed that there are a couple of occasions when I do use cursors but only when the following is true:

- All cursors are read_only / forward-only for optimum speed.
- They're not used in OLTP environments.
- Datasources are small sets, usually system objects, like a list of databases / tables.
- The impact the cursor is many times smaller then the processing steps taken with the row results.
(like a cursor based on sys.databases and with the result of each row a full database backup is made).

Coding guidelines:
- Avoid using cursors and try solve the problem by using set based operations.
- Try to base them on readonly tables or local (#temptable) temporarily objects.
- Cleanup! Always CLOSE and DEALLOCATE cursors.
- Specify the 'LOCAL' option.
- Specify the FAST_FORWARD clause on DECLARE CURSOR. This opens an optimized forward-only, read-only cursor.
- Never use cursors in OLTP environments.
- Never use them as source of an update: UPDATE table SET col1 = 2 WHERE CURRENT OF the_cursor;
- Cursors are memory intensive; base them on smaller sets (less 5,000-10,000 rows, your site could differ: test!).

template:

    DECLARE the_cursor CURSOR FAST_FORWARD LOCAL FOR
        SELECT col1,col2,col3
         FROM dbo.mytable

    OPEN the_cursor

    FETCH NEXT FROM the_cursor INTO @col1,@col2,@col3
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN

        FETCH NEXT FROM the_cursor INTO @col1,@col2,@col3
    END

    CLOSE the_cursor
    DEALLOCATE the_cursor
 

TIP:
If you do need cursors in high load OLTP environments because of some complex calculation that can't be done set based take the following approach:

Copy the required result set in a temporary object. Retrieve only the rows and columns you need, but do include all the fields of the primary key.

create #temptable (CalcValue int, pkCol int)

INSERT INTO #temptable (CalcValue, pkCol)
SELECT 0, PrimaryKeyCol
FROM dbo.HighLoadOLTPtable
WHERE -- your where clause here

Base your cursor on the temp. object.

Loop the cursor, perform your calculation and store the result in the temp. object row:
UPDATE #temptable SET CalcValue=complex_calculated_value
WHERE pkCol=pk_fields_as_fetched_by_cursor

When done looping close/deallocate the cursor, and update the high load OLTP source table(s) set based by primarykey, use a BEGIN TRANSACTION / COMMIT if required:

UPDATE dbo.HighLoadOLTPtable SET CalculatedValue = #temptable.CalcValueFROM dbo.HighLoadOLTPtable, #temptableWHERE dbo.HighLoadOLTPtable.PrimaryKeyCol = #temptable.pkCol

see my posts on TempDB configuration and TempDB objects

Working with SQL Server TempDB objects

Try to avoid them in the first place by using derived tables or CTEs

Table variables are easy an convenient, because you don't have to clean them up.
But only use them with small sets (smaller 1000 rows)

Do not use SELECT col1 INTO #temptable FROM dbo.table1

But do use a create table statement and a INSERT/SELECT:
CREATE #temptable (col1 int,col2 int)

INSERT INTO #temptable (col1,col2) SELECT Col1, Col2 FROM dbo.table1

a bit more work, but less blocking in environments with a higher load.

With larger sets you could consider using indexes and check with the query plan and statistics io to see if there are any benefits in using them.
Create these indexes after the table is filled with a set:

INSERT INTO #temptable (col1,col2) SELECT Col1,Col2 FROM dbo.table1
CREATE CLUSTERED INDEX CIX_#temptable ON #temptable (col1)
CREATE INDEX IX_#temptable_1 ON #temptable (Col2)

Create ony the indexes you absolutely need.

Configure TempDB as suggested in my post configure TempDB

Can your TempDB database disks handle the current load? See my post on fn_virtualfilestats

Prefer locally (#temptable) over globaly (##temptable) scoped definitions

Store results of linked servers in TempDB objects instead of accessing the remote datasource multiple times. See my post on linked servers

Be a good citizen, at the end of your stored procedure or T-SQL code always cleanup after your self and drop all temporarily objects you have created.

    --drop temp tables if they exists
    IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
        DROP TABLE #temptable
 

How to configure SQL Server TempDB

As a general guideline, create one data file for each CPU on the server.
Note that a dual-core CPU is considered to be two CPUs. logical procs (hyperthreading) do not

Only create one Log file

Do not use autoshrink on TempDB

If your'e using SQL Server 2008 set Page verify to ChecksumInvestigate the possibility if you can switch off the properties ‘Auto create’ and ‘Auto Update’ statistics,it can speed up the creation of objects in TempDB. Be careful however: measure the impact these changes can have on production! Change and measure only one parameter at the time.

After installation move the TempDB database to its own disks that differ from those that are used by user databases

ALTER DATABASE TempDB
MODIFY FILE (NAME=tempdev, FILENAME= '\tempdb.mdf');
GO

ALTER DATABASE TempDB
MODIFY FILE (NAME=templog, FILENAME= '\TempLog.ldf');
GO

Restart SQL Server service (not the Windows server), to make changes permanent

Pre-allocate data and log device sizes (create files with same equal size), do not rely on small auto growth steps

If you use auto growth set it to a reasonable size in MB, not a percentage

TempDB file size default FILEGROWTH increment
0 to 100 MB growth:10 MB
100 to 200 MB growth:20 MB
200 to 1000 MB growth:50 to 75 MB
1 GB or More growth:150 to 250 MB

Measure TempDB usage over time with fn_virtualfilestats
New releases of your –vendor- software could have a different impact on TempDB load.

Create multiple files per database and increase performance

In this post I want to explain why to create multiple data files per SQL Server database

Note: this is only relevant for data files, not for log files. Log files always have one file!

Microsoft documentation says:
• The number of data files within a single filegroup should equal to the number of physical CPU cores (hyper threading cores should not be counted)

This is also true for TempDB

So create all the files in your new database with the same initial size – make an estimate on how big you want the files to be- and set a reasonable growth factor. Do not set it to a percentage, but on the same fixed size in MB for all data files. Use the same growth factor for all the files you have created.

SQL Server will fill the data files in a round robin way, meaning that data is spread equally across all the files as the database grows.

Tip!
Another advantage from this approach is that if you have performance issues and you can pin this down to an disk I/O related problem
(see my http://sqlserveradvisor.blogspot.com/2009/02/microsoft-included-fnvirtualfilestats.html on fn_virtualfilestats() post for this), it is then an easy change to add a new raid disk configuration to your server and then spread the data files evenly over both the raid arrays.
Giving you an instant performance boost!!

fn_virtualfilestats: Measure the I/O load on your disk system

Microsoft included the fn_virtualfilestats function in SQL Server so you can monitor the I/O load the instance executes on the disk subsystem.

I use this function mainly to:
- Get the load on TempDB and check if it’s a good idea to move it to it’s own disks (if you have not already done so)
- Get the I/O profile (more read of write activity?) on the database level to help determine if it might be an idea to replace a raid-5 array to a raid-10 or to move datafiles to additional diskarrays (see my post http:// for this)
- Check if there are any disk related issues, in other words are there many stalled I/O’s?
(stalled I/O is an I/O waiting for another I/O)
- Check if the system can handle more db’s, eliminating additional servers

Real life example:
I managed to save big $$$ and could avoid buying new hardware by letting our vendor run this script in comaparable environent of another customer and so proving the load was not so big as they claimed, so we could run the new system on our existing hardware.

Here’s the T-SQL to get the I/O profile of all your database and data files:

DECLARE @TotalIO    BIGINT,
    @TotalBytes BIGINT,
    @TotalStall BIGINT

SELECT @TotalIO  = SUM(NumberReads + NumberWrites),
       @TotalBytes = SUM(BytesRead + BytesWritten),
       @TotalStall = SUM(IoStallMS)
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)

SELECT [DbName] = DB_NAME([DbId]),
      (SELECT name FROM sys.master_files
        WHERE database_id = [DbId]
              and FILE_ID = [FileId]) filename,
    [%ReadWrites]       = (100 * (NumberReads + NumberWrites) / @TotalIO),
    [%Bytes]        = (100 * (BytesRead + BytesWritten) / @TotalBytes),
    [%Stall]        = (100 * IoStallMS / @TotalStall),
    [NumberReads],
    [NumberWrites],
    [TotalIO]       = CAST((NumberReads + NumberWrites) AS BIGINT),
    [MBsRead]       = [BytesRead] / (1024*1024),
    [MBsWritten]        = [BytesWritten] / (1024*1024),
    [TotalMBs]      = (BytesRead + BytesWritten) / (1024*1024),
    [IoStallMS],
    IoStallReadMS,
    IoStallWriteMS,
    [AvgStallPerIO]     = ([IoStallMS] / ([NumberReads] + [NumberWrites] + 1)),
    [AvgStallPerReadIO] = (IoStallReadMS / ([NumberReads] + 1)),
    [AvgStallPerWriteIO]= (IoStallWriteMS / ( [NumberWrites] + 1)),

    [AvgBytesPerRead]  = ((BytesRead) / (NumberReads + 1)),
    [AvgBytesPerWrite] = ((BytesWritten) / (NumberWrites + 1))
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)
ORDER BY dbname
 

Be ware: counters are re-set after the instance is restarted!

The result (not all columns shown here). Click on image to enlarge