Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 –  Shawn Melton Jul 23 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 at 16:30

2 Answers 2

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
share|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 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 at 16:33
1  
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 at 17:20

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

share|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 at 17:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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