The following configurations are recommended to obtain the highest possible performance from Microsoft® SQL Server 

Set the SQL Server to Max Server Memory

Setting the SQL Server Maximum Server Memory to a higher limit generally results in higher performance as it reduces swapping to disk.  Therefore, you must set the Max Server Memory property to the highest practical value, up to the amount of physical memory on the machine.

To set Max Server Memory

  1. Open SQL Server Management Studio and connect to the SQL instance.

  2. Right-click the SQL Instance in Object Explorer and select Properties.

  3. Select Memory in the left panel.

  4. Set Max Server Memory to the desired size in megabytes (MB).

  5. Click OK.

Note

This change is applied across all databases on the SQL instance, not just the FIM database.

Configure the Protocols used by FIM to Connect to SQL Server

Configuring the protocols used by FIM to connect to SQL Server 2008 will result in the highest performance by optimizing the bandwidth required for communication.  The protocols recommended for FIM are:

  • Named Pipes

  • TCP/IP

If your SQL Server is on a local machine, using Named Pipes generally results in the best performance.  If your SQL Server is located on a remote machine, Named Pipes or TCP/IP will generally result in the best performance depending on your network topology and speed.  You should run tests in your own environment to determine which configuration performs better.

Note

For more information regarding SQL protocols, see http://go.microsoft.com/fwlink/?LinkId=101265.

The SQL Server 2008 default configuration enables Shared Memory and TCP/IP. 

To enable Named Pipes on SQL Server 2008

  1. Open SQL Server Configuration Manager.

  2. Expand to the SQL Network Configuration item.

  3. Click Protocols for SQLINSTANCE.

Note

To use Named Pipes protocol with FIM, you must enable both Shared Memory and Named Pipes, and disable TCP/IP.

In SQL Server 2008, you have the ability to specify the priority of protocols for the client configuration.

To configure FIM to connect to SQL Server 2008 with a specific, non-default protocol

  1. Open SQL Server Configuration Manager and connect to the SQL instance.

  2. In Object Explorer select the SQL Instance and select SQL Native Client Configuration.

  3. Right-click Aliases and select New Alias…

  4. Create a new alias for use with FIM.

  5. Click OK. 

  6. Re-install FIM. When prompted during setup for the name of the SQL Server, specify the alias created in step 4.

For more information regarding creating a server alias for use by a client for SQL Server, see http://go.microsoft.com/fwlink/?LinkId=101261.