How can I find what tables are loaded in memory ?

[csomberg]

I'm doing some performance reviews and wish to know what tables SQL has
pinned in memory and which ones have are loaded through usage ...

Is there a way ?

Thanks,

Craig

[Gert-Jan Strik]

Not AFAIK.

However, you can use DBCC MEMUSAGE to list the top 20 objects that are
held in memory. So this would be the top list "through usage".

In general, I would be very cautious with pinning tables. There are only
very few situations in which it is useful to pin a table. In general, it
is best to leave it up to SQL-Server to optimize its memory use.

HTH,
Gert-Jan
cs******@dwr.com wrote:

I'm doing some performance reviews and wish to know what tables SQL has
pinned in memory and which ones have are loaded through usage ...

Is there a way ?

Thanks,

Craig

[Erland Sommarskog]

(cs******@dwr.com) writes:

I'm doing some performance reviews and wish to know what tables SQL has
pinned in memory and which ones have are loaded through usage ...

Is there a way ?

SELECT * FROM sysobjects WHERE objectproperty(id, 'TableIsPinned') = 1

I completely share Gert-Jan's recommendation to stay away from DBCC
PINTABLE.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gert-Jan Strik]

Craig,

This query would show the tables that are "marked for pinning".
SQL-Server does not actively load pinned tables into memory. What
pinning will do is retain all the table's pages that were loaded since
startup.

Gert-Jan
Erland Sommarskog wrote:

(cs******@dwr.com) writes:

I'm doing some performance reviews and wish to know what tables SQL has
pinned in memory and which ones have are loaded through usage ...

Is there a way ?

SELECT * FROM sysobjects WHERE objectproperty(id, 'TableIsPinned') = 1

I completely share Gert-Jan's recommendation to stay away from DBCC
PINTABLE.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp