Difference between revisions of "Create Multiple TempDB files for best performance"

From Galen Healthcare Solutions - Allscripts TouchWorks EHR Wiki
Jump to navigation Jump to search
Line 1: Line 1:
 
'''Microsoft recommends that you create multiple files to reduce contention issues within the TempDB.'''  
 
'''Microsoft recommends that you create multiple files to reduce contention issues within the TempDB.'''  
  
It is recommended that you create one file per CPU core- a server with (2) 4 core processors would need 8 data files
+
''It is recommended that you create one file per CPU core- a server with (2) 4 core processors would need 8 data files.
 +
 
 +
Hint- to find the number of cores go to Task Manger and count the number of Graphs under CPU Usage History''
 +
 
  
 
This is a very straight forward procedure in SQL 2005-  
 
This is a very straight forward procedure in SQL 2005-  
Line 20: Line 23:
 
[[Image:Tempdb2.JPG]]
 
[[Image:Tempdb2.JPG]]
  
6) Continue adding files in this manner, making sure the Initial Size, Autogrowth and Path are all the same
+
6) Continue adding files in this manner, making sure the Initial Size, Autogrowth and Path are all the same (SQL will create File Name)
  
 
-- For some help with setting the initial size and autogrowth size see http://msdn.microsoft.com/en-us/library/ms175527(SQL.90).aspx
 
-- For some help with setting the initial size and autogrowth size see http://msdn.microsoft.com/en-us/library/ms175527(SQL.90).aspx
 +
 +
7) Once you have added one file per core, click ok

Revision as of 18:51, 3 December 2008

Microsoft recommends that you create multiple files to reduce contention issues within the TempDB.

It is recommended that you create one file per CPU core- a server with (2) 4 core processors would need 8 data files.

Hint- to find the number of cores go to Task Manger and count the number of Graphs under CPU Usage History


This is a very straight forward procedure in SQL 2005-


1) Open SQL Server Management Studio

2) Expand System Databases and right click tempdb and select "properties"

3) With in Properties, select "Files" and you should see the window below

Tempdb.jpg

4) Click the Add button (lower right)

5) As you can see below I have already added a couple data files called tempdev2- tempdev4

Tempdb2.JPG

6) Continue adding files in this manner, making sure the Initial Size, Autogrowth and Path are all the same (SQL will create File Name)

-- For some help with setting the initial size and autogrowth size see http://msdn.microsoft.com/en-us/library/ms175527(SQL.90).aspx

7) Once you have added one file per core, click ok