SQL SERVER – Performance Optimization of SQL Query and FileGroups

It is suggested to place transaction logs on separate physical hard drives. In this manner, data can be recovered up to the second in the event of a media failure.

In SQL 2005 When database is created without specifying a transaction log size, the transaction log will be re-sized to 25 percent of the size of data files.

Tables and their non-clustered indexes separated into separate file groups can improve performance, because modifications to the table can be written to both the table and the index at the same time.

If tables and their corresponding indexes in a different file group, they must be backed up the two file groups as a single unit as they cannot be backed up separately.

Set a reasonable size of your database and transaction log (25% of database size).

Leave the Autogrow feature ON for the data files and for the log files with reasonable size of autogrow increment.

Don’t set the autoshrink feature run the task during off-peak hours.

Place the log files on other physical disk arrays than those with the data files to improve I/O Performance.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

One thought on “SQL SERVER – Performance Optimization of SQL Query and FileGroups

  1. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

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