monitoring usage of about 10 tables between 11pm and 2am

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 17 hours 21 minutes ago afterthought
August 21st, 2015 8:26am

Hi,

  There are some DMVs around index usage statistics which will help you monitor.

http://msdn2.microsoft.com/en-us/library/ms188754.aspx - Dynamic Management Views and Functions

http://msdn2.microsoft.com/en-us/library/ms187974.aspx - Index Related Dynamic Management Views and Functions

If you want to see what queries are running:

SELECT * FROM sys.dm_exec_requests

If you want to see what queries have run and currently reside in cache:

SELECT * FROM sys.dm_exec_query_stats

You can schedule this at your desired time and then push the output to a table and check the usage.

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 8:52am

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 17 hours 25 minutes ago more concerns
August 21st, 2015 9:40am

The DMV which you have to use is Sys.dm_index_uage_stats. Assuming the table you are trying to look has index. Please refer to this blog

Keep running this proc in job every 5-10 mins and dump the output in some othet table and then you can analyze the table

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 9:58am

thx.  One poster there had the same question I do.  Who is the user in any given stat?  And additionally how do u get the query and other things I need?  Also, this sounds kind of porous.  Lots of loopholes that would allow something to fall thru the cracks. 
August 21st, 2015 10:12am

There's no light-weight and certain way to get what you want.

You can use change tracking for modifications, but it's expensive, it doesn't automatically track the processes involved, and it doesn't notice reads at all.

Actually using profiler and/or event monitor is probably a good way to get a *sample*.  With good filtering just catching more expensive queries you can often get a good history without impacting performance, and on most systems this tells you what you want to know.

If you want to track thousands or millions of very quick, little reads as "using tables", that's much tougher, and if you want to get 100% coverage, very tough indeed.  Is that what you have in mind, or do you just want a few heavy users?

Josh

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 1:06pm

I want 100% coverage but believe that if sql profiler's heavy handedness is correlated to traffic, there wont be much at all that time of eve.

I'll want to use agent or equivalent to turn it on and off unattended somehow.  And probably look for filters (my recollection is that they r available but i'm not sure) that will minimize the footprint.

I'll want to read as little as possible from the results I capture.  I remember how difficult it can be to find a little needle in the profiler hay stack. 

What do u think?

August 21st, 2015 1:27pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics