Dividing Tempdb datafiles into multiple files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Dividing Tempdb datafiles into multiple files...

Dividing Tempdb datafiles into multiple files

Rate Topic

Display Mode

Topic Options

Author

Message

Ryan007

Posted Sunday, August 30, 2009 3:07 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:43 AM
Points: 1,564, Visits: 1,488

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

Paul White

Posted Sunday, August 30, 2009 3:24 PM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

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

GilaMonster

Posted Sunday, August 30, 2009 3:27 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

Ryan007

Posted Sunday, August 30, 2009 5:10 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:43 AM
Points: 1,564, Visits: 1,488

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

GilaMonster

Posted Monday, August 31, 2009 1:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

gmamata7

Posted Monday, August 31, 2009 11:37 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 01, 2012 10:26 AM
Points: 613, Visits: 1,819

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

GilaMonster

Posted Monday, August 31, 2009 1:34 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

TheSQLGuru

Posted Monday, August 31, 2009 1:52 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 3,572, Visits: 5,106

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

Paul White

Posted Monday, August 31, 2009 5:40 PM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

TheSQLGuru (8/31/2009)

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 Smile

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 »

Permissions