SQL Server Performance Forum – Threads Archive

Derived Table and TempDB relationship ?

Hi; I have a query which is accessing around 1.7 millions records and it gives me around 0.4 millions records as a results. I used four five tables in join. I also used one Derived Table (The query in a Form clause) which is used to give me Count(*) of each Item in a table. Now, what happens after joining this derived table, when I ran it I got this error "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." Whenever I run that query the size of tempdb increased drastically. But I am wondering b/c I am not using any temp table in this query. I want to know is there any link between Derived table and tempdb. Do these are created in tempdb? How can I resolve this issue? Thanks. Essa, M. Mughal
Software Engineer
Canada

---

a derived table may or may not automatically create a temp table, SQL Server will make the decision.
sometimes the temp is unnecessary if no intermediate results are required,
if the intermediate results are small, it could done in memory
if the interemediate results are large, then the best thing may be to create a hidden temp table.
this is why many DW & reporting apps will create several tempdb files, preallocate adequate space so no growth is required, and distribute the file over several physical disks

---

Derived table are kept in memory if they are small enough, otherwise they are stored in tempdb. Do you have tempdb size restricted or you it just occupied all space on the disk?

---

Thanks for the help. Now, I got my answer b/c my result set was very large so it was using Tempdb and my tempdb was set to 10 percent growth rate. So,for now I restarted the SQL Server service and it now the tempdb is empty. I’ll set it to fix size at my production server. Thanks. Essa, M. Mughal
Software Engineer
Canada

---

]]>