Exact System Information

ESI+ tool: Advanced Checks

This document describes and explains the Advanced Checks within the Exact System Information + tool (ESI+ Tool)

Unknown Triggers
Triggers are used to automatically start a process (query) when a certain criterion is met, this can be a delete, insert or an update of a record in a table. An ill written trigger can really harm the performance and also the consistency of the database, therefor triggers not created by the Exact software is not allowed.
You can show the details of an unknown trigger by double clicking on the trigger or right mouse click on the trigger name and select 'Show Details'

Unknown Indexes
The benefit of having a well created index is that you are able to quickly find/show the information you're looking for within a SQL table. 
The down site is that when a record used in the index is updated, deleted of inserted, the index also needs to be maintained. This can cause slower performance in entry or import applications.

Unknown Tables 
We have Exact tables and maybe some custom solution tables which consumes space. Temporary tables can be left behind which are never reused and only consuming space and slow down the backup/restore process.
With this list you can easily find temporary table and delete them, warning! Make sure you do not delete tables created by the customer for non-Exact purposes.

Unknown Views
We have Exact views and maybe some custom-made views which consumes a bit of space. Temporary views can be left behind which are never reused can be deleted.
With this list you can easily find temporary views and delete them, warning! Make sure you do not delete views created by the customer for example reports.
You can check a view by double clicking on the view or right mouse click on the view name and select 'Show Details'.
Ill written views can cause blocking locks or/and slow the performance of your Exact application. Every From and Join needs to contain the option (NOLOCK) to be sure it is not blocking an Exact function.

Additional Collations 
This is just information to check if more than one collation is used within the database. 

Additional Table Owner(s)
This is just information to check the amount of table owner. By default, this should be 1.

Hypothetical indexes
When a customer or Exact employees used the SQL Database Engine Tuning Advisor to optimize a SQL query it can be that the process did not complete, and it left behind statistic data in several tables. With the use of this tool, you can easily delete these indexes, they are not used or updated and only consumes a small space on your disks.

LDF size vs MDF size
Displays in percent how large the LDF (Log) file is compared to the MDF file. 

Orphaned Users 
Shows the number of users in the database where the logon no longer exists.

Consistency Check in log
This option shows the last time a database consistency check is performed.

Legacy Cardinality Estimation enabled
When a customer upgraded this SQL Server to a newer SQL version (SQL 2016 or newer) it might be that some or many functions with the Exact application perform noticeable slower than on the older (lower specifications) SQL Server. Then can be due the SQL Engine to execute the query the fastest way possible. Setting the option for the specific database to True, it will run the SQL queries using the 'old' SQL Engine which can result in a faster performance.

Not Implemented Filtered Indexes
Filtered Indexes will reduce the index size and thus the total database size. Another benefit is that it can increate index performance because of the smaller size of the index and reduce reads to gather the requested information. It can in some cases slowdown the write performance because it has to assess if the insert, update or delete applies to the Filtered Index. 
Within the Setting menu you can change the settings are named: 'Minimum Rows' with a default value of 0, means all SQL indexes will be eligible for filtering. You can increase the number of rows to exclude small indexes.
IMPORTANT, you will always see the number of indexes which can be filtered and contains NULL values. This means that you can see something like 100 to 500, meaning of the 500 indexes, only 100 also have null value which can be filtered out.

Uncompressed Tables 
Shows the number of tables that are not compressed. When a table is compressed, it will reduce the size on disk, and it will increase performance because SQL has to read less information from disk which in most of the time is the main the main reason for performance degradation.

Uncompressed Indexes
Shows the number of indexes that are not compressed. When an index is compressed, it will reduce the size on disk, and it will increase performance because SQL has to read less information from disk which in most of the time is the main reason for performance degradation. 

Fill Factor <> 90 (Default 90)
This option will set a fill factor of 90 (default) on dynamic tables containing a key index with column type: 'uniqueidentifier'. The default fill factor is 100 (or 0 which is the same) which fill up the index pages. With a fill factor of 90 there is empty space on the index pages for quickly inserts. The downside is that the size of the database will be a bit larger due to the reserved space. You can change the default value of 90 in the Settings menu under the section: Advanced Checks'.

Incorrect Fill Factor
Shows the number of tables with a fill factor other then 100 or 0. Static SQL Tables to not benefit from a fill factor because they are not updated frequently. It only consumes unnecessary disk space and does not contribute to the performance.

Database Owner
Show the current database owner. If this is not equal to the 'SA' user, you can change the ownership to the SA user. To more secure your database environment and in case the user is deleted. 

Not optimal Index Statistics
This will show statistic information of all indexes per database and selected tables (sorted by size)
You can search (begin with) for specific tables.

Fragmented Indexes
This will show fragmentation information of all indexes per database and selected tables (sorted by size)
You can search (begin with) for specific tables.

Database Growth 
Shows the number of growth moment and the time in milliseconds that it took to grow. When a database grows, it will wait until the growth is completed before continuing with other tasks.
Reduce the time and number of growth moment by setting the correct auto growth size of the database and using the SQL option for instant file initialization.

Age SQL Backup (days)
Shows the number in days of the last found SQL backup. If there is no SQL back or it is older than 1 day, the option to implement an SQL backup will be shown.
Note - Some systems do not make use of the SQL backup options but create backups of the system of via another way (application). This message can be ignored. 

State AUTO_UPDATE_STATISTICS_ASYNC
Checks the size of the database, number of waits and average modification of the database to indicate that Auto_Update_Statistics_Async might be a performance improvement.
The database needs to be bigger than 10GB, more than 1 second of wait time and more that 20% modifications before this option is suggested.