Hello,
I've got a user who has been trying to run a query for the last few
days. Every time he tries to run it, the tempdb grows to be about 5G and
ends up filling up the hard drive. Anyone have any insight into why
this particular query is filling up the tempdb like this?
SELECT top 50
SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS Corp,
SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS BillGroup,
SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As BillEntity,
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS LOB,
SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS Portfolio,
SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS WorkType.
SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS Client,
SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS Service,
SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS Service Type,
SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS Product,
SUBSTRING(dbo.CIMSDetail.AccountCode, 26, 3) AS Department,
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS LOC,
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS LPAR,
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS ORIGSUM,
dbo.CIMSDetail.AccountCode As Acct_Code,
dbo.CIMSDetail.StartDate AS StartDay,
dbo.CIMSDetail.EndDate AS EndDay,
dbo.CIMSDetail.RateCode as RateCode,
dbo.CIMSDetail.ResourceUnits AS Volume,
dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
FROM dbo.CIMSDetail INNER JOIN
dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID = dbo.CIMSDetailIdent.DetailUID AND
dbo.CIMSDetail.DetailLine = dbo.CIMSDetailIdent.DetailLine INNER JOIN
dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber = dbo.CIMSIdent.IdentNumber
WHERE DATEPART(DD,GETDATE()) - DATEPART(DD, dbo.CIMSDetail.EndDate) = 1
AND dbo.CIMSDetail.RateCode IN ('Z003', 'Z020', 'ZZ05')
AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
OR dbo.CIMSIdent.IdentDescription = 'WORK_ID'
GROUP BY
DATEPART(yyyy, dbo.CIMSDetail.EndDate),
DATEPART(mm, dbo.CIMSDetail.EndDate),
DATEPART(dd, dbo.CIMSDetail.EndDate),
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.StartDate,
dbo.CIMSDetail.EndDate,
dbo.CIMSIdent.IdentDescription,
dbo.CIMSDetailIdent.IdentValue,
dbo.CIMSDetail.AccountCode,
SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
ORDER BY
DATEPART(yyyy, dbo.CIMSDetail.EndDate),
DATEPART(mm, dbo.CIMSDetail.EndDate),
DATEPART(dd, dbo.CIMSDetail.EndDate),
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.StartDate,
dbo.CIMSDetail.EndDate,
dbo.CIMSIdent.IdentDescription,
dbo.CIMSDetailIdent.IdentValue,
dbo.CIMSDetail.AccountCode,
SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You might consider normalization. :-) Why is all that discrete data in one
column? The database has to do a lot of work separating it out with
SUBSTRING like that.
You might also consider putting tempdb on a drive with more than 5GB free.
:-)
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:eJh59sOjDHA.3192@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I've got a user who has been trying to run a query for the last few
> days. Every time he tries to run it, the tempdb grows to be about 5G and
> ends up filling up the hard drive. Anyone have any insight into why
> this particular query is filling up the tempdb like this?
> SELECT top 50
> SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS Corp,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS BillGroup,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As BillEntity,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS LOB,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS Portfolio,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS WorkType.
> SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS Client,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS Service,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS Service Type,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS Product,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 26, 3) AS Department,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS LOC,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS LPAR,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS ORIGSUM,
> dbo.CIMSDetail.AccountCode As Acct_Code,
> dbo.CIMSDetail.StartDate AS StartDay,
> dbo.CIMSDetail.EndDate AS EndDay,
> dbo.CIMSDetail.RateCode as RateCode,
> dbo.CIMSDetail.ResourceUnits AS Volume,
> dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
> dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
> DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
> DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
> DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
>
> FROM dbo.CIMSDetail INNER JOIN
> dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID => dbo.CIMSDetailIdent.DetailUID AND
> dbo.CIMSDetail.DetailLine = dbo.CIMSDetailIdent.DetailLine INNER JOIN
> dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber => dbo.CIMSIdent.IdentNumber
> WHERE DATEPART(DD,GETDATE()) - DATEPART(DD, dbo.CIMSDetail.EndDate) => 1
> AND dbo.CIMSDetail.RateCode IN ('Z003', 'Z020', 'ZZ05')
> AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
> OR dbo.CIMSIdent.IdentDescription = 'WORK_ID'
> GROUP BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
> ORDER BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Typically coputed values in your select statement (like
all of the substring statements) require use of tempdb
space, and the group by and order by portions of your
query will also require a large amount of tempdb space.
In addition to Aaron's comments, you may want to consider
using a summary table that stores some of these
calculated/summary values so that you don't have to
calculate them within the query itself. While this will
use space within your actual database, this will be
easier to monitor and control then the space allocated on
the fly within tempdb.
Just an idea. I hope that this helps somehow.
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hello,
>I've got a user who has been trying to run a query for
the last few
>days. Every time he tries to run it, the tempdb grows to
be about 5G and
>ends up filling up the hard drive. Anyone have any
insight into why
>this particular query is filling up the tempdb like this?
>SELECT top 50
> SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS
Corp,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS
BillGroup,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As
BillEntity,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS
LOB,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS
Portfolio,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS
WorkType.
> SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS
Client,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS
Service,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS
Service Type,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS
Product,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 26,
3) AS Department,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS
LOC,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS
LPAR,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS
ORIGSUM,
> dbo.CIMSDetail.AccountCode As Acct_Code,
> dbo.CIMSDetail.StartDate AS StartDay,
> dbo.CIMSDetail.EndDate AS EndDay,
> dbo.CIMSDetail.RateCode as RateCode,
> dbo.CIMSDetail.ResourceUnits AS Volume,
> dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
> dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
> DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
> DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
> DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
>
>FROM dbo.CIMSDetail INNER JOIN
> dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID =>dbo.CIMSDetailIdent.DetailUID AND
> dbo.CIMSDetail.DetailLine =dbo.CIMSDetailIdent.DetailLine INNER JOIN
> dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber =>dbo.CIMSIdent.IdentNumber
>WHERE DATEPART(DD,GETDATE()) - DATEPART(DD,
dbo.CIMSDetail.EndDate) =>1
> AND dbo.CIMSDetail.RateCode IN
('Z003', 'Z020', 'ZZ05')
> AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
> OR dbo.CIMSIdent.IdentDescription
= 'WORK_ID'
>GROUP BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>ORDER BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32,
4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>
Showing posts with label tempdb. Show all posts
Showing posts with label tempdb. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Query Evaluvation and tempdb
Dear All,
Does SQL server store the intermediate results set in the tempdb? One of the
team members has dropped the indexes for our one large table. I was running
a
query that has multiple joins (including this table) , and this caused tempd
b
to fill up.
According to this article by Microsoft,
http://msdn2.microsoft.com/en-us/library/ms190768.aspx, I'm under impression
that table scan has stored a large set of intermediate data in tempdb, whic
h
caused it to fill up.
My team members seem to argue that temp db is only used when we use temp
tables.
Secondly, does sql server evaluate the join first, or where clause first?
(Or it depends on each query?)
Thank you much for your help in advance.> My team members seem to argue that temp db is only used when we use temp
> tables.
Your team members are wrong. Tempdb can be used for sorting on just about
any type of query. Also see a list of items here under "Causes":
http://www.aspfaq.com/2446
A
Does SQL server store the intermediate results set in the tempdb? One of the
team members has dropped the indexes for our one large table. I was running
a
query that has multiple joins (including this table) , and this caused tempd
b
to fill up.
According to this article by Microsoft,
http://msdn2.microsoft.com/en-us/library/ms190768.aspx, I'm under impression
that table scan has stored a large set of intermediate data in tempdb, whic
h
caused it to fill up.
My team members seem to argue that temp db is only used when we use temp
tables.
Secondly, does sql server evaluate the join first, or where clause first?
(Or it depends on each query?)
Thank you much for your help in advance.> My team members seem to argue that temp db is only used when we use temp
> tables.
Your team members are wrong. Tempdb can be used for sorting on just about
any type of query. Also see a list of items here under "Causes":
http://www.aspfaq.com/2446
A
Subscribe to:
Posts (Atom)