SQL Health Dashboard

Exploring

ClosedWhere does it fit in?

The SQL Health Dashboard provides SYSPRO Administrators and SQL Database Administrators a one-stop-shop for viewing and managing the system for high availability.

The dashboard displays information on both the system and company databases to assist in identifying potential problems, as well as enabling the repair of certain issues found within the databases.

The SQL Health dashboard provides the following functionality:

  • Single view of all your SYSPRO databases
  • Repair capability on certain issues found (e.g. creation of missing tables/columns)
  • Insights providing at-a-glance values and drill-down capabilities

Information displayed includes:

  • Configuration information for the SQL instance and each SYSPRO database
  • Information on the database and log files, including the size, free space and growth options
  • User access and permissions
  • Non-SYSPRO objects that interact with and which are dependent on SYSPRO data (i.e. stored procedures, views, triggers. etc.)
  • Fragmentation of SYSPRO indexes
  • SYSPRO table, column, index and foreign key information
ClosedNavigation

  • This program is accessed from the Program List pane of the SYSPRO menu.

    Program List > Utilities > SQL Server Utilities > SQL Health Dashboard

ClosedTerminology used
ClosedCollation

This refers to a set of rules determining how data is compared and sorted.

Besides determining the alphabet, the collation order also determines whether accents, case and other alphabet properties are considered in the sort order. For example: if the collation is case-sensitive, the uppercase letters are sorted before the lowercase letters.

Binary sort order is case-sensitive (i.e. lowercase precedes uppercase) and accent-sensitive. This is the fastest sorting order.

ClosedCompatibility level

A compatibility level sets certain database behaviors to be compatible with the specified version of SQL Server.

If your compatibility level is set to an older version of SQL then this can result in performance issues.

ClosedIndex Fragmentation

SQL Server fragmentation occurs when data is sorted in a non-contiguous way.

Index fragmentation is an expected and unavoidable characteristic of any OLTP environment.

Fragmentation is defined as any condition which causes more than the optimal amount of disk I/O to be performed in accessing a table, or causes the disk I/O's that are performed to take longer than optimal.

ClosedRecovery model

SQL Server backup and restore operations occur within the context of the recovery model of the database.

Recovery models are designed to control transaction log maintenance.

A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.

Three recovery models exist:

  • Simple
  • Full
  • Bulk-logged

Typically, a database uses the full recovery model or simple recovery model.

A database can be switched to another recovery model at any time.

ClosedRelated Information

PDF and Video Resources

Feature Guides

Starting

ClosedPrerequisites
  • SYSPRO 8 2018 R2 onwards
ClosedRestrictions and Limits
  • The following database information is not available through the SQL Health Dashboard:
    • Point of Sale (PoS)
    • Manufacturing Operations Management (MOM)
  • The following is not verified on any databases that are not on the same version as the system database (which is assumed to be current):
    • Tables
    • Columns
    • Indexes
    • Foreign keys
  • Certain issues found and displayed in the dashboard (e.g. index fragmentation) cannot be corrected in the dashboard, as these must be addressed as part of your SQL maintenance plan

Solving

ClosedError messages
ClosedSystem and Database Information

This typically indicates that the administrator login doesn't have sufficient SQL permissions to run certain functions.

The program will close.

ClosedDatabase Verification

This typically indicates that the administrator login doesn't have sufficient permission to run certain functions.

The program will skip any further processing of that particular database and continue to the next database in the list (if there is one).

ClosedFAQs
ClosedHow do I view the SQL instance that SYSPRO 8 is using?

The SQL Instance Information pane is automatically hidden and can be accessed from the right-hand side of the screen.

ClosedWhat is the recommended SQL version to use?

We advise that you use the latest version of SQL in order to leverage the latest improvements.

ClosedWhat is the recommended SQL compatibility level?

We advise that you use the highest compatibility level available for your version of SQL, in order to leverage the latest improvements.

ClosedWhy should I cap SQL memory?

Unless memory is capped, SQL will use all available memory on a server. This can result in performance issues, especially when SQL exists on the same server as the applications that use it.

ClosedWhat if my company and system database versions don’t match?

If the database version against a company is not the same as the database version on the system database, then no validation is performed on tables, columns, indexes or foreign keys as these will need to be upgraded.

This can be remedied by logging into the company linked to the database which will then process any minor database upgrades required.

ClosedWhat if the dashboard reports tables with high fragmentation?

Fragmentation of indexes in SQL can lead to performance degradation. We therefore recommend that you add a task to REBUILD or REORGANISE the indexes in the database to your SQL Maintenance Plan.

ClosedWhat SQL user permissions do I need as a SYSPRO admin user?

Your account login should either be a member of the sysadmin fixed server role, or a member of the db_owner role on each of the SYSPRO databases.

Using

ClosedHints and Tips

  • Unless memory is capped, SQL will use all available memory on a server.

    This can result in performance issues, especially when SQL exists on the same server as the applications that use it.

  • The collation used against any SYSPRO database must be case-sensitive.

Referencing

ClosedSYSPRO Databases

The SYSPRO Databases pane lists all databases identified in the system database itself (including the system database).

An icon in the Company column quickly identifies if a database is found to contain any issues.

ClosedDatabase Details

The Database Details pane displays relevant information regarding the SYSPRO database selected in the SYSPRO Database pane, as well as physical files for the selected SYSPRO database.

Column

Detail

Repair Issues

Select this to begin the repair function for issues found against:

  • Tables
  • Columns
  • Indexes
  • Foreign keys

Company

Indicates the company ID.

Name

Indicates the company name.

Database

Indicates the SYSPRO database name.

Version

Indicates the SYSPRO database version.

If the database version against a company is not the same as the database version on the system database, then no validation is performed on tables, columns, indexes or foreign keys as these will need to be upgraded. This can be done by logging into the company linked to the database.

Collation

The collation used against any SYSPRO database must be case-sensitive.

Compatibility level

The compatibility level plays an important role in determining what mechanisms SQL uses in processing and, if set lower than the compatibility level for the version of SQL you are running, this can lead to performance issues.

Recovery model

Indicates the recovery model, used to control transactional log maintenance.

Auto close

Indicates if the option is enabled on the database.

This must not be set on SYSPRO databases.

Auto shrink

Indicates if the option is enabled on the database.

Having this switched on can cause performance issues.

Auto create statistics

Indicates whether the option is enabled on the database.

Auto update statistics

Indicates whether the option is enabled on the database.

The auto create and update statistics do affect performance insofar as statistics are used in the SQL query optimizer.

Status

This reflects the status of the database at the time that the query was loaded (e.g. ONLINE).

If this status reflects as anything other than ONLINE, then the checks on tables, columns, indexes, etc. are not carried out.

Possible database states include:

  • ONLINE
  • RESTORING
  • RECOVERING
  • RECOVERY_PENDING
  • SUSPECT
  • EMERGENCY
  • OFFLINE

Read only

Indicates if the option is enabled on the database.

SYSPRO databases should never be set to read only.

Last full backup

Indicates the date and time of last full backup.

Last log backup

Indicates the date and time of last log backup.
ClosedDatabase files

The following information regarding the physical files for the selected SYSPRO database is included in the Database Details pane:

  • Logical name
  • Type (i.e. Data/Log)
  • Physical Location
  • Size (MB)

  • Growth setting (e.g. 10 % or 1 MB)

    It is important that the growth setting on these files is set correctly as this does have a performance impact.

  • Maximum size allowed
  • Space Used (MB)
  • Free space in file (MB)

  • Free space %

    Monitoring of free space within tables is important as it has a bearing both on the growth and the maximum size of the table.

ClosedResults Pane

The bottom pane of the dashboard displays information found during the inspection and repair process, and relates to the following:

  • Tables
  • Columns
  • Indexes
  • Foreign Keys
  • Object Dependencies
  • Index Fragmentation
  • SQL Users
ClosedTables

The following checks are performed against the company databases' tables. Results are displayed in the Tables pane:

Checks on custom form tables are included.
Check/Message Definition Repairable?

Standard table missing

A table defined in the data dictionary does not exist in the database.

Yes

Standard custom form table missing

A definition for custom form information exists, however the table is not in the database.

Yes

User defined table

A table exists in the database that is not defined in the data dictionary.

No

Informational only

User defined custom form table

A table ending in “+“ exists and this is not a standard custom form table.

No

Informational only
ClosedColumns

The following checks are performed against the company databases' columns. Results are displayed in the Columns pane:

Checks on columns against custom forms are included.
Check/Message Definition Repairable?

Standard column missing

A column defined in the data dictionary is missing from the SYSPRO table.

Yes

Standard custom form column missing

A custom form column is missing from the table.

Yes

User defined column

A user defined column exists in a standard SYSPRO table.

No

Informational only

User defined custom form column

A user defined column exists in the custom form table.

No

Informational only

Column definition does not match SYSPRO definition

A column's definition (type, size, collation, default, nullable) does not match what is expected.

Yes

Custom form column definition does not match SYSPRO definition

A custom form column's definition (type, size, collation, default, nullable) does not match what is expected.

Yes

User defined column not nullable and has no default constraint

A user defined column in a standard table does not have a default and is not nullable. This will cause issues when trying to insert a row into the table.

Yes

User defined custom form column not nullable and has no default constraint

A user defined column in a custom form table does not have a default and is not nullable. This will cause issues when trying to insert a row into the table.

Yes
ClosedIndexes

The following checks are performed against the company databases' indexes. Results are displayed in the Indexes pane:

Checks on custom form table indexes are included.
Check/Message Definition Repairable?

Standard index missing

A missing index can lead to performance issues.

In addition, if the missing index is the primary key, then this can lead to loss of data integrity.

Yes

Standard custom form index missing

A missing index can lead to performance issues.

In addition, if the missing index is the primary key, then this can lead to loss of data integrity.

Yes

User defined index

User defined index exists on SYSPRO table.

No

Informational only

User defined custom form index

User defined index exists on custom form table.

No

Informational only

Index column mismatch

The index does not contain the columns SYSPRO expects and can lead to performance issues.

Yes

Custom form index column mismatch

The index does not contain the columns SYSPRO expects and can lead to performance issues.

Yes

Index defined against incorrect table

This occurs when an existing table is renamed without deleting the indexes, etc. and will prevent the creation of the index against the correct table.

Yes

Custom form index defined against incorrect table

This occurs when an existing table is renamed without deleting the indexes, etc. and will prevent the creation of the index against the correct table.

Yes

User defined primary key

A standard table has a primary key defined that is not what is expected by SYSPRO. This can and will lead to data integrity issues.

Yes

User defined custom form primary key

A standard table has a primary key defined that is not what is expected by SYSPRO. This can and will lead to data integrity issues.

Yes
ClosedForeign Keys

The following checks on foreign keys are performed against the company databases. Results are displayed in the Foreign Keys pane:

Check/Message Definition Repairable?

Standard foreign key missing

A foreign key linking standard SYSPRO tables is missing.

Yes

User defined foreign key

A user defined foreign key has been defined against a SYSPRO table and has been created with the NOCHECK directive.

No

Informational only

Standard foreign key is enabled

A standard foreign key has been enabled. SYSPRO uses foreign keys created with the NOCHECK directive, which means that the foreign key is not used for data integrity purposes as SYSPRO performs its own data integrity verification.

Yes

User defined foreign key is enabled

A user defined foreign key has been defined against a SYSPRO table.

We strongly discourage you from adding your own Foreign Keys to a SYSPRO database (especially without the NOCHECK directive) as this could cause SYSPRO applications to fail unexpectedly.

No

SYSPRO doesn't know the purpose of the user defined foreign key, so won't change it in any way. It's your responsibility to address as you see fit.

Foreign key defined against incorrect table

A named foreign key exists against the incorrect table.

This will prevent the creation of the same key against the correct tables.

Yes

Foreign key column mismatch

A foreign key's definition does not match what is expected by SYSPRO.

Yes
ClosedObject Dependencies

After the program completes its checks on the databases, the following list is returned containing all user defined objects (i.e. views, stored procedures, etc.) that are dependent on both standard SYSPRO and custom form tables and columns:

This is for informational purposes and no repair functionality is available or applied.
Column Detail

Table

Indicates the SYSPRO table.

Column

Indicates the SYSPRO column.

Schema

Indicates the schema in which the object is defined.

Name

Indicates the name of the object.

Object Type

Indicates the type of object (e.g. View, Stored procedure, Trigger).

Selected

Indicates whether the column is selected (e.g. SELECT StockCode).

Selected all

Indicates whether the column is selected using a wildcard (e.g. SELECT *).

Updated

Indicates that the column is updated by the object and is flagged as a warning.

We strongly advise against updating SYSPRO data outside of the software, as any change may not follow the business rules associated with the data.
ClosedIndex Fragmentation

Index fragmentation and out of date statistics can have a significant effect on performance. Therefore, this pane displays the following:

  • Indexes in SYSPRO that are fragmented by more than 5 percent
  • Indexes' statistics that have not been updated in the last 30 days
Information on user defined indexes is not displayed.

No repair function is available as it is recommended that the rebuilding or reorganization of an index, as well as the updating of statistics, is performed when the database is not in use.

Column Detail

Table

Indicates the SYSPRO table.

Index

Indicates the SYSPRO index.

Fragmentation %

Indicates the percentage of fragmentation.

Page Count

Indicates the number of pages occupied by the index.

If this is a low count then it is possible that SQL will not rebuild or reorganize the index.

Rows in Table

Indicates the number of rows in the table.

If this is a low count then it is possible that SQL will not rebuild or reorganize the index.

Statistics updated

Indicates the date and time that the statistics for this index were last updated.

SQL will automatically update these depending on usage. If the date and time is set as 1900-01-01 0:00:00.00 then the statistics have never been updated.

Suggested function

Indicates the function that Microsoft suggests to run.

Fragmentation %

Suggested action

More than 30%

Rebuild the index.

Between 5% and 30%

Reorganize the index.

Less than 5%

Indicates that the statistics are older than 30 days and you should consider updating them.
ClosedSQL Users

After the program completes its checks on the databases, the following list is returned detailing all users that have access to the database, as well as their permission levels:

Column

Detail

User

Indicates the user defined against the database.

Any sysadmin logins will be returned as blank due to them having full access to the database and not requiring a link to a specific user.

Login

Indicates the login that the user is linked to.

Type

Indicates the type of login (e.g. WINDOWS_LOGIN or SQL_LOGIN)

Permissions

Indicates the user's permissions as a comma separated list (e.g. db_datareader, db_datawriter, sysadmin)
ClosedSQL Instance Information

This pane is automatically hidden and can be accessed from the right-hand side of the screen.

The dashboard displays the following pertinent information regarding the SQL instance that SYSPRO 8 is using:

  • SQL instance name
  • SQL version
  • Default collation for the SQL instance
  • Minimum memory allocated to SQL
  • Maximum memory allocated to SQL
  • Amount of physical memory on the server
  • Date and time SQL started