Running SQL 2016 Enterprise on Windows 2016
We upgraded to a new version of this product and having slowness issues. The vendor keeps running their tool and tells us sample percentage needs to be 100% for all statistics. The problem is the auto update statistics keeps updating them and so they are not 100% We rebuild them with full scan but once the data changes SQL does its own stats update like its supposed to do.
From their documentation
The Auto update statistics option, located under Database Properties, causes SQL Server to automatically
update statistics when approximately 20% of the table data has changed. When this occurs, SQL Server
uses the default sample percentage (10%) to perform the update. As a result, the sample size is often
too small to capture a true representation of the data distribution. Additionally, this process (and SQL
Server’s periodic checks to see whether the process needs to be done) can cause overhead at
inopportune times and may affect database performance.
However, with this option enabled(i think they meant disabled), query plans are not recompiled after an update of statistics, which
can potentially cause inaccurate plans to persist in SQL Server’s plan cache. Inaccurate plans have the
potential to cause additional I/O, higher CPU, and adverse effects on memory utilization.
Recommendation: Database Administrators are empowered to choose whether the Auto update
statistics option is enabled or disabled. Note the following: If the Auto update statistics database
option within SQL Server is disabled, it is recommended that you clear the plan cache after statistics
are updated. While this may cause a slight degradation to performance initially, the overall benefit of
more accurate query plans is a worthy trade off. If the Auto update statistics database option is
enabled, Database Administrators are encouraged to aggressively manage statistics to ensure that
they are rarely (if ever) automatically updated and that they are always captured with a 100%
This may not be the problem with the slowness, however until they see consistent 100% sample percentage they're not going to focus on the real issue, or this may be the real issue in either case what the thoughts on this?