SQL Server Performance Forum – Threads Archive

Table Analysis

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 [email protected] Thank you

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/

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.

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).

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.

]]>