Find most frequently accessed tables in SQL Server 2014 without VIEW SERVER STATE permission-CodePudding

I'm trying to use this query to get the most frequently accessed tables in a database:

SELECT t.NAME AS tname,
    SUM(ius.user_seeks   ius.user_scans   ius.user_lookups) AS accesses
FROM db_name.sys.dm_db_index_usage_stats ius
INNER JOIN db_name.sys.tables t ON t.OBJECT_ID = ius.object_id
GROUP BY database_id, t.name
ORDER BY accesses DESC

But it fails with the error

Msg 300, Level 14, State 1, Line 1
VIEW SERVER STATE permission was denied on object 'server', database 'master'.

Understandably, our backend hosting company doesn't want to give out the VIEW SERVER STATE permission to just any random intern. Is there a query (or a tool within SQL Server) that might achieve the same or similar results without needing this permission?

CodePudding user response：

You can also use this:

SELECT t.name, range_scan_count singleton_lookup_count AS accesses, * 
FROM sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL) os
INNER JOIN sys.tables t ON t.object_id = os.object_id
ORDER BY accesses DESC

This should work with the VIEW DATABASE STATE permission (even if you don't have VIEW SERVER STATE permission).

However, this query does not return the same thing as your query. See the differences here: http://web.archive.org/web/20180410202012/http://sqlblog.com:80/blogs/paul_white/archive/2011/02/17/Seeking-Without-Indexes.aspx