Help: tempdb grows to 8G suddenly

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,
JackJack,
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
>|||One possibility is a query that caused a lot of worktables to be created.
Since it takes quite a while to create worktables to occupy 8 Gig, you might
use profiler to monitor queries taking > x minutes. Then, shrink the
tempdb, run the query via Query Analyzer and see if tempdb grows again.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"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
>|||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
> >
> >
>|||FYI...
If you're trying to find where this happens...
Profiler has an event called Missing Join Predicate in the errors and
warnings event class that will capture situation in which SQL Server thinks
that a join predicate is missing. I have never tested the logic to see how
it determines the missing predicate to know if it can absolutley catch all
partial cartesian products, but it does a pretty good job and is worth
taking a look at...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:O5iC7i41EHA.1264@.TK2MSFTNGP12.phx.gbl...
> 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
> > >
> > >
> >
> >
>