I'm looking for a tool that will compile historical usage information for a
specific SQL Server 2000 database down to the column-level. This is a
read-only database and I need to know how many times each column of each
table has been included in a query over a period of time. This is a
production database, so it has to be a tool that won't have a major impact on
performance.

Any suggestions?

Thanks,
Hari

Hari Seldon (HariSeldon (AT) discussions (DOT) microsoft.com) writes:
You would have to run a server-side trace that captures the SP:StmtCompleted
and SQL:StmtCompleted events. Exactly what columns you should include
in the trace depends on your needs, but a minimum you would have to
include TextData. The trace should save data to a file.

The performance impact on such a query is not negligible, particularly if
users run many small queries. (If they main run long-running queries, the
number of events to track is much smaller.)

The easiest way to set up such a trace is to use Profiler, and then save
the trace as a script.

That was the easy part. Once you have the trace data, you need to analyse
it, one way or another. Unless you application generates command in a way
that makes things easy for you, for instance it always uses
tablename.columnname, this is a difficult task, and I don't know
of any tool that does this, thereby not said that it does not exist.
Presumably, a program would have to read the file, and parse each query to
determine which columns that are referred to in the query.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" wrote:

Thanks. I really appreciate the info. I had a feeling this wasn't going to
be easy.

Hari

"Erland Sommarskog" wrote:

Hey
If your looking for something simple, check this free tool :
"Actively queries data at a given interval and lets you know of data
changes that occur over time. Interactive reports, email notifications,
generates scripts to mimick operations that happen on the data across any
given timespan."

Free at : http://nobhillsoft.com/Freebies.aspx

Or you can check the better DB compare tool, but its not for free..
http://nobhillsoft.com/NHDBCompare.aspx

"Hari Seldon" wrote: