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

Dividing Tempdb datafiles into multiple files Expand / Collapse
Author
Message
Posted Sunday, August 30, 2009 3:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:21 AM
Points: 1,602, Visits: 1,572
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//
Post #779641
Posted Sunday, August 30, 2009 3:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #779645
Posted Sunday, August 30, 2009 3:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 40,194, Visits: 36,599
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 2008, MVP
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

Post #779646
Posted Sunday, August 30, 2009 5:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:21 AM
Points: 1,602, Visits: 1,572
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//
Post #779655
Posted Monday, August 31, 2009 1:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 40,194, Visits: 36,599
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 2008, MVP
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

Post #779729
Posted Monday, August 31, 2009 11:37 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 13, 2014 5:46 PM
Points: 673, Visits: 2,087


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
Post #780092
Posted Monday, August 31, 2009 1:34 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 40,194, Visits: 36,599
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 2008, MVP
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

Post #780195
Posted Monday, August 31, 2009 1:52 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 4,400, Visits: 6,261
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 at GMail
Post #780216
Posted Monday, August 31, 2009 5:40 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #780356
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse