SQL Tables/Fields Used the Most Frequently

Hi Experts,

I've run into a bit of an interesting issue. I was tasked with ID'ing which tables (or more so, fields) are being accessed the most frequently in our SQL Server 2008 database in order to determine the most commonly used fields. I'm able to get table row counts, but this doesn't tell me how long ago the table was accessed (could be old data). I've come across sys.dm_db_index_usage_stats, however I don't have permissions to run it.

So, my question: can anyone think of a way to solve this issue when your permissions aren't too high?

Thanks in advance for all your help!
uga_godawgsAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
No.  You will definitely need access to the system views, at the very least, to determine how frequently a given table is used.
0
 
uga_godawgsAuthor Commented:
Hi Scott,

Thank you for your answer and here's some more detail that might help. I'm able to get to some of the system views, such as sys.partitions, sys.syscolumns, sys.tables and sys.objects. It seems like sys.dm_db_index_usage_stats is one of the few that I cannot access. Does this help develope a solution?

Thanks!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can know when a table was accessed but for that you really need access to sys.dm_db_index_usage_stats.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
uga_godawgsAuthor Commented:
OK. Thanks for both of your help. Any idea what access I would need to request? Hopefully, it's not admin...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Requires VIEW SERVER STATE permission.
0
 
uga_godawgsAuthor Commented:
As always, Experts, I thank you for your help!
0
 
Scott PletcherSenior DBACommented:
Books Online is very helpful on what permissions you'll need for various objects.

For example, look under:
sys.dm_db_index_usage_stats
in BOL, "Permissions", and you'll see the "VIEW SERVER STATE" referenced above.
0
 
uga_godawgsAuthor Commented:
Thanks Scott..wasn't aware of BOL and will definitely take advantage in the future! Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.