Diagnostics and Tuning

Reading time ~2 minutes

The diagnostics and tuning panel is launched via the respective command from the "Consistency checking and diagnostics" submenu of the database context menu:

diagnostics_and_tuning_command

Diagnostics

Launched for the first time the panel looks as follows:

diagnostics

Press the "Run" button to create a report containing details on SQL server, database, sessions, etc.:

diagnostics

Use the "Save as" button to save the generated report as a TXT file to your local machine.

Note
 If you encounter a problem, please send us full diagnostic report via e-mail.

Indexes

The "Indexes" tab shows you the fragmentation of the Cinegy Archive database:

indexes

Defragmentation

Note
 This is an online operation.

Modern SQL Servers automatically perform some defragmentation in the background, as the fragmentation of the system indexes significantly affects the SQL Server performance. We suggest you periodically monitor the status of your indexes.

Caution
 To avoid affecting the users' work, defragmentation on the SQL Server must be done when its load is minimal.

All highly fragmented indexes will be shown in red; medium fragmented indexes – in yellow. Also check the "% of fragmentation" to see the fragmentation level. Highly fragmented indexes are automatically checked. Press the "Defragment" button to start the defragmentation process.

Before defragmentation:

indexes_defragmentation_before

After defragmentation:

indexes_defragmentation_after
Note
 The defragmentation may take a while.

Rebuild

Note
 This is an offline operation.

As heavily fragmented indexes can degrade query performance and reduce capacity, you can reorganize or rebuild one or more fragmented indexes in SQL Server for the selected database. To do this, follow the steps below:

  1. Back up your Cinegy Archive database.

  2. Disconnect all clients before you rebuild indexes.

  3. Press the "Rebuild" button.

  4. On the "Active user connection" window that appears, press the "Continue" to proceed with index rebuilding or "Cancel" to abort the operation:

    indexes_rebuild

Note
 Rebuilding may be necessary after changing the Cinegy Archive database structure (e.g. applying some upgrade scripts).
Caution
 It is recommended to leave no active connections to the Cinegy Archive database when performing the rebuilding operation.

After rebuilding:

indexes_rebuild_ready