Resolve Jira performance issues after upgrading due to outdated index statistic in the entity_property table

Summary

This article provides suggestions for resolving Jira Data Center performance issues that may occur after an upgrade, caused by outdated index statistics in the entity_property table in the database. By updating these statistics, you can optimize query execution plans and enhance overall system performance.

Environment

• Product: Jira Data Center

• Database: Microsoft SQL Server

• Instance state: Recently upgraded instances

Problem

After upgrading Jira to a newer version, you may experience slow performance, particularly during issue searches or when using JQL queries. Symptoms include:

• Slow page load times

• Timeouts during searches

• High CPU usage on the database server

• Long-running queries involving the entity_property table

Cause

The upgrade process can alter the data distribution in tables or add new features that utilize existing tables differently. Specifically, the entity_property table may have outdated or missing statistics on its indexes after an upgrade. This can lead the SQL Server query optimizer to choose inefficient execution plans, resulting in slower query performance and overall degradation of Jira's responsiveness.

Solution

Update the statistics in the indexes associated with the entity_property table to ensure the SQL Server query optimizer has accurate data to generate efficient execution plans.

While updating statistics is a standard and safe database maintenance task, it's always good practice to perform such operations during a maintenance window to minimize potential impact on users.

Prerequisites:

• Administrative access to the Jira SQL Server database

• Familiarity with executing SQL queries

• Recommended Personnel: It is advisable that a Database Administrator (DBA) or team member with database expertise performs these SQL queries to ensure they are executed correctly and safely.

To resolve the issue:

1. Connect to the Jira database:

   • Open SQL Server Management Studio (SSMS) or your preferred SQL client.

   • Connect to the SQL Server instance hosting your Jira database.

2. Identify the correct schema:

   • By default, Jira databases use the dbo schema.

   • If your database uses a different schema, replace dbo in the SQL commands with your actual schema name.

3. Update index statistics in the entity_property table:

   • Execute the following SQL statements to update statistics in the specified indexes:

     UPDATESTATISTICS[dbo].[entity_property][entityproperty_id_name_key]WITH FULLSCAN;

     UPDATESTATISTICS[dbo].[entity_property][entityproperty_key_name]WITH FULLSCAN;

     UPDATESTATISTICS[dbo].[entity_property][PK_entity_property]WITH FULLSCAN;

     Note:

     • The WITH FULLSCAN option performs a complete scan of the table, providing the most accurate statistics.

     • Replace [dbo] with your schema name if different.

4. Verify the statistics update:

   • Confirm that the statistics have been updated by checking the StatsUpdated column:

     SELECT OBJECT_NAME(s.[object_id])AS TableName, i.name AS IndexName, STATS_DATE(s.[object_id], s.stats_id)AS StatsUpdated FROM sys.stats s JOIN sys.indexes i ON s.[object_id]= i.[object_id]AND s.name = i.name WHERE OBJECT_NAME(s.[object_id])='entity_property';

5. Restart Jira (optional but recommended):

   • Restarting Jira ensures that any cached query plans are refreshed to use the updated statistics.

6. Monitor Jira performance:

   • After performing the updates, monitor Jira to verify that performance has improved.

   • Check for reductions in CPU usage and query execution times.

Additional considerations

Why this happens after an upgrade:

• Upgrading Jira can introduce new functionalities or change how existing features interact with the database.

• The entity_property table may experience changes in data volume or access patterns, making existing statistics outdated.

• Updating statistics ensures that the SQL Server query optimizer has current data to create efficient execution plans post-upgrade.

Regular Maintenance:

• Implement a routine database maintenance plan to regularly update statistics and rebuild indexes.

  • This can be automated using SQL Server Maintenance Plans or scheduled jobs.

  • For detailed guidance, refer to our official documentation on Connecting Jira applications to SQL Server 2017, which includes steps on scheduling automatic updates of statistics.

Monitoring tools:

• Use database monitoring tools to track query performance and index health.

  • Regular monitoring can proactively identify performance issues before they impact users.

References

• Microsoft Docs: UPDATE STATISTICS (Transact-SQL)

• Atlassian Documentation: Upgrading Jira applications

• Atlassian Documentation: Connecting Jira applications to SQL Server

If the issue persists

• Contact Support—if performance issues continue after updating statistics, contact Atlassian Support for further assistance.

• Consult with a database administrator to explore other potential causes, such as hardware limitations or additional database optimizations.