- Trace flag 1118 is used for striping tempdb to overcome file contention. The following steps were taken to configure these optimizations:
· Figure out how many logical processors the SQL server has and keep this number.
· Open the properties of the TempDB and go to «Files»
· Expand the existing data file to 1.5 GB and disable the autogrow settings
· Add more data files to match the number of logical processors in the system (IF there are 4 procs then there need to be 4 TempDB data files). Make sure that all of the data files are the same size (1.5 GB) with autogrow disabled, and located in the same place as the TempDB’s default data file.
· Apply the changes
· Open «SQL Server Configuration Manager»
· Click on «SQL Server 2005 Services» and in the right hand pane right-click on the SQL server instance for which you just added the TempDB files and select «Properties» from the context menu.
· Switch to the «Advanced» tab
· Scroll down to «startup parameters» and at the beginning of the value box (it’s very important to not put it at the end) add the following text: «-T1118;»
· Click ok.
· Using Cluster Administrator take the SQL server offline and then bring it back online (to restart SQL).
· To confirm the settings have been applied correctly you can run the SQL query «DBCC TraceStatus» against the SQL server and the resulting table should show at a row for each flag (1118).