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


Dividing Tempdb datafiles into multiple files


Dividing Tempdb datafiles into multiple files

Author
Message
Ryan007
Ryan007
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1854 Visits: 1595
Hi,
I got one requirement to divide my tempdb data files into 8 files in sql server 2008. This is to improve the performance. So can I add secondary ndf files along with the primary datafile. I have some doubt regarding this. Can anyone answer the below queries.
1. Is it that all the 8 files will be used simultaneously?
2. Should I put autogrowth to all the 8 files i.e. one should grow until the disk becomes full?
3. How can I achieve performance gain if they are put in the same drive/different drive if one file is used once?
4. can anyone please tell me in detail how to divide/create multiple files for tempdb with filegrowth other details?

I will be thakful if anyone please answer my questions. Thanks in advance.

Regards,
Suman

Ryan
//All our dreams can come true, if we have the courage to pursue them//
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15550 Visits: 11355
The following articles may be of interest to you:

http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/tempdb-basics.aspx
http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx
http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86568 Visits: 45245
Sumanta Roy (8/30/2009)
I got one requirement to divide my tempdb data files into 8 files in sql server 2008. This is to improve the performance.


What's the reasoning behind the split?

1. Is it that all the 8 files will be used simultaneously?


Kinda. It's to reduce contention on the allocation pages. If you have 8 processors, each one will use one TempDB file instead of all using the same file

It used to be necessary on SQL 2000 with heavily used TempDB, it's much less likely to be needed on SQL 2005 and above, due to a lot of changes around how temp tables are created.

Are you seeing contention on the allocation pages in TempDB? If not, it's not necessarily going to give you a performance gain

2. Should I put autogrowth to all the 8 files i.e. one should grow until the disk becomes full?


No. To do this properly, all files must be the same size, always. That can only be guaranteed if autogrow is off. Of course, that allows the chance for problems if TempDB gets full.

4. can anyone please tell me in detail how to divide/create multiple files for tempdb with filegrowth other details?


Look up ALTER DATABASE in SQL's Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Ryan007
Ryan007
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1854 Visits: 1595
Thank you for the information.
As you said that if I have 8 processor and I have 8 tempdb datafiles, then each processor will use one file. I just want to know, do I need to do anything to assign one file for one processor or SQL server will automatically assign one processor for one file.
My second question is how can I assign the file size if autogrowth is off for all the files. How to measure the size?

Ryan
//All our dreams can come true, if we have the courage to pursue them//
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86568 Visits: 45245
Sumanta Roy (8/30/2009)
As you said that if I have 8 processor and I have 8 tempdb datafiles, then each processor will use one file. I just want to know, do I need to do anything to assign one file for one processor or SQL server will automatically assign one processor for one file.


You don't have to do anything.
Bear in mind, as I said, unless you're seeing contention on the allocation pages, it's unlikely that splitting TempDB will get you much, if any, performance gain

My second question is how can I assign the file size if autogrowth is off for all the files. How to measure the size?


You need to know how big your TempDB needs to be. I suggest monitoring for a few weeks, see how large the file can get under regular usage, then make your decision from there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


gary1
gary1
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1646 Visits: 2249
Bear in mind, as I said, unless you're seeing contention on the allocation pages, it's unlikely that splitting TempDB will get you much, if any, performance gain


how we will know, if there is a contention on the allocation pages?

We have the Tempdb on a separate drive with 1 data & 1 log file. I have assigned 20 GB for this drive. But tempdb data file & log file never exceeded more than 2GB so far. So before creating multiple data files, I want to know where & how can we see contention on the allocation pages?

thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86568 Visits: 45245
gmamata7 (8/31/2009)
how we will know, if there is a contention on the allocation pages?


You'll see it as latch waits, typically on either resource 2:1:2 or 2:1:3, more commonly the latter. You can check sys.dm_exec_sessions, but it's something that you'll need to monitor over a period of time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12291 Visits: 8544
NOTE: if your IO subsystem sucks (as most I have encountered do), using 8 files for tempdb can actually result in LESS IO throughput than 1 or 2 or perhaps 4 files will due to excessive seek/access times for few-spindle systems.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15550 Visits: 11355
TheSQLGuru (8/31/2009)
NOTE: if your IO subsystem sucks (as most I have encountered do), using 8 files for tempdb can actually result in LESS IO throughput than 1 or 2 or perhaps 4 files will due to excessive seek/access times for few-spindle systems.

Agreed. Personally, I would resist ever using more than four files - under all but the most extreme of circumstances.

From the information provided, it doesn't seem likely that splitting the tempdb files will benefit anything anyway - I'd be very tempted not to bother. It seems at least possible that gmamata7 has seen this practice somewhere promoted as a generally good thing, and is simply trying to follow best practice. Laudable, but it might be a solution looking for a problem which he doesn't have.

Any system that would benefit from this change (or trace flag 1118) is likely to have many hundreds of concurrent users and very heavy temp table creation rates. This should show up as latch waits on PFS, GAM, or SGAM pages (pages 1, 2, and 3 respectively in each file).

The query I usually use to look for allocation contention is:


select session_id, wait_duration_ms, resource_description
from sys.dm_os_waiting_tasks
where wait_type like 'PAGE%LATCH_%'
and resource_description like '2:%';



Without that evidence, I would probably spend my time on something else :-)

References:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/07/08/under-the-covers-gam-sgam-and-pfs-pages.aspx
http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/11/tempdb-monitoring-and-troubleshooting-allocation-bottleneck.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/10/reduce-the-contention-on-tempdb-with-trace-flag-1118-take-2.aspx



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
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