SQL and SQL Server 2019

Microsoft SQL 2019

https://www.microsoft.com/en-au/sql-server/sql-server-2019

Install SQL Server and create database on RHEL

https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup?view=sql-server-ver16#upgrade

Useful References

Maxdop https://www.mssqltips.com/sqlservertip/6211/sql-server-2019-installation-enhancements-for-maxdop-and-max-memory/

Lock pages in memory option https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver16

https://blog.sqlauthority.com/2020/01/07/sql-server-2019-how-to-enable-lock-pages-in-memory-lpim/

tempdb data file number

https://learn.microsoft.com/en-us/answers/questions/74599/tempdb-file-number-for-many-logical-processor

Up to maixum of 8 tempdb files
The number of files depends on the number of (logical) processors on the machine. If the number of logical process is less than or equal to eight, use the same number of data files as the logical processors. If the number of logical processors is greater than eight, use teh eight data files and then if contention continuous, increase the number of data files by the multiples of 4, until the contention is reduced to acceptable levels or make changes to the workload or code.

Prerequisites and Configuration

1. Prerequisites
Install .Net Framework 4.6.11

2. Collation (Default)
SQL_Latin1_General_CP1_CI_AS

3. MAXDOP settings in SQL Server 2019 Setup
# maximum degree of parallelism (MAXDOP)
Default value 0     # 0, meaning use (up to) all cores when the engine believes that will help. 
                    # For many OLTP workloads, 0 is not the optimal setting

4. Lock pages in memroy
This Windows policy determines which accounts can use a process to keep data in physical memory, 
    preventing the system from paging the data to virtual memory on disk.
# When enable for the SQL service account, SQL server will bypass Windows memory management,
    it will no let Windows flush pages from RAM to disk
GPO Settings
    Security settings -> Local Policies -> User Rights Assignments
        "Lock pages in memory", add SQL service account

5. tempdb data files
Add up to 8 tempdb data files   # As the number of logical processors

6. Grow size
Depending on your practices.
a. Grow by percentage
b. Set fix size, relatively large size

7. model database
System database called "model", which acts as a template whenever you create a new database.
# Check database settings, such as
    file size
    autogrowth
    recovery model

8. Server settings
Right click the server in ssms, and select properties
a. Memory tab
    Ensure always have at least 4GB or 10% of available memroy to the operating systems,
    whichever is larger
b. on the Database Settings tab, ensure backup compression is enabled.

9. Parallelism Configuration