1) Format disk with 64K
2) Put tempdb separate disk and add multiple tempdb files ( 2016 on wards)
3) Data Warehouse use Columstore index,this would give performance improvement and grate compression rate
4) OLTP use In-Memory
5) SQL Server defaults to a maximum degree of parallelism
(MAXDOP) of 0, which dictates that SQL will dynamically allocate work up to the
total number of CPU cores seen by the SQL service. With Hyper Thread turned on for a
system with 80 physical cores, SQL will see a total of 160 cores which can, for
many workload mixes, lead to a sub-optimal allocation of SQL threads. To
address this, we can change the “Max Degree of Parallelism” parameter in SQL
Server Advanced properties to less than or equal to the number of physical CPU
cores as shown in following figure.
6) In order to reserve some memory for system
processes, user processes, programs, etc., we can change the SQL Server
“Maximum server memory” setting in SQL Server properties. Typically, a value of
total system memory minus 8GB is sufficient. This means that SQL Server will
allocate all available memory in the system, except for 8GB
7) every table should have Clustered index ( row or Columstore )
7) every table should have Clustered index ( row or Columstore )
No comments:
Post a Comment