When you create multiple data files they will all be in the primary file group and SQL Server uses a proportional fill algorithm to determine which file to use for each request to create an object. If all the files are exactly the same size, then SQL Server uses the files in a “round robin” fashion, spreading the load equally across the files. This is, of course, exactly what you want.
Microsoft recommends up to a 1:1 mapping between the number of files and logical CPU's because during testing of massive workloads they’ve seen performance benefits, even with hundreds of data files.
select DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, CONVERT (DECIMAL (20,2)
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2)
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name from sys.master_files mf
where database_id =2 and type_desc= 'rows'
go
SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info
go
--------------------------------Q Drive is SSD-----------------------------------------------------------------------------------------
-----Move tempdb Files, then restart
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'Q:\MSSQL\Tempdb\tempdb.mdf',SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'Q:\MSSQL\Tempdb\templog.ldf',SIZE =500MB , FILEGROWTH = 500MB)
GO
---ADD 15 More Tempdb files
ALTER DATABASE tempdb ADD FILE
( NAME = N'tempdev2',FILENAME = N'Q:\MSSQL\Tempdb\tempdev2.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb ADD FILE
( NAME = N'tempdev3',FILENAME = N'Q:\MSSQL\Tempdb\tempdev3.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb ADD FILE
( NAME = N'tempdev4',FILENAME = N'Q:\MSSQL\Tempdb\tempdev4.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev5',FILENAME = N'Q:\MSSQL\Tempdb\tempdev5.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev6',FILENAME = N'Q:\MSSQL\Tempdb\tempdev6.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb ADD FILE
( NAME = N'tempdev7',FILENAME = N'Q:\MSSQL\Tempdb\tempdev7.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev8',FILENAME = N'Q:\MSSQL\Tempdb\tempdev8.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev9',FILENAME = N'Q:\MSSQL\Tempdb\tempdev9.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev10',
FILENAME = N'Q:\MSSQL\Tempdb\tempdev10.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev11',FILENAME = N'Q:\MSSQL\Tempdb\tempdev11.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev12',
FILENAME = N'Q:\MSSQL\Tempdb\tempdev12.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev13',FILENAME = N'Q:\MSSQL\Tempdb\tempdev13.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev14',FILENAME = N'Q:\MSSQL\Tempdb\tempdev14.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev15',FILENAME = N'Q:\MSSQL\Tempdb\tempdev15.ndf' , SIZE =500MB , FILEGROWTH = 500MB)
GO
No comments:
Post a Comment