How to Identify Hot Tables

Does anyone know how to identify the hottest, most active tables in a
database?

We have hundreds of users hitting a PeopleSoft database with hundreds
of tables. We are I/O bound on our SAN, and are thinking of putting
the hottest tables on a solid state (RAM) drive for improved
performance. Problem is: which are the hottest tables? Would like to
do this based on hard data instead of developer/vendor guesses.

Any suggestions are much appreciated.Hi

You could profile system usage, this will not give tables if you are using
stored procedures, but you could work it out knowing which procedures are
being used. Knowing the procedures being called also helps to put things
into context and allows you to examine query plans.

John

"Jeff Roughgarden" <jroughgarden@.stanfordalumni.org> wrote in message
news:b8143af9.0407071314.5bcc4ae3@.posting.google.c om...
> Does anyone know how to identify the hottest, most active tables in a
> database?
> We have hundreds of users hitting a PeopleSoft database with hundreds
> of tables. We are I/O bound on our SAN, and are thinking of putting
> the hottest tables on a solid state (RAM) drive for improved
> performance. Problem is: which are the hottest tables? Would like to
> do this based on hard data instead of developer/vendor guesses.
> Any suggestions are much appreciated.|||My immediate thought was "dbcc memusage" but I looked in the documentation
and it's now obsolete. Too bad. I think that it showed the top objects in
memory, which is almost exactly what you seek.

If I had to approach this myself, I might give some thought to evaluating
the locks taken out. They should give you at least some clue as to what
objects are important to SQL Server (but not all objects locked may be in
memory). I suppose I'd snapshot the lock list periodically and then develop
some processes to evaluate what was locked and summarize the objects locked
and sort of estimate how much storage of each is locked (you have table,
extend, page and row locks to consider). I don't think this will tell you
much about logs, though, which may be critical.

"Jeff Roughgarden" <jroughgarden@.stanfordalumni.org> wrote in message
news:b8143af9.0407071314.5bcc4ae3@.posting.google.c om...
> Does anyone know how to identify the hottest, most active tables in a
> database?
> We have hundreds of users hitting a PeopleSoft database with hundreds
> of tables. We are I/O bound on our SAN, and are thinking of putting
> the hottest tables on a solid state (RAM) drive for improved
> performance. Problem is: which are the hottest tables? Would like to
> do this based on hard data instead of developer/vendor guesses.
> Any suggestions are much appreciated.