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

Estimate new time

I have a ordersystem with repeating orders. Each order has a orderline
with a frequency and a intervaltype (day, week, month and so on) for
indicating how often a task should be done.

When an task/job is done, an equivalent receipt with receiptlines is
generated.

When a new job/task should be suggested, it has to calculate the
shortest next intervallength for each repeating orderline. This
intervallength must then be added to the date of the last receiptline.

I want to to this all in a SPROC. My result should be the one next
orderline that should be carried out with a date for when this is.

RESULT
OrderID TaskID TaskDesct EstDate
5000 GETGROCERIES Pick up groceries for miss Lama 19.12.2003

************************************************************************
Is this possible to do all in a SPROC and not having to do any in .NET
(which this is developed in), and can you help me with some ideas?
************************************************************************

To se my starting point, se the diagram at:
http://www.promotion.no/div/diagram.gif

My mind is stuck and this is so far I've reached:

ALTER PROCEDURE dbo.GetNextOrders
AS
CREATE TABLE #Interval
(
ivID int IDENTITY(1,1),
ivTaskID char(20),
ivTaskDescr nvarchar(200),
ivHour int
)
INSERT INTO #TemporaryTable
SELECT RepID, RepIntName
FROM RepeatInterval
SELECT InvoiceDetails.iID, InvoiceDetails.ReceiptID,
 InvoiceDetails.OrderID, InvoiceDetails.TaskID,
 InvoiceDetails.TaskDescr, InvoiceDetails.DateRegistered
FROM InvoiceDetails LEFT OUTER JOIN
Orders ON InvoiceDetails.OrderID = Orders.OrderID
WHERE (Orders.DateStart < GETDATE()) AND
 (Orders.DateEnd > GETDATE() OR Orders.DateEnd IS NULL)

If you are still struggling with this, could you post some example data and results for each table involved?

Terri|||Thanks, but I think the solution is close at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10177