If your tempdb is used heavily and experiences some slow performance with it, adding multiple data files might help to improve the overall performance of tempdb. Having multiple data files addresses issues related IO restrictions and avoids latch contention during Page Free Space (PFS) scans when temporary objects are created and dropped. But how do we decide the number of files should be added to the tempdb?
There is no specific formula to calculate it but a recommendation. Too much of files are not recommended at all as it might degrade performance rather improving performance. General rule is;
1 file per core or 0.25 file per core if as number of core increases.
Optimal configuration can only be identified by doing some tests during peak hours but better to follow below guidelines for configuring your tempdb;
If the system has <= 8 cores, then number of data files = number of cores
If the system has > 8 cores, then number of data files = 8.
Here is a good explanation on latch contention: http://logicalread.solarwinds.com/sql-server-tempdb-latch-contention-w01/#.VWNYRE-qqko
No comments:
Post a Comment