Outdated SQL Statistics Causing Inefficient Query Plans and Poor Performance

SQL Server: Automatically Update Statistics

A fundamental aspect of how SQL Server works is that statistics are used to decide the most efficient query plan to retrieve the data requested in a query.  By default, SQL Server automatically updates statistics.  In SQL Server 2014 and earlier, these automatic updates only happen once 20% of the contents of the table have been modified.  For larger tables this is not frequent enough to keep accurate statistics efficient query plans.  Less efficient query plans can result in high CPU usage, large memory grants, and more blocking.

Note that while this article is primarily focused on SQL Server 2014 and earlier, in some circumstances there can still be value in a DBA deciding to schedule more frequent statistics updates even in later versions of SQL.

Effect in TotalAgility

Performance in KTA is largely dependent on the database hosting it.  When database statistics are not accurate and inefficient query plans are produced, performance suffers drastically.  This problem can occur with any tables, but typically the tables of the TotalAgility_Documents database are most impacted.  The more data that is in the documents database, and the higher volume of data that moves in and out of the documents database, will result in more impact.  This can then result in reduced performance system wide.

Improving Automatic Statistics

Scheduled Statistics Updates

If upgrading to SQL Server 2016 or globally applying the trace flag are not viable options for the environment, another option is to explicitly schedule a SQL job to update statistics of specific tables.  The potential benefit to this approach is that a DBA can schedule this at predicable intervals that work best based on their knowledge of when the system is most and least used.  While the possibility of outdated statistics is applicable to all tables, the large, frequently changing tables in the documents database are most important. 

The following is an example of a script that can be used to regularly update statistics of the most important tables in the TotalAgility_Documents database:

UPDATE STATISTICS BinaryData WITH FULLSCAN
UPDATE STATISTICS Document WITH FULLSCAN
UPDATE STATISTICS DocumentField WITH FULLSCAN
UPDATE STATISTICS Extension WITH FULLSCAN
UPDATE STATISTICS Field WITH FULLSCAN
UPDATE STATISTICS Folder WITH FULLSCAN
UPDATE STATISTICS ObjectHierarchy WITH FULLSCAN
UPDATE STATISTICS Page WITH FULLSCAN
UPDATE STATISTICS PageExtension WITH FULLSCAN
UPDATE STATISTICS TableRow WITH FULLSCAN

Additional tables can be added if needed as determined by the DBA. 

There is also often a benefit in including the TC_DATA table in the main TotalAgility database:

UPDATE STATISTICS TC_DATA WITH FULLSCAN

Note that scheduling statistics updates can be beneficial for performance even in later versions of SQL Server or when the trace flag is enabled.

Related Setting: Auto Update Statistics Asynchronously

The KTA Best Practices Guide recommends enabling Auto Update Statistics Asynchronously.