October 17, 2015 at 4:50 am
Going back to your original question - why there is only 1 tempdb in SQL Server.
I know that some other DBMS systems have multiple scratchpad areas. I understand Oracle has one temporary tablespace (equivalent to a filegroup) for each database. I also understand that DB2 allows the creation of multiple tempdb equivalents, one for each extent size that is used.
SQL Server inherited its design from Sybase, and that had only one tempdb. Providing that IO contention does not become a problem then there is little to gain by having multiple scratchpad databases. As already pointed out, SQL Server provides IO contention relief by the use of multiple tempdb files.
A bigger problem than having a single tempdb is having a single bufferpool. Tempdb shares the same bufferpool with user databases, but its bufferpool requirements are almost the opposite of what a user database needs.
For a user database you typically want a dirty page to be committed to disk as soon as the commit has taken place, if only to make room for the next page you want to make dirty.
For tempdb you want a dirty page to remain in memory for as long as possible, because at some point it will be discarded. Writing a tempdb page to disk is just about the worst thing you can do with it, as this takes system resources away from stuff that really needs to be on disk.
With tempdb and user dbs sharing the same bufferpool then there will always be this dichotomy. SQL2014 and SQL2016 are introducing some improvements that will reduce some of the problems, but will not eliminate them. SQL2014 allows the use of delayed durability, which keeps pages in memory longer, and SQL2016 has this turned on permanently for tempdb.
Another reason why multiple bufferpools could be useful is to allow the segregation of large tables most often are accessed sequentially from other tables. Not every site will have these monsters, but if you do then you will have seen these big tables flooding the bufferpool and driving out pages from other tables. The problem with this is that sequential access (by its nature) only accesses a given page once. But these pages remain in the bufferpool long after the SQL statement has passed them by, until they get old enough to be replaced by something else. The end result is that other users suffer sub-optimal performance because something has done its sequential read of a large table.
If the big sequentially-accessed tables could be isolated in separate bufferpools, these could be sized to give optimal sequential access performance. Typically this would be a few MB, enough to allow sequential read-ahead to populate the bufferpool and for the pages to remain there long enough to be read.
In the end we have to make the best use out of what we have. Back in the 1990s I really enjoyed tuning multiple bufferpools in DB2 to get the best performance out of a few GB memory. In 2001 I found there was more money in working with SQL Server, and I have enjoyed many years working in this environment. If you don't do the best with what you have got, then you are unlikely to do the best with something else.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 17, 2015 at 10:42 am
EdVassie (10/17/2015)
It sounds like your users have a Stage 4 predicate in their query...
They do, sort of, and office politics are not a problem. There is no management between us - I work directly for and with the end users. Makes lots of things very pleasant, so I can't complain on that score. It's just that this application started out as a spreadsheet, then migrated to multiple copies of single-user Access databases, before finally becoming a full-blown client-server, multi-user, hairy-chested database. The users got used to the format of having lots of rows available to scroll through, and developed the nervous habit of shuffling up and down through the list when they can't figure out what they want to do next. I've tried explaining what the impact is, but they get tense when they can't do this senseless up-and-down twiddling through a large recordset. And really, the associated delay doesn't seem to bother them, so there's little point in harping on it. The esthetics of the design bother me, but the end users' opinions are the important ones.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply