Is there a way to find a statistics on table read and write count on SQL Server 2005/2008?
是否有办法找到SQL Server 2005/2008表读和写计数的统计数据?
I am specifically looking for DMVs/DMFs without using triggers or audits.
我特别想在不使用触发器或审计的情况下查找DMVs/DMFs。
The goal here is to find out appropriate fill factor for indexes - got an idea from this article (Fill Factor Defined).
这里的目标是为索引找到合适的填充因子——从本文中得到一个概念(填充因子定义)。
[UPDATE] There is a follow up question on ServerFault
How to determine Read/Write intensive table from DMV/DMF statistics[更新]服务器故障的后续问题是如何从DMV/DMF统计信息中确定读/写密集型表
4 个解决方案
#1
8
- sys.dm_db_index_physical_stats (size and fragmentation)
- sys。dm_db_index_physical_stats(大小和碎片)
- sys.dm_db_index_usage_stats (usage, number of scans/seeks/updates etc)
- sys。dm_db_index_usage_stats(使用、扫描次数/查找次数/更新次数等)
- sys.dm_db_index_operational_stats (current activity on index)
- sys。dm_db_index_operational_stats(索引中的当前活动)
Remember 'table' means the clustered index or the 'heap'.
记住“table”是指聚集索引或“heap”。
#2
17
Following query can be used to find number of read and writes on all tables in a database. This query result can be exported to CSV file and then using excel formulas you can easily calculate read/write ratio. Very useful while planning indexes on a table
以下查询可用于查找数据库中所有表上的读和写数量。这个查询结果可以导出到CSV文件，然后使用excel公式可以很容易地计算读/写比率。在规划表上的索引时非常有用
DECLARE @dbid int
SELECT @dbid = db_id('database_name')
SELECT TableName = object_name(s.object_id),
Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = @dbid
GROUP BY object_name(s.object_id)
ORDER BY writes DESC
#3
3
To determine an appropriate fill factor for a table's indexes, you need to look at the number of page splits occuring. This is shown in sys.dm_db_index_operational_stats:
要为表的索引确定适当的填充因子，您需要查看正在发生的页分割的数量。这在sys.dm_db_index_operational_stats中显示:
Leaf allocation count: Total number of page splits at the leaf level of the index.
叶分配计数:索引的叶级上的页分割的总数。
Nonleaf allocation count: Total number of page splits above the leaf level of the index.
非叶分配计数:在索引的叶级之上分割的页面总数。
Leaf page merge count: Total number of page merges at the leaf level of the index.
叶页合并计数:页面的总页数合并在索引的叶级。
After doing a bit of digging, I've seen a few posts that say the page split numbers from the DMV's are not that useful (I haven't personally confirmed this), but there is also a performance counter "page splits/sec" (but it's is only at SQL Server instance level).
在做了一些挖掘之后，我看到了一些文章，说从DMV的页面分割号不是很有用(我还没有亲自确认这一点)，但是还有一个性能计数器“页面分割/秒”(但它只在SQL Server实例级)。
I use the rule of thumb that ordinary tables use the default 90% fill factor, high insert tables somewhere between 70 - 85% (depending on row size). Read only tables can utilise a fill factor of 100%
我使用经验法则，普通表使用默认的90%填充因子，高插入表在70 - 85%之间(取决于行大小)。只读表可以使用100%的填充因子
#4
1
If you have a good clustered index (i.e., ever increasing, unique, narrow) then the real determining issues for Fill Factor are how the table is updated and the data types of the columns. If the columns are all fixed size (e.g., integer, Decimal, Float, Char) and non-nullable then an update cannot increase the storage required for a row. Given the good clustered index you should pick a Fill Factor of 90+ even 100 since page splits won't happen. If you have a few variable length columns (e.g. a Varchar to hold User Name) and the columns are seldom updated after insert then you can still keep a relatively high Fill Factor. If you have data that is highly variable in length (e.g., UNC paths, Comment fields, XML) then the Fill Factor should be reduced. Particularly if the columns are updated frequently and grow (like comment columns). Non-Clustered indexes are generally the same except the index key may be more problematic (non unique, perhaps not ever increasing). I think sys.dm_db_index_physical_stats gives the best metrics for this but it is after the fact. Look at the avg/min/max record size, avg frag size, avg page space used to get a picture of how the index space is being used. HTH.
如果您有一个好的聚集索引(例如。因此，填充因子的真正决定性问题是如何更新表和列的数据类型。如果列都是固定大小(例如，integer、Decimal、Float、Char)和non-nullable，则更新不能增加行所需的存储。考虑到良好的聚集索引，您应该选择90+甚至100的填充因子，因为页面分割不会发生。如果您有一些可变长度的列(例如一个Varchar来保存用户名)，并且在插入之后很少更新列，那么您仍然可以保持较高的填充系数。如果数据的长度变化很大(例如UNC路径、注释字段、XML)，那么填充因子应该减少。特别是如果列经常更新并增长(如注释列)。非聚集索引通常是相同的，但是索引键可能会有更多的问题(非惟一的，可能不会增加)。我认为系统。dm_db_index_physical_stats提供了对此的最佳度量，但它是在事实之后。查看avg/min/max记录大小，avg frag大小，avg页面空间用于获得索引空间的使用情况。HTH。
#1
8
- sys.dm_db_index_physical_stats (size and fragmentation)
- sys。dm_db_index_physical_stats(大小和碎片)
- sys.dm_db_index_usage_stats (usage, number of scans/seeks/updates etc)
- sys。dm_db_index_usage_stats(使用、扫描次数/查找次数/更新次数等)
- sys.dm_db_index_operational_stats (current activity on index)
- sys。dm_db_index_operational_stats(索引中的当前活动)
Remember 'table' means the clustered index or the 'heap'.
记住“table”是指聚集索引或“heap”。
#2
17
Following query can be used to find number of read and writes on all tables in a database. This query result can be exported to CSV file and then using excel formulas you can easily calculate read/write ratio. Very useful while planning indexes on a table
以下查询可用于查找数据库中所有表上的读和写数量。这个查询结果可以导出到CSV文件，然后使用excel公式可以很容易地计算读/写比率。在规划表上的索引时非常有用
DECLARE @dbid int
SELECT @dbid = db_id('database_name')
SELECT TableName = object_name(s.object_id),
Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = @dbid
GROUP BY object_name(s.object_id)
ORDER BY writes DESC
#3
3
To determine an appropriate fill factor for a table's indexes, you need to look at the number of page splits occuring. This is shown in sys.dm_db_index_operational_stats:
要为表的索引确定适当的填充因子，您需要查看正在发生的页分割的数量。这在sys.dm_db_index_operational_stats中显示:
Leaf allocation count: Total number of page splits at the leaf level of the index.
叶分配计数:索引的叶级上的页分割的总数。
Nonleaf allocation count: Total number of page splits above the leaf level of the index.
非叶分配计数:在索引的叶级之上分割的页面总数。
Leaf page merge count: Total number of page merges at the leaf level of the index.
叶页合并计数:页面的总页数合并在索引的叶级。
After doing a bit of digging, I've seen a few posts that say the page split numbers from the DMV's are not that useful (I haven't personally confirmed this), but there is also a performance counter "page splits/sec" (but it's is only at SQL Server instance level).
在做了一些挖掘之后，我看到了一些文章，说从DMV的页面分割号不是很有用(我还没有亲自确认这一点)，但是还有一个性能计数器“页面分割/秒”(但它只在SQL Server实例级)。
I use the rule of thumb that ordinary tables use the default 90% fill factor, high insert tables somewhere between 70 - 85% (depending on row size). Read only tables can utilise a fill factor of 100%
我使用经验法则，普通表使用默认的90%填充因子，高插入表在70 - 85%之间(取决于行大小)。只读表可以使用100%的填充因子
#4
1
If you have a good clustered index (i.e., ever increasing, unique, narrow) then the real determining issues for Fill Factor are how the table is updated and the data types of the columns. If the columns are all fixed size (e.g., integer, Decimal, Float, Char) and non-nullable then an update cannot increase the storage required for a row. Given the good clustered index you should pick a Fill Factor of 90+ even 100 since page splits won't happen. If you have a few variable length columns (e.g. a Varchar to hold User Name) and the columns are seldom updated after insert then you can still keep a relatively high Fill Factor. If you have data that is highly variable in length (e.g., UNC paths, Comment fields, XML) then the Fill Factor should be reduced. Particularly if the columns are updated frequently and grow (like comment columns). Non-Clustered indexes are generally the same except the index key may be more problematic (non unique, perhaps not ever increasing). I think sys.dm_db_index_physical_stats gives the best metrics for this but it is after the fact. Look at the avg/min/max record size, avg frag size, avg page space used to get a picture of how the index space is being used. HTH.
如果您有一个好的聚集索引(例如。因此，填充因子的真正决定性问题是如何更新表和列的数据类型。如果列都是固定大小(例如，integer、Decimal、Float、Char)和non-nullable，则更新不能增加行所需的存储。考虑到良好的聚集索引，您应该选择90+甚至100的填充因子，因为页面分割不会发生。如果您有一些可变长度的列(例如一个Varchar来保存用户名)，并且在插入之后很少更新列，那么您仍然可以保持较高的填充系数。如果数据的长度变化很大(例如UNC路径、注释字段、XML)，那么填充因子应该减少。特别是如果列经常更新并增长(如注释列)。非聚集索引通常是相同的，但是索引键可能会有更多的问题(非惟一的，可能不会增加)。我认为系统。dm_db_index_physical_stats提供了对此的最佳度量，但它是在事实之后。查看avg/min/max记录大小，avg frag大小，avg页面空间用于获得索引空间的使用情况。HTH。