SEO

#1 (**permalink**) 02-29-2008, 06:32 AM

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

#2 (**permalink**) 02-29-2008, 06:32 AM

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

csomberg@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

#3 (**permalink**) 02-29-2008, 06:32 AM

(csomberg@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, esquel@sommarskog.se

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

#4 (**permalink**) 02-29-2008, 06:33 AM

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:
>
> (csomberg@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, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How can I find what tables are loaded in memory ?