Here are seventeen little known tips you can use to ensure your SQL Server environment is performing in the most efficient manner possible.

1. Try to perform backups at the local hard disk first, and then copy backup file(s) to the tape later.

When you perform a backup, some SQL Server commands cannot be made, for example: during a backup you cannot run ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options, you cannot shrink a database, you cannot run a CREATE INDEX statement, you cannot make SELECT INTO, bulk load and so on. So, to improve backup performance, you can perform backups on the local hard disk first, and then copy backup file(s) to the tape later.

2. Use nonlogged bulk copy whenever possible.

The nonlogged bulk copy is much faster than the logged one, but to use it you must provide all the following conditions:
@nbsp; @nbsp;1. The database option 'select into/bulkcopy' is set to true.
@nbsp; @nbsp;2. The target table is not being replicated.
@nbsp; @nbsp;3. The TABLOCK hint is specified.
@nbsp; @nbsp;4. The target table has no indexes or, if the table does have indexes, it is empty when the bulk copy starts.

3. Use native mode bulk copy whenever possible.

This can improve performance in comparison with the character mode.

4. Try to use the 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.

5. 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.

6. Keep transactions as short as possible.

This can be used to prevent deadlocks.

7. Consider horizontally partitioning very large tables into the current and archives versions.

This can improve performance of your select statements.

8. Normalize your database's tables to the third normal form.

A table is in third normal form (3NF) if it is in second normal form (2NF) and does not contain transitive dependencies. In most cases, you should normalize your tables to the third normal form.

9. Consider the denormalization of your database's 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, so it is sometimes necessary to denormalize your database's tables to prevent performance degradation.

10. Create the table's columns as narrow as possible.

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

11. 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, then it can take more time to get the text/ntext values.

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

Againt, this helps you reduce the table's size, which can improve the performance of your queries and some maintenance tasks (such as backup, restore and so on).

13. Use char/nchar data type instead of varchar/nvarchar if the column's size will be 4 bytes or less.

The char data type is a fixed-length data type and varchar data type is variable-length data type.

So, by using char data type, you can increase the probability of an in-place update instead of delete/insert or deferred update. The in-place update is the most effective method of modification; when it is used, the data is changed on its physical place. When the row's size is changed, the delete/insert modification method can be used. This does result in some performance degradation, though.

14. If you need to delete all tables rows, consider using TRUNCATE TABLE instead of the DELETE command.

Using the TRUNCATE TABLE is a much faster way to delete all of the table's rows as it removes all rows from a table without logging the individual row deletes.

15. You can increase the speed of sorting operations up to 20 percent if you use the Binary sort order instead of the Dictionary sort order, which is used by default.

Binary sort order is the simplest and fastest sort order, but it is used less often due to the binary sort not being case-insensitive and being based on the numeric values (from 0 through 255) of the characters in the installed character set.

16. Don't use Enterprise Manager to access remote servers over a slow link or to maintain very large databases.

Because using Enterprise Manager is very resource expensive, use stored procedures and T-SQL statements, in this case.

17. Try to install SQL Server on a standalone server box, not on a primary or backup domain controller.

Because domain controllers have extra overhead, you should install SQL Server on a standalone server box to dedicate all of the server's power to SQL Server.

» See All Articles by Columnist Alexander Chigrik