Problem Definition

MSSQL and PostgreSQL DBs require to run maintenance job regularly to update statistics on the database. Not doing this leads to using the wrong plan by the query optimizer and this later causes slow performance for Select queries. See JSWSERVER-15917 and JRASERVER-63002 as an example.

Quote

Our DBA is updating the statistics on the two tables every hour between the hours of 6am and 11:59:59PM. He was also more aggressive in the index maintenance that runs nightly at 2am performing more index rebuilds (which automatically updates all statistics on the table the index supports) instead of performing index reorgs.

Suggested Solution

Update the "Connecting Jira applications to a database" docs, specifically pages:

• https://confluence.atlassian.com/adminjiraserver/connecting-jira-applications-to-postgresql-938846851.html
• https://confluence.atlassian.com/adminjiraserver/connecting-jira-applications-to-sql-server-2017-970613480.html

and mentioned the need to run maintenance job daily.
We can use examples from related KB: https://confluence.atlassian.com/kb/optimize-and-improve-postgresql-performance-with-vacuum-analyze-and-reindex-885239781.html

for MS SQL:

Daily for hot tables - UPDATE STATISTICS <table.name>
Weekly for all Jira DB - UPDATE STATISTICS with fullscan;   

Workaround

None