SQL Tips - Miscellaneous Optimization Tips

*****

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

*****

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.

*****

Keep transactions as short as possible.
This can be used to prevent deadlocks.

*****

Consider the horizontally partitioning the very large tables into the current and archives versions.
This can improve performance of your select statements.

*****

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 if it does not contain transitive dependencies. In most cases, you should normalize your tables to the third normal form.

*****

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 results in some performance degradation, so it can be necessary to denormalize your database's tables to prevent performance degradation.

*****

Create the table's columns as narrow as possible.
So, you can reduce the table's size, this can improve performance of your queries and some maintenance tasks (such as backup, restore and so on).

*****

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.

*****

Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.
So, you can reduce the table's size, this can improve performance of your queries and some maintenance tasks (such as backup, restore and so on).

*****

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 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 changed on its physical place. When the row's size is changed, the delete/insert modification method can be used. This results in some performance degradation.

*****

If you need to delete all tables rows, consider using TRUNCATE TABLE instead of DELETE command.
Using the TRUNCATE TABLE is much fast way to delete all table's rows, because it removes all rows from a table without logging the individual row deletes.

*****

You can increase the speed of sorting operation up to 20 percent, if you will use Binary sort order instead of Dictionary sort order, which is used by default.
Binary sort order is the simplest and fastest sort order, but it is used not often, because binary sort is not case-insensitive and it is based on the numeric values (from 0 through 255) of the characters in the installed character set.

*****

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.

*****

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.