Could not allocate new page for database 'TEMPDB'.

I recently had a routine using a table variable involved in a fairly
complex query using a number of fairly large tables which ended with
the following error.
Server: Msg 1101, Level 17, State 10, Line 40
Could not allocate new page for database 'TEMPDB'. There are no more
pages
available in filegroup DEFAULT. Space can be created by dropping
objects,
adding additional files, or allowing file growth.
The tempdb is on autogrow.
The drive on which the tempdb resided was filling up after running for
a number of minutes. The drive had a couple of GB of space left until
the routine started. I looked into it a bit and read that temp
tables were better performers with larger tables than table vars so
tried using a temp table instead. The routine finished in a timely
manner without expanding the tempdb significantly from it's previous
size before the routines initial run(rebuilt tempdb). Any idea why the
big difference in performance and effect on the tempdb? The rest of
the routine remainded the same with the exception of the table var -->
temp table.
ThanksUse of temp tables and table v ariables will affect the query plans used
in a procedure.
It is likely that the use of the table variables has resulted in a query
plan that involved heavy use of worktables i.e sorting and thus the filling
up of tempdb.
If you can post the query plans for the different code (and the code) it
migth help to diagnose the problem.

> I recently had a routine using a table variable involved in a fairly
> complex query using a number of fairly large tables which ended with
> the following error.
> Server: Msg 1101, Level 17, State 10, Line 40
> Could not allocate new page for database 'TEMPDB'. There are no more
> pages
> available in filegroup DEFAULT. Space can be created by dropping
> objects,
> adding additional files, or allowing file growth.
> The tempdb is on autogrow.
> The drive on which the tempdb resided was filling up after running for
> a number of minutes. The drive had a couple of GB of space left until
> the routine started. I looked into it a bit and read that temp
> tables were better performers with larger tables than table vars so
> tried using a temp table instead. The routine finished in a timely
> manner without expanding the tempdb significantly from it's previous
> size before the routines initial run(rebuilt tempdb). Any idea why
> the
> big difference in performance and effect on the tempdb? The rest of
> the routine remainded the same with the exception of the table var -->
> temp table.
> Thanks
>|||I took a look at the query plans, they are identical. Odd thing is
that this used to run on the same environment in a timely manner using
the table var a w ago. Finding out the truth here may be difficult
as our application is being hosted by a client and we do not have
security permission to view their sensitive data.
Thanks for the post!