Very Computer

by **Brendan Coyl** » Wed, 14 Aug 2002 20:23:04

I have a stored procedure (SQL Server 2000) to recreate a fact table by
joining a number of base tables together. The process creates a number of
tables (I avoid creating temporary tables). Once the fact table has been
built, other intermediate tables are deleted.

I am finding this procedure causes Tempdb to grow abnormally in size. I
rebuilt indexes on the main tables used in the procedure, but it has not
solved the problem. Am I missing something simple here?

cheers

Brendan.

by **Bob Pfeiff [MS** » Wed, 14 Aug 2002 21:32:39

If you have GROUP BY and/or ORDER BY clauses in the SQL that you use to
create the fact table, worktables are created in tempdb to resolve them. If
there is a lot of data, you can expect tempdb to expand.

The best bet is to set your tempdb to a size adequate for these operations
so you don't have the overhead of allocating new extents as your SQL
executes. You might also consider putting tempdb on its own set of disks if
this is a long-running operation on huge amounts of data.

--
Bob
Microsoft Consulting Services
------
This posting is provided AS IS with no warranties, and confers no rights.

Very Computer

Growth of Tempdb database

Growth of Tempdb database

Growth of Tempdb database