Issue
The new scheduled SQL maintenance functions don't appear to defragment the events table, whilst the trendlogvalues table has been defragmented.
Environment
Vista 5.1.7
SQL 2008 R2 SP1
Cause
A Microsoft SQL Server database is prone to data fragmentation and this is also true for the new TAC Vista SQL database layout introduced. It is recommended to keep the SQL database index fragmentation level at a low level to ensure performance in the TAC Vista system.
A new feature has been added to TAC Vista Server to schedule regular maintenance tasks on the TAC Vista SQL database to prevent the index fragmentation level to obstruct performance deterioration; this feature is called SQL Maintenance.
The SQL Maintenance feature is supported for both the new and the previous database layouts and is found in the TAC Vista Server Setup tool. TAC Vista Server Setup will also display the database layout used by TAC Vista. An event will be created in the TAC Vista Workstation event log when the maintenance task starts and ends. This event is created by TAC DSS Writer Service which launches the maintenance task. If a slave node is down in a TAC Vista network these events will not be captured by the Master node.
Resolution
TAC Vista SQL Maintenance feature will only execute if the total amount of data pages exceeds 1000 for the database. Maintenance should not be run when there is a low amount of data pages in a table. The total amount of data pages for a table is found in the index properties which can be seen using Management Studio.
- Open SQL management studio and connect to the tacvista database instance.
- Locate the taclogdata (default) database and expand the database in the tree.
- Under tables\event\Indexes locate PK_Events(clustered) and right-click on it.
-
In the Index properties dialogue, under Select a page, select Fragmentation. The pages property can be seen in this page.
- Repeat the process for tables\trendlogvalue\Indexes\PK_Trendlogvalue(Clustered).
