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

Chalcidice

The link in the question current redirects to what seems to be a virus. – Gambill

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

Parent

@Remus: After some investigation, combination of all above mentioned DMV/DMF were what I needed to find out what Fill Factor values to set. I will ask a follow up question later to see if what I have came up with correct or not. Thanks, Remus. – Chalcidice

Answer 2

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

Usherette

can we have a date filter? – Saltwater

No, we cannot have a date filter @PhilipMorris – Screw

Answer 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:

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%

Bainite

@Mitch: Let me see if "page split numbers from the DMV's are not that useful" after updating fill factor on some heavily updated tables with high leaf/non leaf allocation count and get back to you on that issue. Thank you for pointing that out for me. – Chalcidice

Answer 4

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.

Birthroot