Is there any justification for having a single tempdb database to be used by all databases on a SQL Server intstances?
Despite the fact that I have programmed against SQL Server for quite a while I did not pay much attention to the tempdb database. This is especially true if application logic is mostly written using the ORM instead of stored procedure.
However, I have just realized that the SQL Server architecture is pretty strange in that the tempdb is a single database shared by all the other databases within the instance. Under heavier loads this can lead to performance issues as mentioned here or here.
I am wondering about what is the rationale of having a single special database for temporary tables (and others). Why not simply use another "space" (e.g. schema) within the database where the query runs? This would certainly ensure a better isolation between databases activity within the same instance.
I tried to find the reasons for such a design decision, but perhaps it was decided such a long time ago that nobody argues about it now.
1 answer
I can't speak for the designers' motivations, but here are some possible reasons:
- It's simple. Having one tempdb for everything is likely simpler to implement and simpler to configure.
- It works. A lot of the time the shared tempdb isn't a problem. When it is, your links provide some mitigation. If it still remains a problem, then you can use separate servers to resolve it and there's a decent chance you would need to do that for other reasons as well, i.e. the shared tempdb isn't your only performance problem.
- It's easier to manage. You can set one quota size for the tempdb rather than one for each per-database tempdb. Further, you don't need to try to predict and over-allocate how much temp space each DB would need. You can easily stripe and/or host the tempdb on a different disk as suggested in your links.
- SQL Server supports cross-database queries for which it is not obvious which tempdb should be used in your proposed scheme.
- It shares infrastructure, e.g. the log file.
My understanding is that early[1] in SQL Server's life one of its major selling points was simpler database management relative to other products at the time (i.e. Oracle, DB2). My understanding is a large part of this accomplished by reducing and simplifying the configurability, e.g. by using algorithms that were inherently more adaptive, incorporating "auto-tuning" algorithms internally, or simply not providing the ability to configure certain aspects. I'm speculating here, but I also suspect tempdb contention was likely much less of an issue at the time than tempdb allocation. Having a single shared tempdb simplified management and made it easy to allocate a set amount of disk space for temporary data. Being shared, it would also allow any database to use as much of that temporary storage as it needed in a burst of work while not requiring allocating temporary storage for each database in accordance to its worst-case usage.
I could easily imagine a new, mostly from-scratch database (re-)implementation by Microsoft choosing to make databases more isolated and significantly eliminating shared resource to improve scalability/elasticity.
0 comment threads
0 comment threads