March 9, 2009 at 7:35 pm
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
March 9, 2009 at 8:24 pm
Could you post the code? Or attach the SQL file?
Mohit.
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]
March 10, 2009 at 7:31 am
please see the query attached.
thanks
March 10, 2009 at 5:01 pm
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.
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]
March 10, 2009 at 8:42 pm
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
March 10, 2009 at 11:01 pm
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.
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]
March 11, 2009 at 7:02 am
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
March 11, 2009 at 7:03 am
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
March 11, 2009 at 7:04 am
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.
March 11, 2009 at 8:01 am
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.
March 11, 2009 at 9:22 am
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.
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]
March 11, 2009 at 10:54 am
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.
March 11, 2009 at 4:39 pm
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
March 12, 2009 at 4:47 am
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.
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]
March 12, 2009 at 8:20 am
Sorru i posted in wrong place. I am running on SQL 2005 Enterprise.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply