Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2002
    Posts
    10

    Unanswered: TEMPDB increases size used at alarming pace.

    Help.

    I have two servers one is called prod and the other test. Both are running 7.0 with sp3.

    When I do a query selecting just a count(*) for both DBs I get a result in 28 seconds (using Query Analyzer). If I actually select columns instead of a count(*), the TEST db returns all 166K rows in 1 minute and 30 seconds and doesn't fill up the TEMPDB to do so. BUT the same query against PROD, fills up the TEMPDB (so far at 4.7GB and growing) and hasn't returned a result set within 30 minutes.

    Initially I created the TEST box by restoring PROD's backups. I verified that the indexes on both machines are the same for the tables I'm working on.

    The TEST box is a smaller machine in relation to cpu and memory, yet seems to be working better. Can some one direct me to other settings that may tell the PROD system to use tempdb more than TEST?

    Thanks

    Chris

  2. #2
    Join Date
    May 2002
    Posts
    299
    Have you reindex and update statistics?

    dbcc dbreindex & sp_updatestats
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Jul 2002
    Posts
    10

    will try re-indexing and updating stats

    I'll try your suggestions since it can't hurt, but would that cause the tempdb of PROD to rise drastically whilst TEST doesn't?

    I'll let you know the results..


    "I'm not a DBA, but I play one' at work
    Chris

    seriously not a SQLserver DBA (we don't have one)

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Hi cfauvel

    It sounds like the only thing that you are doing is a straight SELECT. So for a SELECT to fill up TEMPDB I can only think that you are doing a GROUP BY or an ORDER BY, are you? If you are doing the same ORDER BY in PROD and TEST, however the indexes are not the same in both environments then this may be the cause. If you SELECT data ORDER BY a defined clustered index very little TEMPDB activity, however if you ORDER BY a non-clustered column then SQL Server will use TEMPDB to sort the data.
    MCDBA

  5. #5
    Join Date
    Jul 2002
    Posts
    10

    nope - no groupings or order by

    Originally posted by achorozy
    Hi cfauvel

    It sounds like the only thing that you are doing is a straight SELECT. So for a SELECT to fill up TEMPDB I can only think that you are doing a GROUP BY or an ORDER BY, are you? If you are doing the same ORDER BY in PROD and TEST, however the indexes are not the same in both environments then this may be the cause. If you SELECT data ORDER BY a defined clustered index very little TEMPDB activity, however if you ORDER BY a non-clustered column then SQL Server will use TEMPDB to sort the data.

    here is the original sql.

    select
    a.asi_client, a.vnd_name,
    a.rpt_start, a.rpt_end,
    a.dist_name, a.unit_name,
    a.address, a.city, a.state,
    a.mfg_id, a.dist_id, a.itm_desc,
    a.sales_qty, a.rebate_trk , a.extended
    from valutrak a
    where exists (select 1
    from (((asibig6.dbo.vtk_program d inner join
    asibig6.dbo.company c on
    d.manufacturer_id = c.company_id)
    inner join asibig6.dbo.vtk_programclient vpc on
    vpc.program_id = d.program_id)
    inner join asibig6.dbo.vtk_client vc on
    vc.client_id = vpc.client_id)
    WHERE
    d.program_grp_id = 1
    and a.vnd_name = c.name
    and a.asi_client = vc.client_name
    and a.rpt_start between d.start_date and d.end_date
    and a.asi_client in ('IRP', 'AGC','PER','B2B','SAU','SSF')
    and a.year_rpt = 2002
    and a.qtr_rpt = 1
    and a.rebate_trk > 0
    and a.vnd_name <> '[UNASSIGNED ALIASES]'
    )


    Then to do a count I simply replace the columns with count(*).

    I verified that the two databases have the same indexes by creating the SQL syntax for the indexes from PROD and ran the resulting SQL against TEST.... I have just re-indexed PROD and ran sp_updatestats on PROD...so I'll re-run the query and see what happens.

    any other clues?

    Thanks/Merci/Gracias/Maruru/Ef haristo[ics]

    Chris

  6. #6
    Join Date
    Jul 2002
    Posts
    10

    One more symptom

    Originally posted by achorozy
    Hi cfauvel

    It sounds like the only thing that you are doing is a straight SELECT. So for a SELECT to fill up TEMPDB I can only think that you are doing a GROUP BY or an ORDER BY, are you? If you are doing the same ORDER BY in PROD and TEST, however the indexes are not the same in both environments then this may be the cause. If you SELECT data ORDER BY a defined clustered index very little TEMPDB activity, however if you ORDER BY a non-clustered column then SQL Server will use TEMPDB to sort the data.

    another thing is when I run the query within query analyzer against TEST I start getting rows right away. I do it against PROD and I DON'T get rows back to the screen.

  7. #7
    Join Date
    Jul 2002
    Posts
    10

    Post reindex and updatestats results

    Much much better..not perfect but better.

    The query returns rows to the query analyzer immediately and does not fill up the TEMPDB anymore.

    However the query in Prod still takes almost 3 times longer.
    Prod: 3:38
    Test: 1:20

    So what was wrong? Were the indexes on the table corrupt? Stats corrupt? Help me understand please.

    what next as far as getting the query to run as fast on Prod as on Test?

    Chris

  8. #8
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    It hard to say but given that one is TEST and the other is PROD, I would say that PROD is much more active, INSERTs, UPDATEs and DELETEs. That the data pages become defragmented (read Books online DBCC SHOWCONTIG). Also as more pages get added and values changes the status that SQL Server saves become out dated and need to be updated.

    Now again since one is TEST and the other is PROD, I would guess that when you run your query on PROD more endusers are using the system then when you are running against TEST. If you don't mind "Dirty Reads" which in our system is not a problem then either place the option "(nolock)" after each table name or prior to your SELECT issue SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and after the SELECT issue SET TRANSACTION ISOLATION LEVEL READ COMMITTED. This will prevent your query from blocking endusers from INSERT,UPDATE and DELETE, but will also stop those very commands from blocking you.
    MCDBA

  9. #9
    Join Date
    Jul 2002
    Posts
    10

    Indexes checked again - results

    I verified once again that the indexes were the same and did the execution plan for both server for the same query and they are nearly the same.

    The PROD plan has an extra "table spool/lazy" and to the left has a "nested loop/left semi join"

    The TEST plan doesn't have the "table spool/lazy spool" and has a "Hash Match/right semi join" instead of the "nested loop/left semi join"

    The other scans are the same, using the same indexes in the same order.

    (hard to describe the plan without a picture....sorry).

    anyway, the query in PROD takes from 3:38 - 4:45 minutes, and TEST is pretty consistent at 1:20 - 1:40 minutes

    At leasttempdb is not growing and using 4.5 gb of space.

  10. #10
    Join Date
    Sep 2002
    Posts
    1

    Question

    Have you turned statistics on so you can see exactly where and why the worksets are being created that are maxing out your tempdb?

    SET STATISTICS IO ON
    GO
    SET STATISTICS TIME ON
    GO
    SET SHOWPLAN_ALL ON
    GO
    Last edited by BrenBoy; 09-18-02 at 12:23.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •