(Script can be downloaded here)
TempDB is the work horse of most SQL Servers, if a query is doing something wrong, or just being a resource hog, chances are it’s TempDB picking up the slack. It’s a safe bet that the majority of queries running on your system are using TempDB in some way. (Good technet article hereon how TempDB is used)
So it pays to give your TempDB some TLC. Starting out as a DBA I found it pretty confusing as to what the “Best Practices” actually were when it came to TempDB configuration, as there are many differing opinions. So here is a script I’ve put together with what I’m going to call my “Recommended Practices”, rather than “Best Practices”. In my experience structuring your layout to get green lights on each of these checks has yielded the best performance and stability, but your mileage may vary!
What am I checking, and why?
The script performs 6 checks, and I’ll break them down below:
1. If you have multiple CPU’s, have you got multiple TempDB data files?
On a multi core system, it is recommended to have multiple TempDB data files. This is to reduce the contention of threads requiring TempDB space trying to hit a single file. The official recommendation from Microsoft is to have 1 TempDB file for each CPU/Core in the server.
Now this is massive overkill, particularly on large multi core boxes, so a ratio of 1 File for every 4 cores is a good place to start, potentially rising to a 1:2 ratio if you still have some hotspots. Paul Randal wrote an excellent blog on this already, so I won’t repeat the effort.
This check measures the ratio of CPU’s to TempDB data files, and reports back accordingly
2. If you have multiple TempDB Data files, are they all sized equally?
If you have multiple files, you want to ensure they are all sized equally to ensure the Round Robin effect is in play, which gives you that performance boost. Again Paul discusses this in the previous blog link, so I won’t duplicate on the reasoning.
This check ensures that if multiple files exist, they are all equally sized, and if not, it has a loud whinge.
3. Do you have TF1118 enabled?
This is a contentious one, as it’s not necessarily a best practice, but I’ve found it really helps concurrency in any situation I’ve had issues in. This checks if TF1118, which disables mixed extents (Over to Paul Randal for this one again blog), and in my experience enhances concurrency.
One important note here, this disables mixed extents server wide, not just for TempDB. This isn’t a worry in most cases as disk space isn’t as expensive any more, but just be aware of this.
4. If you have multiple data files, do you have TF1117 enabled?
This trace flag ensures that if one data file experiences a growth event, then all data files grow equally. This means that file growth on a data file doesn’t effectively take it out of the Round Robin pool, as all files must be sized equally to be considered.
You should be pre-allocating anyway, but this trace flag will ensure you don’t take a performance hit if one or more of your files grows.
Again this is server wide, so if you have any user DB’s with multiple files in a Filegroup, they will exhibit the same behaviour
5. Has TempDB data file experienced any auto growth events
You should be pre-allocating space for these files where possible, but this is a check to make sure you got it right. If you have no auto growth events, then you are sized right for your system. If you do, it may be worth taking a look at how much it has grown, and re-evaluate your sizing.
6. Are the TempDB data files on their own storage
TempDB is generally the most I/O intensive database in an instance, and it can be very heavy random I/O. While a lot of workloads are now on SAN’s, it still makes sense to separate TempDB from all of your other files. This is from a management perspective within the OS, but also if you are using tiered storage, such as the EMC VNX, the partition holding TempDB can be placed on the higher tier if it’s out on its own, without having to move all of your user DB’s too.
It’s a recommended practice from me, but if you’re having no issues and you are within your I/O thresholds, then this is by no means a must do.
So there you have it, some recommended practices, and a way to give your TempDB a quick health check. If anyone has any feedback, questions or comments, or rebuttals, on any of this, as always I’d love to hear it.