Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Tempdb issue Expand / Collapse
Author
Message
Posted Thursday, May 20, 2010 6:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 15,660, Visits: 28,046
samsql (5/20/2010)
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.


Yes, that procedure that is calling all the other procedures will hold those temp tables in place until it completes. It doesn't matter that those procedures have transactions of their own, the wrapper proc acts as the main transaction and the temp tables will be held until it commits or rolls back.


----------------------------------------------------
"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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #925046
Posted Saturday, May 22, 2010 8:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539
in SQL Server 2005+, they no longer drop all temp tables from the tempDB. A small cache of 16 temp tables are saved in a special area of cache. This was done to prevent the overhead of constantly allocating new tables for systems that create a lot of temp tables. If an unused table exists in cache, it will use one of them, if not, it creates a new one.

This is why you will see temp tables hang around. You can read more about that on Paul Randal's blog: Misconceptions around TF-1118

If you are experiencing tempDB contention, then you likely do not have your tempDB configured per best practices. Your data files should all be the same size, and you should have multiple data file, somewhere from 1/4 to 1 data file per logical processor. I would suggest starting with 1/4 to 1/2 data file per CPU and increase it if contention persists. You should also separate your tempDB files to a dedicated drive separate from the other databases' files.

When you are experiencing heavy blocking in the tempDB, you can use the script on my blog for determining if the contention is on allocation pages (GAM, SGAM, PFS) or data pages: Breaking Down TempDB Contention




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #926474
Posted Monday, May 31, 2010 3:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 11, 2011 8:31 PM
Points: 297, Visits: 277
Samsql,Grant i had a discussion with MS Team this is fixed in SQL 11 version but not sure about 2008 R2..
They are making an hotfix for it for sqlserver 2005.will let u know once it is launches.
Post #930300
Posted Tuesday, June 8, 2010 3:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 11, 2011 8:31 PM
Points: 297, Visits: 277
Hi sam below is the KB for ur issue
KB Article Number(s): 2133566
Post #933864
Posted Monday, June 6, 2011 1:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:33 AM
Points: 1,283, Visits: 2,959
saby (6/8/2010)
Hi sam below is the KB for ur issue
KB Article Number(s): 2133566
'

Hey , can you please post the actual link for the hotfix?
Post #1120045
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse