rapid growth of tempdb

Question

Post reply

rapid growth of tempdb

Mike Levan

SSChampion

Points: 14201
More actions
March 9, 2009 at 7:35 pm

#202129

our developers running many long running queires in which multiple temp tables are involved which runs for more than 8-10 hrs and meanwhile the tempdb is growing rapidly for ober 700GB, I want to change the way they run queries. how can they run all thier queries in batches, most of their queiores are select statements for which they dump bulk data into temp tables and then query from the temp table for thier requirements.
thanks

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

Could you post the code? Or attach the SQL file?

Mohit.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Mike Levan SSChampion Points: 14201 More actions · Answer 2

Mike Levan

SSChampion

Points: 14201

March 10, 2009 at 7:31 am

#956778

please see the query attached.

thanks

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 3

Hi Mike,

How many rows are are in "dbo.EmpAdmin" table? I am concerned about the time it is taking you to complete this; and how it is being done.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Mike Levan (3/10/2009)
please see the query attached.
thanks

Heh... if you want more people to look at it, post it in a format more people have. I sure haven't spent the money to upgrade to Office 2007. Of course, it's simpler for me this way... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 5

What I am shocked at is these quries taking such long time to run; so if we can get row number for the dbo.EmpAdmin table. Also what does the MASTER.DBO.FN_STRING_DECODE function t-sql code it might be easier to understand what is going on.

Right now why not decode the value in the first select? So you don't have to have two temp tables that later get joined?

Could you also list table structure .. I think these queries need to be be re-writen ...

Thanks.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Mike Levan SSChampion Points: 14201 More actions · Answer 6

Ok, i understand everyone may not be able to see the atatched file and so am copying here.

drop table #Temp1
select distinct *,'1/1/2005' as EmptYear
,case
when RevID='DE' THEN '4/30/2008'
when RevID in ('CA', 'GA') then '5/31/2008'
when RevID in ('IN', 'KY', 'MO', 'OH', 'WI') then '4/30/2008'
when RevID in ('VA','NV') then '5/31/2008'
ELSE '6/30/2008'
end as IND_END
into #Temp1
from dbo.EmpAdmin
DROP TABLE #TEMP2
SELECT *,MASTER.DBO.FN_STRING_DECODE(IDCODE) AS REVJOBID
INTO #TEMP2
FROM #TEMP1
select a.RevID,COUNT(DISTINCT A.REVJOBID)
from #TEMP2 as a INNER join obbeaver4.obbeaver_full_lab.dbo.FULL_LAB AS B
ON A.REVJOBID=B.REVJOBID AND A.REVID=B.PLAN_ID
inner join dbo.Eligdates as c on a.idcode=c.IDCODE
,#Temp1 as d
WHERE (JOBDATE BETWEEN d.EMPTYEAR AND d.IND_END)
and (jobdate between startdate and enddate )
and ( Budgetin ('11039-5','14634-0','45062-7','48421-2'
,'16503-5','1988-5','30522-7','35648-5','43402-7'
,'4537-7','4538-5','18184-2','30341-2','4539-3'
,'11571-7','9839-2','17532-3','17533-1','15203-3'
,'15204-1','15205-8','15206-6','11572-5','13191-2'
,'13634-1','13929-5','13930-3','14034-3','43932-3'
,'45156-7','5297-7','5298-5','5299-3','5300-9'
,'6823-9','6928-6','17534-9','17857-4','25745-1'
,'25746-9','29351-4','30231-5','31046-6','33910-1'
,'46435-4','46437-0','33313-8','33314-6','11573-3'
,'9338-5','33577-8','32218-0','42898-7','33935-8'))
GROUP BY A.REVID
select a.RevID,COUNT(DISTINCT A.REVJOBID)
from #TEMP2 as a INNER join obbeaver4.obbeaver_full_lab.dbo.FULL_LAB AS B
ON A.REVJOBID=B.REVJOBID AND A.REVID=B.PLAN_ID
inner join dbo.Eligdates as c on a.idcode=c.IDCODE
,#Temp1 as d
WHERE (JOBDATE BETWEEN d.EMPTYEAR AND d.IND_END)
and (jobdate between startdate and enddate )
and (a.ra=1)
and ( Budget in ('11039-5','14634-0','45062-7','48421-2'
,'16503-5','1988-5','30522-7','35648-5','43402-7'
,'4537-7','4538-5','18184-2','30341-2','4539-3'
,'11571-7','9839-2','17532-3','17533-1','15203-3'
,'15204-1','15205-8','15206-6','11572-5','13191-2'
,'13634-1','13929-5','13930-3','14034-3','43932-3'
,'45156-7','5297-7','5298-5','5299-3','5300-9'
,'6823-9','6928-6','17534-9','17857-4','25745-1'
,'25746-9','29351-4','30231-5','31046-6','33910-1'
,'46435-4','46437-0','33313-8','33314-6','11573-3'
,'9338-5','33577-8','32218-0','42898-7','33935-8'))
GROUP BY A.REVID

Mike Levan SSChampion Points: 14201 More actions · Answer 7

the same query again tempdb growed to 1 TB and i have to restart the service. can i restrict the growth from unrestricted to like 400GB? does it solve the problem.

thanks

Mike Levan SSChampion Points: 14201 More actions · Answer 8

How many rows are are in "dbo.EmpAdmin" table? I am concerned about the time it is taking you to complete this; and how it is being done.

Mohit

That takes less than 5 seconds, may only 1014000 rows are pulled from that.

DROP TABLE #TEMP2
SELECT *,MASTER.DBO.FN_STRING_DECODE(IDCODE) AS REVJOBID
INTO #TEMP2
FROM #TEMP1

for this part it took 2.40 min to run 1014000 records

select a.RevID,COUNT(DISTINCT A.REVJOBID)
from #TEMP2 as a INNER join obbeaver4.obbeaver_full_lab.dbo.FULL_LAB AS B
ON A.REVJOBID=B.REVJOBID AND A.REVID=B.PLAN_ID
inner join dbo.Eligdates as c on a.idcode=c.IDCODE
,#Temp1 as d
WHERE (JOBDATE BETWEEN d.EMPTYEAR AND d.IND_END)
and (jobdate between startdate and enddate )
and ( Budgetin ('11039-5','14634-0','45062-7','48421-2'
,'16503-5','1988-5','30522-7','35648-5','43402-7'
,'4537-7','4538-5','18184-2','30341-2','4539-3'
,'11571-7','9839-2','17532-3','17533-1','15203-3'
,'15204-1','15205-8','15206-6','11572-5','13191-2'
,'13634-1','13929-5','13930-3','14034-3','43932-3'
,'45156-7','5297-7','5298-5','5299-3','5300-9'
,'6823-9','6928-6','17534-9','17857-4','25745-1'
,'25746-9','29351-4','30231-5','31046-6','33910-1'
,'46435-4','46437-0','33313-8','33314-6','11573-3'
,'9338-5','33577-8','32218-0','42898-7','33935-8'))
GROUP BY A.REVID

this part is took 40min as it is querying cross over server

and the last step took about 12 sec to run.

Mike Levan SSChampion Points: 14201 More actions · Answer 9

If i run this each step individually it takes around 50min for whole process but if i run all at once it is taking for ever.

guys..whats your suggestions.

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 10

Mike Levan (3/11/2009)
the same query again tempdb growed to 1 TB and i have to restart the service. can i restrict the growth from unrestricted to like 400GB? does it solve the problem.
thanks

Wouldn't recommend it because if it is growing to 1TB if you restrict it will then it will complain about drive space issues.

Thanks.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Mike Levan SSChampion Points: 14201 More actions · Answer 11

but i see a lot of diffrence when i run step by step.

is there a way i can do it automatically instead of running each step manually.

Mike Levan SSChampion Points: 14201 More actions · Answer 12

Here is the weired thing i observed, am not sure how sql server is reacting.

If i ran the whole query it takes around an hour but when login as another user (developer) with this login id into sql server and run the same query it is taking for ever, it is still running for over 4 hrs, i dont understand how sql server works accroding to the login.

someone please clear this doubt, its kind of frustrating to me about sql server.

thanks

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 13

Question is this SQL Server 2005? If it is running as your self fast but as developer maybe you are having issue with USERTSTORE_TOENPERM.

Check out: http://support.microsoft.com/kb/933564.

When you run it how much does the tempdb grow by?

Mohit.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Mike Levan SSChampion Points: 14201 More actions · Answer 14

Sorru i posted in wrong place. I am running on SQL 2005 Enterprise.