Two Quick SQL Server 2005 Tips

I am really happy with Microsoft SQL Server 2005 so far, it is a huge improvement over SQL Server 2000 in terms of features and performance. One area that initially frustrated me was the fact that Microsoft did not do the best job of documenting two pretty important configuration settings that are extremely important for large, high volume 64-bit databases.
 
The first one is Windows Instant File Initialization, which allows SQL Server 2005 to skip the step of "zeroing out" the space used by a database file at the operating system level. Having this turned on makes a huge difference in the time it takes to restore a database, create a new database file or database, or expand the size of an existing database file.  This only works on Windows Server 2003 and Windows XP.
 
In order to turn this on, you have to give the "Perform volume maintenance tasks" right to the SQL Server Service Account on the server where SQL Server is running. One tool you can use to do this is the Group Policy snap-in. Just type GPEDIT.MSC at the Run prompt, and then navigate to Windows Settings, Security Settings, Local Policies, User Rights Assignment, and then add your SQL Server Service account to the "Perform volume maintenance tasks" policy. After you do this, you will need to bounce the server where SQL Server is running in order for the change to take effect.
 
The second one is the "Lock pages in memory" policy located in the same area in GPEDIT.MSC. You should also add the SQL Server Service account to this policy in order to prevent the operating system from being able to page SQL Server buffer data out of memory when it wants to, rather than letting SQL Server control it. This also requires a restart of the server to take effect.
 

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

3 Responses to Two Quick SQL Server 2005 Tips

  1. SATTARU says:

    this feature of instant file initilization is also present in 2000.

  2. T8tube.com says:

    thanks

  3. Pingback: Two Quick SQL Server 2005 Tips | Rich Kreider

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