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 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
Remember 'table' means the clustered index or the 'heap'.
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
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
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%
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.
