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:
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 does have indexes, it is empty when the bulk copy starts.