Tempdb issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Tempdb issue

15 posts, Page 2 of 2

««1 2

Tempdb issue

Rate Topic

Display Mode

Topic Options

Author

Message

Grant Fritchey

Posted Thursday, May 20, 2010 6:27 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 13,378, Visits: 25,163

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

Robert Davis

Posted Saturday, May 22, 2010 8:22 PM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 19, 2013 8:09 AM
Points: 1,558, Visits: 1,396

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
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring

Post #926474

saby

Posted Monday, May 31, 2010 3:42 AM

SSC 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