Miscellaneous SQL Server Optimization Tips

By Alexander Chigrik


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:

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 does have indexes, it is empty when the bulk copy starts.
Read more on DatabaseJournal.com
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s