Here are some things I’ve learned with many clients when setting up Sql Server 2005 systems.

1. Get 64-bit if you can. Throw in as many processors, memory, good disk system (properly tuned SAN, for example) as you can possibly get;

2. Apply the latest service packs for Windows. At this writing, it is SP2 for Windows;

3. Apply the registry changes mentioned in this kb article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;920739 You do not need to get the hot fix if you have Windows SP2, because it is already part of it. However, you do need to get the registry changes in;

4. Apply this hot fix mentioned in this kb article: http://support.microsoft.com/default.aspx/kb/931308/en-us This hot fix is especially important, if your Sql Server 2005 is an edition other than Enterprise or Developer. This hot fix should help with the error message below:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 24484, committed (KB): 48036, memory utilization: 50%

I am not too sure if this hot fix is necessary if you have Sql Server 2005 Enterprise/Developer edition and have “Lock pages in memory” enabled.

5. Run gpedit.msc. Go to Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignments. And give Sql Server startup account “Perform volumn maintenance tasks” (for instant file inialization).

6. If you will be running Sql Server 2005 Enterprise/Developer Edition, give startup account “Lock pages in memory” also.

7. Install Sql Server 2005. Then apply Sql Server 2005 Service Pack 2, currently the latest Service Pack for Sql Server 2005;

8. Open up Internet Explorer. Go to Tools -> Options -> Advanced Tab. Scroll all the way down to Security section, uncheck “Check for publisher’s certificate revocation”. This should help if your Management Studio is slow;

9. Enable AWE, even for 64-bit systems.

10. If your system has a lot of memory, I would leave around 2 to 3 gig of RAM to the OS.

Feedback welcome.