One moment please...
 
 
Exact System Information   
 

Help File: Exact System Information +

The Exact System Information + T

The System Info tool + (ESI+) is developed to easily retrieve and adjust Windows, SQL and Database settings to get an optimum performance out of your SQL system.
It will also automatically send statistical information to Exact.
For the best results it advised to execute the ESI+ tool on the SQL Server using an administrator or equivalent Windows account.



Table of content

Connect
System Info
Database Info
Optimize
SQL Maintenance
Logs
Exact Script
Advanced Indexes
Advanced Checks
Blocking Locks
Performance
Settings
Installation and Uninstall Information
Supported and tested environments


Connect
When you start the ESI+ tool the Connect menu will be shown.
The ESI+ tool will detect the installed SQL instances this (virtual) machine and will show them in the dropdown box with then 'SQL Server name'
Select or enter the SQL Server you want to connect to and If you agree with the 'REQUEST FOR ANONYMOUS INFORMATION' you can click on the button: 'Accept and Start Analyzing'
Depending on the speed of your system and the amount of databases, this process can take a few minutes.

Optional:
You can enter your e-mail address to receive an Improvement Report.
It can take about 10 minutes before the Improvement Report is sent to the provided e-mail address

The Connect button only makes a connection to the selected SQL instance and enables the menu options SQL Maintenance and Blocking Locks




< Back

System Info
After the the ESI+ tool has analyzed the system the 'System Info' menu is automatically shown.
This shows an overview of all the retrieved items with their values and recommendations.
This information is stored in the file: 'ExactSysInfo.HTML' which is located on the folder where you executed the ESI+ too.

< Back


Optimize
The menu shows all the optimization that can be applied to the system. Depending on the optimization you selected, you might need to restart the SQL Server service of the whole system.
After you click on the Apply button a disclaimer is shown and will ask for confirmation before the changes are applied.
The second pane is called: 'General Recommendations', which shows general recommendations some which cannot be adjusted by the ESI+ tool.
With the button 'Copy' you will copy all values of both panes into the clipboard

< Back

Database Info
Within this menu option you will see all information about your SQL databases shown in two panels.
The first panel shows the most important settings of all the databases.
The second panel displays information about the size, growth and location of the database files.
When you right mouse click on row in the upper grid you have the following options:

Button: Table Sizes
Which will show all the table sizes of the selected databases in the lower grid.

Button: SQL Queries
This option shows historic executed SQL queries on the connected SQL instance. This is useful to detect queries that are responsible to a high work load in the past. This information is reset every time the SQL service is restarted.

Button: Auto-Growth
Shows the growth moments of a database. If a database growth the SQL system wait until it completes before handling other query

Optional buttons are shown in the lower database grid when Table Size button is executed.
Check Fragmentation
This shows the fragmentation of the selected table. The '% Fragmented' needs to be a low as possible.

Rebuild Index
If you have a high '% Fragmented' indexes, you can use the function to rebuild the selected index.
Exact advices to rebuild index after working hours! You can also create a schedule SQL job using the 'SQL Maintenance' menu, so indexes are rebuild every working day after working hours.

You can easily copy data grid info using the copy buttons


< Back

SQL Maintenance
This menu let you create and schedule SQL jobs for rebuilding indexes and statistics, which are important to let your SQL databases run as fast a possible.
Next to that you have the option to implement a SQL job to create backup of your SQL databases on every working day.

First you need to to select one of the 3 option in the dropdown box.

Rebuild Indexes Job
This option created a SQL job for the selected database(s) to optimize the indexes for every workday. This will improve the performance by keeping the indexes up-to-date.
The default time when the SQL job is executed is 23:00 hours on Monday, Tuesday, Wednesday, Thursday and Friday.

Lock Time Out (sec) is by default set to 60 seconds. This means that if an index cannot be rebuild do to locks or other causes, it will be skipped after the selected seconds.

Update Statistics Job
This option created a SQL job for the selected database(s) to optimize the index statistics.
The default time when the SQL job is executed is on 01:00 every Sunday.

With the 'Select Day' option, you can select a different day to start the statics update job.
Backup Databases Job
With this option you can create a SQL job to create a backup of the selected databases.
By default, the SQL job will run every working day at 21:00 hours.

With the option Append / Overwrite (Default Overwrite) you can select the behavior of the backup file.
With this option you can select the backup file need to be added or overwritten each time the SQL job is executed.
Keep in mind that using the 'Append' option the size of the backup will can grow very large over time.|

Job History
This grid shows the history and status of the executed SQL jobs created by the ESI+.


< Back

Logs
This menu by default is not shown, once enabled you can view three logs:
- The Adjustment log of all the adjustments applied via the ESI+ tool.
- The Errors log might shows the errors that occurred while using the ESI+ tool.
- The Debug log show debug information. Debug information will only be traced once you start the ESI + tool using the /DEBUG parameter.
- The DiskSpeed [date/time] logs, shows the result of the DISKSPD from the Performance menu.



< Back


Exact Script
The Exact Script menu displays a result set of Exact specific related information about Error Logs, index information and statistical table information.
This information is sent to the Exact FTP server.

< Back

Advanced Indexes
This menu by default is not shown. Please only use this menu option of you have experience in adding and removing indexes!
With the menu you have 3 options:

Important is the field: 'SQL Server running for x day(s)' shows 30 days or more. If it is less the suggested or missing indexes might not cover all expects of the SQL Server usage!

View Missing Indexes
This shows information about indexes that can be added to improve the general performance. The higher the impact column the more likely it can reduce the work load on the SQL Server.
It is advisable to investigate specific functions that might have a slow performance rather that implement a general index.
Within this option the following buttons are enabled.


Implement
This will implement the selected missing indexes
Save: This will save the selected indexes to a file called: 'ESI_View Missing Indexes.sql' so you are able to create the missing indexes on a later timeslot.

View Unused Indexes
Shows the indexes of the selected database that are not used during the start of the SQL server. If the SQL Server is running for a long time, those indexes are eligible to be deleted.
Within this option the following buttons are enabled.
Delete: This will delete selected missing indexes
Save: This will save the selected indexes to a file called: 'ESI_View Unused Indexes.sql' so you are able to delete the missing indexes on a later timeslot.

View Installed ESI Indexes
This show the indexes that are implemented via the 'View Missing Indexes' option.
Delete: This will delete selected ESI indexes
Save: This will save the selected ESI indexes to a file called: 'ESI_View Installed ESI Indexes.sql' so you are able to delete the created ESI indexes on a later timeslot.

View Duplicate Indexes
Via this option you can detect and delete duplicate or overlapping indexes in the selected database (obligated).
Save: This will save the selected Duplicate indexes to a file called: 'ESI_View Duplicate Indexes.sql' so you are able to delete the Duplicate indexes on a later timeslot.

< Back


Advanced Checks
This menu by default is not shown. The following checks will be executed:

Unknown Triggers: Ill created trigger can cause several issues within our products. Double click on a trigger to show the details.
Unknown Indexes: Every index needs to be maintained, unnecessary indexes can cause bad performance in writing information to the database.
Unknown Tables: Temporary or manually created non Exact SQL Tables can consume unnecessary disk space.
Unknown Views: Temporary or manually created non Exact Views. You can delete them and also check if the NoLock option is set for every From and Join in the view.
Multiple Collations: This might cause some incompatibility issues
Table Owners: By default this must be 1, if there are more table owners it indicated non Exact table are present
_dta Indexes: Are left over indexes or statistics from the Data Tuning Advisor of SQL, which can be deleted.
Consistency Check: Check the SQL lock of a DBCC is executed on this database and shows that information.

General Check

The following checks are present:

Legacy Cardinality Estimation: When upgrading from a older SQL Server version to SQL Server 2016 or newer it can be that you experience a huge performance degradation. Turning on this option might solve this issue.

# without Filtered Indexes:
This will reduce the size of some of the indexes and can improve the speed as result of that. It also set the fill factor to 90 to add some performance improvement.|
For more information see the document about Filtered Indexes

# Uncompressed Tables:
Show the number of tables that are not compressed. Compression will result in a smaller database size and most of the time a faster performance with a little overhead on the SQL Server CPU cores.
For more information about compression, see the document about Page Compression

# Uncompressed Indexes:
Show the number of Indexes that are not compressed. Compression will result in a smaller database size and most of the time a faster performance with a little overhead on the SQL Server CPU cores.
For more information about compression, see the document about Page Compression

# without Fill Factor (90):
Show the number of Indexes that does not have a fill factor of 90. Dynamic tables which leave no room for insert, will slowdown insert because the index page is full up to 100%, if you leave space on the page, insert will be faster because of less data movement.
For more information about fill factor, see Microsoft webpage

Database MaxDOP:
Shows if at a database level the MaxDOP (number of processors) can be used for parallel execution. Leave this value to 0 so it can be correctly set on SQL Server level.
There are undo buttons for the functions: Filtered Indexes, Uncompressed Tables and Indexes and the function Fill Factor:


< Back


Blocking Locks
With this menu you are able to check if blocking locks occur on the connected SQL instance. A blocking lock will decrease the performance of your Exact application or can cause crashes.
If you click on the Start button the blocking check process will start and will stop if you click on the Stop button or the provided time is reached. Default the end time will be on the current day at 18:00 hours.
In the folder where you executed the ESITool+ application a BlockingLocks[start time].csv file will be created.

You can enable the option: 'Trace Queries' which will automatically create an Extended Event that will trace all queries for the selected database and will stop at the provided Stop Date/Time.
The maximum default log size is 5GB but can be change within the Settings menu: (Min 1 max 100GB). There is a check if there is enough free space to create such a log file.
Within the Settings menu you can also additionally filter on Username.

With the use of the icons on the right part of the window, you can either open the created Extended Event log file or the Blocking Lock log file with the associated application. 



< Back


Performance
This menu displays several performance indicators of your SQL Server. If a performance counter is below its threshold it will change the color from black to red.

Button: Performance (default)
% Free space in TempDB
Buffer cache hit ratio: Need to close to 100
Maximum size of server memory (MB): Show the amount of RAM that SQL can use
Memory Grants Pending: Needs to 0
Memory Pressure Forced Grants: Needs to be 0
Number of Deadlocks/sec: Needs to be 0
OS Available Memory (MB)
Page life expectancy: Needs to be above the Page life expectancy Threshold value
Processes blocked: Needs to be 0
SQL Server Days Active: Information 
Target Server Memory (MB): Memory needed by SQL
Total Server Memory (MB): Currently used by SQL
Unique Users: Information about the unique users currently active
User Connections: Information about the amount of user connecting to the SQL Service

Button: SQL Processes
This option will show current processes running on the connected SQL instance. This is useful to check which query or queries is responsible for the most load on the SQL Server.

Button: SubTree Cost
Shows the top 1000 queries for calculating the Cost threshold for Parallelism.

Button: DISKSPD
This will start (by default) a 10 seconds disk IO test using the DISKSPD tool which created a log file within the ESI+ folder. The results will be shown in the Log menu.
For more information see, Use DISKSPD to test workload storage performance - Azure Stack HCI | Microsoft Docs

Button: Copy
Will copy the performance indicators and values  

Disk Performance
Latency on average needs to be lower then 20ms.
A warning is shown in the Optimize menu when the option Warning Read/Write Latency is met.
The default value is 50ms, this can be changed within the Settings Menu.


< Back

Settings

Application Settings
Auto Execute ESI_EXACT.SQL script, this option is enabled by default and exact the script and sent anonymous information to Exact.
The option Delete created CSV File automatically deletes the file. This file is send to Exact and not needed afterwards.
Default Query Time out (default: 300 sec) set the query time out SQL waits for the SQL Query to completed.

Menu
This will show or hide menu's from the menu bar.
The Show Tool tips will show the tooltips with every main function.
Option Compressed Menu will collapse or expand the left menu of the ESI+ tool.
With the button: 'Select Color' you can set personal color theme to the tool.

Checks
Enables of disables the option to check the collation of every database against the SQL instance.

Exact Links
Opens the links about the Change Logs, System and Hardware requirements in your default browser.

Filtered Indexes
Option: '% Null (Slow)' (default disabled)
If you enable this option, all tables are checked if the value NULL is used more than the provided value (default 50%). IF not that this index will not be suggested for filtered indexes.
Option: Minimum # Rows (default 10.000)
This option checks if the table contains at least the provided number of rows otherwise it will not be suggested for filtered indexes.
Option: Create with compression
This will automatically create the new filtered index using Page compression. This way you do not have to compress it after applying a filter to the index.

Initial TempDB Size
Option: Use Current TempDB size 
When this option is enabled the current TempDB size will be the initial TempDB size (when restarting the SQL Service).
If this option is not enabled, you can set the initial TempDB size to (default value) 10% of the largest database.
You can set the default (64MB) growth size of the TempDB.

Blocking Locks
Option: CSV Separator
You can select the separator used within the CSV file (, or ;)
Option: Filter on User name
With option you can filter Trace Queries (Extended Event) on a specific user name 
Option: Maximum File Size (GB) 
Set the maximum file size of the Extended Event (XEL) file. Default is 5 GB.

Warning Read/Write Latency
Set the threshold for latency, by default this is set to 100 milliseconds. Then a higher value then the threshold is reached, the value will show up with a red color.

DISKSPD
Here you can adjust the duration (default 10 seconds) for the DISKSPD tool.

Search
With this option you can search for functions within the ESI+ tool.

< Back


Installation and Uninstall Information
The Exact System Information Tool needs the Microsoft .NET Framework 4.7 to run.
To install the application, download the ESITool+.exe file to a folder on a hard drive.


Uninstall the application
To uninstall just remove all files in the folder where you executed the ESITool+.exe.

Download the latest version from the following FTP site:
ftp://ftpesi.exact.com/ESI/ESITool+.exe

Startup Parameter:
/AUTO when you start the application with this parameter, it will execute the tool, upload the information and close the tool. This can be used to schedule the ESI tool.

< Back


Supported and tested environments (x64 platforms)
Windows 10, 11
Windows Server 2012 (R2), 2016 and 2019
Microsoft SQL Server 2012, 2014, 2016, 2017 and 2019
Microsoft Azure SQL

< Back


Disclaimer:
In article 4.5 of the Customer License Agreement states that the license-holder is responsible for the installation and configuration of the software on his system.
The license-holder is responsible for adjustments made to the system with the use of the Exact System Information Tool.

     
 Main Category: Attachments & notes  Document Type: Online help main
 Category:  Security  level: All - 0
 Sub category:  Document ID: 29.692.185
 Assortment:  Date: 05-11-2021
 Release:  Attachment:
 Disclaimer