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
Security
Cleanup Logs
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'
You have the option to enable: 'Search Network (slow)' to show all SQL instances that can be found within your network.

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



< 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.

When you right mouse click on the grid you have the following options: 

  • Show Only Recommendations, only shows the rows where the recommendation contains a value
  • Show All, will show all available rows
  • Search, searches and highlight the provided word(s)
  • Reset Colors, reset the highlights set by the Search function
  • Copy, copy the select cell to the clipboard.


< 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: Auto-Growth
Shows the growth moments of a database. If a database growth the SQL system wait until it completes before handling other query

Search Database
With the function you can search for databases using starting with option, if you include % you can search within the database name.

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.

Show Statistics
Show statistical information of the selected table

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.

Search Database
With the function you can search for databases using starting with option, if you include % you can search within the database name.

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. You can also create a SQL Job to schedule to create backups of your selected databases. 
Optimizing indexes and statistics is important for optimal performance of your SQL databases.


Optimize Indexes & Statistics Job
This option creates a SQL job for the selected database(s) to optimize the indexes and statistics for every workday. This will improve the performance by keeping the indexes and statistics 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 modified do to locks or other causes, it will continue to the next index when after the selected seconds.
  • Minimal Index Fragmentation (default 5%) only indexes that have a logical fragmentation of 5% or higher will be optimized.
  • Minimal Index Page Count (Default 1000) only indexes that have a minimal page count of the provided value will be optimized. This way small tables are excluding from optimizing.
  • Detail Mode (default: Limited) with this mode the query to get the current state of the indexes will be retrieved faster, based on the values of the query the indexes will be optimized.
    The 'Detailed' mode with also include Page Density values (which makes the query slower) but more accurate. Next to the value of the 'Minimal Index Fragmentation' also indexes with 70% or less 'Page Density' will also be optimized.
  • If you have the SQL 'Enterprise Edition' you have the option to perform an online rebuild of the indexes which will be slower and you can continue working in your database.

For Statistics you have 2 options to choose:
  • Percentage Statistics Modified (default 10), means that when 10% of the index/table is changed (modified, inserted, update, deleted) the statistics will be updated.
  • Minimal Rows Update Statistics (default 500) means, that the index needs a minimum of 500 rows to be eligible to be updated. 

 

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.

With the button: Save you can save the content to a file
With the button: Clean you can clear the content



< 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.
  • Additional Collations: By default 0, more might cause some incompatibility issues
  • Additional Table Owners: By default this must be 0, 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.
  • LDF vs MDF Size: Show the size of the LDF in percent compared to the MDF file.
  • Orphaned Users, show the number of users in the database which no longer exists in the master database.
  • DBB CHECKDB in log: Checks the SQL log of a consistency check is executed for this database.
  • Legacy Cardinality Estimation: When upgrading from an 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.
  • Not Implemented Filtered Indexes: This will reduce the size of the selected indexes which and improve the performance. 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
  • Fill Factor <> 90 (default 90): Show the number of indexes that does not have the default factor 90 for a number of selected indexes. Dynamic tables which leave no room for insert, can slowdown inserts, 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. It is not advised to use lower numbers, because of the empty space in the indexes, which results in more pages need to be read into memory.
    You can adjust the default value in the Settings menu.
    For more information about fill factor, see Microsoft webpage

 

There are undo links the functions: Filtered Indexes, Uncompressed Tables and Indexes and the 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 hosts several overviews to check the behavior of the SQL server.
This are the following options: 

Performance (default)
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 or orange.
If you counter is red or orange you can hoover over the value to get more information.

Wait Stats
Can be used to view the main Wait Stats which indicated certain performance bottlenecks. You need to know how to read those counters and values.

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.

SubTree Cost
Shows the top 1000 queries for calculating the Cost threshold for Parallelism.
This includes the Execution plan from the used query

Historic SQL Queries
Shows all the heavy queries executed sense the last restart of the SQL server.
This includes the Execution plan from the used query 

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

SQL Jobs
Will show all jobs that will be executed during the working day set within the Setting menu caption: Performance, setting: SQL Jobs Work Time [default 07:00 - 18:00 hours]

Application Time
Will show CPU, Physical IO, Memory and WaitTime group by Application name to quickly identify resource consuming applications

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

 

Security
This menu performs several SQL security scans to check for security improvements. If a scan shows a recommendation you can click on the link to get more information from the Microsoft website.
In the Severity column you can see how important the check is.

< Back


Cleanup Logs

With the use of this functionality, you can quicky reduce the size and growth of the Log tables from Exact Globe, Synergy or ELIS.
You can also create an SQL job to automatically remove old data.
For more information, see document: 'Cleanup Logs using hte ESI+ tool'.


< 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 sent 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 menus 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 Font' you can set your personal font.
With the button: 'Select Color' you can set personal color theme to the tool.
With the Theme option you can select a light or dark theme for the tool.

Performance
DSKSPEED duration in second, default 10 seconds how long the tool measures the diskspeed.
CPU duration in minutes, default 60 minutes. Measures the average CPU performance for the last 60 minutes.
SQL Query / Subtree Costs > in Seconds, default 0.5 sec, default value how long a query must run to be shown in the list.
Minimal Disk Latency in ms, default 50, is used with the Performance option within the Performance menu to indicate if a disk is performing below the threshold.
SQL Jobs work time, default 07:00 hour and 18:00 hours, shows if there are SQL jobs running within the provided time frame

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

Optimize
Backup Age (days), default 7, Will check if there are backups create within the last 7 days of all Exact databases.
Option: Use Current TempDB size, default enabled. When the SQL Service is restarted the size of the TempDB is based on the current size. If you disable this option, you can provide a percentage of the largest database for the initial size of the TempDB.

Advanced Checks
Show disabled triggers, default disabled. Will also show the disabled triggers in the overview.
Show disabled indexes, default disabled. Will also show the disabled indexes in the overview.
Only Exact Tables, default enabled. Will only incorporate Exact related tabled in the overview.
Filtered Indexes minimum # Rows, default 1.000. Only indexes with more than the provided number will be eligible for Filtered Indexes.
Fill Factor default value, default 90. Will set the default value for a couple of specific selected Exact indexes.

Advanced Indexes
Duplicate Indexes 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.
Missing Indexes minimum Score, default 100.000. This only show indexes with a minimum score 100.000. This value incorporates the number of records and improvement to be eligible for optimization.

Blocking Locks
Filter on Username, with this option you can filter Trace Queries (Extended Event) based on a specific username.
Maximum File Size (GB), default 5 GB, sets the maximum file size of Extended Event) file.
CSV Separator default a comma, you can select the separator used within the CSV file (, or ;)

System Information 
Check SQL Error Log, enabled default. By default, this option will only check the current (active) SQL Log file for critical errors.
You can change to look in all SQL error logs.

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.

To download the latest version from our FTP Site, do the following:  

  • Open your Windows File Explorer
  • Copy/Paste the following ftp://ftpesi.exact.com/ESI
  • Download the ESITool+.EXE to your harddrive

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, 2019 and 2022
Microsoft SQL Server 2014, 2016, 2017, 2019 and 2022
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: 26-09-2023
 Release:  Attachment:
 Disclaimer