none
tempdb configuration with multiple datafiles?

    Question

  • We have a robust E7 server with 200GB RAM and 60 core CPU shared among 5 instances.

    I am planing to change my tempdb configuration in one of the instance. We have 200GB disk for temp drives dedicated to each instance......and as per SoftNUMA this instance has almost 20 CPU's assigned. So I am planning to add 20 data files of same size which is clear to take benefit of cpu parallel processing.

    However my question is do you recommended creating 10GB files and fill the disk or create 8GB files and keep 20% disk free incase of future growth. Please help me selecting the optimal configuration.

    Thanks a lot in advance.

    Friday, May 25, 2012 6:23 AM

Answers

  • the main purpose of using the multiple tempdb data files is to reduce the likelyhood of allocation contention. 20 data files are too many. it is not like to create one file per CPU, this concept is not correct.

    The first sentence here is correct, the second one is not.  If your workload requires a 1:1 ratio of files per core to reduce the contention then 20 data files would be absolutely correct.  I've worked on workloads that require 2:1 file per core to eliminate the tempdb pfs contention on 48 way servers in the past, this is all workload specific.

    if is normally recommended to create 1/2 CPUs of files, and Microsoft tested and suggests there is no more performance gain once the the tempdb files go beyond 8 files.

    Microsoft has never suggested what you say above.  In fact all the written and currently published documentation says exactly the opposite which is why Paul Randal has blogged about the 1/2 to 1/4 file to core ratio in the past, because 1:1 is overkill for most environments.

    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    The best information currently for tempdb configuration was presented by Bob Ward (Senior Escalation Engineer at Microsoft) at PASS 2011, which was if you have less than 8 cores do 1 file per core, if you have more than 8 cores, create 8 files and monitor.  IF you continue to have PFS contention with 8 files, add 4 more files and monitor again, increasing by 4 files until the contention is gone.

    IF you don't have tempdb contention on a PFS, GAM, or SGAM page, changing file count won't matter a single bit for the performance of the server, so it would be best to monitor for the contention first and then base your configuration on whether you have contention or not:

    SELECT 
    	session_id,
    	wait_type,
    	wait_duration_ms,
    	blocking_session_id,
    	resource_description,
    	ResourceType = Case
    	WHEN PageID = 1 OR PageID % 8088 = 0 THEN 'Is PFS Page'
    	WHEN PageID = 2 OR PageID % 511232 = 0 THEN 'Is GAM Page'
    	WHEN PageID = 3 OR (PageID - 1) % 511232 = 0 THEN 'Is SGAM Page'
    		ELSE 'Is Not PFS, GAM, or SGAM page'
    	END
    FROM (	SELECT  
    			session_id,
    			wait_type,
    			wait_duration_ms,
    			blocking_session_id,
    			resource_description,
    			CAST(RIGHT(resource_description, LEN(resource_description)
    			- CHARINDEX(':', resource_description, 3)) AS INT) AS PageID
    		FROM sys.dm_os_waiting_tasks
    		WHERE wait_type LIKE 'PAGE%LATCH_%'
    		  AND resource_description LIKE '2:%'
    ) AS tab;
    



    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Friday, May 25, 2012 12:07 PM
    Moderator

All replies

  • Hi Preeiah,

    I think "creating 10GB files and fill the disk" better than " create 8GB files and keep 20% disk free incase of future growth".
    but do you need such so big tempdb files for every sql instance?
    I would suggest you create 20 200MB tempdb data files first , after one week or on month, and then determine the size of tempdb data file.

    thanks,

    nicofer

    Friday, May 25, 2012 7:16 AM
  • Hey Preeiah,

    The otpimal solution for creating TEMPDB files totally depends up on your load. When I say Load it depends upon the number of temporary tables or objects you use,The versioning activities you plan etc..

    The recommendation is just to reduce the allocation problems in TEMPDB,so we need to take care of creating equal sized temdb data files and if you have more tempdb load ofcourse your templog will also increase so in your case creaeating with 8 GB will do as you have room to grow templog file.

    If you dont have heavy load create 10 * 10 GB files and increase the files with the observations going forward.

    Its better and advisable to turn on TF1118 when you are talking about temdb configurations.

    Thanks,


    Thanks, Aditya Badramraju, ____________________________________________________ Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties and confers no rights.

    Friday, May 25, 2012 11:03 AM
  • the main purpose of using the multiple tempdb data files is to reduce the likelyhood of allocation contention. 20 data files are too many. it is not like to create one file per CPU, this concept is not correct.

    if is normally recommended to create 1/2 CPUs of files, and Microsoft tested and suggests there is no more performance gain once the the tempdb files go beyond 8 files.

    But for your case 10 data files are propably a good start or even just 5 data files. If you decide to go with 10, then I will probably go with 18 GB for each file and this leave 10% disk free space. (As I believe that you must have a corporate policy to monitor the disk space, and 10% was a reasonable alert line.).

    You didn't mentioned your tembdp log file, I will suggest to give it a reasonable big size to prevent it from autogrow, as autogrow log file for tempdb is an expensive operation.

    Friday, May 25, 2012 11:51 AM
  • the main purpose of using the multiple tempdb data files is to reduce the likelyhood of allocation contention. 20 data files are too many. it is not like to create one file per CPU, this concept is not correct.

    The first sentence here is correct, the second one is not.  If your workload requires a 1:1 ratio of files per core to reduce the contention then 20 data files would be absolutely correct.  I've worked on workloads that require 2:1 file per core to eliminate the tempdb pfs contention on 48 way servers in the past, this is all workload specific.

    if is normally recommended to create 1/2 CPUs of files, and Microsoft tested and suggests there is no more performance gain once the the tempdb files go beyond 8 files.

    Microsoft has never suggested what you say above.  In fact all the written and currently published documentation says exactly the opposite which is why Paul Randal has blogged about the 1/2 to 1/4 file to core ratio in the past, because 1:1 is overkill for most environments.

    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    The best information currently for tempdb configuration was presented by Bob Ward (Senior Escalation Engineer at Microsoft) at PASS 2011, which was if you have less than 8 cores do 1 file per core, if you have more than 8 cores, create 8 files and monitor.  IF you continue to have PFS contention with 8 files, add 4 more files and monitor again, increasing by 4 files until the contention is gone.

    IF you don't have tempdb contention on a PFS, GAM, or SGAM page, changing file count won't matter a single bit for the performance of the server, so it would be best to monitor for the contention first and then base your configuration on whether you have contention or not:

    SELECT 
    	session_id,
    	wait_type,
    	wait_duration_ms,
    	blocking_session_id,
    	resource_description,
    	ResourceType = Case
    	WHEN PageID = 1 OR PageID % 8088 = 0 THEN 'Is PFS Page'
    	WHEN PageID = 2 OR PageID % 511232 = 0 THEN 'Is GAM Page'
    	WHEN PageID = 3 OR (PageID - 1) % 511232 = 0 THEN 'Is SGAM Page'
    		ELSE 'Is Not PFS, GAM, or SGAM page'
    	END
    FROM (	SELECT  
    			session_id,
    			wait_type,
    			wait_duration_ms,
    			blocking_session_id,
    			resource_description,
    			CAST(RIGHT(resource_description, LEN(resource_description)
    			- CHARINDEX(':', resource_description, 3)) AS INT) AS PageID
    		FROM sys.dm_os_waiting_tasks
    		WHERE wait_type LIKE 'PAGE%LATCH_%'
    		  AND resource_description LIKE '2:%'
    ) AS tab;
    



    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Friday, May 25, 2012 12:07 PM
    Moderator
  • Hi Jonathan,

    Thanks for your clarification to make things clear. I might have misunderstood the Bob's presentation and only listen to half ear.

    I always learned a lot from your blog and your comments on the forum.

    Best regards.

    Friday, May 25, 2012 12:28 PM
  • Just saw this thread, and have a question about implementing this concept. Adding files is easy enough, but can any of you who have done this comment on whether or not the SQL service needs to be restarted to remove the contention? I would thing that the proportional fill SQL Server uses would require a restart.

    Thanks --

    Ned Otter

    Tuesday, July 24, 2012 12:14 PM
  • Just saw this thread, and have a question about implementing this concept. Adding files is easy enough, but can any of you who have done this comment on whether or not the SQL service needs to be restarted to remove the contention? I would thing that the proportional fill SQL Server uses would require a restart.

    Thanks --

    Ned Otter

    It might require a restart to balance the allocations out depending on the workload.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, July 24, 2012 3:54 PM
    Moderator