How to test tempdb log file?

• ricardo_chicas

  SSCertifiable

  Points: 5024

  More actions

  June 13, 2011 at 11:01 am

  #252677

  Hello all

  I think that I have a strange behavior on one of my servers, we have several big processes running on that one, but the log file never increase its size, it is always in 700mb, the usual was several gb, and the performance decreased heavily, is there a way to test if my tempdb log is wirking as it should? also we moved from 12 tempdb files to 8 ( according to MS best practices, ), but I am not sure if that settings are the right ones for my 16 processor server, what do you recommend?

  Thanks in advance

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  June 13, 2011 at 11:06 am

  #1337919

  I'm assuming you recently restarted the server which has a side effect of clearing out the data and proc caches.

  That alone can severely strain the server for a while.

  Also assuming that you have 1-2 rogue plans now after recompile I'd use this to spot them :

  http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

  http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/