Performance tuning tips

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 )

Shrink Tlog

use below script in SP and schedule it maintenance window.

checkpoint;

go

DBCC SHRINKFILE('log file name',25)

The following query will give you the reason on why log file is not getting reused.



select name,log_reuse_wait_desc from sys.databases


If you get nothing as description for the logfile you will also be able to shrink the log file