sql 2016中的统计数据？ [英] statistics in sql 2016?
问题描述
大家好，
我在网上看到 很多 脚本调查sysindexes并更新过时的统计数据;比如下面的那个：
http://sqlcommunity.com/scripts/Post/320/sql-script-update-statistics-in-sql-server
这真的有必要吗？我们不能相信'自动更新统计'吗？！为了证明我的错，我发现的唯一的事情就是这样：
对于非常大的表，这些RT值也非常大 - 在很多情况下，这个值大得令人无法接受。例如，如果您的表有一百万行，则需要进行200,000次修改才能触发重新编译。在许多情况下，在进行200,000次更改之前，不同的查询计划可能会更好。在运行测试之后，您可以设置自己的作业以更频繁地更新某些大表的统计信息，以确保这样做确实可以提高查询性能。
但是，再次，这可以在sql 2016中修复一些小的启发式......这是在sql 2016中修复的吗？想法？
更多信息：令人震惊的是，大多数脚本在网页上找到的行rowmodctr在 sysindexes中，如下所示，它不可靠......
正如您刚刚了解到的那样，RT值是SQL Server识别统计信息过时所需的更改次数。在SQL Server 2005之前的版本中，sysindexes系统表会跟踪名为rowmodctr的列中表中实际发生的变化数
。这些计数器跟踪表或索引的任何行中的任何更改，即使更改是未涉及任何索引或有用统计信息的列。 SQL Server现在使用一组列修改
计数器（colmodctr）值，并为表中的每列维护单独的计数，但计算的非持久列除外。这些计数器不是事务性的，这意味着如果事务开始，将数千行插入表
然后回滚，则不会回滚对修改计数器的更改。与sysindexes中的rowmodctr值不同，
colmodctr值对用户不可见，但在查询优化器内部可用。
含义：我们可以在sysindexes中看到我们的表中有很多变化;我们抱怨优化器没有更新统计信息，但是这些信息不真实，真实的信息保存在不可见的表格中。
自动更新统计数据不会使更新统计数据的需要无效。 这在SQL 2016中也没有变化。
非常大的表上的自动更新统计信息需要在触发之前更改20％的行。 这通常是查询性能无法接受的。
请参阅：
http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/
Hi guys,
I saw many scripts in the web that look into sysindexes and update out-dated statistics; like the one from below:
http://sqlcommunity.com/scripts/Post/320/sql-script-update-statistics-in-sql-server
Is this really necessary? can't we trust the 'auto update statistics'?! to prove my wrong the only thing I found is this:
For very large tables, these RT values are also quite large—and in many cases, unacceptably large. For example, if your table has a million rows, you need to make 200,000 modifications to trigger a recompile. In many cases, a different query plan might be better well before 200,000 changes are made. You can set up your own jobs to update statistics more frequently on certain big tables, after running tests to verify that doing so will indeed improve query performance.
But again, this could be fix in sql 2016 with some minor heuristic... is this fixed in sql 2016? thoughts?
More information: Shockingly, most of the scripts found in the web will look at column rowmodctr in sysindexes, which per below, it is not that reliable...
The RT values, as you just learned, are the number of changes required for SQL Server to recognize that statistics are stale. In versions prior to SQL Server 2005, the sysindexes system table keeps track of the number of changes that had actually occurred in a table in a column called rowmodctr. These counters keep track of any changes in any row of the table or index, even if the change was to a column that wasn’t involved in any index or useful statistics. SQL Server now uses a set of Column Modification Counters (colmodctr) values, with a separate count being maintained for each column in a table, except for computed nonpersisted columns. These counters aren’t transactional, which means that if a transaction starts, inserts thousands of rows into a table, and then is rolled back, the changes to the modification counters are not rolled back. Unlike the rowmodctr values in sysindexes, the colmodctr values aren’t visible to the user but are available internally to the Query Optimizer.
Meaning: we can see in sysindexes that there are a lot of changes in our table; and we complain that the optimizer is not updating the statistics, but that information is not truthful, and the real truthful information is kept in a non-visible table.
Auto update stats does not invalidate the need to update stats. This also is unchanged in SQL 2016.
Auto update stats on very large tables require 20% of the rows to change before it triggers. This is generally not acceptable for query performance.
Please see:
http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/
这篇关于sql 2016中的统计数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
