Home / server / Questions / 113900
In Process
Mani
Asked: 2010-02-18 09:42:17 +0800 CST

Ideal SQL server configuration

  • 772

I am planning to buy a new server with hope of having better performance. I do not intend to have any other service/application running on this server. Hardware configuration has been already finalized.

This will be dedicated SQL server. It will be very helpful if someone can list

  1. What options to be chosen while installing SQL server enterprise 2005?
  2. What option to be chosen while installing Windows 2008 R2?
  3. what all services I can disable to improve performance?
  4. Any other optimization technique...

Please note this server will also replicate itself (transaction push replication) to another SQL Server 2005 which will be a reporting server.

Edit1

  1. Will use SQL 2008.
  2. Storage is 160 GB fusion-io ssd io drive for databse , intel 64 gb ssd for OS and program.
sql-server installation configuration

3 Answers

  1. There is no magic 'make it faster' option :)

    The only important performance decission during SQL Server instalation is when you choose the default location for user databases, log files and tempdb location. The 'correct' placement depends on the physical layout of your disks. If you get it wrong is not a big issue, as it is fairly easy to change post-install.

    After you install the SQL Server you should go into the Local Security Policies and grant to the local group SQLServerMSSQLUser$<machinename>$MSSQLSERVER the 'Lock Pages in Memory' and 'Perform Volume Maintenance tasks' priviledges. See How to: Enable the Lock Pages in Memory Option (Windows). The first priviledge is required to use AWE (and you should use AWE even on x64 architecture) and the second priviledge is required to perform instant dtabase file initialization and growth. Do not grant these priviledges to the account running the SQL serevr service (as is somehow 'recommended'). Instead grant them to the local adminsitrative group created specifically for this purpose, ie. SQLServerMSSQLUser$<machinename>$MSSQLSERVER, which will contain the configured service account as a member.

    Also when you isntall SQL Server 2005 you need to do an aditional step: press the DVD eject button, put the SQL 2005 DVD into a drawer and replace it with a SQL Server 2008 DVD instead. This will ensure you get a server capable of data page compression which is a huge gain when it comes to performance.

    All the other recommendations go to the hardware: buy as much RAM as it can physically fit in the box (all slot occupied with biggest dimms you can find) and buy as many small disks as it fist in the box, as opposed to few big disks. Everything else (CPU type, number of cores, board type etc) are secondary when compared to RAM and disks.

    • 2

  2. You do not speak about storage, which is vital to SQL Server...

    In all case, you are going 64 bit, so you can go far in memory allocation (how many did you buy) ?

    How many SQL instance ? one ?

    Take care that the SQL server account can do instant file initialization and lock page in memory

    Increase the size of NTFS block to maximum when formating, you won't loose the defrag option as it's Windows Server 2008.

    If you want more things in your check list, you will have to provide more infos !

    • 0

  3. The install options you have depend mainly on what you plan to do with that server; of course, it's useless to install things such as Analysis Services or Reporting Services if you won't be using them. Other than that, the SQL Server setup in quite straightforward, so there isn't much optimization to do here.

    Windows Server 2008 R2 is really hardened out-of-the-box; it doesn't offer almost any service, whatever you want your server to do, you'll have to install it (as a role and/or a feature). So you don't have services to disable, unless you explicitly install them.

    Not knowing what your plans for this server are, I can't really tell you much more... how many databases? Which size? How many users? How much memory do you have? How much storage, and of what kind?

    BTW, since you're running on Windows Server 2008 R2: why not use SQL Server 2008? Do you have any specific reason for using 2005?

    • 0