estimating tempdb usage

Is there any way to estimate how much space will be needed by tempdb?
I have a user executing a simple query similar to:
select orders.customerid, sum(quantity*unitprice) as amount
from orders inner join [order details]
on orders.orderid=[order details].orderid
group by orders.customerid
In other words, two thin (not too many columns) tables, equi-join,
summarizing and a group by. The only trouble is her two tables have
28000 rows and nearly 800 million rows. Her query dies after a few
hours when tempdb autogrows and runs out of disk space at 17 gig. I
know tempdb is used for work tables, joins, sorts, group bys, etc.
But knowing the columns sizes and number of rows, is there a way to
estimate how much tempdb will be needed? She's basically the only
user on the system.
By the way, this is SQL Server 2000.
Thanks,
ScottI guess it would be better to Grow the tempdb first, then run oyur query.
I had the same problem on SQL Server 6.5 to 2000 migration with the log
files (we had big tables as well) , the Autogrowth didn´t work. So we had to
create the database and transaction logs big enough to support migration
process before initiate it.
HTH
"scott parmelee" <s_parmelee@.hotmail.com> escreveu na mensagem
news:e14a8116.0311130819.725c0a4@.posting.google.com...
> Is there any way to estimate how much space will be needed by tempdb?
> I have a user executing a simple query similar to:
> select orders.customerid, sum(quantity*unitprice) as amount
> from orders inner join [order details]
> on orders.orderid=[order details].orderid
> group by orders.customerid
> In other words, two thin (not too many columns) tables, equi-join,
> summarizing and a group by. The only trouble is her two tables have
> 28000 rows and nearly 800 million rows. Her query dies after a few
> hours when tempdb autogrows and runs out of disk space at 17 gig. I
> know tempdb is used for work tables, joins, sorts, group bys, etc.
> But knowing the columns sizes and number of rows, is there a way to
> estimate how much tempdb will be needed? She's basically the only
> user on the system.
> By the way, this is SQL Server 2000.
> Thanks,
> Scott