Cursor Optimization Tips

Try to avoid using SQL Server cursors, whenever possible.

SQL Server cursors can results in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

*****

Do not forget to close SQL Server cursor when its result set is not needed.

To close SQL Server cursor, you can use CLOSE {cursor_name} command. This command releases the cursor result set and frees any cursor locks held on the rows on which the cursor is positioned.

*****

Do not forget to deallocate SQL Server cursor when the data structures comprising the cursor are not needed.

To deallocate SQL Server cursor, you can use DEALLOCATE {cursor_name} command. This command removes a cursor reference and releases the data structures comprising the cursor.

*****

Try to reduce the number of records to process in the cursor.

To reduce the cursor result set, use the WHERE clause in the cursor's select statement. It can increase cursor performance and reduce SQL Server overhead.

*****

Try to reduce the number of columns to process in the cursor.

Include in the cursor's select statement only necessary columns. It will reduce the cursor result set. So, the cursor will use fewer resources. It can increase cursor performance and reduce SQL Server overhead.

*****

Use READ ONLY cursors, whenever possible, instead of updatable cursors.

Because using cursors can reduce concurrency and lead to unnecessary locking, try to use READ ONLY cursors, if you do not need to update cursor result set.

*****

Try avoid using insensitive, static and keyset cursors, whenever possible.

These types of cursor produce the largest amount of overhead on SQL Server, because they cause a temporary table to be created in TEMPDB, which results in some performance degradation.

*****

Use FAST_FORWARD cursors, whenever possible.

The FAST_FORWARD cursors produce the least amount of overhead on SQL Server, because there are read-only cursors and can only be scrolled from the first to the last row. Use FAST_FORWARD cursor if you do not need to update cursor result set and the FETCH NEXT will be the only used fetch option.

*****

Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.

If you need read-only cursor and the FETCH NEXT will be the only used fetch option, try to use FAST_FORWARD cursor instead of FORWARD_ONLY cursor. By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified the other cannot be specified.

*****

Bulk Copy Optimization Tips

Use nonlogged bulk copy whenever possible.

The nonlogged bulk copy is much faster than logged one, but to use it you must provide all the following conditions:

1. The database option 'select into/bulkcopy' is set to true.
2. The target table is not being replicated.
3. The TABLOCK hint is specified.
4. The target table has no indexes, or if the table has indexes, it is empty when the bulk copy starts.

*****

Use native mode bulk copy whenever possible.

This can improve performance in comparison with the character mode.

*****

Try to use BULK INSERT command instead of bcp or DTS to load data into SQL Server.

The BULK INSERT command is much faster than bcp or the data pump to perform text file import operations, however, the BULK INSERT statement cannot bulk copy data from SQL Server to a data file.

*****

Use the bcp utility instead of DTS when you need to export data from the SQL Server table into a text file.

The bcp utility is much faster than DTS, so try to use it whenever possible.

*****

Specify the number of the first and the last row to bulk copy, if you need to bulk copy not all the rows from the specified data file.

This can results in good performance benefits, because the total amount of data copied will be less.

*****

Specify the number of rows per batch of data copied, if the transaction log was filled before the bulk copy is complete.

Because each batch is copied to the server as one transaction, SQL Server commits or rolls back the transaction for every batch. When you bulk copy large data files, the transaction log can be filled before the bulk copy is complete. In this case, enlarge the transaction log, allow it to grow automatically or specify the number of rows per batch of data copied.

*****

Try to increase the packet_size option.

The packet_size option specifies the number of bytes, per network packet, sent to and from the server. The packet_size can be from 4096 to 65535 bytes with the default of 4096. Increased packet size can enhance performance of bulk copy operations. Try to set the packet_size option to 8192 bytes and continue monitoring.

*****

Use the ORDER hint, if the clustered index exists on the table and the data file is sorted according to the clustered index.

This can significantly improve performance of the bulk copy operation, because SQL Server will load data in the clustered index order without any reorders operations.

*****

If you create a new table and bulk copy data into it, try to bulk load data first and only after that create any indexes.

This can significantly improve performance of the bulk copy operation, because data will be loaded into SQL Server table without any index pages creation during the bulk copy.

*****

If you load data into an empty table with the existing nonclustered indexes, try to drop the nonclustered indexes, bulk load data and only after that re-create the nonclustered indexes.

This can significantly improve performance of the bulk copy operation, because data will be loaded into SQL Server table without any index pages creation during the bulk copy.

*****

If you load data into a nonempty table with the existing clustered and/or nonclustered indexes, and the amount of data added is large, it can be faster to drop all indexes on the table, perform the bulk copy operation, and then re-create the indexes after the data is loaded.

Check the time needed to load data with dropping/re-creating indexes and without dropping/re-creating indexes on your test server before run bulk copy operation on the production server.

*****

If your SQL Server box has multiple CPUs, try to divide loaded data into two or more sources and run multiple instances of BCP on separate clients to load data in parallel.

Because SQL Server allows data to be bulk copied into a single table from multiple clients in parallel using the bcp utility or BULK INSERT statement, try to use parallel data loads whenever possible. To bulk copy data into SQL Server in parallel, you must provide all the following conditions:

1. The database option 'select into/bulkcopy' is set to true.
2. The TABLOCK hint is specified.
3. The target table does not have any indexes.

*****

Specify the TABLOCK hint, if you bulk copy data into an empty table from a single client.

This can improve performance of the bulk copy operation, because this causes a table-level lock to be taken for the duration of the bulk copy operation.

*****

Try to avoid using CHECK_CONSTRAINTS and FIRE_TRIGGERS hints.

Using these hints can significantly degrade performance of the bulk copy operation, because for each row loaded the constraints and insert triggers defined on the destination table will be executed.

Optimization for designing tables

Normalize your tables to the third normal form.

A table is in third normal form (3NF) if it is in second normal form (2NF) and if it does not contain transitive dependencies. In most cases, you should normalize your tables to the third normal form. The normalization is used to reduce the total amount of redundant data in the database. The less data there is, the less work SQL Server has to perform, speeding its performance.

*****

Consider the denormalization of your tables from the forth or fifth normal forms to the third normal form.

Normalization to the forth and fifth normal forms can result in some performance degradation, especially when you need to perform many joins against several tables. It may be necessary to denormalize your tables to prevent performance degradation.

*****

Consider horizontal partitioning of the very large tables into the current and the archives versions.

The less space used, the smaller the table, the less work SQL Server has to perform to evaluate your queries. For example, if you need to query only data for the current year in your daily work, and you need all the data only once per month for the monthly report, you can create two tables: one with the current year's data and one with the old data.

*****

Create the table's columns as narrow as possible.

This can reduce the table's size and improve performance of your queries as well as some maintenance tasks (such as backup, restore and so on).

*****

Try to reduce the number of columns in a table.

The fewer the number of columns in a table, the less space the table will use, since more rows will fit on a single data page, and less I/O overhead will be required to access the table's data.

*****

Try to use constraints instead of triggers, rules, and defaults whenever possible.

Constraints are much more efficient than triggers and can boost performance. Constraints are more consistent and reliable in comparison to triggers, rules and defaults, because you can make errors when you write your own code to perform the same actions as the constraints.

*****

If you need to store integer data from 0 through 255, use tinyint data type.

The columns with tinyint data type use only one byte to store their values, in comparison with two bytes, four bytes and eight bytes used to store the columns with smallint, int and bigint data types accordingly. For example, if you design tables for a small company with 5-7 departments, you can create the departments table with the DepartmentID tinyint column to store the unique number of each department.

*****

If you need to store integer data from -32,768 through 32,767, use smallint data type.

The columns with smallint data type use only two bytes to store their values, in comparison with four bytes and eight bytes used to store the columns with int and bigint data types respectively. For example, if you design tables for a company with several hundred employees, you can create an employee table with the EmployeeID smallint column to store the unique number of each employee.

*****

If you need to store integer data from -2,147,483,648 through 2,147,483,647, use int data type.

The columns with int data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with bigint data types. For example, to design tables for a library with more than 32,767 books, create a books table with a BookID int column to store the unique number of each book.

*****

Use smallmoney data type instead of money data type, if you need to store monetary data values from 214,748.3648 through 214,748.3647.

The columns with smallmoney data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with money data types. For example, if you need to store the monthly employee payments, it might be possible to use a column with the smallmoney data type instead of money data type.

*****

Use smalldatetime data type instead of datetime data type, if you need to store the date and time data from January 1, 1900 through June 6, 2079, with accuracy to the minute.

The columns with smalldatetime data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with datetime data types. For example, if you need to store the employee's hire date, you can use column with the smalldatetime data type instead of datetime data type.

*****

Use varchar/nvarchar columns instead of text/ntext columns whenever possible.

Because SQL Server stores text/ntext columns on the Text/Image pages separately from the other data, stored on the Data pages, it can take more time to get the text/ntext values.

*****

Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.

The char/varchar value uses only one byte to store one character, the nchar/nvarchar value uses two bytes to store one character, so the char/varchar columns use two times less space to store data in comparison with nchar/nvarchar columns.

*****

Consider setting the 'text in row' SQL Server 2000 table's option.

The text, ntext, and image values are stored on the Text/Image pages, by default. This option specifies that small text, ntext, and image values will be placed on the Data pages with other data values in a data row. This can increase the speed of read and write operations and reduce the amount of space used to store small text, ntext, and image data values. You can set the 'text in row' table option by using the sp_tableoption stored procedure.

*****

If you work with SQL Server 2000, use cascading referential integrity constraints instead of triggers whenever possible.

For example, if you need to make cascading deletes or updates, specify the ON DELETE or ON UPDATE clause in the REFERENCES clause of the CREATE TABLE or ALTER TABLE statements. The cascading referential integrity constraints are much more efficient than triggers and can boost performance.

*****