Wednesday, September 28, 2016

How to get the most accessed tables from SQL Server database

While discussing with one of my clients, this question raised but I knew that there is no direct way unless we use something related to indexes. So the query is simple, all we have to do is, get the total count of all scans, seeks and lookups for particular indexes;

SELECT 
 t.name AS TableName,
 SUM(u.user_seeks + u.user_scans + u.user_lookups) AS NumberOfTimeAccessed
FROM sys.dm_db_index_usage_stats u
 INNER JOIN sys.tables t 
  ON t.object_id = u.object_id
WHERE database_id = DB_ID()
GROUP BY t.name
ORDER BY SUM(u.user_seeks + u.user_scans + u.user_lookups) DESC

Is this 100% accurate? As per my understanding, this is not 100% accurate. You might get larger numbers when you have multiple indexes but if you all want is to know which tables get more hit, this does a reasonable job.

Does this capture the tables that are based on heap structure? Yes of course, still you see the count on heap tables too.

No comments: