Ignite Global Support Home
How can we help?
  1. FogBugz Support
  2. Fogbugz
  3. Database
  4. Performance

Rebuild Database Table Indexes (FogBugz On-Premise)


Botond Bajka
  • Updated
Follow

Overview

It is a recommended practice to rebuild (reindex) FogBugz On-Premise database table indexes on a regular basis. If database table indexes are not rebuilt for an extended period, you might experience performance issues or even unexpected 500 Internal Error due to FogBugz not being able to retrieve the data from the database in a timely manner.

Recommended reading: FogBugz On-Premise Database Maintenance - Cheat Sheet to know more about Microsoft SQL Database maintenance, index fragmentation, and index rebuild.

Diagnosis

Running an SQL database for a long time will make the table indexes fragmented on the disk. The database engine will start to run slower as the database and indexes grow until FogBugz queries take longer than 30 seconds to resolve. This contributes to unwanted timeouts and JSON API errors on the interface.

There are two ways to quickly check if your indexes are fragmented and you might need to rebuild database table indexes:

  • do a simple select on the tables typically containing the biggest amount of data
  • you experience from time-to-time 500 Internal Error 

Do a simple select on a table containing a big amount of data

Perform a select query on the FogBugz database (via SSMS) on one of the following tables: Bug, BugEvent, Attachment. 

Example:

USE Trial1
SELECT TOP 500 * FROM Attachment

Note: If the system takes more than 30 seconds to get results for the first 500 rows, then it indicates that the SQL database performance has been degraded.

500 Internal Error

Due to the poor data retrieval performance of the database, you might experience some slowness when accessing cases and encounter multiple JSON API errors or 500 Internal Error timeouts. Something like this:

JSON API ERROR: Could not load /api/0/notifications/20
500 Internal Error

JSON_API_Error_-_500_Internal_Error.png

Back to the top

Prerequisites

Back to the top

Solution

NOTE: The index rebuild process is a "heavy" process. It might take a long time to execute, and it will put a high load on your database server. Before starting it, please check the following points:

  • Please read what other known issues are causing slow performance. Maybe your issue does not need the heavy process of a full database table reindexing.
  • Execute this task outside business hours to minimize the impact on the end-users. FogBugz will not be available during the table index rebuild process.
  • Because it puts a high load on your database server, be prepared that during the index rebuild, other applications using the same database server might be impacted performance-wise.
  • If your database recovery model is set to Full, index rebuild will result in a huge transaction log size increase. Please make sure you have enough storage space available.

Rebuilding the Indexes

Before jumping into the script, please follow these steps for a safer execution:

  1. Issue a FogBugz database backup before running the index rebuild task. It is highly recommended to make a backup (or take a snapshot, if you are working on a virtual environment) before making any changes to the production data.

  2. Run a database report to identify the size of the data currently stored in your SQL server. When connected to the server with SSMS, right-click on the FogBugz database (ex: trial1) and select Reports > Standard Reports > Disk Usage by Table:

    mceclip1.png

    Reference: Identifying the Size of FogBugz Database

  3. Using SSMS, run the rebuild_indexes.sql script attached to this article and wait for it to finish. Before running it, change Trial1 to the name of your FogBugz database.

Back to the top

Testing

Re-run the Disk Usage by Table report

Re-run the Disk Usage by Table report to compare results. If the index rebuild was successful, you should see a significant index size decrease when comparing the indexes before and after the rebuilding process, mostly for the bigger sized tables. For some smaller tables, the index size will decrease only a small amount, or not at all. This is normal.

You should experience improved performance also while using the FogBugz application.

If you still experience performance issues, please read what other known issues are causing slow performance.

Preventive Maintenance

It is highly recommended to configure a routine maintenance task on your SQL server to run a scheduled SQL server index reorganization and rebuild process on the FogBugz Database.

While it is not mandatory (and out of FogBugz's scope of support), it is an excellent server administration practice that can give significant performance improvements on FogBugz with minimum downtime. This practice keeps your database file working at top performance and prevents any of the unwanted symptoms of a low-performance database system.

Note: We do not endorse in any way the site provided above. However, we strongly recommend its application since the technical content is based on the best practices on database maintenance and administration.

back to top

Related Articles