Tuesday, July 07, 2009

Kevin Kline : Old Performance Tuning Recommendations Die Hard

  • Multiple data files for tempdb = yes, 1 of equal size per cpu (or cpu core)
  • Multiple log files = no
However, the recommendation fails when you get to the log portion of the equation. Why? It’s because data file IO is written using the proportional file algorithm where each data file has data written to it in round-robin style.  On the other hand, log files are written using the active file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on… Long story made short (too late, I know) – you get no performance gain from having multiple files in the log because all writes occur on only one file. You can only get a performance gain from multiple files on the data portion of a database.

Kevin Kline : Old Performance Tuning Recommendations Die Hard

No comments: