Does it store all the results to tempdb database when I query against a large table which joins

Hi, all experts here,

I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

It doesn't store all the results - but it may store some intermediate results if it needs to.

You really shouldn't be putting tempdb on your local system drive. tempdb always has a lot of activity (typically the busiest database in any system), and ideally should be on its own disk - a fast one.

If you have over 4 million records in a single table, 1GB doesn't sound too big for tempdb. But I appreciate this doesn't fix your immediate problem.

Rob|||

Hi, Rob,

Thank you for your kind advices.

But how can we change the storage location of the tempdb database? It looks like tempdb database can not be detached.

I am looking forward to hearing from you further for your kind advices.

With best regards,

Yours sincerely,

|||

And also, though I have moved the data files of tempdb to other places, but when I restarted the database engine, the system automatically generates the data files of tempdb again back to its root data files?

Please give me some more advices for this.

Thanks a lot in advance.

With best regards,

Yours sincerely,

|||I believe moving tempdb is as simple as altering the file paths and restarting SQL Server. It'll create the new files automatically. I'm not sure if it removes the old ones automatically, so you may have to to some brief housekeeping once the database is up and running in its new location.

ALTER DATABASE tempdb MODIFY FILE (NAME = logical_filename, FILENAME = 'new_physical_file_path')

Just make sure you do the log in addition to all the data files you have.
|||

Hi, davidbrit2,

Thank you for your kind attention.

But I failed to modify the files paths for the data files of the tempdb database, I did restart the service, but the data files of tempdb again went back to its original file paths. Therefore I does not seem like we are able to change the file paths of the data files of tempdb.

Any more ideas? Thanks a lot in advance.

With best regards,

Yours sincerely,

|||You need to alter the database to tell it to have its files in a different location. But then you need to restart SQL Server (ie, the service - use SQL Configuration Manager) to actually have it start using those new locations. Once you've restarted SQL, you should see the new files start to be used. Then you can delete the old ones.

Hope this helps,

Rob|||

Hello Helen999888,

Let me start with giving you a brief of what is tempdb.
The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

User Objects

User objects are explicitly created by the user. These objects may be in the scope of a user session or in the scope of the routine in which the object is created. A routine is a stored procedure, trigger, or user-defined function. User objects can be one of the following:

User-defined tables and indexes
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in table-valued functions
|||

Hi, HS_DJ,

Thanks a lot for your very kind and helpful advices. Got it done now.

With best regards,

Yours sincerely,

Does Alocating Database files affect Table size?

I am having an issue with allocated sizes verse actual
data and index sizes in some of my large fact tables.
These tables are recreated once a week with a default fill
factor size of 95%. These tables never receive any
insert, update, or delete transactions running against
them.
I have several different RAID arrays that I am using for
performance and maintenance issues. On my data and index
arrays, through enterprise manager, I have allocated 80%
of the disk space to my databases. This was done so that
fields would not have to grow.
My question is because I have allocated a fix size to my
database files does this mean the my tables will receive
this allocated space? In other words does this allocated
space get distributed amongst existing tables in a
database or is it pooled in some way?
Thanks,
Jonathan Lacefield
MCDBAWhen you allocated a fix size of your db or log file, the file grows with
empty space to that size, you shall also disable automatically file grow
since you have allocated all the space that will be ever needed or set it to
0.
Yes as data is added to the table they will be place on the empty space
allocated to the file, Once the the file is full no more data will be able
to be added.
Yovan Fernandez
P.S
If you have 2 dbs on the same disk and yor disk zise is 40GB
you have to remember you cannot allocate 80% of 40GB to each DB.
"Jonathan Lacefield" <jonathan.lacefield@.solutionbuilders.com> wrote in
message news:00fe01c376e4$97c929f0$a101280a@.phx.gbl...
> I am having an issue with allocated sizes verse actual
> data and index sizes in some of my large fact tables.
> These tables are recreated once a week with a default fill
> factor size of 95%. These tables never receive any
> insert, update, or delete transactions running against
> them.
> I have several different RAID arrays that I am using for
> performance and maintenance issues. On my data and index
> arrays, through enterprise manager, I have allocated 80%
> of the disk space to my databases. This was done so that
> fields would not have to grow.
> My question is because I have allocated a fix size to my
> database files does this mean the my tables will receive
> this allocated space? In other words does this allocated
> space get distributed amongst existing tables in a
> database or is it pooled in some way?
> Thanks,
> Jonathan Lacefield
> MCDBA