What am I doing wrong?? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

What am I doing wrong??

I have a database that is about 60GB. When I run the query below I over-run the filegroup – which has about 12gb free. I dont understand why it is pulling more than a 6th of the database when it should only be returning about 30,000 rows. First, I create a temp table that limits the query by the last week (to about 21,000 rows). This works fine when ran as a seperate query. Then I join it to the other tables to create a new static table for reporting.
Select _tInvTranHeader.CustKey, _tInvTranHeader.InvcKey, _tInvTranHeader.InvoiceNo,_tInvTranHeader.InvoiceDate INTO #ELP_Temp
From _tInvTranHeader
where _tInvTranHeader.InvoiceDate > ’12/21/2006′ SELECT _tInvTranDetail.QtyShipped, _tInvTranDetail.UnitCost, #ELP_Temp.InvoiceDate, tarSalesperson.SperName INTO ELP_VOG
FROM tarCustomer INNER JOIN #ELP_Temp ON tarCustomer.CustKey = #ELP_Temp.CustKey INNER JOIN
tarCustAddr ON tarCustomer.CustKey = tarCustAddr.CustKey INNER JOIN
tarInvoice ON tarCustomer.CustKey = tarInvoice.CustKey INNER JOIN
tarSalesperson ON tarCustAddr.SperKey = tarSalesperson.SperKey INNER JOIN
tarSalesTerritory ON tarCustAddr.SalesTerritoryKey = tarSalesTerritory.SalesTerritoryKey INNER JOIN
tciAddress ON tarCustAddr.AddrKey = tciAddress.AddrKey INNER JOIN
_tInvTranDetail ON #ELP_Temp.InvcKey = _tInvTranDetail.InvcKey
Drop Table #ELP_Temp Any ideas or suggestions? Thanks, ~Donovan
what exactly is the error message?
Query 1 ran fine, but Q2 failed? look at the execution plan
my thinking is the plan for Q2 has a hash join or something that uses tempdb space, so the error message would be something like: unable to allocate more space for tempdb.
in which case, freespace on the main db is irrelevent usually, i like to put tempdb data on the same disk as the main db data, but if you can manage the space on a separate partition
Instead of #table you could even try this…
and instead of select InTO use Insert into select statement INSERT INTO ELP_VOG
SELECT _tInvTranDetail.QtyShipped, _tInvTranDetail.UnitCost, _tInvTranHeader.InvoiceDate, tarSalesperson.SperName
FROM tarCustomer INNER JOIN _tInvTranHeader ON tarCustomer.CustKey = _tInvTranHeader.CustKey
AND _tInvTranHeader.InvoiceDate > ’12/21/2006′
INNER JOIN
tarCustAddr ON tarCustomer.CustKey = tarCustAddr.CustKey INNER JOIN
tarInvoice ON tarCustomer.CustKey = tarInvoice.CustKey INNER JOIN
tarSalesperson ON tarCustAddr.SperKey = tarSalesperson.SperKey INNER JOIN
tarSalesTerritory ON tarCustAddr.SalesTerritoryKey = tarSalesTerritory.SalesTerritoryKey INNER JOIN
tciAddress ON tarCustAddr.AddrKey = tciAddress.AddrKey INNER JOIN
_tInvTranDetail ON _tInvTranHeader.InvcKey = _tInvTranDetail.InvcKey
where _tInvTranHeader.InvoiceDate > ’12/21/2006′
>> When I run the query below I over-run the filegroup << Since you are using a temporary table, you are over-running the filegroup of the tempdb database and not your database. Check your tempdb database and make sure that it has enough space to accommodate your temporary table. SQL Server Helper
http://www.sql-server-helper.com
quote:Originally posted by joechang what exactly is the error message?
Query 1 ran fine, but Q2 failed? look at the execution plan
my thinking is the plan for Q2 has a hash join or something that uses tempdb space, so the error message would be something like: unable to allocate more space for tempdb.
in which case, freespace on the main db is irrelevent usually, i like to put tempdb data on the same disk as the main db data, but if you can manage the space on a separate partition

The error message is "Primary File Group is Full" If I run only the first three lines (the part that creates the temp table) everything comes back within 25 seconds. Q2 runs for about 30 – 45 minutes and then gives the error mentioned.
quote:Originally posted by sshelper >> When I run the query below I over-run the filegroup << Since you are using a temporary table, you are over-running the filegroup of the tempdb database and not your database. Check your tempdb database and make sure that it has enough space to accommodate your temporary table. SQL Server Helper
http://www.sql-server-helper.com

The temp (#ELP_Temp) that is created is only about 7400 rows. It executes without error when run by it self within about 25 seconds. The Select Into is where I am running into problems. I am just letting the query create the table using Select Into and it seems to grow without limit. I can watch the file group for the main database (the one that the ELP_VOG table is created in) fill up and go to less than 5mb free in enterprise manager. ~Donovan
Where is tran log located? All the changes are being logged until the insert completes and is committed. Watch the tran log size while the query runs. Hank
quote:Originally posted by Donoval I have a database that is about 60GB. When I run the query below I over-run the filegroup – which has about 12gb free. I dont understand why it is pulling more than a 6th of the database when it should only be returning about 30,000 rows. First, I create a temp table that limits the query by the last week (to about 21,000 rows). This works fine when ran as a seperate query. Then I join it to the other tables to create a new static table for reporting.
Select _tInvTranHeader.CustKey, _tInvTranHeader.InvcKey, _tInvTranHeader.InvoiceNo,_tInvTranHeader.InvoiceDate INTO #ELP_Temp
From _tInvTranHeader
where _tInvTranHeader.InvoiceDate > ’12/21/2006′ SELECT _tInvTranDetail.QtyShipped, _tInvTranDetail.UnitCost, #ELP_Temp.InvoiceDate, tarSalesperson.SperName INTO ELP_VOG
FROM tarCustomer INNER JOIN #ELP_Temp ON tarCustomer.CustKey = #ELP_Temp.CustKey INNER JOIN
tarCustAddr ON tarCustomer.CustKey = tarCustAddr.CustKey INNER JOIN
tarInvoice ON tarCustomer.CustKey = tarInvoice.CustKey INNER JOIN
tarSalesperson ON tarCustAddr.SperKey = tarSalesperson.SperKey INNER JOIN
tarSalesTerritory ON tarCustAddr.SalesTerritoryKey = tarSalesTerritory.SalesTerritoryKey INNER JOIN
tciAddress ON tarCustAddr.AddrKey = tciAddress.AddrKey INNER JOIN
_tInvTranDetail ON #ELP_Temp.InvcKey = _tInvTranDetail.InvcKey
Drop Table #ELP_Temp Any ideas or suggestions? Thanks, ~Donovan

My guess is you may not have all of your joins completely set up and this query is returning many more records than you are expecting it to. I’d suggest taking out you joins and add them in one by one and check the result set before adding in the next one. Dave Schlieder
That is exactly what the problem was. I went back to the first join and added the tables back in one by one. One of the joins was producing 8 millions rows. I redid the query and pushed it to a temp table and it came back in 2 seconds. Thanks for help. ~Donoval
quote:Originally posted by dschlieder
quote:Originally posted by Donoval I have a database that is about 60GB. When I run the query below I over-run the filegroup – which has about 12gb free. I dont understand why it is pulling more than a 6th of the database when it should only be returning about 30,000 rows. First, I create a temp table that limits the query by the last week (to about 21,000 rows). This works fine when ran as a seperate query. Then I join it to the other tables to create a new static table for reporting.
Select _tInvTranHeader.CustKey, _tInvTranHeader.InvcKey, _tInvTranHeader.InvoiceNo,_tInvTranHeader.InvoiceDate INTO #ELP_Temp
From _tInvTranHeader
where _tInvTranHeader.InvoiceDate > ’12/21/2006′ SELECT _tInvTranDetail.QtyShipped, _tInvTranDetail.UnitCost, #ELP_Temp.InvoiceDate, tarSalesperson.SperName INTO ELP_VOG
FROM tarCustomer INNER JOIN #ELP_Temp ON tarCustomer.CustKey = #ELP_Temp.CustKey INNER JOIN
tarCustAddr ON tarCustomer.CustKey = tarCustAddr.CustKey INNER JOIN
tarInvoice ON tarCustomer.CustKey = tarInvoice.CustKey INNER JOIN
tarSalesperson ON tarCustAddr.SperKey = tarSalesperson.SperKey INNER JOIN
tarSalesTerritory ON tarCustAddr.SalesTerritoryKey = tarSalesTerritory.SalesTerritoryKey INNER JOIN
tciAddress ON tarCustAddr.AddrKey = tciAddress.AddrKey INNER JOIN
_tInvTranDetail ON #ELP_Temp.InvcKey = _tInvTranDetail.InvcKey
Drop Table #ELP_Temp Any ideas or suggestions? Thanks, ~Donovan

My guess is you may not have all of your joins completely set up and this query is returning many more records than you are expecting it to. I’d suggest taking out you joins and add them in one by one and check the result set before adding in the next one. Dave Schlieder

]]>