October 12, 2015 at 12:34 pm
Hello SQL Server Gurus,
I am not sure if this is too silly to ask, but can someone explain why SQL Server only has one TempDB?
Is there any benefit if each user DB has got their OWN tempDB instead of sharing the only TempDB?
Thanks in advance.
October 12, 2015 at 1:01 pm
DBA in Unit 7 (10/12/2015)
Hello SQL Server Gurus,I am not sure if this is too silly to ask, but can someone explain why SQL Server only has one TempDB?
Is there any benefit if each user DB has got their OWN tempDB instead of sharing the only TempDB?
Thanks in advance.
If each DB had their own tempdb, there would be no need for a tempdb at all. Each database would have the needed objects.
Tempdb is there to provide functionality that would be difficult to replicate on the user databases and maintaining a tempdb per user database would be a nightmare.
That's my opinion, maybe someone else can give a better input.
October 12, 2015 at 1:10 pm
I think there are plenty of people that would be happy to see multiple tempdb databases but Microsoft have never added this.
If you are seeing tempdb contention this can be down to the allocation pages, GAM and SGAM getting hit pretty hard. Adding equally sized tempdb data files can relieve this contention, there are formulas for this depending on the amount of processors but 4 tempdb data files can be a good starting point.
Andrew Mansell
sqlmansell.com Useful everyday hints and tips for SQL Server
Twitter: @sqlmansell
October 12, 2015 at 1:29 pm
Temporary tables, spools, hashes, etc. are not tied to any specific database. For example, assuming we wanted to have a separate temporary work area for each databases, if you have a stored procedure in database [A] that joins tables from databases and [C], then which database's work area should contain the temporary objects?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 12, 2015 at 1:47 pm
Thanks for your responses...
Or maybe there could be some Spool TempDB, Sorting TempDB, Tempobjects Tempdb? So they can be managed differently?
October 12, 2015 at 1:49 pm
Eric M Russell (10/12/2015)
Temporary tables, spools, hashes, etc. are not tied to any specific database. For example, assuming we wanted to have a separate temporary work area for each databases, if you have a stored procedure in database [A] that joins tables from databases and [C], then which database's work area should contain the temporary objects?
I can see how that would be a problem if we wanted one tempdb per user database. What if we wanted say 2 shared tempdb's that work like the current tempdb, the only difference being that there are 2 of them so the work load is shared? I realise that this obviously isn't an easy one or Microsoft would have done it already, interesting that they have changed some of the default options in 2016 to alleviate contention and encourage multiple data files.
Andrew Mansell
sqlmansell.com Useful everyday hints and tips for SQL Server
Twitter: @sqlmansell
October 12, 2015 at 2:00 pm
SQLmansell (10/12/2015)
Eric M Russell (10/12/2015)
Temporary tables, spools, hashes, etc. are not tied to any specific database. For example, assuming we wanted to have a separate temporary work area for each databases, if you have a stored procedure in database [A] that joins tables from databases and [C], then which database's work area should contain the temporary objects?I can see how that would be a problem if we wanted one tempdb per user database. What if we wanted say 2 shared tempdb's that work like the current tempdb, the only difference being that there are 2 of them so the work load is shared? I realise that this obviously isn't an easy one or Microsoft would have done it already, interesting that they have changed some of the default options in 2016 to alleviate contention and encourage multiple data files.
We don't need multiple logical temporary work areas (multiple tempdbs), only one tempdb supporting multiple files, which we have.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 12, 2015 at 3:06 pm
I had a look on connect just to see and it has been requested in the past mainly for reasons around TDE and collation but it was a no anyway.
Andrew Mansell
sqlmansell.com Useful everyday hints and tips for SQL Server
Twitter: @sqlmansell
October 13, 2015 at 6:59 am
Adding equally sized tempdb data files can relieve this contention
This helped a lot where at another place I worked and is now one of the first things I do if there are contention issues. I always add the multiple files when I set up a database.
October 13, 2015 at 12:48 pm
So it all boils down to files ( container for temp stuff).... make sense. Thank you guys for the inputs.:-P
October 13, 2015 at 1:19 pm
Server level resources are available to all databases equally (network, data cache, tempdb).
Using SSD for tempdb might make a lot of sense and benefit all databases for a little cost.
October 14, 2015 at 11:52 am
I understand that using multiple files for TempDB can be useful to spread the workload, but does it make any sense when the server is very lightly loaded? Like, so lightly that it's rare to have two users even logged on at the same time, let alone contending for anything.
My greatest bottleneck is volume of data transfer, since users insist on a legacy format that loads (sometimes) huge block of records, which they then scroll through. Haven't been able to break them of it, so despite queries that respond usually in the zero to sub-second range, actual response can be sluggish due to continual reloading of tens of thousands of records, most of which never change.
Do multiple TempDB files make any sense in such a situation?
October 14, 2015 at 12:33 pm
October 14, 2015 at 3:00 pm
vedau (10/14/2015)
http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/
I'm familiar with that site. However, it has nothing to say about the situation I described.
October 17, 2015 at 4:18 am
It sounds like your users have a Stage 4 predicate in their query...
Stage 0: Data access is eliminated by the optimiser. Anything the optimiser can reduce to a WHERE 1=0 situation means no data access. A typical scenario is partition elimination for multi-partition tables.
Stage 1: Data is filtered an index. These are also known as Sargable queries.
Stage 2: Data is filtered by an expression in the access plan that cannot be indexed. A typical scenario is WHERE a.col1 + 10 = b.col2 -- SQL cannot use an index to match a.col1 to b.col2
Stage 3: Data is filtered by the application program. The application receives a result set from SQL then returns only a portion of it to the user.
Stage 4: Data is filtered by the human eyeball. This takes the longest in getting a result and can be prone to errors 🙂
Sometimes a stage 4 predicate really is what is needed. I once did some work for a Utility where all outstanding problems for a locality were shown to the scheduling team, which allowed them to make judgement calls about what jobs could be done that day by a maintenance team working in the area. Automation of this type of scheduling is getting better, but even now a bit of human domain knowledge can give a better answer.
All I can suggest is that you talk through the business process with your users. If they can work out for themselves they are making their job worse by showing all this data all of the time then suddenly the pressure will be on you to make things better. If you get some insights your user group does not want to accept then you can talk to your management, and maybe it can get escalated to their management. Be careful to not push your viewpoint too far with your users or their management unless you have backing from your own management - office politics often take precedence over good sense.
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply