Database Establishment Preliminary

1, the data file equals the number of CPUs, but less than or equal to 8 the number of tempdb files is half the number of CPUs, but the size is equal, or the self-growth may occur on the largest file, (refer to the Microsoft Database Support Group: How does tempdb become a performance bottleneck) File size can be set larger, such as 1G, self-growth set to 1G, do not set to a percentage, the data will not be the same log file, because it is sequential write, write a file and then write a 2, change the default port 3, if the conditions allow (there are 3 or more than 3 physical disk), Log and data files should be located on different disk 4, disable SA5, establish timed run index maintenance to reduce index fragmentation, and reduce table space. Index maintenance 1-index defragmentation disables the SA user. Create login [Admin_sql] from Windows;gosp_addsrvrolemember [Admin_sql],sysadmingoalter login [ SA] Disablego changes the tempdb data file, the log file, based on the number of CPUs. Use Masterdeclare @i int,@s varchar (4000), @c intset @i=1select @c=cpu_count from sys.dm_os_sys_info-assigning variables to get the number of CPUs while (@i<@c/2 and @i<8) begin--gets the script that creates the tempdb file set@s= ' ALTER database tempdb add file (Name=tempdev ' +convert (varchar,@i + ', filename= ' D:\SQLData\tempdev '--File storage path naming rules: if it is 2 disks, the secondary data file is placed on the D disk, the rest remains unchanged. +convert (varchar,@i) + '. ndf ', SIZE = 1024000KB, filegrowth = 512000KB) '--Execute script exec (@s) Set @[email protected]+1end--Modify log file, initial configuration of data file alter DATABASE [ TEMPDB] MODIFY file (NAME = N ' tempdev ', SIZE = 102400KB, filegrowth = 102400KB) goalter DATABASE [tempdb] MODIFY file (n AME = N 'Templog ', SIZE = 1024000KB, filegrowth = 102400KB) GO 

Database Setup Preliminary