TEMPDB Configuration per vCPU

--
-- http://support.microsoft.com/kb/2154845/en-us
--

USE tempdb
GO

SELECT * FROM sys.database_files
GO
--
-- Letter drive named paths below should be carefully checked, proceed with caution as this is a not easy to revert operation
--
ALTER DATABASE TEMPDB MODIFY FILE (NAME='tempdev', FILENAME='E:\MSSQL\DATA\tempdev.mdf',SIZE=640MB, FILEGROWTH=64MB)
GO

ALTER DATABASE TEMPDB MODIFY FILE (NAME='templog', FILENAME='L:\MSSQL\TLOG\templog.ldf',SIZE=640MB, FILEGROWTH=64MB)
GO


SET NOCOUNT ON

IF ( (SELECT count(name) FROM sys.database_files) > 2 ) -- that is, besides "tempdev" and "templog" default logical files...
BEGIN
SELECT('!!! Error, suplemental tempdev files already exist at the file-system, please check before proceeding!!!') AS ERROR
PRINT CHAR(13) + '!!! Error, suplemental tempdev files already exist at the file-system, please check before proceeding!!!'
END
ELSE
BEGIN
DECLARE @nCPUs_wAffinity tinyint = ( SELECT COUNT(*) from sys.dm_os_schedulers WHERE STATUS = 'VISIBLE ONLINE' )

IF ( @nCPUs_wAffinity > 1 )
ALTER DATABASE TEMPDB ADD FILE (NAME=tempdev2, FILENAME='E:\MSSQL\DATA\tempdev2.ndf',SIZE=640MB, FILEGROWTH=64MB)

IF ( @nCPUs_wAffinity > 2 )
ALTER DATABASE TEMPDB ADD FILE (NAME=tempdev3, FILENAME='E:\MSSQL\DATA\tempdev3.ndf',SIZE=640MB, FILEGROWTH=64MB)

IF ( @nCPUs_wAffinity > 3 )
ALTER DATABASE TEMPDB ADD FILE (NAME=tempdev4, FILENAME='E:\MSSQL\DATA\tempdev4.ndf',SIZE=640MB, FILEGROWTH=64MB)

IF ( @nCPUs_wAffinity > 4 )
ALTER DATABASE TEMPDB ADD FILE (NAME=tempdev5, FILENAME='E:\MSSQL\DATA\tempdev5.ndf',SIZE=640MB, FILEGROWTH=64MB)

IF ( @nCPUs_wAffinity > 5 )
ALTER DATABASE TEMPDB ADD FILE (NAME=tempdev6, FILENAME='E:\MSSQL\DATA\tempdev6.ndf',SIZE=640MB, FILEGROWTH=64MB)

IF ( @nCPUs_wAffinity > 6 )
ALTER DATABASE TEMPDB ADD FILE (NAME=tempdev7, FILENAME='E:\MSSQL\DATA\tempdev7.ndf',SIZE=640MB, FILEGROWTH=64MB)

IF ( @nCPUs_wAffinity > 7 )
ALTER DATABASE TEMPDB ADD FILE (NAME=tempdev8, FILENAME='E:\MSSQL\DATA\tempdev8.ndf',SIZE=640MB, FILEGROWTH=64MB)

SET NOCOUNT OFF

SELECT * FROM sys.database_files
END

/* *****************************************************************************************************************************

Expected output results (similar):

BEFORE:
-------

file_idfile_guidtypetype_descdata_space_idnamephysical_namestatestate_descsizemax_sizegrowthis_media_read_onlyis_read_onlyis_sparseis_percent_growthis_name_reservedcreate_lsndrop_lsnread_only_lsnread_write_lsndifferential_base_lsndifferential_base_guiddifferential_base_timeredo_start_lsnredo_start_fork_guidredo_target_lsnredo_target_fork_guidbackup_lsn
1NULL0ROWS1tempdevD:\SQLServer\Data\tempdev.mdf0ONLINE65536-1819200000NULLNULLNULLNULL32000000030700037BAF06B70-CABB-4637-BF1F-F9128BD08A362013-11-11 15:33:48.753NULLNULLNULLNULLNULL
2NULL1LOG0templogD:\SQLServer\Tlog\templog.ldf0ONLINE65536-1819200000NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

AFTER (for 8 CPU threads):
------

file_idfile_guidtypetype_descdata_space_idnamephysical_namestatestate_descsizemax_sizegrowthis_media_read_onlyis_read_onlyis_sparseis_percent_growthis_name_reservedcreate_lsndrop_lsnread_only_lsnread_write_lsndifferential_base_lsndifferential_base_guiddifferential_base_timeredo_start_lsnredo_start_fork_guidredo_target_lsnredo_target_fork_guidbackup_lsn
1NULL0ROWS1tempdevD:\SQLServer\Data\tempdev.mdf0ONLINE65536-1819200000NULLNULLNULLNULL32000000030700037BAF06B70-CABB-4637-BF1F-F9128BD08A362013-11-11 15:33:48.753NULLNULLNULLNULLNULL
2NULL1LOG0templogD:\SQLServer\Tlog\templog.ldf0ONLINE65536-1819200000NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
3F9EC30AA-3DDB-433A-91CC-0DAAB0482A5D0ROWS1tempdev2D:\SQLServer\Data\tempdev2.ndf0ONLINE640-181920000033000000946200233NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
4A21CA032-AE18-4FC2-B524-2E9F68211F1D0ROWS1tempdev3D:\SQLServer\Data\tempdev3.ndf0ONLINE640-181920000033000000952900006NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
5B22E497C-E50D-4E8A-A206-8FCFC70F929C0ROWS1tempdev4D:\SQLServer\Data\tempdev4.ndf0ONLINE640-181920000033000000953600006NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
632C574E6-4132-418F-928A-25BCE77811CD0ROWS1tempdev5D:\SQLServer\Data\tempdev5.ndf0ONLINE640-181920000033000000954300006NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
76D747C0A-2A20-4CBF-B7FC-C876B050020C0ROWS1tempdev6D:\SQLServer\Data\tempdev6.ndf0ONLINE640-181920000033000000955000006NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
805A464A4-C4FC-4FFA-9086-65F81111841B0ROWS1tempdev7D:\SQLServer\Data\tempdev7.ndf0ONLINE640-181920000033000000955700006NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
9FC933C20-4866-4768-9F2D-76446D3ED5900ROWS1tempdev8D:\SQLServer\Data\tempdev8.ndf0ONLINE640-181920000033000000956400006NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL

Expected output message (similar):

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

(9 row(s) affected)


NOTE:

If secondary data files for tempdb already exist, then following message is displayed:

!!! Error, suplemental tempdev files already exist at the file-system, please check before proceeding!!!
**************************************************************************************************************************************** */