Hi we run std 2008 r2.  I'd like to monitor what processes (if any) are using 10 tables on 3 different databases between certain hrs.  Two of the dbs are on one server.  If a process is determined to be using any of them i'd like to know what account and from what server it is running and anything else I can glean about that process as well as how it is using the table(s).  I may want to monitor for 3 or 4 months.

My understanding is that sql profiler is heavy handed in this kind of thing.   Can the community make recommendations? 

 An afterthought...if anything is happening on the db's themselves that might affect table availability, I guess i'd want to know that too.

• Edited by db042190 Friday, August 21, 2015 1:45 PM afterthought

thx r u saying I should keep running these every so often, perhaps every second or minute?  Wouldn't that potentially let one slip thru the cracks?

I don't see a table name or more than first word of query in first view.  I don't see table name in second.

I don't believe there is a way to determine what process, account etc on either.

• Edited by db042190 Friday, August 21, 2015 1:42 PM more concerns

How about a Server Audit? You can schedule start and stop of the audit, and define whatever tables in the database that you want to audit. Requires Enterprise Edition, though.

Tracing (whether client side, Profiler, or Server-side) is difficult since the tables can be accesses indirectly. Consider stored procedures and views, for example. I think that in the end you need to capture execution plan (phew) and parse those.

What do u think?

I think you want to start fiddling with it in dev, when you think you have a nice trace script try it out in prod.

Have to still hope that sampling will get you the info you want without going 100%, but again, try and see.

Josh