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" <jr**********@stanfordalumni.org> wrote in message
news:b8**************************@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.
