4

Is it possible to figure out which tables contribute the most to read / writes operations if all of them in same file?

I been looking at the following DMV

SELECT *
FROM sys.dm_io_virtual_file_stats

but it only shows statistics for the whole file and not sure how to split into individual tables. Trying to do this in order to identify tables that should be places in their own files on different disk.

  • 1
    As pointed out this is not directly accessible information but the "Top I/O Consumers" query from Michael Swart Resource Hog Queries can help pinpoint those objects in the plan cache: michaeljswart.com/2013/09/my-queries-for-top-20-resource-hogs – user507 Jul 23 '14 at 16:12
  • That is helpful resource, I found queries that have overlooked previously. Might need to clean up some bad performing queries before I find tables that cause high I/O. – Vladimir Oselsky Jul 23 '14 at 16:30
5

The following two queries give index usage stats per table which should be a good indicator of which tables are causing the most IO operations.

SELECT d.name as [Database],  OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],          
I.[NAME] AS [INDEX NAME],          
USER_SEEKS,          
USER_SCANS,          
USER_LOOKUPS,          
USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S          
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID]               
AND I.INDEX_ID = S.INDEX_ID 
Join sys.Databases d on s.database_id = d.database_id
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 
Order by USER_SEEKS + USER_SCANS + USER_LOOKUPS + USER_UPDATES desc


SELECT d.name, t.name, OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],       
 I.[NAME] AS [INDEX NAME],       
  A.LEAF_INSERT_COUNT,        
  A.LEAF_UPDATE_COUNT,        
  A.LEAF_DELETE_COUNT

FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A        
INNER JOIN SYS.INDEXES AS I          ON I.[OBJECT_ID] = A.[OBJECT_ID]   
join sys.tables t on i.object_id = t.object_id      
join sys.databases d on a.database_id = d.database_id
AND I.INDEX_ID = A.INDEX_ID WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

order by A.LEAF_INSERT_COUNT + A.LEAF_UPDATE_COUNT + A.LEAF_DELETE_COUNT desc
| improve this answer | |
  • After reading documentation on DM_DB_INDEX_OPERATIONAL_STATS at msdn.microsoft.com/en-us/library/ms174281.aspx it might be exactly what I need. But I'm still trying to understand results produced by first query versus the second one – Vladimir Oselsky Jul 23 '14 at 16:11
  • The top query will give you reads while the second will show writes and deletes. The second is also a good indicator of indexes that may be fragmented – James Anderson Jul 23 '14 at 16:33
  • 2
    I'd be careful about upper-casing catalog views and DMVs... on a case sensitive system, those queries might break. Also I think the objectproperty check is redundant, since sys.tables won't contain any non-user tables. – Aaron Bertrand Jul 23 '14 at 17:20
0

There is no direct DMV which will tell you I/O for specific table. I mostly use sys.dm_exec_query_stats DMV which has useful information about query which is taking high Physical and logical reads. Looking at query you can see tables involved if query has multiple table it wont give accurate information but you would know what all tables generate most I/O requests

SELECT TOP 25 
QS.EXECUTION_COUNT AS [EXECUTION_COUNT]      
,QS.TOTAL_WORKER_TIME AS [CPU UTILIZATION]     
,QS.TOTAL_ELAPSED_TIME AS [ELAPSED_TIME]      
,QS.TOTAL_LOGICAL_READS AS [LOGICAL_READS]      
,QS.TOTAL_LOGICAL_WRITES AS [LOGICAL_WRITES]      
,QS.TOTAL_PHYSICAL_READS AS [PHYSICAL_READS]
,ST.TEXT
FROM SYS.DM_EXEC_QUERY_STATS QS         
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) ST
ORDER BY QS.TOTAL_PHYSICAL_READS DESC;

Hope this helps

| improve this answer | |
  • Same comment as above - need to be careful about case sensitive collations. DMVs and catalog views are defined in the system as lower case, so you should have queries that match. – Aaron Bertrand Jul 23 '14 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.