SQL Server Performance

How to Monitor Object Usage?

Discussion in 'General DBA Questions' started by steve.milner, Nov 7, 2007.

  1. steve.milner New Member

    I am hoping someone can point me in the correct direction.


    I have inherited a database with many tables, views, procedures etc. I suspect that a significant number of these are not needed. What I would like to do is count the number of times that each of these objects are 'used' during the normal operation of the database.
    For example each time tblMyTable has a SELECT, INSERT, UPDATE, DELETE etc performed the counter for that table would incremented.

    Is there a way I can achieve this?
    I'm not looking for chapter and verse on the answer, but just to be pointed in the correct direction.
    Regards,
    Steve

  2. martins New Member

    Hi Steve,
    You can run a trace through SQL Profiler to see what queries are executed against your database. This should help with your analysis.
  3. steve.milner New Member

    OK, let me get this clear.
    I can capture all queries with profiler to a text file and then search that text file for table names, views names and procedure names etc.
    Is this the area I should be looking in?
    I shouldn't be looking into some of the system tables and adding a trigger to them to capture what's going on in some way. Forgive me if this last bit sound a bit vague, but my understanding of this is a bit vague and this was the area I was thinking of.
    Regards,
    Steve
    Steve
  4. martins New Member

    Yip, you can use a trace and save the data from it either in a text file or in a table somewhere. This will then give you the queries and stored procs executed during the time that the trace was running.
    You can create triggers for auditing on all tables if you want, but that is quite a job if you have many tables.
    The only other alternative would be to use a 3rd party tool and have a look at your transaction logs.
    I would advise you to try the trace option first. This would already give you a good indication of what is happening on SQL Server.
  5. martins New Member

    Just to add to your system tables question:
    The system tables (sysprocesses) will give you details about the connections on the server, and won't really tell you which tables are being queried. That is why I wouldn't go with that if I were you.
  6. steve.milner New Member

    Many thanks, this will help me do what I need

Share This Page