March 5, 2013 at 1:48 pm
polkadot (3/5/2013)
So user names are not logged in any system tables, right?
User names are in several of the system tables and DMVs.
And profiler would have to be run as long as necessary (month?....I know, depends) to get a good idea of who is querying the database and when, correct?
Well as long as you want to get a view of. Btw, on a moderately busy server the events you'll need to track usage to the table level may well take 500 MB - 1 GB of disk space an hour.
Have you done this?
No. Never needed to and if anyone asked me to do this I'd do my best to persuade them not to.
Note, if you want to trace to a table level, as opposed to the easy server and slightly harder database level, you either need all code in procedures and 100% accurate dependencies or you need to write or locate a T-SQL parser (or trace and aggregate the lock events which are so frequent that there will likely be unacceptable impact on the server).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2013 at 2:13 pm
If user names are in several of the system tables and DMVs, are you saying that they are not joinable to the index usage system table? Is there any combination of views/tables that can be used for this query?
It seems like a reasonable request. Say, the company is subscribing to a feed, that ends up in a database, that is supposedly valuable information but no one is actually using the data in that database. Would be nice to know...and disconnect that feed...and save money, right?
--Quote me
March 5, 2013 at 2:47 pm
polkadot (3/5/2013)
If user names are in several of the system tables and DMVs, are you saying that they are not joinable to the index usage system table? Is there any combination of views/tables that can be used for this query?
No, I'm saying that the index usage stats DMV contains only aggregated information. ie index 2 on table 1 had 15000 index scans, 56 index seeks, 0 updates. That's all that's there.
There is no DMV or table that stores 'User X accessed Table Y at time Z'. There can't be, it would get impractically large. If you want that information, you put long-running profiler trace or extended events session (or SQL audit quite probably), run it for the time period that you're interested in then analyse the data (and it will be huge amounts of data)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 14, 2013 at 11:55 am
Thank you. I learned a lot.
--Quote me
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply