Re: tempdb grows to 8G suddenly

From: Jack Hwang (jack_hc_at_hotmail.com)
Date: 12/01/04

  • Next message: Hugo Kornelis: "Re: any impact if create index and then drop index" 
    Date: Wed, 1 Dec 2004 17:32:48 +0800

    Hi, Mike,

    thanks for your valuable info, let me see what I can do, things are messed
    up.

    Regards,

    Jack

    "Mike Hodgson" <mwh_junk@hotmail.com> wrote in message
    news:OonAlcq0EHA.1308@TK2MSFTNGP09.phx.gbl...
    > Jack,
    >
    > In my experience, sudden increases in tempdb size are usually attributable
    > to someone running a query which has a join that has not been qualified
    > properly. In such a case, a huge result set gets built up (in tempdb
    where
    > temporary working space is always allocated during query execution) in
    order
    > to satisfy the query and tempdb quite often runs out of space. As a
    really
    > basic example, in the old pre SQL-99 syntax, let's say someone was trying
    to
    > write
    >
    > select * from tableA a, tableB b
    > where a.id = b.id
    > and a.surname like 'H%'
    > and b.country = 'Australia'
    >
    > and they left off the join part of the WHERE clause
    >
    > select * from tableA a, tableB b
    > where a.surname like 'H%'
    > and b.country = 'Australia'
    >
    > they'd end up with the results getting pulled from the Cartesian product
    of
    > tableA & tableB (ie. a CROSS JOIN) rather than an INNER JOIN of the two
    > tables. Usually, you see this in much larger queries with many JOIN
    > statements, some with multiple join criteria (eg. multi-column primary
    > keys), and so it is much harder to identify but if you can get onto the
    > server while tempdb is filling up rapidly and check sp_who repeatedly, you
    > should be able to see which SPID is chewing up the CPU & I/O most rapidly;
    > then you can do a DBCC INPUTBUFFER (<spid>) to find out what query that
    SPID
    > is trying to execute and if you look hard enough you can often find a join
    > in that query that is not qualified properly.
    >
    > HTH
    >
    > --
    > Cheers,
    > Mike
    >
    > "Jack Hwang" <jack_hc@hotmail.com> wrote in message
    > news:e0WrtAq0EHA.3236@TK2MSFTNGP15.phx.gbl...
    > > Hi, guys,
    > >
    > > The tempdb is usually 70/80M in size when SQL start, but it grows to 8G
    > > suddently during times, how could I identify where the issue is, it
    > > happened
    > > after application upgraded to newer version, but no sp or job changed.
    > > application db is now <>10G.
    > >
    > > I am using SQL2000+SP3 IN WIN2000+SP4
    > >
    > > Thanks in advance.
    > >
    > > Regards,
    > >
    > > Jack
    > >
    > >
    >
    >

  • Next message: Hugo Kornelis: "Re: any impact if create index and then drop index"

    Relevant Pages

    • Help: tempdb grows to 8G suddenly
      ... The tempdb is usually 70/80M in size when SQL start, ... Regards, ... Jack ...
      (microsoft.public.sqlserver.server)
    • Re: simple SunBlade 1500 external question
      ... Are there commands to query for this ... > only, dvd and one of the writable formats, etc. ... Up front you get two USB 1.1 ports, a headphone jack, and a microphone ...
      (comp.unix.solaris)
    • Re: Union query
      ... "John Spencer" wrote: ... Jack,building,room combo from my Jack Table that is not listed in my ... Query 1 gives me 3924 reponses ... The PhoneJack table has ...
      (microsoft.public.access.queries)
    • RE: Link to Office Word 2003
      ... Is it possable to export data in a field to a word doc., such as names, ... "Jack" wrote: ... > Is there a way of sending the results of a query to a Word mailmerge ...
      (microsoft.public.access.gettingstarted)
    • Re: MacBook 13" charger circuit
      ... Jack B. Pollack Inscribed thus: ... possible components (voltages, etc) to check. ... I strongly doubt there is one - Apple services these machines at the ... Best Regards: ...
      (sci.electronics.basics)