Feature Selection :
We don’t want to install all the features. Identify the features needed and install only those features.
Model Database Properties :
Figure out all the Model database Properties and configure them Correctly.
File Layout :
Make sure Data and log files are stored appropriately.
Permissions:
Make sure SQL Server Service account has permissions for
Instant File Initializtion
and Lock Pages in Memory ( Probably its good to do it )
http://technet.microsoft.com/en-us/library/ms175935(v=sql.105).aspx
https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/
Instant File Initialization Article :
http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx
Power Plan :
Make it High Performance and make sure its set that way.
http://blogs.msdn.com/b/cindygross/archive/2011/03/09/power-saving-options-on-sql-server.aspx
TempDB :
Create number of files appropriate to number of Cores or CPUs Available on the Machine.
MAXDOP and Cost Threshold for Parallelism Configuration:
http://ramblingsofraju.com/technology/more-cpus-in-sql-server-does-it-help-or-hurt-maxdop/
Max Memory Configuration
Script to Create Additional TempDB Files
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
USE Master GO SET NOCOUNT ON GO PRINT '-- Instance name: '+ @@servername + ' ; /* Version: ' + @@version + ' */' -- 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 = 1024 -- 1 Gbytes , it's easy to increase that after file creation but harder to shrink. ,@new_tempdbdev_Growth_MB = 512 -- 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 < 8 then no change , if between 8 & 32 inclusive then 1/2 of cores number IF @NUMPROCS >8 and @NUMPROCS <=32 SELECT @NUMPROCS = @NUMPROCS /2 -- IF cores > 32 then files should be 1/4 of cores number If @NUMPROCS >32 SELECT @NUMPROCS = @NUMPROCS /4 -- Get number of exisiting TEMPDB datafiles and the location of the primary datafile. SELECT @tempdb_files_count=COUNT(*) ,@tempdbdev_location=( SELECT SUBSTRING(physical_name, 1, CHARINDEX('tempdb.mdf', physical_name) - 1) FROM sys.master_files WHERE database_id = DB_ID('tempdb') AND file_id = 1 ) 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 SELECT @SQL = 'ALTER DATABASE [tempdb] ADD FILE (NAME = N''tempdev_new_0'+CAST (@tempdb_files_count +1 AS VARCHAR (5))+''',FILENAME = N'''+ @new_files_Location + 'tempdev_new_0'+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 ) GO' PRINT @SQL SET @tempdb_files_count = @tempdb_files_count + 1 END |
Output from the Query :
— Instance name: DEVSERVER\DEV2008 ;
/* Version: Microsoft SQL Server 2008 (SP3) – 10.0.5500.0 (X64)
Sep 21 2011 22:45:45
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)
*/
— TOTAL numbers of CPU cores on server :16
— Number of CPU cores Configured for usage by instance :16
— Current Number of Tempdb datafiles :1
/*
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
****Proposed New Tempdb Datafiles, PLEASE REVIEW CODE BEFORE RUNNIG *****/
——————————————————————————————————————————————————————————
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_02',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_02.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_03',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_03.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_04',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_04.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_05',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_05.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_06',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_06.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_07',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_07.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_08',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_08.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_09',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_09.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_010',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_010.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_011',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_011.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_012',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_012.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_013',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_013.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_014',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_014.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_015',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_015.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_new_016',FILENAME = N'G:\SQLServer\MSSQL10.DEV2008\tempdev_new_016.ndf',SIZE = 4096MB,FILEGROWTH = 512MB ) GO |
Query to See Te How Writes are distributed across Files
1 2 3 4 5 6 7 8 |
SELECT files.physical_name, files.name, stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms, stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms FROM sys.dm_io_virtual_file_stats(2, NULL) as stats INNER JOIN master.sys.master_files AS files ON stats.database_id = files.database_id AND stats.file_id = files.file_id WHERE files.type_desc = 'ROWS' |
Leave a Reply