Pinning in SQLSERVER

[sureshramaswamy]

Is it possible to pin datbase objects such as tables,stored procedures,functions in sqlserver database similar to oracle?If we can can anybody suggest the command to do it in SQLSERVER.

THANKS

[Stephen]

sp_tableoption 'mytable', 'pintable', true
will pin a table.

I don't know of any way to pin other object types

[Markus]

SET @db_id = DB_ID('myDB')
SET @tbl_id = OBJECT_ID('myDB..table1')

DBCC PINTABLE (@db_id, @tbl_id)

[rmiao]

Be careful. If you don't have enough memory on the server, may affect overall performance.

[skhanal]

Pinning is only recommended for small static tables like code tables which are frequently used.

You can monitor the performance of your application after pinning tables you want and see what difference it makes.