Improve tempdb performance

• madhu-686862

  SSChampion

  Points: 10707

  More actions

  December 9, 2008 at 12:52 pm

  #203890

  Hi,

  We just ran the SQLBPA on server A. It is recommending to do no.of tempdb datafiles should equal the no.of processor and files should be equally sized and to turn on trace flag 1118.

  Could plz tell me how to do this.

  Improve tempdb performance on server A

  Server A has trace flag 1118 as status 0,temdb datafile count[1],logical cpu count[8] and predicate tempdb datafiles are equally sized as[1]. We recommond that to reduce the allocation resource contension for tempdb that is experiencing heavy usage,trace flag 1118 should be turned on.The no.of tempdb datafiles should equal the no.of processor and files should be equally sized

• Jason Crider

  SSCertifiable

  Points: 6019

  More actions

  December 9, 2008 at 1:16 pm

  #909899

  I'd start with these resources:

  http://weblogs.sqlteam.com/tarad/archive/2008/05/20/60604.aspx (will actually create the temp files for you)

  http://weblogs.sqlteam.com/tarad/archive/2008/08/05/How-to-get-physical-CPU-count-on-a-server.aspx

  MCITP, Database Administrator
  A hodgepodge of Information Technology and Life
  LinkedIn Profile
  My Twitter