Find  which tables are most accessed or frequently used in SQL server

Varshini S
Varshini S used Ask the Experts™
on
How do I find out which tables are most accessed or frequently used in SQL server ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
Try this:

select * from sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL)

More details at:
http://sqlblog.com/blogs/paul_white/archive/2011/02/17/Seeking-Without-Indexes.aspx


Or this:


SELECT DB_NAME() + '.' + object_name(b.object_id), a.user_seeks, a.user_scans, a.user_updates
FROM sys.dm_db_index_usage_stats a
RIGHT OUTER JOIN sys.indexes b on a.object_id = b.object_id and a.database_id = DB_ID()
--WHERE b.object_id > 100

http://jeffstevenson.karamazovgroup.com/2011/10/sql-server-most-utilized-tables.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial