Re: tempdb grows to 8G suddenly
From: Brian Moran (brian_at_solidqualitylearning.com)
Date: 12/01/04
- Next message: Keith Kratochvil: "Re: If statement in subqueries"
- Previous message: J Wolfgang Goerlich: "SQL Agent Backup Failure: "Pipe has been ended""
- In reply to: Jack Hwang: "Re: tempdb grows to 8G suddenly"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 1 Dec 2004 09:26:25 -0500
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 > > > > > > > > > > > >
- Next message: Keith Kratochvil: "Re: If statement in subqueries"
- Previous message: J Wolfgang Goerlich: "SQL Agent Backup Failure: "Pipe has been ended""
- In reply to: Jack Hwang: "Re: tempdb grows to 8G suddenly"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
