SQL Server misunderstanding: on the 30 th day, the number of TempDB files in 12th days should be consistent with the number of CPUs.

Misunderstanding #12: The number of TempDB files must be consistent with the number of CPUs

Error

Alas, the above misunderstanding is Microsoft's "official" suggestion, and there are still a large number of blog posts sticking to this viewpoint. This misunderstanding is already commonplace.

But what is confusing is that the SQL CAT team gave a suggestion of, which is derived from the principle of expansion rather than a general rule. The reason is that most of the large customer data servers and I/O subsystems have no chance to meet them.

Each instance only allows one TempDb, but there are many places where TempDB is needed, so TempDB is easy to become a performance bottleneck. I think everyone knows this, what most people do not know is when additional TempDB files are needed.

When you see the PAGELATCH type blocking, it indicates that you have encountered a contention problem for allocating bitmaps in the memory. When we see PAGEIOLATCH, we see that we have encountered an I/O subsystem contention problem. For Latch, you can think of it as something like a common lock, but it is lighter, shorter, and only used inside the storage engine.

MVP Glenn Berry has a blog post showing the DMV of sys. dm_ OS _wait_stats. In this blog, you can find out what causes the most blocking on your server. If you find it is a PAGELATCH wait, you can use this script to check whether it is caused by FPS, GAM, or SGAM contention.

If you encounter a lock contention, you can track 1118 or create more TempDB files to ease this situation (the principle can be found in KB 328551 of the knowledge base ), I have written a long blog article about why tracing 1118 is still needed. Link: Misconceptions around TF 1118.

In the SQL server 2000 era, the number of TempDB files must be with the number of CPU cores. This recommendation also applies to SQL SERVER 2005 and 2008, however, due to the optimization measures after SQL SERVER 2005 + (see my blog for details), you no longer need to set the number of CPU cores and the number of TempDB files in strict accordance with the ratio, the ratio of the number of files to the number of CPU cores is kept at or.

[Digress: At SQL pass 2011, my good friend Bob Ward is also the best person in SQL CSS. A new formula is provided: if the number of CPU cores is less than or equal to 8, the ratio is kept at. If the number of CPU cores is greater than 8, 8 files are used, when you find the lock contention, add four files at a time]

However, this cannot be generalized. I encountered a problem last week. A customer's TempDB load was as high as 32 CPUs and 64 TempDB files were required to reduce the lock contention. Does this mean this is a best practice? Of course not.

Then you may be wondering why the ratio is poor, because too many TempDB may cause another performance problem. If some operations (such as sorting) in a query require a large amount of memory, but the memory is insufficient, You need to allocate the content to TempDB. When multiple TempDB files exist, the performance may be slowed down due to the cyclic allocation mechanism of TempDB. This is also true for large temporary tables.

So why does the cyclic allocation mechanism cause performance problems when TempDB has a large number of files? There are several possibilities:

• The cyclic allocation algorithm is used for file groups, but only one file group exists for TempDB. When this file group contains 16 or 32 files, the thread of the cyclic allocation algorithm is limited, but TempDB still needs to do some extra synchronization work for a large number of files, therefore, this part of work may cause performance loss.
• If the file size of TempDB is inconsistent, it may automatically increase the size of a single file, resulting in hotspot IO.
• When the buffer zone needs to be released through LazyWriter (the Checkpoint of TempDB does not write back), multiple TempDB files may cause random read/write of the IO subsystem, this causes IO performance problems.

Therefore, this choice will make you feel at ease and at ease. How many TempDB files are suitable? I cannot give you a specific answer, but based on my years of consulting experience and experience in attending various conferences, I can give you a guideline-be careful when creating multiple files for TempDB in order to solve the lock contention, and add the TempDB file only when necessary. That is, you need to strike a balance between scalability and performance.

I hope the above guidelines will help you.

PS: Respond to some comments: TempDB files do not need to be distributed across multiple memories. If you see the PAGELATCH wait type, the performance will not be improved even if you are distributed. If the PAGEIOLATCH wait type, you may need multiple memories, but this is not inevitable-you may need to talk about migrating the entire TempDB to another storage system, rather than simply adding a file for TempDB. This requires careful analysis and further determination.