In Equitrac Office and Equitrac Express, performance issues are encountered, such as when you attempt to generate a large report, it might take a very long time for the process to complete. It might appear as if the system has stopped responding, and the report is never created. Performance can be manifested in other ways as well, such as login issues, printing issues, general slowness, etc.
Microsoft SQL Server has a feature referred to as snapshot isolation. This feature provides Equitrac the ability to perform its regular functions quickly and without conflict even when multiple activities are taking place. This must be enabled for proper operation of the software. Nuance requires this setting to be enabled to provide support.
Run the following query against the Equitrac database (default name is eqcas) to check if it is enabled:
select is_read_committed_snapshot_on from sys.databases where name = 'eqcas'
- A return code of '1' means enabled
- A return code of '0' means not enabled.
To enable snapshot isolation, please refer to the Equitrac installation guide which is by default located in:
C:\Program Files\Equitrac\[Office or Express]\Documentation
(or in older versions - C:\Program Files\Equitrac\[Office or Express]\Docs
See the section in Chapter 2 titled Configuring the Database.
In the C:\Program Files\Equitrac\
(In older versions of Equitrac, the script is called EQCASSQLEXPR2005.sql)
The following information provides a simplified explanation of what snapshot isolation does. For a formal explanation, please refer to Microsoft's support documentation:
Without snapshot isolation, if an operation is being performed on some data in a table in the database, SQL 'locks' the entire table until the operation is complete. That way, it guarantees the data it gets is accurate at that time. No other operation can be performed on that database table while the table is locked - not even a read operation.
If an operation is attempted on a locked database table, this may create what is known as a deadlock. SQL will then evaluate the two operations and make a decision on how to proceed. It may wait a short while to try and complete both operations but if the first operation is not completing quickly, say for a large Equitrac report, then SQL considers this an actual deadlock and will 'kill' one of the operations. This is usually based on how long an operation is running, the memory it is using, whether it is read or write, etc.
Now here is where database snapshot isolation comes in. Basically, whenever a database table is being manipulated, a copy is held in the tempdb allowing another operation access to the original data while the first operation continues. In principle, the first query may not be able to collect the data that is currently being added to the system however in terms of an Equitrac report for example, the data returned will be accurate as of the time of the request for the report.
Note: The snapshot isolation cannot be set if there are any connections to the SQL database. So, when running this script, please make sure Equitrac CAS service is stopped and run the script from the master database (it does not target the master database - it just needs a separate location to run from as there can be absolutely no actions on the database when the operation runs - even the snapshot script itself counts)