Split tempdb into multiple files

Gail Shaw. How big's TempDB now? They should all be the same size. IO latch contention is not why one would typically split TempDB. IO latch contention PageIOLatch suggests that the IO subsystem is being driven beyond what it can handle, maybe because there's too much demand, maybe because there's a poor IO subsystem.

The indicator that TempDB has the allocation problem cmmonly solved by adding files is latch contention PageLatch on the first set of allocation pages in the file. These typically appear as PageLatch waits on most common , or We split all our tempdb files into 8 equally divided and we see very little waits on tempdb. I would like to know where isw round-robin and proportional algorithm applied? You must be logged in to reply to this topic.

Login to reply. To be perfectly honest, I do not believe that the number of data files only depends on the number of cores, but it also depends on concurrency, tempdb contention issues, the workloads on your server and, clearly, the performance of your queries. So, there is no a rule for it. There are some situations where it will work splendidly with only one data file since each database environment is unique and, therefore, we need to determine the best for it.

Today we are not going to discuss more details about it. I just would like to suggest splitting your tempdb database into four data files if you have four or eight core or eight data files for 16, 32, 64, or more cores and ONLY one log file. So your goal is to have 4 tempdb files, each 5GB? Below is a sample script to accomplish the task within the 20GB space constraint.

First of all 20 GB is really not much. If your Server has so little space left you should start searching for more.. No, you should always have only one Log file per database.

There is no performance advantage of having multiple log files like there is with database files. Multiple log files and why they're bad. If you are on SQL or later, there is no need enable trace flag , as it is on by default and cannot be disabled. Office Office Exchange Server. Not an IT pro? Sign in. United States English. Ask a question.