InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 22:34:06 GMT20http://www.sqlservercentral.com/Forums/Topic779641-146-1.aspx[quote][b]TheSQLGuru (8/31/2009)[/b][hr]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.[/quote]Agreed. Personally, I would resist [i]ever[/i] 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 [i]gmamata7[/i] 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:[code="sql"]select session_id, wait_duration_ms, resource_descriptionfrom sys.dm_os_waiting_taskswhere wait_type like 'PAGE%LATCH_%'and resource_description like '2:%';[/code]Without that evidence, I would probably spend my time on something else :-)References:[url]http://blogs.msdn.com/sqlserverstorageengine/archive/2006/07/08/under-the-covers-gam-sgam-and-pfs-pages.aspx[/url][url]http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/11/tempdb-monitoring-and-troubleshooting-allocation-bottleneck.aspx[/url][url]http://sqlblog.com/blogs/linchi_shea/archive/2007/08/10/reduce-the-contention-on-tempdb-with-trace-flag-1118-take-2.aspx[/url]Mon, 31 Aug 2009 17:40:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic779641-146-1.aspxNOTE: 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.Mon, 31 Aug 2009 13:52:59 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic779641-146-1.aspx[quote][b]gmamata7 (8/31/2009)[/b][hr]how we will know, if there is a contention on the allocation pages? [/quote]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.Mon, 31 Aug 2009 13:34:40 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic779641-146-1.aspx[quote]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[/quote]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?thanksMon, 31 Aug 2009 11:37:35 GMTgmamata7http://www.sqlservercentral.com/Forums/Topic779641-146-1.aspx[quote][b]Sumanta Roy (8/30/2009)[/b][hr]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.[/quote]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[quote]My second question is how can I assign the file size if autogrowth is off for all the files. How to measure the size?[/quote]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.Mon, 31 Aug 2009 01:50:58 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic779641-146-1.aspxThank 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?Sun, 30 Aug 2009 17:10:11 GMTRyan007http://www.sqlservercentral.com/Forums/Topic779641-146-1.aspx[quote][b]Sumanta Roy (8/30/2009)[/b][hr]I got one requirement to divide my tempdb data files into 8 files in sql server 2008. This is to improve the performance. [/quote]What's the reasoning behind the split?[quote]1. Is it that all the 8 files will be used simultaneously?[/quote]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 fileIt 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[quote]2. Should I put autogrowth to all the 8 files i.e. one should grow until the disk becomes full?[/quote]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.[quote]4. can anyone please tell me in detail how to divide/create multiple files for tempdb with filegrowth other details?[/quote]Look up ALTER DATABASE in SQL's Books Online.Sun, 30 Aug 2009 15:27:48 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic779641-146-1.aspxThe following articles may be of interest to you:[url]http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/tempdb-basics.aspx[/url][url]http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx[/url][url]http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx[/url]PaulSun, 30 Aug 2009 15:24:41 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic779641-146-1.aspxHi,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,SumanSun, 30 Aug 2009 15:07:43 GMTRyan007