August 30, 2009 at 3:07 pm
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//
August 30, 2009 at 3:24 pm
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
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 30, 2009 at 3:27 pm
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
August 30, 2009 at 5:10 pm
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//
August 31, 2009 at 1:50 am
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
August 31, 2009 at 11:37 am
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
August 31, 2009 at 1:34 pm
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
August 31, 2009 at 1:52 pm
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
August 31, 2009 at 5:40 pm
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:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply