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

thx Tibor we run std 2008.    r u saying a proc's sql doesn't show in profiler?  Just the name of the proc? 

I can do a where used and even if views and procs show up, I suppose I can add them to my script. 

There is a potential loophole already (I suppose) if this server is linked from a remote proc.

 

r u saying a proc's sql doesn't show in profiler?  Just the name of the proc? 

No, profiler can show you each line that runs inside a proc, of course that costs more, and you can even record only lines that contains the name of one of your ten tables (well it's lexical, so the names might get confused in some cases).  But that won't show some indirect references like foreign keys, I'm not sure what else Tibor had in mind.

Parsing the execution plan is a great idea, but very complicated, maybe not even always possible.

Again, suggest you start with sampling just the big guys, that's cheap and easy, and a lot better than nothing, and the first step in developing more complete solutions.

Josh

I started following the instructions at https://www.mssqltips.com/sqlservertip/1715/scheduling-a-sql-server-profiler-trace/ and feel like this isn't going to work with what I would consider a reasonable investment of time.   Like u said it looks limited to one object name and for the few seconds it ran while I was capturing the definition, I got a lot of stuff that doesn't even have my  object name in it.  I'm going to look for a better article.

I guess it all boils down the how much you are willing to spend with the trace definition, and what amount of overhead you are willing to accept.

I just browsed through the available events and columns. I didn't find an obvious event to catch for which we also can filter on OjectId (the ID for the table in question). So you can catch the text for the SQL statements and try to filter based on that. But what we the table is accessed through a view? Or, as Josh exemplified, indirectly a foreign key? In the end, I believe that from a tracing perspective, you *might* be able to do a fool-proof object "auditing" using the execution plan, but from a practical perspective I doubt it. So, from a realistic perspective, your options are limited and I'd go for polling the DMV's - hopefully it will be good enough.

I haven't given up yet.  I'm open to capturing everything and wading thru sql profiler info for evenings when we know something interfered with one of our processes. 

I created the proc (that can be run by agent) described by the author at the link I provided. 

Big question (for me) .  Can that proc profile activity on another server?  I'm reluctant to add it to the server i'm monitoring. 

Can it at least be added to and run from a different db than the one its monitoring on the same server ?   

The work is done y the database server so "on another server" doesn't come into play in the first place. And it will write the output to a file so "a different db" is also a non-relevant concept. You can write to a *file* on a different server (a share), provided that the service account ffor the SQL Server service has permissions on that share.

I started following the instructions at https://www.mssqltips.com/sqlservertip/1715/scheduling-a-sql-server-profiler-trace/ and feel like this isn't going to work with what I would consider a reasonable investment of time.   Like u said it looks limited to one object name and for the few seconds it ran while I was capturing the definition, I got a lot of stuff that doesn't even have my  object name in it.  I'm going to look for a better article.

I'm suggesting you use cmdtext like %tablename%, and you can give multiple names to match, you have to use that on just statement or RPC completion, and I forget, but may have to also specify "only record on match".

Work on the filter values in the interactive Profiler GUI then have it write the script for you, with that as an example typically you can quickly see how to modify directly.  If not, go back and work the GUI again until you get it!

Profiler is well worth spending some time to get familiar with - profiler and the trace script version of same.

(also - filter only statements with duration > 1000 ms for starters, see how much traffic that gets you, then adjust it up or down as appropriate.  you almost never want to capture *everything* profiler can see, that can generate gigabytes of noise per minute and that much capture will slow down the server, but with reasonable filters it's invisible)

You can probably do a lot of it with event handlers as well in SQL 2008, but I never have.

Josh

• Edited by JRStern 7 hours 56 minutes ago