[SQL Server] Configuer TempDB par CPU
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_id file_guid type type_desc data_space_id name physical_name state state_desc size max_size growth is_media_read_only is_read_only is_sparse is_percent_growth is_name_reserved create_lsn drop_lsn read_only_lsn read_write_lsn differential_base_lsn differential_base_guid differential_base_time redo_start_lsn redo_start_fork_guid redo_target_lsn redo_target_fork_guid backup_lsn
1 NULL 0 ROWS 1 tempdev D:\SQLServer\Data\tempdev.mdf 0 ONLINE 65536 -1 8192 0 0 0 0 0 NULL NULL NULL NULL 32000000030700037 BAF06B70-CABB-4637-BF1F-F9128BD08A36 2013-11-11 15:33:48.753 NULL NULL NULL NULL NULL
2 NULL 1 LOG 0 templog D:\SQLServer\Tlog\templog.ldf 0 ONLINE 65536 -1 8192 0 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
AFTER (for 8 CPU threads):
------
file_id file_guid type type_desc data_space_id name physical_name state state_desc size max_size growth is_media_read_only is_read_only is_sparse is_percent_growth is_name_reserved create_lsn drop_lsn read_only_lsn read_write_lsn differential_base_lsn differential_base_guid differential_base_time redo_start_lsn redo_start_fork_guid redo_target_lsn redo_target_fork_guid backup_lsn
1 NULL 0 ROWS 1 tempdev D:\SQLServer\Data\tempdev.mdf 0 ONLINE 65536 -1 8192 0 0 0 0 0 NULL NULL NULL NULL 32000000030700037 BAF06B70-CABB-4637-BF1F-F9128BD08A36 2013-11-11 15:33:48.753 NULL NULL NULL NULL NULL
2 NULL 1 LOG 0 templog D:\SQLServer\Tlog\templog.ldf 0 ONLINE 65536 -1 8192 0 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 F9EC30AA-3DDB-433A-91CC-0DAAB0482A5D 0 ROWS 1 tempdev2 D:\SQLServer\Data\tempdev2.ndf 0 ONLINE 640 -1 8192 0 0 0 0 0 33000000946200233 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
4 A21CA032-AE18-4FC2-B524-2E9F68211F1D 0 ROWS 1 tempdev3 D:\SQLServer\Data\tempdev3.ndf 0 ONLINE 640 -1 8192 0 0 0 0 0 33000000952900006 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5 B22E497C-E50D-4E8A-A206-8FCFC70F929C 0 ROWS 1 tempdev4 D:\SQLServer\Data\tempdev4.ndf 0 ONLINE 640 -1 8192 0 0 0 0 0 33000000953600006 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
6 32C574E6-4132-418F-928A-25BCE77811CD 0 ROWS 1 tempdev5 D:\SQLServer\Data\tempdev5.ndf 0 ONLINE 640 -1 8192 0 0 0 0 0 33000000954300006 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
7 6D747C0A-2A20-4CBF-B7FC-C876B050020C 0 ROWS 1 tempdev6 D:\SQLServer\Data\tempdev6.ndf 0 ONLINE 640 -1 8192 0 0 0 0 0 33000000955000006 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
8 05A464A4-C4FC-4FFA-9086-65F81111841B 0 ROWS 1 tempdev7 D:\SQLServer\Data\tempdev7.ndf 0 ONLINE 640 -1 8192 0 0 0 0 0 33000000955700006 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
9 FC933C20-4866-4768-9F2D-76446D3ED590 0 ROWS 1 tempdev8 D:\SQLServer\Data\tempdev8.ndf 0 ONLINE 640 -1 8192 0 0 0 0 0 33000000956400006 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
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!!!
**************************************************************************************************************************************** */