Is it possible to find the number of reads and writes performed against each tables in a database? We can depend on sys.dm_db_index_usage_stats DMV to achieve this partially. The columns user_lookups, user_scans, user_seeks, user_updates are counters and gets incremented for each individual seek, scan or update on the specific index on a table when a query is executed.
Here is the query,
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'
AND database_id = DB_ID()
GROUP BY S.name
The Reads column displays the number of times sql server accessed the indexes on that table and, the Updates column represent the number of time sql server performed update/write operation on the indexes of a table.
For example – I have a table, Employee
CREATE TABLE Employee
(
ID INT PRIMARY KEY,
EmpName NVARCHAR(30),
DeptID TINYINT
)
CREATE INDEX IX_NCL_Employee_EmpName
ON Employee(EmpName)
CREATE INDEX IX_NCL_Employee_DeptID
ON Employee(DeptID)
Our Employee table has three indexes, One Clustered Index on column ID and the rest are non-clustered index on EmpName and DeptID. For each insert against the Employee table SQL Server needs to do update the underlying indexes on the table also, in our case it is three. If we are doing an update on ID column then SQL Server needs to update the three indexes, because when you update a clustered index all the non-clustered indexes are also get updated. What if we update DeptID of that table? This time SQL Server needs to update two indexes, index on DeptID and the Clustered Index.
Well, there are some limitations with this query, the major one is that the counters reset when sql server starts. Another limitation is, when we drop any indexes on that table, we will lost reads/update information performed against those indexes. So this method is a temporary solution only!
