SQL Server Performance

Table Analysis

Discussion in 'SQL Server 2005 Analysis Services' started by rmarrerocmx, Apr 18, 2007.

  1. rmarrerocmx New Member

    I am currently for a tool that if possible that can analyze both SQL 2000 & 2005 respective data. This tool should be able to scan all tables, fields, stored procedures, operators, jobs and views in the database to see if they have been utilized for any activity. It should also be to scan the database to identify which of the objects listed above has not been utilized, and sugest a course of action.

    Any information would be greatly appreciated. My email is rafael.marrero@cargomatrix.com. Thank you
  2. ndinakar Member

    What do you mean by 'utilized'? accessed?
    AFAIK, there isnt any tool that tells you whether or not a table is being used by the application.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. MohammedU New Member

    I don't see tools other than sql trace/profiler but you have run it continuouly for certain period of time and load the trace files into a table for analysis...

    If there is any tools in the market, mostly depends on sql trace only...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  4. edzaske New Member

    On this topic I would like to know if anybody has a canned trace script that will do this. I would like to run a trace for some weeks to determine which tables in my database are being used (SELECT, INSERT, UPDATE, DELETE).
  5. satya Moderator

    As suggested you could use PROFILER(SQLTrace) to capture such events, but it may be slow if the server is already stressed for resources.

    Using DMVs you can capture the number of objects that are accessed, refer to the BOL and also on the subject refer:
    http://sqlserver-qa.net/blogs/perft...istics-on-query-plan-optimizer-execution.aspx
    http://sqlserver-qa.net/blogs/perft...g-list-for-the-objects-indexes-using-dmv.aspx

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page