SQL Server Performance

What am I doing wrong??

Discussion in 'T-SQL Performance Tuning for Developers' started by Donoval, Jan 3, 2007.

  1. Donoval New Member

    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
  2. joechang New Member

    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
  3. ranjitjain New Member

    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'
  4. sshelper New Member

    >> 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
  5. Donoval New Member

    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.
  6. Donoval New Member

    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
  7. HankS New Member

    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
  8. dschlieder New Member

    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
  9. Donoval New Member

    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

Share This Page