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
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 *****/
——————————————————————————————————————————————————————————
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
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'