Explanation of the Recommended Optimizations of the ExactSysInfo Tool
This document describes all the possible 'Recommended Optimizations' of the
Exact System Information Tool (for more information about the tool see document 17.712.656)
The following areas of adjustments can be found on the Recommended
Optimizations tab:
- [REG] adjustments to the registry of the SQL Server
- [SQL] adjustments to the properties of the SQL Server
- [DATABASE] adjustments to the Database properties
- [BOOT] adjustments to the C:\BOOT.INI file or Windows Boot Manager
- [SQL Agent] adjustments to the SQL Agent Service
- [PageFile] adjustment to the Paging File settings
- [GPEDIT] Adds the SQL login account to the Group Policy:
"Lock pages in memory"
The individual options:
[SQL]
sp_configure 'affinity mask', 0
Use all available cores for SQL
sp_configure 'affinity I/O mask', 0
Use all available cores for SQL
sp_configure 'max worker threads', 0
Set to the default value of SQL
sp_configure 'Priority boost', 0
Set to the default value of SQL
sp_configure 'lightweight pooling', 0
The settings above are set back to the default settings for SQL Server. For
more information, see MS site: http://support.microsoft.com/kb/319942/EN-US/
sp_configure 'max server memory (MB)',[value]
This value is set to the maximum installed memory of the SQL server.
If the SQL server is not installed on a dedicated server for SQL, you might
reduce the amount of memory for SQL to make it available for the other
application(s) such as ASImport jobs.
sp_configure 'max degree of parallelism', 0
This value is set to the default value of SQL. 0 means use all CPUs to execute
the query.
Depending on the amount of cores in the SQL server the optimum number will be
suggested.
sp_configure 'AWE enabled', [value]
To let SQL Server use all available memory on a 32 bits Operating System you
need to enable AWE, see table. On 64 bits Operating Systems or 32 bits Operating Systems with less than
4 GB is option is not needed.
sp_configure 'min memory per query (KB)', 1024
This value is set to the default value of SQL.
sp_configure 'index create memory (KB)', 0
This value is set to the default value of SQL. 0 is dynamic.
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON
Enable memory-optimized tempdb metadata using the default resource pool
For more information see MS site: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-server-configuration-transact-sql?view=sql-server-ver15
Buffer Pool Extension (BPE)
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = [Drive letter]\ESI_BPE\ESI_ExtensionFile.BPE',SIZE = [size] MB)")
If you SQL configuration needs more RAM and the SQL Server contains a Solid State Disk (SSD) with enough free disk space, BPE can be used to utilize the fast SSD for the Database Engine buffer pool to significantly improve I/O throughput.
For more information see MS site: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/buffer-pool-extension?view=sql-server-ver15
<< Back
[DATABASE]
SET AUTO_CLOSE OFF WITH NO_WAIT
On SQL Server 2000 Desktop Engine/Personal Edition or SQL Server Express this
option enabled can close the database to free up resources, but also decrease
performance.
SET AUTO_SHRINK OFF WITH NO_WAIT
Shrinking the database causes an unneeded overhead and will cause fragmentation
of the database resulting in a bad performance.
SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT
SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT
Statistics are needed to create an optimized execution plan for a query. If
this option is disabled, the performance of a query will be reduced.
EXEC dbo.sp_dbcmptlevel @dbname=N'[value]', @new_cmptlevel=[value]
This option sets the correct compatibility level for the SQL database:
Value 100 for Microsoft SQL Server 2008 (R2)
Value 110 for Microsoft SQL Server 2012
Value 120 for Microsoft SQL Server 2014
Value 130 for Microsoft SQL Server 2016
Value 140 for Microsoft SQL Server 2017
Value 150 for Microsoft SQL Server 2019
ALTER DATABASE [value] SET PAGE_VERIFY CHECKSUM
The optimal Page Verify setting is the value Checksum. If you have upgraded
from SQL Server 2000 to SQL Server 2005 or 2008 (R2) this setting contains most
likely the default value of TORN_PAGE_DETECTION from SQL Server 2000.
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev[value]', FILENAME =
N'[value].ndf', SIZE = [value] KB, FILEGROWTH = [value]
Based on the number physical or core processors installed in your hardware you
can improve performance to create extra TempDB files per physical or core CPU.
The maximum suggested TempDB files is no more than 8.
For more information, see MS site: http://support.microsoft.com/kb/328551
ALTER DATABASE [value] MODIFY FILE (NAME = N'[value]', FILEGROWTH
="[value]")
On some SQL editions the 'Autogrowth' is set to 1 MB, this setting can create
overhead in creating new space for the MDF file. We suggest to use 10%
Autogrowth or if a database growth beyond 10 GB, we set the autogrowth to 2 GB
For more information see MS site: http://support.microsoft.com/kb/315512
ALTER DATABASE [value] SET DELAYED_DURABILITY = FORCED WITH NO_WAIT
This option is only suggested by the ESI Tool if your recovery model of your
database is in Simple mode (for more info about recovery models, check website:
https://msdn.microsoft.com/en-us/library/ms189275.aspx). This function improves
performance due to the transaction commit processing does not wait for log IO
to finish and return control to the client.
For more information see MS site: https://msdn.microsoft.com/en-us/library/dn449490.aspx
ALTER DATABASE [value] Set QUERY_STORE (OPERATION_MODE = READ_WRITE)
Enable the Query Store for easier performance troubleshooting.
For more information see MS site: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15
ALTER DATABASE [value] Set AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN =
ON)
Automatic
tuning in SQL Server 2017 (14.x) or newer notifies you whenever a potential
performance issue is detected, and lets you apply corrective actions, or lets
the Database Engine automatically fix performance problems.
For more information see MS site:https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver15
<< Back
[BOOT]
Adjusting BOOT.INI [Remove /3GB | Remove /PAE | Add /3GB | Add /PAE]
Adjusting Windows Boot Manager [Remove INCREASEUSERVA | Remove PAE | Add
INCREASEUSERVA | Add PAE]
Note The /3GB switch changes the way that the Microsoft Windows 4 GB
virtual address space is divided. Instead of dividing the 4 GB virtual address
space into 2 GB of user mode virtual address space and 2 GB of kernel mode
virtual address space, the division is modified to 3 GB of user mode virtual
address space and 1 GB of kernel mode virtual address space. In certain
scenarios, this division can starve the kernel for memory. The division can
also cause system-wide problems.
If you want to take advantage of more than 2 GB of RAM on a 32 bits Windows
platform the BOOT.INI needs to contain certain parameters to be able to use all
the memory for SQL: See table: How to configure the SQL Server
to use all available memory
|
Memory Size
|
BOOT.INI
|
Enable AWE
|
Permission to lock pages
in memory
|
Set Working
set size value
|
|
< 2 GB
|
|
No
|
No
|
1
|
|
3-4 GB
|
/3GB
|
No
|
No
|
1
|
|
5-16 GB
|
/3GB /PAE
|
Yes
|
Yes
|
0
|
|
> 16 GB
|
/PAE
|
Yes
|
Yes
|
0
|
Note - For more information about the switches and how
to add the switch to the BOOT.INI, click on the switch name: /3GB or /PAE
<< Back
[SQL Agent]
Service: [value] Status: Running and Startup Mode: Auto
If the SQL Agent service is not started, no SQL job (including index
optimization) can be executed, therefore this server needs to be started and
the startup mode needs to Auto.
<< Back
[PageFile]
Set Pagefile to System Managed
The optimum setting for Windows Vista and Windows 2008 setting the Paging File
to System Managed.
Set Initial size (MB) and Maximum size (MB) to Windows recommended size
The optimum setting for Windows 2000/3 is to set the initial size and maximum
size to the value of the recommended size set by Windows. This setting will
result in fewer disks I/O because the size is not changing every time.
The size of the PageFile is by default 1.5 times the size of the installed RAM.
For systems with a large amount of RAM we need to set at least the maximum size
to the actual size in RAM + 1 MB. For more information, see Microsoft
page: http://support.microsoft.com/kb/889654
<< Back
[REG]
Set Processor Scheduling to Programs
On workstation platforms like Windows XP and Vista the processor scheduling
needs to be set to Programs so application in the foreground will get a higher
priority than applications on the background.
Set Processor Scheduling to Background Services
On server platforms the SQL Server will execute queries in the background
process, therefore the Processor Scheduling needs to be set to Background
Services for an optimum performance.
Set Memory usage to Programs
Set File & Printer Sharing for Microsoft Network to 'Maximize data
throughput for network applications'
On Windows Server 2000 and 2003 server you can set the network card to be
optimized for SQL. See example: The ‘?’ indicated a clear statement for this
optimizations.
<< Back
[GPEDIT]
[GPEDIT] NTRights.EXE -u [login account] +r SeLockMemoryPrivilege
If the server contains more than 2 GB RAM intern memory and the login
account of the SQL service is not found in the local group policy: "Lock
Pages in Memory"
This option will be displayed in the Recommended Optimization tab.
This application uses the Microsoft Resource Kit 2003 NTRIGHTS.EXE (included in
the ZIP) to add this account.
For more information, see Microsoft web page: http://support.microsoft.com/kb/918483
[GPEDIT] NTRights.EXE -u [login account] +r SeManageVolumePrivilege
Increases performance with the following options:
- Create a database.
- Add files, log or data, to an existing database.
- Increase the size of an existing file (including autogrow operations).
- Restore a database or filegroup.
For more information, see Microsoft web page: https://technet.microsoft.com/en-us/library/ms175935(v=sql.105).aspx