November 27, 2008 at 10:29 pm
No, you can't add filegroups to tempdb.
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
December 1, 2008 at 3:04 am
This topic was discussed a few times at PASS, and the people who author SQL Server and the most experienced people in the user community are clearly saying you can help protect SQL Server performance by adding one file to tempdb for every processor core, or maybe every 1.5 processor cores, up to a maximum of 8 files.
If you have a server with a large number of processor cores, a situation we will all get over the next few years, having one tempdb file for every processor core can cause SQL to spend a measureable amount of time working out where to put new tempdb objects. Further, having more than about 8 tempdb files does not give any measureable performance benefit.
So, it seems that best practice is moving to 'have one tempdb file for about every 1.5 processor cores, up to a maximum of about 8 files'. All the files should be the same size and allow zero growth.
You should treat best practice as something you should normally do, unless there are circumstances at your site that render the advice invalid. Likewise, poor practice advise is something you should avoid unless circumstances mean it is useful.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 3, 2008 at 3:19 am
EdVassie (12/1/2008)
This topic was discussed a few times at PASS, and the people who author SQL Server and the most experienced people in the user community are clearly saying you can help protect SQL Server performance by adding one file to tempdb for every processor core, or maybe every 1.5 processor cores, up to a maximum of 8 files.If you have a server with a large number of processor cores, a situation we will all get over the next few years, having one tempdb file for every processor core can cause SQL to spend a measureable amount of time working out where to put new tempdb objects. Further, having more than about 8 tempdb files does not give any measureable performance benefit.
So, it seems that best practice is moving to 'have one tempdb file for about every 1.5 processor cores, up to a maximum of about 8 files'. All the files should be the same size and allow zero growth.
You should treat best practice as something you should normally do, unless there are circumstances at your site that render the advice invalid. Likewise, poor practice advise is something you should avoid unless circumstances mean it is useful.
Thank you for the info.
Is the figure of 8 related to this max degree of parallisme guideline (max =8) http://support.microsoft.com/kb/329204/ ?
Print 'Double check drives !';
/*
help protect SQL Server performance by adding one file to tempdb for every processor core,
or maybe every 1.5 processor cores,
up to a maximum of 8 files.
http://support.microsoft.com/kb/187824
http://support.microsoft.com/kb/224071
http://www.tech-recipes.com/rx/2342/sql_server_2005_move_tempdb/
- "Concurrency enhancements for the tempdb database"
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en (shorter story)
- Working with tempdb in SQL Server 2005", paragraph "tempdb size, including the right number of files and file size",
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
- related to max_degree_of_parallelism ? http://support.microsoft.com/kb/329204/ (only if needed)
*/
USE [master]
GO
Declare @HotrunYN char(1)
Set @HotrunYN = 'N' -- If @HotrunYN = 'Y' then exec else print end !
Declare @DefaultFileSizeMB int, @DefaultFileGrowthMB int
Select @DefaultFileSizeMB = 50
, @DefaultFileGrowthMB = 50
Declare @NumberOfCPUCores int -- Number of CPU-cores available for SQLServer
create table #CPUCores(ID int, Name sysname, Internal_Value int, Value nvarchar(512))
set nocount on
insert #CPUCores
exec master.dbo.xp_msver
SELECT @NumberOfCPUCores = isnull(( select Internal_Value
from #CPUCores
where Name = N'ProcessorCount'
),1)
drop table #CPUCores
Begin
If @NumberOfCPUCores < 1
begin
/* send errormessage and stop connection !!*/
Raiserror ('DBA message: Invalid Number of CPU-cores : [%d]', 20, 1, @NumberOfCPUCores) with log
end
/*
http://qa.sqlservercentral.com/Forums/Topic607318-146-1.aspx
This topic was discussed a few times at SQLPASS2008, and the people who author SQL Server and the most
experienced people in the user community are clearly saying you can help protect SQL Server
performance by adding one file to tempdb for every processor core, or maybe every 1.5 processor cores,
up to a maximum of 8 files.
If you have a server with a large number of processor cores, a situation we will all get over
the next few years, having one tempdb file for every processor core can cause SQL to spend a
measureable amount of time working out where to put new tempdb objects.
Further, having more than about 8 tempdb files does not give any measureable performance benefit.
So, it seems that best practice is moving to 'have one tempdb file for about every 1.5 processor cores,
up to a maximum of about 8 files'. All the files should be the same size and allow zero growth.
You should treat best practice as something you should normally do, unless there are circumstances at
your site that render the advice invalid.
Likewise, poor practice advise is something you should avoid unless circumstances mean it is useful.
*/
print '/* Number of CPUCores available for SQLServer: ' + cast(@NumberOfCPUCores as varchar(3)) + ' */'
If @NumberOfCPUCores > 8
begin
set @NumberOfCPUCores = 8
end
Declare @tempdbpath varchar(1000)
Declare @filesizeKB int
Select @tempdbpath = [filename]
from tempdb.sys.sysfiles
where name = 'tempdev'
Select @tempdbpath = reverse(substring(reverse(@tempdbpath),charindex('\',reverse(@tempdbpath)),datalength(@tempdbpath)))
print '/* tempdb path: ' + @tempdbpath + ' */'
Declare @SQLstmt varchar(max)
Declare @ctr int
Select @SQLstmt =''
, @ctr = 2
While @ctr <= @NumberOfCPUCores
begin
Select @SQLstmt = @SQLstmt
-- all files same Size !!
+ case @SQLstmt when '' then 'ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N''tempdev'', SIZE = '+ cast(@DefaultFileSizeMB as varchar(15)) + 'MB, FILEGROWTH = ' + cast(@DefaultFileGrowthMB as varchar(15)) + 'MB ); '
else ''
end
+ 'ALTER DATABASE [tempdb] ADD FILE ( NAME = N''tempdev' + cast( @ctr as char(1)) + ''', FILENAME = N''' + @tempdbpath + 'tempdev' + cast( @ctr as char(1)) + '.ndf'', SIZE = '+ cast(@DefaultFileSizeMB as varchar(15)) + 'MB, FILEGROWTH = ' + cast(@DefaultFileGrowthMB as varchar(15)) + 'MB );'
Set @ctr = @ctr + 1
end
If @HotrunYN = 'Y'
begin
exec (@SQLstmt)
end
else
begin
print '/* ' + char(10) + ' NOT EXECUTED ' + char(10) + ' */'
print @SQLstmt
end
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 3, 2008 at 3:52 am
The figure of 8 tempdb files was not linked to and given value of MAXDOP. It was presented as the results of testing on large servers (32 and 64 processor cores).
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply