SQL Server MSDTC settings

During installation of a new Windows OS I set the following properties for the MSDTC security settings and transaction timeout.

Required when you want to configure and use linked servers

Security setings:



Transaction Timeout:


To configure these properties in Windows Component Services, complete the following actions:

1.From your Microsoft Windows desktop,click Start > Settings > Administrative Tools > Component Services, or start dcomcnfg.exe from a command prompt.

2. Expand the tree view to locate the computer where you want to change the configuration for;for example, My Computer.

3. Display the context menu for the computer name, and then click properties.

4. Click the 'Options' tab, and set the 'Transaction Timeout' to a length of time that suits your environment.The minimum setting I use is 180 seconds.

5. Click the 'MSDTC' tab, and then 'Security Configuration'. make the changes as shown in the .jpg above and click OK to save.

The MSDTC service must be restarted for changes to take effect.The OS will ask for confirmation.

Enable Instant File Initialization

In SQL Server 2005 (and higher versions), data files can be initialized instantaneously.
Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files.

This can lead to a significant performance boost of ‘create database’, restore and autogrow operations.
For example, when a thread executing a query and is a victim of an autogrow, less disk activity needs to be done and it can finish much quicker.

Unfortunately this option is not available for Log files.

How to assign ‘Perform volume maintenance tasks’ to your SQL Server instance:

Start the gpedit.msc utility, browse to ‘user rights assignment’ and add your SQL Server service account to the ‘Perform volume maintenance tasks’ policy.
Restart the SQL Server service (not the windows server), for changes to take effect.

(click to enlarge):



Check the assigned privileges:

Open SQL Server Management Studio under the same account the SQL Server service is running. Open a query window, set result to out put to text and paste/run the following code:

EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

EXEC ('xp_cmdshell ''whoami /priv''');

EXEC sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

Result in SSMS should contain SeManageVolumePrivilege with state Enabled

(click to enlarge):

SQL Server Windows OS Settings

Favor 64bit OS'es
32 bit = dinosaur

Impact of other applications:
Limit the numbers of services / applications running to an absolute minimum.
Virus scanner: do not scan on-line but schedule it as a task during off-peak hours.
During the scanning process exclude the SQL Server device extensions (.MDF, .NDF, .LDF, .BAK, .DIF and .TRN), so that they are not accessed during the scanning process.

Pagefile
Create a pagefile 1.5 - 2 times the amount of internal memory.
Don't put it on a bussy database volume.

Memory:
Use the /3GB switch in boot.ini on dedicated 32bit SQL Server OS’es with 4GB of memory and no other programs like Reporting Services installed. This switch instructs the OS to assign max 3GB to processes instead of the 32bit OS limit of 2GB, but leaving max. 1GB to the OS and other services.
Favor 64 bit over AWE

Network properties:
Maximize Data Throughput for Network Applications

(click to enlarge)

















System properties:
Application Response" setting: "Background services"
Change the memory allocation to favor "Programs."

(click to enlarge)