Track Reads and Writes

Question

Track Reads and Writes

am-244616

SSCarpal Tunnel

Points: 4936
More actions
March 5, 2008 at 3:23 pm

#120331

GUys,
Is there any way track tables which have most no of reads and writes from a database of 400 tables.
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 1

You can run a profiler trace for a given period of time. The trace will include reads and writes and this data can be exported to a database. Be careful when running a trace as a high volume database can create hundreds of thousands of records very quickly and can overwhelm your system.

DAB

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

You could use the index DMV's (sys.dm_db_index_usage_stats) to get an idea of how frequently your tables are hit (assuming they're indexed properly), but other than that I'm not sure what other options you have outside of profiler if you are targeting tables vs. databases.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

This will give you all your i/o since your last SQL restart:

SELECT

DB_NAME(database_id) AS database_name

,FILE_ID

,sample_ms

,num_of_reads

,num_of_bytes_read

,io_stall_read_ms

,num_of_writes

,num_of_bytes_written

,io_stall_write_ms

,io_stall

FROM

sys.dm_io_virtual_file_stats(NULL, NULL)

Bob Fazio SSChampion Points: 10794 More actions · Answer 4

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Then from the performance dashboard, you can drill down to what you want.

It makes the DM views much more accessible.