exec sp_updatestats是做什么的？ [英] What does exec sp_updatestats do?

问题描述

sp_updatestats 有什么用？我可以在生产环境中运行它以提高性能吗？

What is the use of sp_updatestats? Can I run that in the production environment for performance improvement?

推荐答案

sp_updatestats 更新数据库中所有表的所有统计信息，甚至单行都已更改。它使用默认示例执行此操作，这意味着它不会扫描表中的所有行，因此它产生的统计信息可能会比其他方法差。

sp_updatestats updates all statistics for all tables in the database, where even a single row has changed. It does it using the default sample, meaning it doesn't scan all rows in the table so it will likely produce less accurate statistics than the alternatives.

包含重建索引的维护计划，它还可以刷新统计信息，但更精确，因为它可以扫描所有行。重建索引后无需重建统计信息。

If you have a maintenance plan with 'rebuild indexes' included, it will also refresh statistics, but more accurate because it scans all rows. No need to rebuild stats after rebuilding indexes.

使用 update statistics 命令手动更新特定的统计对象或表您可以更好地控制流程。要使其自动化，请查看此处。

Manually updating particular statistics object or a table with update statistics command gives you much better control over the process. For automating it, take a look here.

仅当优化程序决定必须执行时，才会触发自动更新。 2012年的数学有所变化：在< 2012中，表格行每变化500 + 20％，就会触发自动更新；在2012年以上为SQRT（1000 *表格行）。这意味着在大桌子上更频繁。临时表的行为当然会有所不同。

Auto-update fires only when optimizer decides it has to. There was a change in math for 2012: in <2012, auto update was fired for every 500 + 20% change in table rows; in 2012+ it is SQRT(1000 * Table rows). It means it is more frequent on large tables. Temporary tables behave differently, of course.

总而言之， sp_updatestats 实际上可能造成的损害大于利弊，并且最不推荐的选项。

To conclude, sp_updatestats could actually do more damage than good, and is the least recommendable option.

这篇关于exec sp_updatestats是做什么的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！