SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tempdb issue


Tempdb issue

Author
Message
samsql
samsql
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 558
Hi Experts,

i have an issue wen i create a temp table it take
1 sec to create a temp table in temp db also wen i check it from the perfmon
the active temp tables shows more the 16 lacs of temp table
the table name in the tempdb shows as this #table_________________
blakmk
blakmk
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 337
temp tables are also created by other processes that perform sorts as part of their operation

Sql Server Blog
Sql Server Consultancy

samsql
samsql
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 558
yes but wat i m staying here is

there is a procedure in which there are around 30 more procedure which are getting called

Eg create procedure abcdef
(

begin

exec ab ---doing some operations creatin 5 temp table doing some updation,insertion etc

exec abc ---doing some operations creatin 5 temp table doing some updation,insertion etc

exec abe ---doing some operations creatin 5 temp table doing some updation,insertion etc

exec abd ---doing some operations creatin 5 temp table doing some updation,insertion etc

end

)


what i know that after the procedure is exec the temp tables gets destroyed from the tempdb.
but here in this senario the temp tables are not getting getting destroyed from tempdb.
they are getting dropped wen the abcdef procedure gets executed completly.
Also wen we r running this procedure from different session we are getting blocking on tempdb
while creating a temp tables within the procedures
blakmk
blakmk
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 337
looks like you are using global temporary tables which may cause blocking:

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Sql Server Blog
Sql Server Consultancy

samsql
samsql
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 558
no not using global temp tables ...
samsql
samsql
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 558
waiting for the reply...Jeff can u help in this process
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39315 Visits: 32621
Temp tables last for the length of the process that created them. That wrapper proc is the process, so as long as it's running, you'll keep those temp tables. You can explicitly drop temp tables too.

What are the procs doing? It doesn't sound like you're using the temp tables in an efficient manner.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
saby
saby
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 277
Your process was involved in unresolved deadlock, therefore, the tables were not dropped and blocked the creating table process in tempdb.

This might be the bug in sqlserver 2005.

create proc proc1

as

begin

create table #abcd ( i int)

drop table #abcd

end

go



BEGIN TRAN

go

DECLARE @I INT

SET @I = 1

WHILE @I < 5000

BEGIN

EXEC proc1

SET @I = @I + 1

END

go

Select count(*) from tempdb.sys.tables

go


If you notice the last output, the tempdb now has 5000 temp tables. From a programmer perspective, as soon as a stored procedure finishes, the temp table scope is over and it is destroyed. In SQL2005 we do a deferred drop, but that doesn’t kick in until transaction is over.


Because of the begin tran command, the temp tables are not dropped and hence tempdb locks not released. However, these tables are no longer accessible even to the SPID which created it, but are still present in tempdb.

Saby DBA
samsql
samsql
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 558
yes saby i m gettin blocking in the tempdb.

Grant there are procedure with begin and tran
E.g
proc ssa
(
Begin tran
exec procedure1
-----create some temp table and do some updation,insertion etc on temp table and physical table as there are more than 5 tables in each proc ...

exec procedure2 -----create some temp table and do some updation,insertion etc on temp table and physical table as there are more than 5 tables in each proc ...

exec procedure3-----create some temp table and do some updation,insertion etc on temp table and physical table as there are more than 5 tables in each proc ...

commit

run with diffrent sessions got the blocking while creating a temp table
and one more thing is that the table creation takes 2 secs to create which is really more time.

it destroy the table after the 1 procedure get fully exec till commit.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39315 Visits: 32621
Saby, I'm confused. Where did you get deadlocks in this situation. We're talking about blocking. That's not the same thing as deadlocks.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search