DMV query - Table Reads & Writes ?

I need to list the tables in my database along with how many reads and writes they are involved in.

Has anyone got an idea of how to achieve this with DMVs (or another method)



more ▼

asked Sep 29, 2010 at 03:03 AM in Default

avatar image

117 1 3 4

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

May be this will help

SELECT S.name, SUM(user_lookups + user_scans + user_seeks) AS Reads, SUM(user_updates) AS Updates FROM sys.dm_db_index_usage_stats v INNER JOIN sys.objects S ON S.object_id = v.object_id WHERE type = 'U' GROUP BY S.name

more ▼

answered Sep 29, 2010 at 03:13 AM

avatar image

10.8k 37 57 51

Please note that these statistics are reset when SQL Server is restarted or certain database properties are changed (OFFLINE->ONLINE, Collation change etc.)

Also if an index is dropped it will disappear from this DMV.

Sep 29, 2010 at 03:36 AM WilliamD

William good point regarding SQL Server restart and Property change. We can tackle this situation by keeping a backup table to store the DMV information.

The later part "Dropping Index", if you don't have an index also, this DMV will get updated because of a table scan.

Sep 29, 2010 at 04:09 AM Cyborg

Thanks guys, the original sql is just fine. I'm looking at splitting a db into filegroups across different drives and that query will enable me to balance the tables in terms of use.

cheers again


Sep 29, 2010 at 05:28 AM rich76

Please do mark Cyborg's answer as correct then - use the tick to the left hand side of his post...

Sep 29, 2010 at 06:22 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
  • to Cyborg, but I would want to find out what you mean by reads and writes. If you mean reads and writes in terms of '1 query = 1 read' then Cyborg's query is pretty much what you want.

If you mean '1 physical i/o read = 1 read' then that's more difficult, as you can't necessarily get the information exactly as you want it. You could get the number of reads performed by queries involving each table using sys.dm_exec_query_stats, but that doesn't separate out the reads by target object. You could also use the sys.dm_io_virtual_file_stats function to get the number of reads at the table level, and then try and apportion those using the data from Cyborg's query - but it would be very hit and miss - a scan would cause a lot more reads than a seek, for example.

more ▼

answered Sep 29, 2010 at 03:40 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

Cyborg was mostly right. I have tweaked it slightly...

 SELECT  S.[name] AS [Name],
         SUM(IUS.user_lookups + IUS.user_scans + IUS.user_seeks) AS [Reads],
         SUM(IUS.user_updates) AS [Updates]
 FROM    sys.dm_db_index_usage_stats IUS
         INNER JOIN sys.objects S 
         ON S.object_id = IUS.object_id
 WHERE   S.type = 'U'
     AND IUS.[database_id] = DB_ID()
 GROUP BY [Name]
 ORDER BY [Name];

This line: AND IUS.[database_id] = DB_ID() is required in order to limit the results to the current dB.

The issue is that OBJECT IDs are unique in a dB but not across the entire instance. Therefore stats for a given ID may be collected from multiple databases unless we filter them out. What may be a base table in the dB of interest could be an extremely busy Stored Proc in another and greatly skew your results.

And, as has been stated by @WilliamD, this DMV is only good since the last reset. A row is added to sys.dm_db_index_usage_stats only as an index is accessed. If you want your report to show all indexes (including the ones that have NOT been accessed) you'll have to do a little extra logic to pull that data out.

The original request was to determine busy indexes/tables so this should do.

more ▼

answered Sep 30, 2010 at 07:08 AM

avatar image

12.1k 30 36 42

By the way... I did some testing and with the match on dB ID I found an object that was accessed ~1,500 times. I removed the match and the object's access rose to ~162,000. After digging a little deeper it turned out to be a Stored Procedure in Master. More ammo for the holistic approach!

Oct 04, 2010 at 06:18 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

I'd want to drill down on what specifically you're looking for and why, but, in terms of measuring reads against tables, no, there's really not a way to do reliably do just that.

I'd say, that in general, it's not really a question of which tables are being accessed so much as if the access that is being done is being done efficiently. That's why most performance tuning focuses on some combination of these two sets of measures: what waits and queues are occurring on the system, and/or which queries are causing the most pain. In general, focusing on either or both of these sets of measures will arrive at what you need. While working with them, you may find the need to drill down to determine which statistics, indexes, file groups, files, etc., are being accessed, but again, with a focus on "is that access efficient" not so much "is it occurring."

But I'm honestly not sure what exactly you're going for here. So this might be a bit off in terms of advice.

more ▼

answered Sep 29, 2010 at 04:25 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 29, 2010 at 03:03 AM

Seen: 4538 times

Last Updated: Sep 29, 2010 at 03:13 AM

Copyright 2018 Redgate Software. Privacy Policy