/*--------------------------------------------------------------------------
 
Teil3:
TempDB in Größe anpassen und bis zu 8 zusätzliche Files.
 
---------------------------------------------------------------------------*/
 
-- Groeße der vorhandenen TempDB Files ändern
 
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 256MB , FILEGROWTH = 256MB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 128MB , FILEGROWTH = 128MB )
GO
 
-- Variables
 
DECLARE @BITS BIGINT -- Affinty Mask
,@NUMPROCS SMALLINT -- Number of cores addressed by instance
,@tempdb_files_count INT -- Number of exisiting datafiles
,@tempdbdev_location Nvarchar(4000) -- Location of TEMPDB primary datafile
,@X INT -- Counter
,@SQL Nvarchar(MAX)
,@new_tempdbdev_size_MB INT -- Size of the new files,in Megabytes
,@new_tempdbdev_Growth_MB INT -- New files growth rate,in Megabytes
,@new_files_Location Nvarchar(4000) -- New files path
 
-- Initialize variables
 
SELECT @X = 1, @BITS = 1
SELECT
@new_tempdbdev_size_MB = 256 -- Four Gbytes , it's easy to increase that after file creation but harder to shrink.
,@new_tempdbdev_Growth_MB = 256 -- 512 Mbytes , can be easily shrunk
,@new_files_Location = NULL -- NULL means create in same location as primary file.
 
IF OBJECT_ID('tempdb..#SVer') IS NOT NULL
BEGIN
DROP TABLE #SVer
END
CREATE TABLE #SVer(ID INT, Name sysname, Internal_Value INT, VALUE NVARCHAR(512))
INSERT #SVer EXEC master.dbo.xp_msver processorCount
 
-- Get total number of Cores detected by the Operating system
 
SELECT @NUMPROCS= Internal_Value FROM #SVer
 --Print '-- TOTAL numbers of CPU cores on server :' + cast(@NUMPROCS as varchar(5))
SET @NUMPROCS = 0
 
-- Get number of Cores addressed by instance.
 
WHILE @X <= (SELECT Internal_Value FROM #SVer ) AND @X <=32
BEGIN
 SELECT @NUMPROCS =
 CASE WHEN CAST (VALUE AS INT) & @BITS > 0 THEN @NUMPROCS + 1 ELSE @NUMPROCS END
 FROM sys.configurations
 WHERE NAME = 'AFFINITY MASK'
 SET @BITS = (@BITS * 2)
 SET @X = @X + 1
END
 
IF (SELECT Internal_Value FROM #SVer) > 32
 BEGIN
 WHILE @X <= (SELECT Internal_Value FROM #SVer )
 BEGIN
 SELECT @NUMPROCS =
 CASE WHEN CAST (VALUE AS INT) & @BITS > 0 THEN @NUMPROCS + 1 ELSE @NUMPROCS END
 FROM sys.configurations
 WHERE NAME = 'AFFINITY64 MASK'
 SET @BITS = (@BITS * 2)
 SET @X = @X + 1
 END
END
 
IF @NUMPROCS = 0 SELECT @NUMPROCS= Internal_Value FROM #SVer
 
--Print '-- Number of CPU cores Configured for usage by instance :' + cast(@NUMPROCS as varchar(5))
 
-------------------------------------------------------------------------------------
-- Here you define how many files should exist per core ; Feel free to change
-------------------------------------------------------------------------------------
 
-- IF cores < 1 then no change , if between 1 & 8 inclusive then number tempdb equal to cores number
IF @NUMPROCS >1 AND @NUMPROCS <=8
SELECT @NUMPROCS = @NUMPROCS
 
-- IF cores > 8 then files should be 8
IF @NUMPROCS >8
SELECT @NUMPROCS = 8
 
-- Get number of exisiting TEMPDB datafiles and the location of the primary datafile.
 
SELECT @tempdb_files_count=COUNT(*) ,@tempdbdev_location=(SELECT REVERSE(SUBSTRING(REVERSE(physical_name), CHARINDEX('\',REVERSE(physical_name)) , LEN(physical_name) )) FROM tempdb.sys.database_files WHERE name = 'tempdev')
FROM tempdb.sys.database_files
WHERE type_desc= 'ROWS' AND state_desc= 'Online'
 
 --Print '-- Current Number of Tempdb datafiles :' + cast(@tempdb_files_count as varchar(5))
 
-- Determine if we already have enough datafiles
IF @tempdb_files_count >= @NUMPROCS
BEGIN
 --Print '--****Number of Recommedned datafiles is already there****'
RETURN
END
 
SET @new_files_Location= Isnull(@new_files_Location,@tempdbdev_location)
 
-- Determine if the new location exists or not
DECLARE @file_results TABLE(file_exists INT,file_is_a_directory INT,parent_directory_exists INT)
 
INSERT INTO @file_results(file_exists, file_is_a_directory, parent_directory_exists)
EXEC master.dbo.xp_fileexist @new_files_Location
 
IF (SELECT file_is_a_directory FROM @file_results ) = 0
BEGIN
 --print '-- New files Directory Does NOT exist , please specify a correct folder!'
RETURN
END
 
-- Determine if we have enough free space on the destination drive
 
DECLARE @FreeSpace TABLE (Drive CHAR(1),MB_Free BIGINT)
INSERT INTO @FreeSpace EXEC master..xp_fixeddrives
 
IF (SELECT MB_Free FROM @FreeSpace WHERE drive = LEFT(@new_files_Location,1) ) < @NUMPROCS * @new_tempdbdev_size_MB
BEGIN
 print '-- WARNING: Not enough free space on ' + UPPER(LEFT(@new_files_Location,1)) + ':\ to accomodate the new files. Around '+ CAST(@NUMPROCS * @new_tempdbdev_size_MB AS VARCHAR(10))+ ' Mbytes are needed; Please add more space or choose a new location!'
END
 
-- Determine if any of the exisiting datafiles have different size than proposed ones.
IF EXISTS
(
 SELECT (CONVERT (BIGINT, SIZE) * 8)/1024 FROM tempdb.sys.database_files
 WHERE type_desc= 'Rows'
 AND (CONVERT (BIGINT, SIZE) * 8)/1024 <> @new_tempdbdev_size_MB
)
 
PRINT
'
/*
WARNING: Some Existing datafile(s) do NOT have the same size as new ones.
It''s recommended if ALL datafiles have same size for optimal proportional-fill performance.Use ALTER DATABASE and DBCC SHRINKFILE to resize files
 
Optimizing tempdb Performance : http://msdn.microsoft.com/en-us/library/ms175527.aspx
'
 
Print '****Proposed New Tempdb Datafiles, PLEASE REVIEW CODE BEFORE RUNNIG *****/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
'
 
 
-- Generate the statements
WHILE @tempdb_files_count < @NUMPROCS
 
BEGIN
 --EXEC
 SELECT @SQL = 'ALTER DATABASE [tempdb] ADD FILE (NAME = N''tempdev'+CAST (@tempdb_files_count +1 AS VARCHAR (5))+''',FILENAME = N'''+ @new_files_Location + 'tempdb'+CAST (@tempdb_files_count +1 AS VARCHAR(5)) +'.ndf'',SIZE = '+CAST(@new_tempdbdev_size_MB AS VARCHAR(15)) +'MB,FILEGROWTH = '+CAST(@new_tempdbdev_Growth_MB AS VARCHAR(15)) +'MB )
'
    --print @sql
    EXECUTE (@SQL)
 SET @tempdb_files_count = @tempdb_files_count + 1
END