Sql-server – How to determine Read/Write intensive table from DMV/DMF statistics

sql serversql-server-2005

This is a follow-up question on Stack Overflow question

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

The goal is the same as the previous question

The goal here is to find out appropriate fill factor for indexes

According to Mitch Wheat's answer, he suggested 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%

Even after finding out index statistics, I am having trouble making sense of the result.

How do you determine whether a table is a high

  • insert
  • read
  • insert/read
    table?

Do you have to take all stats like leaf_insert_count, leaf_delete_count, user_seek/scan/lookup_count etc into account?
or Would I need to look at only few domains?

I would love to see what other ways there are to determine how to determine fill factor for a particular index.

Query used to get index stats,

select  db_name(US.database_id)
    , object_name(US.object_id)
    , I.name as IndexName
    , OS.leaf_allocation_count
    , OS.nonleaf_allocation_count
    , OS.leaf_page_merge_count
    , OS.leaf_insert_count
    , OS.leaf_delete_count
    , OS.leaf_update_count
    , *
from    sys.dm_db_index_usage_stats US
    join sys.indexes I 
        on I.object_id = US.object_id 
        and I.index_id = US.index_id
    join sys.dm_db_index_operational_stats(db_id(), null, null, null) OS
        on OS.object_id = I.object_id and OS.index_id = I.Index_id
where   I.type <> 0  -- not heap
    and object_name(US.object_id) not like 'sys%'
order by    OS.leaf_allocation_count desc, 
        OS.nonleaf_allocation_count desc, 
        OS.leaf_page_merge_count desc,
        US.User_updates desc, 
        US.User_Seeks desc, 
        US.User_Scans desc, 
        US.User_Lookups desc

And sample result.

alt text

Best Answer

I asked a similar question, but specifically for clustered indexes made up of identity fields. Had good answers:

Correct value for fill factor for clustered indexes with surrogate identity keys

I decided to globally set the fill factor on all tables / indexes to 80% and ones with identity clustered indexes 90%

I remove fragmentation daily- rebuilding indexes with fragmentation >=30% and reorganising indexes >5% and <30%.

I record these actions in a table which I review periodically and use as a guide to upping / lowering fill factors.