The Exact
System Information Tool (ESI)
This System Info tool is developed to easily retrieve and
adjust Windows, SQL and Database settings to get an optimum performance.
It will also automatically send statistical information about Exact tables and
index usage to Exact.
For this tool to function proper, you need run this tool on the SQL Server
using an administrator or equivalent Windows account.
Download the latest version from the following FTP site:
ftp://ftpesi.exact.com/ESI/ExactSysInfo.zip
How to use the tool:
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.
/DEBUG This will enable the extra debug information (see Options Window)
Step 1: Start and Connect
To start the application you need to execute the ExactSysInfo.EXE, see Installation and Uninstall
information
Establish a connection to the local SQL server instance via Windows or SQL
Authentication. See example:
You can send the Improvement Report to the specified
e-mail address.
(It can take about 10 minutes before the Improvement Report is sent to the
provided e-mail address)
Click on the button 'Start Analysis' to start the
process. This can take several minutes depending on the number and sizes of the
Exact databases.
During the analyze process the following information will be displayed, see
example:
The Option button provides some settings for the tool,
click here for the help about the available Options.
When the process is complete the following information will
be displayed, see example:
In the Recommendation column, you can see optimized recommendations for the
specific items.
Note - This tool cannot retrieve information about the disk system
(RAID Level) and how many disks are stored in the disk system!
The option Start GPedit will only be displayed when the option 'Locked
Pages in Memory' is not set correctly.
You can adjust this option manually via the link (start the Group Policy
Editor) or via this tool in the Recommended Optimization tab.
<< Back
Step 2: Applying the
Recommended Optimizations
After the process is complete the number of recommendations
will be displayed in the 'Recommended Optimization' tab. See example:
Selected the optimizations that you want to apply to the system (or enable the
Select All option) and click on the button: 'Apply Optimizations'.
Click the button 'Yes' on the disclaimer popup to apply the recommendations or
'No' ignore the recommendations.
After the recommendations are applied a popup might appear to restart your
system.
It's is advisable to perform the restart of the server of the SQL instance
after working hours.
The second window pane shows the general recommendations some which cannot be adjusted by the ESI tool.
With the button 'Copy Recommendation to Clipboard' you will copy all values of both panes into the clipboard.
<< Back
Step 3: SQL Maintenance (not
available for Express or MSDE editions)
Select the Exact databases for the SQL jobs you want to save.
Rebuild Indexes Job
This option created a SQL job for the selected database 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 to optimize the
index statistics.
The default time when the SQL job is executed is 01:00 on every Sunday.
With the 'Select Day' option, you can select a different day to start the statics update job.
Optional Optimizations:
With the use of SQL Server 2008 or newer you can implement Filtered Indexes
to increase the performance in decrease the size of your indexes.
After you selected the database from the dropdown box, a check is performed how many tables do not have filtered indexes.
If there are still tables that do not contain the Filtered Indexes, the Implement button will be enabled and you can start the filtered indexes process for the remaining tables.
For more information see the document about Filtered
Indexes
Page Compression is only available on SQL Server
2008 Enterprise Edition or newer. It will increase the performance and reduces
the size of your database.
After you selected a database from the dropdown box, a check is performed if the selected database does
not already contain Page Compression.
For more information see the document about Page
Compression
Create SQL Backup Schedule
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.
You can select the folder where the backup file will be stored.
Append / Overwrite (Default Overwrite)
With this option you can select if every time the SQL job is executed, the backup will be added to the existing backup file or that the backup file will be overwritten.
Keep in mind that using the 'Append' option the size of the backup will can grow very large over time.
Show information or Delete a SQL Job
If you select an option within the 'Installed Optimization Jobs' information will be updated automatically to show which databases are selected, time and day for the selected optimization job.
If you want to delete an optimization or backup job created by the ESI Tool, you can just right mouse click on 'Installed Optimization Jobs' can click on the Delete option.
Job History
If you select an option within the 'Installed Optimization Jobs' the Job History for the selected Job will automatically displayed in the Job History table.
<< Back
Tab: Database
Information
Information about your SQL databases are 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.
<< Back
TAB: Exact Databases
The Exact Databases tab 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
The Menu
Hamburger Menu
- Connect: Displays the Connection window
to make a connection to the local SQL instance
- Start Analysis: Starts the analyzing
process
- Save System Information tab to HTML or
CSV format
- Prints System Information and Database
Information tabs to a selected printer
- E-Mail: Sends the System Information tab
in HTML format to the e-mail address you have entered
- View - ‘Database Information’: Show or
hide the tab that contains information about database settings, location,
sizes etc.
- View - ‘Exact Data’: Show or hide the tab
that contains information about statistical table information, indexes
usage etc.
- View - ‘SQL Processes’: Displays the current or historic activity on the SQL server. You can see who is causing high CPU, Physical_IO, Memory consumption or bad performing queries. *
- View - ‘Performance Monitor’: Provides a quick snap shot of several performance counters for the current performance. **
- Logs - ‘Applied Optimizations’: Displays
in Notepad the ExactSysInfo.log file contains all the applied changes.
- Logs - ‘Error’: Displays in Notepad the
ExactSysError.log file all the errors that occurred using the ESI tool.
- Logs - ‘Created CSV File;’ Displays in
Notepad the created CSV file located on the desktop containing the
information send to Exact.
- Logs - ‘Event Viewer Log’: Displays in
Notepad all the information saved in the Windows Application log.
- Logs - 'Performance Monitor Log': Displays in Notepad the created Performance Monitor log from the Performance Monitor tab within the ESI tool.
- Help - ‘Exact System Information’: Is this
help page…
- Help - ‘Recommended Optimization’: Opens
in your default Internet browser the page about the ‘Explanation of the
Recommended Optimizations of the ExactSysInfo Tool’
- Help - About: Display version information
about this tool.
- Options: Set the application options
- Exit: Close the ESI tool application
<<
Back
* SQL Process
With the new SQL Processes tab you can easily check which process/user/application is causing high CPU, Physical_IO (disk) or Memory usage using the 'Current' activity.
If you select the 'Historic' activity your can find the top 50 previously executed worse performing queries since the last restart of SQL.
For both views you can click on the header of a column to change the sorting order.
It also possible to drilldown to a specific database by simple selecting a database and click on the Refresh button.
By default the SQL Processes tab is not visible, you need to enable the tab by selecting SQL Processes in the hamburger menu - View.
** Performance Monitor
You can quickly check the several performance monitor counters on the Performance Monitor tab.
Just select the Disk System where the SQL databases resides on and click on the Start button.
By default the option: 'Interval Time (ms)' is set to 1000, so every second the counters are checked.
You can stop the process by clicking on the Stop button or wait until the Set End Date/Time is reached (default 18:00 hour of the current day).
With the option 'Enable Logging' enabled, a file is created with the name ESI_PerfMon_[current date].LOG containing date and time of a counter that reached the threshold.
Use the Windows Performance Monitor to really analyze the performance of the system over a longer period.
By default the Performance Monitor tab is not visible, you need to enable the tab by selecting Performance Monitor in the hamburger menu - View.
With the Options button
you can select the appropriate options before starting the analyses or leave
every setting to de the default.
Program Setting
- Auto Execute ESI_EXACT.SQL script, this
option is enabled by default and will sent/analyze important information
of your databases
- Compress CSV File, this option by default
enabled to compress the CSV file before uploading to the Exact
- Debug, this is by default
disabled and once enable it will store debug information in the file ExactSysError.log
Calculate Recommended Hardware
Option 'Based on Largest Database' will calculate
the recommendation based on the size of only the largest SQL database.
Other SQL databases will not be taken in account. This option is useful when u
use 1 database most of the time.
Option 'Based on Total Size of All User Databases'
will calculate the recommendation based on all user SQL databases.
This option is useful when you use multiple SQL databases most of the time
E-mail Settings
SMTP server is only need if you want to e-mail the results
(html file) directly from this application. The default port for SMTP is 25,
but this can differ.
SMTP Hint will fill in a suggestion when might be the SMTP server of your
company.
Ask your administrator for the correct SMTP address of your mail server.
<< Back
Installation
and Uninstall Information:
The Exact System Information Tool needs the Microsoft .NET Framework 4.5.2 to run.
To install the application, unzip the ExactSysInfo.zip file
to a folder on a hard drive. The zip contains 6 files, the
ExactSysInfo.exe, ESI_Exact.SQL, ESI_AutoUpdater.exe, NTRights.exe,
Page_Compress.sql and ImplementFilteredIndexesGlobe.sql
Uninstall the application
To uninstall or remove all extracted files from the
ExactSysInfo.zip file.
To remove the registry entries created by this application, you can execute the
attached: 'Remove registry setting of the ESI tool.reg'
<< Back
Supported and
tested environments: (x64
platforms)
Windows 8, Windows 10
Windows Server 2012 (R2), 2016 and 2019
Microsoft SQL Server 2012, 2014, 2016, 2017 and 2019
Microsoft Azure
<< 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.