Pinned Tables

Question

Pinned Tables

Nilssond

Hall of Fame

Points: 3285
More actions
August 17, 2005 at 5:18 pm

#167947

Is there any way of determining which tables, if any, have be pinned?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Not sure about a single query to list all pinned tables. Maybe one of the DBCC commands that shows chace usage could help.

To see if individual tables are pinned you can use the OBJECTPROPERTY function to check the TableIsPinned property.

--------------------
Colt 45 - the original point and click interface

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

Piggybacking on Phill again:

SELECT [id], [name], OBJECTPROPERTY([id], 'TableIsPinned') Pinned

FROM sysobjects

WHERE xtype = 'U'

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I think this is more readable.. especially if you have only 1 pinned table out of 3000 .

SELECT id, name, OBJECTPROPERTY(id, 'TableIsPinned') Pinned

FROM dbo.sysobjects

WHERE XType = 'U' AND OBJECTPROPERTY(id, 'TableIsPinned') = 1

ORDER BY Name

Lowell SSC Guru Points: 323518 More actions · Answer 4

just wanted to flesh the background of the question out for some less knowledgable readers:

you can "pin" a table, or mark it so that it's contents are held in memory, rather than handled the way it is normally: cached in memory and then flushed out if not used.

quoting from http://www.sql-server-performance.com/vk_fun_numbers_transactsql.asp

If you can spare a few KiloBytes of memory, you can pin the Numbers table into memory, so that the pages of this table remain in memory, once read into memory. This is okay with smaller tables like the 'Numbers' table, but do not try this with larger tables as that can negatively impact SQL Server's performance. The following command can be used to pin the 'Numbers' table in memory (Also see DBCC PINTABLE in SQL Server Books Online (BOL)):

one of the ways to pin the table is this command:

EXEC sp_tableoption 'SomeTableName', 'pintable', 'true'

and to unpin it it is just

EXEC sp_tableoption 'SomeTableName', 'pintable', 'false'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Thanx for clarifying... good thing this wasn't started in the newbie forum .