Monitor DB Usage at Column Level

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@.discussions.microsoft.com) writes:
> 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.
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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"Erland Sommarskog" wrote:

> Hari Seldon (HariSeldon@.discussions.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@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Thanks. I really appreciate the info. I had a feeling this wasn't going to
be easy.
Hari
"Erland Sommarskog" wrote:

> Hari Seldon (HariSeldon@.discussions.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@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>