January 26, 2011 at 8:44 am
is it possible to track how many queries each user executed per day on a particular instance?
January 26, 2011 at 8:47 am
Not without continual logging. Also, what would you use this for?
CEWII
January 26, 2011 at 8:53 am
just wondering if this is possible by using sys.dm_exec_query_stats . So can you please help me on how to do this, i need this for reporting purpose?
January 26, 2011 at 9:02 am
if there are any scripts already on this forum, that would be great .
January 26, 2011 at 9:05 am
No, that wouldn't help, keep in mind that the individual user stats are dropped at the end of a session. A session could last for 1 query or all day. Number of queries in a day tells you nothing and SQL does not provide an easy way to determine it. You might be able to trace the queries and get a count that way but I gotta say there is NO WAY I would do that. The load on the server to capture that data would be pretty heavy, and it doesn't provide useful data. I would STRONGLY recommend against this approach.
It sounds like you are trying to generate some kind of usage report. Things like IO and CPU counts would seem to be more useful. Realistically you could have a user exec 10 queries that used 10000 CPU and 1,000,000 IOs and another user exec 10 queries that used 50 CPU and 1100 IOs. This is not theoretical, you can see this on virtually every production system out there. Where I'm going with this is that what you are trying to measure doesn't provide a useful picture of what has gone on on the server throughout a day.
CEWII
January 26, 2011 at 12:11 pm
Elliott is Correct. Follow his advice.
But if you are bent on doing this for any special reason, then you can run a profiler trace, log the results into a SQL Server table and then use a TSQL query and group the results by the login name. This can help you up to some extent. If you are doing this then try to run the profiler as a server side trace from a remote machine and select ONLY and ONLY the needed columns which would drastically reduce the load on the source server.
Thank You,
Best Regards,
SQLBuddy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply