How to find out SQL Server table's read/write statistics?

Question

Is there a way to find a statistics on table read and write count on SQL Server 2005/2008?

I am specifically looking for DMVs/DMFs without using triggers or audits.

The goal here is to find out a**ppropriate fill factor for indexes** - got an idea from this article (Fill Factor Defined).

---

[UPDATE] Follow up question on ServerFault
How to determine Read/Write intensive table from DMV/DMF statistics

Answer 1

• sys.dm_db_index_physical_stats (size and fragmentation)
• sys.dm_db_index_usage_stats (usage, number of scans/seeks/updates etc)
• sys.dm_db_index_operational_stats (current activity on index)

Remember 'table' means the clustered index or the 'heap'.

Answer 2

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:

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).

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%