Question / Problem:
Why might SQL Server 2014 and earlier have more issues with blocking queries and slow performance?
Answer / Solution:
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 scheduling 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.
Fixing Outdated SQL Statistics
The following options are available to fix outdated statistics, which lead to performance issues.
Improving Automatic Statistics
Improvements in SQL Server 2016 or Later
Starting with SQL Server 2016 or later (and with database compatibility level set to 130), Microsoft has changed the threshold used to trigger automatic update of statistics such that it depends on the number of rows in the table, rather than a fixed 20%. Thus switching to SQL Server 2016 or later will better allow automatic statistics updates to keep up to date and avoid negative performance impacts.
Trace Flag 2371 in SQL Server 2014 and Earlier
As far back as SQL Server 2008 R2 SP1, Microsoft provided Trace Flag 2371 which allows for enabling the same lower threshold which is used by default in SQL Server 2016. Enabling this flag will better allow automatic statistics updates to keep up to date and avoid negative performance impacts.
For more details from Microsoft see the following links. These cover both the change to default behavior in SQL Server 2016, and enabling the change in lower versions with Trace Flag 2371:
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.