TempDB

Question

TempDB

sqlfriend

SSC Guru

Points: 52781
More actions
January 23, 2019 at 3:46 pm

#376870

Can SQL server have multiple Tempdb, and assign users to use different tempdb like Oracle does?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

sqlfriends - Wednesday, January 23, 2019 3:46 PM
Can SQL server have multiple Tempdb, and assign users to use different tempdb like Oracle does?
Thanks

I would guess you mean like temporary tablespaces in Oracle and the answer would be no.

Sue

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

yes, I did mean tablespace in Oracle. For in oracle you can assign different users to different tablespace.
In case one user use extensivly tablespace, we can assign it a different one from others.

But it looks like in SQL server we can have only one tempdb.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 3

for tempdb there is nothing you can do.

if however you are also talking about having all permanent tables of a user on their own file(s) then it is possible although not in a easy way.

on each database where you wish to force the users data to go to their individual files (filegroup in SQL Server terms) you can create a database trigger to rebuild any table created onto a specific filegroup - this could be based on the user name.

Once off task
- create a trigger to rebuild any table/index on the filegroup assigned to each user if applicable (otherwise do nothing). based on a hardcoded list or on a configuration table.

once off steps at user creation time
- create filegroup (and add corresponding files)
- add user/filegroup mapping to a table (or hardcode on the trigger)

Issues with this is that if the user creates a table with "select * into permtable from ...." the trigger will cause the table creation to fail.
Only works if a explicit "create table" is issued.

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 4

sqlfriends - Wednesday, January 23, 2019 3:46 PM
Can SQL server have multiple Tempdb, and assign users to use different tempdb like Oracle does?
Thanks

You can create multiple data files for tempdb which will increase the performance.
Why do you want more than one tempdb?

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

Yes, make tempdb in multiple files that could make use of the cores and reduce the contention.
We have already done so. 8 tempdb files. Our situation is some that adhoc queries and BI self service tools usage from users that highly use the tempdb.

So I am thinking that Oracle can assign different users to use different tempdb to isolate usage of the tempdb.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

sqlfriends - Thursday, January 24, 2019 9:26 AM
Our situation is some that adhoc queries and BI self service tools usage from users that highly use the tempdb.
So I am thinking that Oracle can assign different users to use different tempdb to isolate usage of the tempdb.

This IS a production box, right? You're trying to resolve the wrong problem. If users have ad hoc queries and BI self service tools that are driving TempDB into the stops, you need to fix those problems rather than ignore them.

Also, how big is each file for TempDB?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GeorgeCopeland SSCertifiable Points: 6997 More actions · Answer 7

What Jeff said. It is worth it to tune tempdb and then make everyone use it correctly. The management strategy that you are suggesting just sounds like pushing the real problem off.

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

i am not ignoring the problem. Just want to separate the hdhoc, or self -service based query aside from other main functionality of this server.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 9

sqlfriends - Friday, January 25, 2019 2:23 PM
i am not ignoring the problem. Just want to separate the hdhoc, or self -service based query aside from other main functionality of this server.

That's ignoring the problem. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GeorgeCopeland SSCertifiable Points: 6997 More actions · Answer 10

What Jeff said again. For example, if you have a bunch of ad hoc users that are affecting production, the solution is to probably build them a data warehouse. Doing weird junk to a production tempdb is not the way to go.